Logic to find out items soldy qty

In inventory if u need to find the aging details of sales report u need to refer to cutomer tables

i.e:
 ra_customer_trx_lines_all and ra_customer_trx_all

conditions to filter will be inventory_item_id,organization_id

e.g query:

select
        sum(nvl(quantity_invoiced,0))  + sum(nvl(quantity_Credited,0))  Qty,to_number(ltrim(rtrim(cta.interface_header_attribute10)))
from
        ra_customer_trx_lines_all ctla ,ra_customer_trx_all cta
where
        cta.customer_trx_id =  ctla.customer_trx_id
        and inventory_item_id = 86343
       -- and to_number(ltrim(rtrim(cta.interface_header_attribute10))) = o_id
    --    and cta.trx_date >= sysdate - TDays
group by
        ltrim(rtrim(cta.interface_header_attribute10)),
        inventory_item_id;

Functional flow

Purchasing Responsibility:Purchase Order->Purchase Order
Supplier: *************
Type: Standard Purchase Order
Item: Expense Items
Item No: 10131
Quamtity: 5
Amount 200
Total:1000

Check Shipment tab before saving for ->Receiveing Control:Recepit Routing: Direct delivery

 

In distrubutions Tab can locate the sub-inventory.

Click on Approve Buttom
The status of PO will be Approved

 

Create Receipts:

Purchasing: Receiving->Receipts
In Find Expected receipts window search using PO number:Click on Find button
Tick the checkbox in Lines tab
Fill The sub Inventory Information

 

Hit on Save..Open the header and note the receipt Number

Now In Inventory Responsibility and check on On Hand Quantity

Inventory->On-hand Quantity
 

Search Using receipt number
Check Material transaction in inventory itself
Transaction->Material Transaction
Query the item With date
Now can create Invoice in payables responsibility for the receipt

Fill the Invoice Number,Supplier Info and Match with the receipt

Match with full amount with receipt number and validate the invoice

Click on Actions And Validate the Invoice

For Full payment we have done:-
Matching Receipts : Half Payment
Two Receipts Matching for a single invoice
Create two receipts for a Single PO,
Match the receipts with different invoice numbers.

 

Procure to Pay cycle-For techies Reference

Procure to Pay cycle covers the following steps:

1. Creating Requisition:

This depends on internal good requirement.This will be raised by an employee only. 

• Inter Org  transfer-From one inventory to other inventory
• Raising Purchase order-No stock supplier should provide

Important tables:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL.

Joins can be made using REQUISITION_HEADER_ID and REQUISITION_LINE_ID 

2. Creating Purchase Order

• PO_HEADERS_ALL
• PO_LINES_ALL
• PO_DISTRIBUTIONS_ALL (REQ_HEADER_REFERENCE_NUM in Distributions table is the Requisition number for this PO)
• PO_LINE_LOCATIONS_ALL

The join columns are

header_id and line_id 

3. Creating Receipt

Receipt Tables are:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

4. Creating Invoice in AP
Once the goods is Recieved, We will pay the vendor.Raise an invoice in AP

Invoice Tables:

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Joins made using Invoice_id

Accounting Entries Tables:

AP_ACCOUNTING_EVENTS_ALL

AP_AE_HEADERS_ALL

AP_AE_LINES_ALL

Joins made using Accounting_event_id and ae_header_id

5. Paying the Invoice

Payment Tables:

AP_INVOICE_PAYMENTS_ALL

columns can be joined
invoice_id,accounting_event_id 

AP_PAYMENT_SCHEDULES_ALL:invoice_id

AP_CHECKS_ALL:doc_seq_id is the invoice number

AP_CHECK_FORMATS

AP_BANK_ACCOUNTS_ALL

AP_BANK_BRANCHES

AP_TERMS

6. Transfer, Import and Post to GL

ORACLE PURCHASING

Creating Purchase Orders

Oracle Purchasing provides four types:

Standard purchase order

Contract purchase agreement

Blanket purchase agreement

Planned purchase order



Standard Purchase Order Elements need:

One time purchases


Know details of purchase:

Specific goods or service

Known pricing

Quantity

Delivery schedule

Internal charges

Short-term agreement

Need to encumber the purchase immediately
 
 
When to Use a Contract Purchase Agreement:
 
Contract Purchase Agreement:


Details of goods or services to be purchased are not known

Terms and conditions are specified with your supplier

You reference contract purchase agreements directly on standard purchase order lines


When to Use a Blanket Purchase Agreement:

Long-term agreement:


Effective date and expiration date

Details of goods or services are known:

Pricing

Dates of delivery are not known

Actual purchases occur when a blanket release is issued

When to Use Planned Purchase Orders:

Planned Purchase Order


Dates of delivery tentatively scheduled

Details of services or goods are known

Actual purchases occur when a scheduled release is issued

Purchase Order Flow

Create Requisition:


When the company/person wants to purchase any item a Requisition is created

Step 2: RFQ (Request For Quotation)

Once Requisition is approved then the company/person will Request for Quotation (RFQ) specifying the required item, quantity.... etc and send them to the vendors who can supply the item



Step 3: Quotation



Once the vendor receives the RFQ then he will prepare the Quotation and send it back to the company/person



Step 4: Purchase Order (PO)



After receiving different quotations from different vendors company/person will go through the quotations and approves the quotation that is best suited/reliable for the company/person.

Based on the approved quotation Purchase Order is generated to the vendor. Now the vendor will supply the item.



Step 5: Receipts



Once the Item is shipped the vendor will raise the Receipt to the company/person.



Step 6: Invoice



Based on the receipt Invoice is generated. For this invoice the company/person will make Payments.