, , ,

Canceled Requisitions in the PO Module

You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id “requisition_header_id”
,prha.segment1 “Requisition Number”
,prha.preparer_id “preparer_id”
,TRUNC(prha.creation_date) “creation_date”
,prha.description “description”
,prha.note_to_authorizer “note_to_authorizer”
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code=’CANCEL’
AND pah.object_type_code=’REQUISITION’
AND pah.object_id=prha.requisition_header_id

/by
, , ,

Internal Requisitions has not converted to Internal Sales Order

Requisitions (PO Module) would be 2 types.

1) Purchasing Requisitions (Handled by the PO Module)

2) Internal Requisitions (Handled by the OM Module).

We create the Purchase Orders for the Purchasing Requisitions and Internal Sales Orders will be created for the Internal Requisitions.

Note:- We create Internal Sales Order to transfer the material between the Inventories with-in the Organization.
We can use the following Query to find out all the Internal requisitions are created but not converted to the Internal Sales Orders.

SELECT rqha.segment1 “Requisition Number”
, rqla.line_num “Line Number”
, rqla.requisition_header_id “Requisition Header ID”
, rqla.requisition_line_id “Requisition Line ID”
, rqla.item_id “Inventory item ID”
, rqla.unit_meas_lookup_code “Unit Of Measure”
, rqla.unit_price “Unit Price”
, rqla.quantity “Quantity”
, rqla.quantity_cancelled “Quantity Cancelled”
, rqla.quantity_delivered “Quantity Delivered”
, rqla.cancel_flag “Cancelled”
, rqla.source_type_code “Source Type”
, rqla.source_organization_id “Source Organization ID”
, rqla.destination_organization_id “Destination Organization ID”
, rqha.transferred_to_oe_flag “Transferred to OE Flag”
FROM po_requisition_lines_all rqla
, po_requisition_headers_all rqha
WHERE rqla.requisition_header_id = rqha.requisition_header_id
AND rqla.source_type_code = ‘INVENTORY’
AND rqla.source_organization_id IS NOT NULL
AND NOT EXISTS ( SELECT ‘existing internal order’
FROM oe_order_lines_all line
WHERE line.source_document_line_id =
rqla.requisition_line_id
AND line.source_document_type_id = 10)
ORDER BY rqha.requisition_header_id
, rqla.line_num

/by
, , ,

PO Requisition associated with PO Orders

We Using following URL, we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.

1) Purchasing Requisition

2) Internal Requisition.

Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 “Requisition Number”,
prha.type_lookup_code “Requisition Type”,
pha.segment1 “Purchase Order Number”,
pha.type_lookup_code “Purchase Order Type”
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

/by
, , ,

Purchase Orders Updated on previous day

You can use the following query to get all the Purchasing Orders upadetd on previous day in any Instance.

You can filter-out based on category set or Inventory Organization etc.
I have commented out category_set_id and Organization ID conditions in the where clause.
You can modify as per your requirement.

Note: – This kind of information would be required for the Organization (Management) for the tracking the PO’s created on daily bases.

Note:- There are some values I am getting from attribute columns. This values would vary in your Instance. These values depends on the DFF setup in your Instance.

SELECT mcb.segment1 “GMA”
, mcb.segment2 “Group”
, mcb.segment3 “Dept”
, mcb.segment4 “Cat” 
, poh.segment1 “PO”
, fu.user_name “PO Created by(User_id)”
, fu.description “PO Created by(Name)”
, fu2.user_name “Last Updated By(User id)”
, fu2.description “Last Updated By(Name)”
, vs.vendor_site_code
, vn.vendor_name
, hou1.name
, hrl1.location_code “Ship To”
, poh.authorization_status “PO status”
, msi.segment1 “SKU”
, msi.description “Item Description”
, pll.quantity
, pll.quantity_received
, pll.quantity_cancelled
, fu1.user_name “Cancelled by”
, pll.cancel_reason
, pll.cancel_date
, poh.creation_date
, pll.promised_date
, pll.need_by_date
, poh.last_update_date
,(SELECT DISTINCT rsh.attribute8 
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) “Appointment Number”
,(SELECT DISTINCT rsh.attribute9 
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) “Appointment Date”
,pol.unit_price “Unit Cost”
,(pol.unit_price*pol.quantity) “Ext Cost”
FROM 
po_headers_all poh
, po_lines_all pol
, po_line_locations_all pll
, mtl_system_items_b msi
, inv.mtl_item_categories mic
, inv.mtl_categories_b mcb
, fnd_user fu
, fnd_user fu2
, po_vendors vn
, po_vendor_sites_all vs
, hr_locations_all_tl hrl1
, hr_organization_units hou1
, fnd_user fu1
WHERE TRUNC(poh.last_update_date)=TRUNC(SYSDATE-1)
AND poh.type_lookup_code=’STANDARD’
–AND poh.attribute2 LIKE ‘M%’
AND poh.po_header_id=pol.po_header_id
AND pol.po_line_id=pll.po_line_id
AND pol.po_header_id=pll.po_header_id
AND pol.item_id=msi.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
— AND mic.category_set_id = 1
AND mcb.category_id = mic.category_id
–AND msi.organization_id=22
AND poh.created_by=fu.user_id
AND poh.vendor_id=vn.vendor_id
AND vs.vendor_id = poh.vendor_id
AND vs.vendor_site_id = poh.vendor_site_id
AND hrl1.location_id(+) = poh.ship_to_location_id
AND hrl1.LANGUAGE(+) = USERENV (‘LANG’)
AND hrl1.location_id=hou1.location_id
AND fu1.user_id(+)=pll.cancelled_by
AND poh.last_updated_by=fu2.user_id

/by
, , , ,

R12 – Procure To Pay Cycle in Oracle Apps (P2P)

I tried to explain the steps involved in Procure to Pay Cycle. This is a pure functional Stuff and helps you to understand the navigation steps.
I tried to keep as simple as Possible for clear understanding. The screenshots given below are taken from R12.1.1 apps instance.
Stage 1: Choosing an Item
Let us choose an item to be procured in our example.
Go to Purchasing Responsibility and follow the below navigation to check for the suitable item.


The item picked for our example should be purchasable item as above. Click on tools and choose “Categories” to view the below screen.

Stage 2: Creation of Requisition
Follow the below Navigation to reach Requisition Form.

Create a new Requisition for the item viewed in Stage 1.

Click on Distributions to View the charge Account.

Save and Submit for Approval

Now note down the Requisition number and open the “Requisition Summary Form” to view the status of it. For our Example, Requisition number is: 14855

Stage 3 : Checking the Status of Requisition

Query for the Requisition Number and click Find.

Here for our example purpose, I kept the submitted and approved person has same and hence it shows the status as approved.

To see the approval status, please follow the below navigation.

Stage 4 : Creation of Purchase Order
For creating a Purchase order, let us use the “Autocreate Documents” Form. Follow the below Navigation


Query for the Requisition

Click on Automatic as shown in the above figure to create a Purchase Order

Click on “Create” button to create a Purchase order

View the shipment screen to change the “Match Approval Level” to “2-Way”.

Click the “Receiving Controls” to make sure that the “Routing” is made as “Direct Routing”

Click Save and submit for Approval.


Note down the PO Number.

Stage 5: Creation of Receipts


Query with the Purchase order created in the above stage.

Check the check box near to the lines that are received and click save.

Click the “Header Button” to view the Receipt Number.

Stage 6: Checking the On Hand
Go to any Inventory Responsibility and follow the below Navigation

Query for our Receipt and make sure the Organization is the same as we received.

Below screen will show that our inventory has been increased by 5 quantities.

Stage 7: Check the Material Transactions
Follow the below Navigation to reach “Material Transactions” Form


Query for the item and date as below


Below screen shows the source and transaction Type

Below screen shows you the Serial Numbers of the items received.

Stage 8: Creation of Invoice
Navigate to any Purchasing Responsibility and view à Requests
Submit the below requests by providing the Receipt number as Parameter to create an invoice.

Check the status of the program.

Stage 9: Checking the Invoice
Change to any Payables Responsibility and open the invoices Form.
Query for the Purchase order as below,


Click “Actions” Button then tick the “Validate Check Box” and press “Ok” to validate the invoice

Below screenshot will give you the status of the invoice

Stage 10: Creation of Accounting and Payment
Once invoice got approved, we can “Create Accounting” and “Create Payments” via “Action” Button in the “Invoice Form” as we validated the invoice.

/by