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

collections methods in oracle

Collection Methods:

A variety of methods exist for collections, but not all are relevant for every collection type:

    * EXISTS(n) - Returns TRUE if the specified element exists.
    * COUNT - Returns the number of elements in the collection.
    * LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
    * FIRST - Returns the index of the first element in the collection.
    * LAST - Returns the index of the last element in the collection.
    * PRIOR(n) - Returns the index of the element prior to the specified element.
    * NEXT(n) - Returns the index of the next element after the specified element.
    * EXTEND - Appends a single null element to the collection.
    * EXTEND(n) - Appends n null elements to the collection.
    * EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
    * TRIM - Removes a single element from the end of the collection.
    * TRIM(n) - Removes n elements from the end of the collection.
    * DELETE - Removess all elements from the collection.
    * DELETE(n) - Removes element n from the collection.
    * DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

Ur's

AmarAlam

VARRAY in Oracle

==================

A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database,
 but unlike nested tables individual elements cannot be deleted so they remain dense:


sql>SET SERVEROUTPUT ON SIZE 1000000
sql>DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
 
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

Ur's

AmarAlam

Nested Table in Oracle

Nested table collections are an extension of the index-by tables.
The main difference between the two is that nested tables can be stored in a database column but index-by tables
cannot. In addition some DML operations are possible on nested tables when they are stored in the database.
During creation the collection must be dense, having consecutive subscripts for the elements.
Once created elements can be deleted using the DELETE method to make the collection sparse.
The NEXT method overcomes the problems of traversing sparse collections.

sql>SET SERVEROUTPUT ON SIZE 1000000
sql>DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
 
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
 
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/


Ur's
AmarAlam

PLSQL Index-By Tables:

===============
The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds,
 allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive.
The collection is extended by assigning values to an element using an index value that does not currently exist.

sql>SET SERVEROUTPUT ON SIZE 1000000
sql>DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
 
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
 
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
 
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/


Ur's
AmarAlam

How to cancel running concurrent request

To cancel running concurrent request
———————————-
sqlplus>

UPDATE fnd_concurrent_requests
SET PHASE_CODE=’C’
,STATUS_CODE=’E’
WHERE request_id=’3477766′;

sqlplus>

UPDATE fnd_concurrent_requests
SET  PHASE_CODE=’C’
,STATUS_CODE=’E’
WHERE  CONCURRENT_PROGRAM_ID=’20393′;

(when large number of request are pending …kill all thiose with concurrent_program_id)

Hope this will help you.


Ur's
AmarAlam

Oracle Apps Interview Questions

What is context flexfield?
How to move report from one instance to another?
How to move data definition from one instance to another?
How to get last created record from a table?
How to get employee info group by and order by designation?
How to move in file path in SQL*Loader from one instance to another?
How to get line number in XML publisher output?
What are the major diff in 11i and R12? In AP invoice
What is SLA concept?
What is TCA concept?
How to migrate data from 9i database tables to 11g database tables?
How to use XML publisher with out RDF?
What is MOAC concept in R12?
What is Diff between AP invoice and AR invoice?


Ur's
AmarAlam

Generating XML Tags Using SQL Query

We have a standard oracle package to generate the XML Tags.
We just need to pass the SQL statement as parameter to the package.

Below is an example, which prints Todays date as XML Tag

SELECT   DBMS_XMLGEN.getxml ('select sysdate today_date from dual') xml
  FROM   DUAL;

*****************************************************************
There is another way to get the same output using some other functions.

Let me start off with a simple query to print TODAY_DATE tag only

SELECT   XMLELEMENT ("TODAY_DATE", SYSDATE) FROM DUAL;

Below is the output for the above statement

2011-11-21

Now, Let me change the date format as DD-MON-YY

SELECT   XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY'))
FROM DUAL;

Below is the output for the above statement

21-NOV-11

Now, i will modify the query to get same output as we got using standard package

SELECT   ''
         || XMLELEMENT (
               "ROWSET",
               XMLELEMENT (
                  "ROW",
                  XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY'))
               )
            )
  FROM   DUAL;

Below is the output for the above statement, which is same as what we got using standard package




21-NOV-11

*****************************************************************************


Ur's
AmarAlam

Creating Purchase Order Requisitions

Below is the example to create a requisition.

We just need to insert data into Interface table and then we need to call standard oracle import program.

In below example, i have created a Internal Requisition.

************************************************************************************************
DECLARE
   l_request_id   NUMBER;
BEGIN
   INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (interface_source_code,
                                              source_type_code,
                                              requisition_type,
                                              destination_type_code,
                                              item_id,
                                              item_description,
                                              quantity,
                                              authorization_status,
                                              preparer_id,
                                              autosource_flag,
                                              uom_code,
                                              destination_organization_id,
                                              deliver_to_location_id,
                                              deliver_to_requestor_id,
                                              need_by_date,
                                              gl_date,
                                              charge_account_id,
                                              org_id,
                                              suggested_vendor_id,
                                              suggested_vendor_site_id,
                                              unit_price,
                                              creation_date,
                                              created_by,
                                              last_update_date,
                                              last_updated_by,
                                              batch_id
                                              )
     VALUES   ('INV',
               'INVENTORY',
               'INTERNAL',
               'INVENTORY',
               1831768,
               'leadsets',
               50,
               'APPROVED',
               48880,
               'P',
               'EA',
               2609,
               75018,
               48880,
               SYSDATE,
               SYSDATE,
               237125,
               2592,
               1058,
               607,
               150,
               SYSDATE,
               59944,
               SYSDATE,
               59944,
               454
               );

   COMMIT;

   l_request_id :=
      fnd_request.submit_request (application   => 'PO',
                                  program       => 'REQIMPORT',
                                  argument1     => 'INV',
                                  argument2     => '454',
                                  argument3     => 'ALL',
                                  argument4     => '',
                                  argument5     => '',
                                  argument6     => 'N');
   COMMIT;
   DBMS_OUTPUT.put_line ('request_id - ' || l_request_id);
END;
/


Ur's
AmarAlam

Order to Cash Cycle - Tables get Affected @ Each Step

1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'


oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'


2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'


oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'


wsh_delivery_details.released_status = 'R' (ready to release)


wsh_delivery_assignments.delivery_id = BLANK


3) Reservation
------------------------------------
mtl_demand


mtl_reservations


4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'


wsh_delivery_assignments.delivery_id gets assigned


wsh_delivery_details.released_status = 'S' (submitted for release)


mtl_txn_request_headers


mtl_txn_request_lines


mtl_material_transactions_temp


5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'


mtl_material_transactions


wsh_delivery_details.released_status = 'Y' (Released)


mtl_onhand_quantities


6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries


oe_order_lines_all.flow_status_code = 'SHIPPED'


wsh_delivery_details.released_status = 'C' (Shipped)


wsh_serial_numbers


data will be deleted from mtl_demand and mtl_reservations


item qty gets deducted from mtl_onhand_quantities


7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to


ra_interface_lines_all


Auto invoice program picks up records from interface table and insert them into


ra_customer_trx_all (trx_number is invoice number)


ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)


8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'


9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'


oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'


10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL


11) Transfer to General Ledger
------------------------------------
GL_INTERFACE


12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES


13) Posting
------------------------------------
GL_BALANCES


Ur's
AmarAlam

Accounts Payable Interview Questions In R12

1.Explain about Accounts Payable. 
Ans)The Accounts Payable application component records and manages accounting data for all
vendors. It is also an integral part of the purchasing system: Deliveries and invoices are
managed according to vendors. The system automatically triggers postings in response to the
operative transactions. In the same way, the system supplies the Cash Management application
component with figures from invoices in order to optimize liquidity planning.


2.What is the meaning of invoice?
Ans)An invoice or bill is a commercial document issued by a seller to the buyer, indicating the products, quantities, and agreed prices for products or services the seller has provided the buyer. An invoice indicates the buyer must pay the seller, according to the payment terms.

In the rental industry, an invoice must include a specific reference to the duration of the time being billed, so rather than quantity, price and discount the invoicing amount is based on quantity, price, discount and duration. Generally speaking each line of a rental invoice will refer to the actual hours, days, weeks, months etc being billed.


3) Can you give a sample Process Flow for Procure to Pay Cycle?

Ans) Process flow for Procure to pay will go through two departments
(Commercial & Finance)
Procure - Commercial Department The following steps invovle to prcure any item
1. Received Requsition from concern Department
2. Request for Quotation from Suppliers at least three
3. Finalize the best Quotation by keeping in mind about our companies standard
4. Check the Budget for the same
5. Negociate with supplier for more economic pricing and finalize the payment terms
6. Process the PO and forward to the supplier to supply the goods and services

Pay Cycle - Finance Department 
The following steps need to be fulfil
1. Invoice should be match with PO
2. Invoice should has all the supporting documents such as PO copy,Delivery note duly signed by reciever (our staff who authorized to received goods / store keeper)
3. If the invoice is for services then it should be forwarded to the concern department head or project manager for his confirmation of work done and his approval
4. Even if it not the services invoice, it should forwarded to the concern person's approval who request the PO for the same
5. Finance can reject the invoice if it is not budgeted and ask for the reasons.
6. After receiving all the confirmation and approvals from the concern department heads the invoice will be update in to the accounting system first in order to avoid any duplication of Invoice and PO (it shown on accounting package if the invoice is duplicate if not, altelast it tells you if the PO already used or cancel)
7. Finance approved the invoice and process the payment base on payment terms with the supplier.


4)What are the journals entries in Procure to Pay Cycle.
Ans)

Description                                                                             DR                                  CR

A) Po creation                                                                    No Entry                          No Entry

B)  While Receiving the goods                                       Material Receiving            Ap Accurval

C) While Inspection                                                        No Entry                            No Entry

D) While Trans ford the good to Inventory               Inv Org Material              Material Receiving
                                                                                     Purchase price Varience

F) While Po Is Matching to Invoices                         Ap Accurval                        Liability


G) While Making the Payment                                 Liability                               Cash Clearing


 H) Ofter Reconciliation                                           Cash Clearing                     Cash



 I) Final Entry                                                            Inv Org Material                Cash



5)What is the difference between EFT & Wire?
Ans)EFT and WIRE are the most popular form of electronic payment method. EFT stands for electronic fund transfer and it is one of the fastest mode of electronic payment after WIRE. EFT is a batch oriented mechanism for transfering funds from one bank to another because of which clearing & settlement takes around 2 to 4 days. On the other hand, WIRE is a RTGS i.e. real time gross settlement system of making the fund transfer on real time and gross basis. Clearing and settlement happens on the same day. WIRE is more expensive and faster than EFT.

6) WHAT IS MEANT BY DISTRIBUTION SETS:

 Ans)You can use a Distribution Set to automatically enter distributions for an invoice when you are not matching it to a purchase order. For example, you can create for an advertising supplier a Distribution Set that allocates advertising expense on an invoice to four advertising departments.
You can assign a default Distribution Set to a supplier site so Payables will use it for every invoice you enter for that supplier site. If you do not assign a default Distribution Set to a supplier site, you can always assign a Distribution Set to an invoice when you enter it.

Use Full Distribution Sets to create distributions with set percentage amounts, or use Skeleton Distribution Sets to create distributions with no set distribution amounts. For example, a Full Distribution Set for a rent invoice assigns 70% of the invoice amount to the Sales facility expense account and 30% to the Administration facility expense account. A Skeleton Distribution Set for the same invoice would create one distribution for the Sales facility expense account and one distribution for the Administration facility expense account, leaving the amounts zero. You could then enter amounts during invoice entry depending on variables such as that month's headcount for each group.

7)What is the meaning of GRN? 
Ans) Goods Receipt Note Means It Proves That Material Is Delivered At Stores Department. Grn Is The Base Document And Important Documents For Proof Of Receipt Of Material At Ware House.This Can Be Prepared By Stores Department And Approved By Plant Head. Grn Contains Ordered Qty,Received Qty And Accepted Qty. Bill Will Be Passed Based The Grn Note. Once The Grn Is Prepared Automatically Inventory Will Be Updated And Accordingly Payment Will Be Released To The Vendor.

GRN contains the following details.

1.Ordered quantity .
2.Received Quantity.
3.Defective quantity in received quantity .
4.Quality standards details.

8) How does the payment mechanism work?
Ans) The open items of an account can only be cleared once you post an identical offsetting amount to the account. In other words, the balance of the items assigned to each other must equal zero.During clearing, the system enters a clearing document number and the clearing date in these items. In this way, invoices in a vendor account are indicated as paid, and items in a bank clearing account are indicated as cleared.

You generally use the payment program to clear invoices. Manual clearing of open items is therefore not usually necessary. However, you will sometimes have to clear items manually if, for example, you receive a refund from your vendor or you have set up a direct debit procedure.


9) Difference between interface tables and base tables?
Ans)   The difference between the interface and base tables is as below

Interface table: is the table where the data gets validated before data get posted to the base tables. There are many interfaces which are seeded with Oracle. You can consider as the entry point of the data, and the interface checks the sanity of data.

Base tables: As told earlier once the data is validated will get updated in the base tables, and is considered as the data which is in the base table is accurate and used in many ways. (Reporting..etc..)


The base tables in AP are as follows:

1) ap_invoices_all

2) ap_invoice_payments_all

3) ap_invoice_distibutions_All

4) ap_payment_schdules

5) ap_payment_dustributions_all

6) ap_checks_all

7) ap_accounting_events_all

8) ap_bank_accounts_all

9) ap_bank_accounts_uses_all
 

10) What is the process of creating an Invoices and transferring it to GL?
Ans)
 1. create batch
2. create invoice
3. create distribution
4. validate the invoice
5. actions -à approve
6. if individual create accounting click ok
7. If batch go to batch create accounting.
8. Create accounting hits Payable Accounting(Transfer) ??Program which will create accounting.
9. Run Transfer to GL Concurrent Program
10. Journal Import
11. Post journals
12. Hits balances.

11) How do u Transfer from AP to GL? 
Ans)“Payables transfer to GL program” is used to transfer from AP to GL.




12) How many types of invoices are there in AP.
Ans)
1. Standarad invoice
2. Debit Memo
3. Credit Memo
4. Mixed Invoice
5. Retain age Invoice
6. Transportation invoice
7. Prepayment invoice
8. Expenses Report Invoice
9. Payment Request Invoice
10. Po default

13) How many types of purchase order types/agreements are there?

A) Standard Purchase Order: You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions. If you use encumbrance accounting, the purchase order may be encumbered since the required information is known

B) Planned PO : A planned purchase order is a long-term agreement committing to buy it
items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities and estimated cost.
EX: Buying goods for Christmas from a specific dealer.

C) Contract PO : You create contract purchase agreement with your supplier to agree on specific terms and conditions without indicating the goods and services that you will be purchasing i.e. for $ amount you must supply this much quantity. You can later issue standard PO referencing your contracts and you can encumber these purchase orders if you use encumbrance accounting.

D) Blanket PO : You create blanket purchase agreements when you know the detail of goods or services you plan to buy from a specific supplier in a period , but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
A Blanket Purchase Agreement is a sort of contract between the you and ur supplier about the price at which you will purchase the items from the supplier in future. Here you enter the price of the item not the quantity of the items. When you create the release you enter the quantity of the items. The price is not updatable in the release. The quantity * price makes the Released Amount. Now suppose your contract with your supplier is such that you can only purchase the items worth a fixed amount against the contract.


14.Payment Method:


A funds disbursement payment method is a medium by which the first party payer, or deploying company, makes a payment to a third party payee, such as a supplier. You can use a payment method to pay one or more suppliers. Oracle Payments supports several payment methods for funds disbursement, including the following:

Check
Electronic
wire
Clearing
Check:

You can pay with a manual payment, a Quick payment, or in a payment batch.


Electronic:

Electronic An electronic funds transfer to the bank of a supplier.You create electronic payments either through the e- Commerce Gateway, or by delivering a payment batch file to your bank. For both methods, Payable's creates a file during payment batch creation. If you are using the e-Commerce Gateway to create the file of payments, an EDI translator is required to create the EDI Formatted file prior to delivering it to your bank.For electronic funds transfers, the file is formatted and delivered to your ap.out directory for delivery to your bank.

Wire:

Wire Funds transfer initiated be contacting the bank and requesting wire payment to the bank of a suplier.A payment method where you pay invoices outside of Payables by notifying your bank that you want to debit your account and credit your supplier’s account with appropriate funds. You provide your bank with your supplier’s bank information, and your bank sends you confirmation of your transaction. Your supplier’s bank sends your supplier confirmation of the payment. You then record the transaction manually.

Clearing:

Clearing Payment for invoices transferred from another entity within the company without creating a payment document.Payment method you use to account for inter company expenses when you do not actually disburse funds through banks. You do not generate a payment document with the Clearing payment method. When you enter the invoice, you enter Clearing for the payment method.You can record a Clearing payment using a Manual type payment only.

15.What id recurring invoices? What are AP setup steps? 
 
Ans) some times suppliers would not be sending any invoices. but still the payment have to made to home: rent, lease rentals. in this situation we have to create invoice every period wise. For that purpose we have to create one recurring invoice template. Template means with one master copy creating the multiple invoices is called template. Here we are creating the one invoice master copy is formally known as recurring invoice or recurring invoice template.

 SET UP:
 1)we have to create one special calendar
2)we have to create one full distribution set
3)we have to enter payment terms in the recurring invoice window
4)enter the template no, first invoice amount, special invoice amounts


Ur's
AmarAlam

Difference between Draft, Final, etc in Create Accounting / Online Accounting

Online Create Accounting:

1. Create Draft Accounting:

Draft will create journal entries, which are not final, which means they are not ready to be transferred to GL.

You can see the accounting in xla_ae_headers and xla_ae_lines. But the xla_ae_headers.accounting_entry_status_code is D and also the xla_events.process_status_code is D and event_status_code is U.

You can run create accounting on this transaction again and again, which will delete the old journal entries and create new ones.

You can't transfer these journal entries to GL.

2.Create Final Accounting:

Final will create journal entries, which can be transferred to GL.

You can see the accounting in xla_ae_headers and xla_ae_lines. But the xla_ae_headers.accounting_entry_status_code is F and also the xla_events.process_status_code is P and event_status_code is P.

Once it is finally accounted you can't run create accounting on the particular transaction (specifically on that event).

You can transfer them to GL using Transfer Journal Entries to GL program.

3. Create Final Accounting Post To GL:

Final Post will create journal entries in final mode, transfer them to GL and post them.

You can see the accounting in xla_ae_headers and xla_ae_lines. But the xla_ae_headers.accounting_entry_status_code is F and also the xla_events.process_status_code is P and event_status_code is P.

Once it is finally accounted you can't run create accounting on the particular transaction (specifically on that event).

It will transfer the journal entries to GL using Journal Import and you can find the data in gl_je_headers and gl_je_lines.
Now the xla_ae_headers.transfer_status_code is Y.
And also it will post to gl_balances (gl_je_headers.status is P).

Create Accounting (concurrent program): (For more entities)

1. Accounting Mode: Draft

 It is same as Draft online accounting.

2. Accounting Mode: Final, Transfer to GL: No

It is same as Final online accounting.

3. Accounting Mode: Final, Transfer to GL: Yes, Post to GL: No

 It will create journal entries in final mode, transfer them to GL.

You can see the accounting in xla_ae_headers and xla_ae_lines. But the xla_ae_headers.accounting_entry_status_code is F and also the xla_events.process_status_code is P and event_status_code is P.

Once it is finally accounted you can't run create accounting on the particular transaction (specifically on that event).

It will transfer the journal entries to GL using Journal Import and you can find the data in gl_je_headers and gl_je_lines.
Now the xla_ae_headers.transfer_status_code is Y and gl_je_headers.status is U.

4. Accounting Mode: Final, Transfer to GL: Yes, Post to GL: Yes

 It is same as Final Post online accounting.

Main differences:
1. Report Output
2. Bulk Accounting


Ur's
AmarAlam