PURCHASING (PO USEFUL INFORMATION)
/* PO Notes
The purchasing deals with buying the material from the supplier and
for this we use the purchase order. The purchase order should not be
confused with the sales order. A purchase order is one which you place
to a supplier for the goods while the sales order is the one which the
customer places for the products that you sell to the customer.So once
we place the purchase order, the supplier will provide the goods for us
and he also provides the invoice which goes to the Accounts payable system.
Now we have also seen in the AP system, this
invoice can be matched against the purchase order.
Now in general, in a typical company, a purchase order is not raised
right away.There is a process where a requisition is first raised and
in response to that requisition, a purchase order is created. For ex
in a PO system,an employee first raises a requisition for, say, a
inkjet printers, which goes thru the standard workflow process and it
gets approved by the manager etc. So in order to raise a requisition,
the user must be defined as a EMPLOYEE.(This can be done in setup,
personnel ,employees)
*/
/* REQUISITIONS : Once the requisition goes thru, the user can raise
a purchase order.
Now for the purchase order to be raised the user must be defined as BUYER.(This
can be done in setup, personnel, buyer)
So let's go thru the route of creating the purchase order by initially creating a requisition.
This is quite similar to creating a sales order. Here we can request an item which
can be an asset, could be an expense or an item which eventually goes to the
inventory etc. Let us see the difference, an asset is one which is like a chair
or a keyboard for your computer which we are using. An expense is like going
for a dinner and an item can be requested which eventually goes into the companys
inventory. Here we mention the supplier details,organization etc information and
then create a requisition. Get the requisition from the base header and detail tables as
*/
SELECT
requisition_header_id,
authorization_status,
type_lookup_code,
wf_item_type,
wf_item_key,
created_by,
preparer_id
FROM
po_requisition_headers_all
WHERE
created_by= (select user_id from fnd_user where user_name ='LAJAGARL')
--Get the Requisition details.
SELECT
requisition_header_id,
requisition_line_id,
source_type_code,
item_id,
destination_type_code,
org_id,
item_description,
destination_organization_id
FROM
po_requisition_lines_all
WHERE
creation_date = (select max(creation_date) from po_requisition_lines_all)
-- We can get to know the status of that PO item (as we can get it in OM)
from the workflow tables using the query..
SELECT item_type, item_key, activity_status, begin_date, end_date,
activity_name, process_activity
FROM wf_item_activity_statuses, wf_process_activities
WHERE item_key = '1420741-14412' -- '57516224'
AND item_type = 'REQAPPRV'
AND process_activity = instance_id
ORDER BY begin_date, end_date
/*
Requisition Import :
In addition to creating the requisitions online, they can also be imported
as as batch. Once imported they go into the table po_requisitions_interface_all.
Once imported, we need to run the "Requisition Import" from the
"GL Corporate Purchasing" responsibility which then populates the table
"po_requisition_headers_all" and "po_requisition_lines_all" tables. While
running the requisition import, the concurrent program asks for the source
(from where to be imported). This source value is coming from the above
interface table.
*/
-- Vendor Information can be found from this query.
select vendor_id, vendor_name, segment1,vendor_type_lookup_code,
accts_pay_code_combination_id ccid
from po_vendors
where vendor_name like 'SAFEGUARD%'
/*PURCHASING : The following queries store the information about the
purchase orders.
When a purchase order is created/approved, there are no accounting impact
on GL. The only account that is specified at the PO Level is the
PO Charge Account and that is an accrual account. What this means is the
when you receive against this PO(at periodend or at receipt), automatically the accrual
lines are generated and transferred to GL i.e these lines hit the accrual account.When we
say automatically,means seamlessly or online, we dont need to run any specific program to push
those lines to GL.
Accrue at Period End means that when a receipt is saved, the accrual transactions are NOT
immediately recorded and sent to the general ledger; instead, the accounting entries are generated
and sent at the end of the month by running the Receipt Accruals - Period-End Process.
End-of the month accruals are only available for expense purchases, i.e period end accruals and
upon-receipt accruals are available for expense items, while inventory items are always accrued
immediately.
*/
-- The default ship-to, bill-to location is defaulting from Financial Options.
select po_header_id, type_lookup_code,segment1 po_number,vendor_id,
vendor_site_id,authorization_status,wf_item_type
wf_item_key,creation_date, last_update_date
from po_headers_all
where segment1 = '10000080'
select po_header_id,po_line_id,item_id, category_id, item_description,
list_price_per_unit,unit_price, quantity, request_id
from po_lines_all
where po_header_id =24271
/*Typically the purchase order header information will go into po_headers_all,
The purchase order lines information will go into po_lines_all
the purchase order shipment information will go into po_line_locations_all
(i.e the time and location, need_by , the location where this needs to be shipped).
These locations are usually the inventory locations.
the purchase order distribution information will go into po_distributions_all (code_combination etc).
So each PO will have headers,lines,shipments and distributions. Another
caveat here in creating the PO shipments is as follows. While creating
shipments, we specify the ship to org and the ship to location. Here the
default value is coming from the item orgs,i.e if the item and its revision
is assigned to say org V1, then that org only comes. Here you can specify
master org as well, but you cannot receive in the master org. Hence it is
important that you specify an org which is not item master org.
*/
select po_header_id, po_line_id, po_distribution_id,code_combination_id
,destination_type_code, destination_subinventory, request_id
from po_distributions_all
where po_header_id = 24271
/* PO APPROVAL PROCESS : Having created the PO, the next step is the PO Approval Process.
Purchasing and HR Stuff.
If HR is not completely installed. We can define the employees and positions
in Oracle purchasing,
However if HR is installed then the applications will force you to define
the employees ,supervisors and positions in the HR module.
In Oracle HR,People are different from the employees. Not all the people defined
are employees. Employee information goes into the table "hr_employees" and
the people information goes into the "per_all_people_f" table,but an employee
will be in both the hr_employees and per_all_people_f tables. Typically if
there are any contacts for an employee ,then they will be entered as the people.
-- The below query can be tried to see if the HR product is installed or not.
Interesting all the HR related code is present in the applicaction
--user PER and not HR
select INIT_FUNCTION_NAME from FND_PRODUCT_INITIALIZATION
where APPLICATION_SHORT_NAME = 'PER'
and exists (select 1 from FND_PRODUCT_INIT_CONDITION C
where C.APPLICATION_SHORT_NAME = 'PER'
and C.RE_INIT_CONDITION in ('USER','RESP','APPL','NLS'))
Normally, whenever a purchase order is raised ,say,by an employee. then it will
be routed thru the next in the hierarchy. And there are two types of hierarchies
in the Oracle purchasing module. One is position approval hierarchy and another
employee/supervisor hierarchy. This setting can be defined using the path
setup => Organization => Financials Options screen. => Human Resources tab.
If the "Use Approval Hierarchies" check box is checked, then it means that the
position approval hierarchy is used.
If the "Use Approval Hierarchies" check box is unchecked, then it means that the
employee/supervisor hierarchy is used.
Let us briefly dwell on what is the difference between these two hierarchies.
In the case of Position Approval Hierarchy, firstly a hierarchy of all the
positions in the company are defined. for ex, Manager, Senior Manager, Executive director
,Vice President,etc; all these positions are first defined. So if a manager
raises a PO, then that document is routed up the hierarchy i.e it is routed
to the senior manager.
In the case of Employee/Supervisor Hierarchy, while we are defining an
employee ,say ,Smith, then his manager ,say, David, is also specified.
And a document is routed to that manager David.
Incidentally in Oracle purchasing, there are lot of document types defined.
That is ,a requisition(Internal or Purchase), planned purchase order,
standard purchase order.etc. We can see these types from the path
setup => Purchasing => Document Types.
*/
/* Requisition/PO Approval Process
-- For PO Approvals on Position based , do the following items on checklist.
If the requester needs to approve the requisition that he has created, then
ensure that the document type in
setup=> purchasing => document types => Owner can approve etc.
Firstly the requester should be an employee,make sure that this is defined
as the HR employee. Go to the HR, and find out the position for this
particular employee(requester) for ex, ENGINEER III.
Now for this POSITION, Go to
Setup => Approvals => Approvals Assignment.
and assign the appropriate document type, and approval group
(for ex DIRECTOR APPROVAL GROUP)
Approval Group : Approval Group basically ensures what is the approval limit. Importantly check the PO dist account and ensure that, that particular account is specified in the account range in the Approval group.
Document Type : whether that position can approve this document type like
standard,planned PO's.
Make sure that this approval group has the sufficient amount as the approval limit using the menu
Setup => Approvals => Approval Groups.
-- For approval of Employee /supervisor based (JOB)
If you are not using the position approval hierarchy ,that means you are using employee/supervisor
hieararchy,then approval assignments and approval groups need to be defined for that particular JOB.
And follow the same above procedure.
Hence to summarize, if the positional approval hierarchy is used then the
approval groups and approval assignment should be created for that position.
If the employee/supervisor hierarchy is used, then the approval groups and
approval assignment should be created for that Job.
Now in this case, we are assuming a simple case of owner approving the
requistion. So in this case, once the preparer creates the requisition and
presses the approve button, the document automatically gets approved , as
long as every thing is fine.
Howerver let us take another case, where in the once the document is submitted
for approval based on the following setting, it is routed to the next
person in the hierarchy.
This setting can be defined using the path
setup => Organization => Financials Options screen. => Human Resources tab.
Now let us say if the system is using the employee/supervisor hierarchy, then the
supervisor will login to the system and from the worklist notifications, he can
find a requisitions pending to approve. which he can pick and approve.
Now once the requisition stuff is done, we can do an Autocreate and create
a purchase order from the requisition. Once the purchase order is created,
we can use the following query to ensure that it works and then the PO is approved.
*/
/*Usually for our testing purposes, the purchase order we can create one, and
for approval we can use the following query to make it work.Once the po is
approved, we will be able to receive against that po.*/
SELECT ph.segment1, ph.po_header_id, ph.type_lookup_code,
ph.displayed_field TYPE, ph.vendor_name, ph.vendor_id, ph.full_name,
'Vendor'
FROM po_pos_val_v ph
WHERE (segment1 LIKE '1100024688')
AND ( EXISTS (
SELECT 'Valid PO Shipments'
FROM po_line_locations poll
WHERE ph.po_header_id = poll.po_header_id
AND NVL (poll.approved_flag, 'N') = 'Y'
AND NVL (poll.cancel_flag, 'N') = 'N'
AND poll.shipment_type IN
('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.ship_to_organization_id = :2
)
AND ROWNUM >= 1
)
/* RECEIVING :
So once the PO is approved, it is now ready for receiving the goods from the Supplier.
Understanding the Receipts form : When you first want to receive any thing and open the
receipts form, firstly it show the "Find expected receipts". This form had various field
like PO, receipt number, receiving location etc. Now what we have to understand from this form
is that,if we want to receive items corresponding to a PO, then we have to enter that PO number
and then press "Find" button. Now if we want receive all the items that are supposed to be shipped
at a receiving location, then we enter that receiving location and press "Find" button,
which in turn will show all the purchase orders that ship item to that specific receiving location.
What this means is that the form checks if it has been already received, and if it is, then it
will say no records exist otherwise, we can create a new receipt record.
IF YOU HAVE ALREADY RECEIVED, THEN THIS FORM DOES NOT SHOW ANY RECORDS AND HENCE IT
IS CALLED FIND EXPECTED RECEIPTS. AND ONCE RECEIVED ,THEN YOU CAN GO TO
"RECEIVING TRANSACTIONS SUMMARY FORM" and check the transactions.
*/
/* While recieving checklist :
1. Just like GL,AR ,Inventory has its own periods as well & we can open period from
(setup => costs => cost periods .And the data goes into the org_acct_periods??)
Oracle Inventory --> Accounting Close Cycle --> Inventory Accounting Periods --> Change status
In inventory only one period at any time, while in GL or Purchasing any periods can be open.
This is the most trickiest part. Actually in inventory if you need to open any period, then the
previous period needs to be already opened. That is if the previous period is not open yet,
just open and close it.
2. Also ensure that the purchasing periods are open, however in this case more than one periods can be open
simultaneously.
Setup => Financials => Accounting => Control Purchasing Periods.
3. One important thing is ,we talked about the ship to org id above. So when we open the receipts
form, make sure you pick only that organization that you mentioned in the PO, i.e you are receiving
only in that org. That is from the inventory ,pull this item up and click on org assignments and
assign this item to that particular receiving organization and save it.This basically means that item is
transactable in that particular organization.
4. Another important point is the Item revision which is is also related to the organization
assignment. That is once we define an item in master org and assign them to different orgs.
And also if we go to the org assignments and click on the "org attributes" button, we will find that
we can define a revision for this item.
Hence when you are entering a PO using the PO from,just ensure that you are giving the right
revision for this item,otherwise we may not be able to select the correct ship to organization id
in the shipments form.
5. You cannot receive in a master organization and hence you will not see the master organization
in the LOV when receipt'ing.*/
/* The following queries give the information about the RECEIPTS we make for the purchase orders created above.
Once we enter a receiving txn,and the rec txn processor processes it, there will be an entry in this.
rcv_transactions stores the historical information about receiving transactions that you have performed.
There will not be any updates on this table. */
select transaction_id,transaction_type,quantity,interface_source_code, source_document_code,
destination_type_code, po_header_id, po_line_id, po_distribution_id,
routing_header_id ,subinventory,shipment_header_id,shipment_line_id,creation_date
from rcv_transactions
where po_header_id = 434
--and creation_date >= '15-OCT-2004'
-- The key thing is that select the check box and save it.
/* Any receipts go into rcv_shipment_headers and lines. the column receipt_source_code
indicates whether the material has come as part of internal order or from a vendor
(or supplier). If it is an internal order ie. inter-org transfer, then the
organization_id column indicates from which org it has come.
It is important to note that the rcv_transactions talk about 2 transactions
for each receipt ie.
one for receiving the item from the vendor to the receiving dept
another for receiving the item from the receiving dept to Inventory.
However in the case of shipments, we find only one entry i.e from the vendor
and you mention the location of shipment.
*/
-- Get the shipment header id and shipment line id from the above query.
select receipt_source_code,bill_of_lading,vendor_id,vendor_site_id,
receipt_num,ship_to_org_id,organization_id
from rcv_shipment_headers
where shipment_header_id = 132612
select last_update_date,shipment_header_id,shipment_line_id,quantity_shipped,quantity_received,
shipment_line_status_code,source_document_code,item_description,item_id,
po_header_id,po_line_id,po_distribution_id,po_line_location_id,routing_header_id
,destination_type_code,to_subinventory,deliver_to_location_id,
charge_account_id
from rcv_shipment_lines
where shipment_header_id = 132612
Hence you are receive against a PO, Requisition, a RMA.
/* Just a quick word. As we mentioned before, when we receive automatically the two GL lines are
created all the way in gl_interface table,which can be checked from the following query.
These two lines correspond to two GL accounts and they are receiving material account
and AP accrual account. I have seen some times both the accounts are given as same
and in that case, the two lines will look exactly identical i.e with the same code
combination id.
Receiving options => Receiving Inventory Account
Purchasing options => AP Accrual Account.
HenceSo the only thing that goes from PO to GL is the accruals.( all these liabilities
,expenses will go from AP). For more info, look at the AP queries file.
*/
select * from gl_interface
/* The two other inventory tables which get updated are mtl_material_transactions
and mtl_transaction_accounts.
Here we can give the rcv_transaction_id that we obtain from the above query
(for DELIVER record, because that is the one which fills the inventory) and get
that material transaction.*/
select transaction_id,inventory_item_id,organization_id, subinventory_code,rcv_transaction_id,
primary_quantity,actual_cost,source_code,cost_category_id, owning_organization_id,creation_date
,transaction_type_id
from mtl_material_transactions
where inventory_item_id
= (select inventory_item_id from mtl_system_items_b
where segment1='0000799102923'
and organization_id = 82)
and creation_date >='18-OCT-2004'
--and rcv_transaction_id = 582138
-- Now since there is an increment in the inventory i.e the on-hand balance should
--increase for that inventory item.
SELECT
inventory_item_id,
organization_id org,
transaction_quantity,
primary_transaction_quantity qty,
subinventory_code subinv,
cost_group_id ,
owning_organization_id,
planning_organization_id,
creation_date
FROM
mtl_onhand_quantities_detail
WHERE
inventory_item_id = (select inventory_item_id from mtl_system_items_b
where segment1='0000799102923' and
organization_id = 82)
/* PROCURE TO PAY FLOW : So having gone thru the Purchasing ,Receiving ,here is the typical flow for P2P
Procure to Pay Flow
Try to put this into the equation like
PO ==> RCVT ==> INV ==> PMT
Keeping that in mind, we can have several reports.
If there is a PO, and there is a receipt, then it is normal case.
If there is no PO, and there is receipt, then we can get it from the "Unordered Receipts" report
If there is a RCVT, and then there is an invoice => Normal Case
If there is a RCVT, and then there is NO invoice => Uninvoiced Receipts Report
*/
You specify which supplier site is participating in the Pay on Receipt feature
by setting on the self billing region.
Supplier => Supplier Site => Purchasing tab =>Self Billing Region
=> Pay on => Receipt
*/
-- From Item Master..
SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = (select inventory_item_id from mtl_system_items_b where segment1='0000799102923'
and organization_id = 82)
/* Return Material Authorization : The Purchasing's Receiving can be used to receive
the customer RMA's. Typically this is what happens. for a item which is returned
by your customer, the sales rep will create a RMA. Hence while receiving that
item, we can enter the RMA number and then receive that customer returned item.
Hence , we can see that while we are receiving any thing we need the corresponding
number like PO number or RMA number etc.*/
/* Generally there are some managers(like Cost Manager) which we need to run explicitly
(or scheduled) to generate the accounting entries for the materials in inventory. The
access path is
setup=> Transactions =>Interface Managers.
Hence it is very important to understand that this table will get updated only if the
Cost Manager is up and running. What the cost manager basically does is that, for what
GL account this item which has been received should be attributed to.*/
select last_update_date, transaction_id, reference_account,inventory_item_id, organization_id org,
transaction_date, primary_quantity, gl_batch_id, rate_or_amount,cost_element_id
from mtl_transaction_accounts
where inventory_item_id = (select inventory_item_id from mtl_system_items_b
where segment1='0000799102923'
and organization_id = 82)
and creation_date >= '19-OCT-2004'
/*We can know the complete details of the account by giving the code combination id
obtained(reference account) from above query to the following query.*/
SELECT
a.segment1||'-'||a.segment2||'-'||a.segment3
||'-'||a.segment4||'-'||a.segment5||'-'||a.segment6 acct_code ,
a.*
FROM
gl_code_combinations a
WHERE
code_combination_id = 1083
/* For RETURNS, we do find a transaction in the rcv_transactions, with the corresponding
shipment header id.
As an ex, we can return an item in two steps, first step is to return to receiving
dept and second is to return to the vendor */
select rma_reference,transaction_id,transaction_type,quantity,interface_source_code, source_document_code,
destination_type_code, po_header_id, po_line_id, po_distribution_id,
routing_header_id ,subinventory,shipment_header_id,shipment_line_id
from rcv_transactions
where po_header_id = 23855
--and po_line_id = 213748
/* Here is an important test case which we can test. Let us say we have a PO which has
an item for a price of $5. We have raised another PO for the same item at a different
time , but for a price of $10. Now if we return the item corresponding to the first
PO now, does the cost manager update the mtl_transaction_accounts with $5 or $10. As
per the standard functionality, the cost mgr will update the table with the same price
as that of the original PO.
*/
-- AP related queries.
select * -- po_distributions_id
from ap_invoices_all
order by creation_date desc
select *
from ap_invoice_distributions_all
where po_distribution_id = 213198
select * from ap_holds_all
where invoice_id = 55856
select * from ap_invoices_all
where invoice_id = 55856
/* Receiving Locations are designated areas where you temporarily store items
before they are moved to their final destinations, ex (receiving dock and
inspection area are receiving locations.If necessary we can create even additional
locations like cold storage area where the items are pending inspection */
select *
from hr_locations_all
/*Receiving Controls, Options and Tolerances. We can look at the whole bunch of options at
setup => Organizations => Receiving Options
Ex. If the receiving tolerance is say 1%, then you may receive the item more than the
ordered amount by 1% less or more. Similarly if the receipt days early/late is 5, then
you can receive 5 days early or late by 5 days. */
/* ASN : Advance Shipment Notice : An ASN is transmitted via EDI(Electronic Data
Interchange) from a supplier to let the receiving organization know that a
shipment is coming. */
-- can we have more than 1 shipment for an PO line, y they r giving another window for that. ???????
-- y am i not able to receive using the receiving transaction from ??????
------------------------HR STUFF
-- Set the environment in the production environment ( fnd_client_info.set_org_context)
BEGIN
dbms_application_info.set_client_info(485);
END;
SELECT employee_id FROM fnd_user WHERE user_name LIKE 'PLUI'
SELECT * FROM per_all_people_f WHERE person_id in (3015615)
SELECT job_id, person_id, position_id FROM per_all_assignments_f WHERE person_id in (3015615)
SELECT name job_name FROM per_jobs WHERE job_id = 1602
-- This query given a job function,will give the approval group.
select control_group_name from po_control_groups where control_group_id in
(select control_group_id from po_position_controls where end_date is not null and job_id in (
select job_id from per_jobs_vl where name like 'E7-G AND A' ) )
-- This query ,given a approval group, will give the approval limits.
select * from po_control_rules where control_group_id in
(select control_group_id from po_control_groups where control_group_name = 'NCE 10/CE 10')
-- This query given a job function name, will give the approval limits.(Make sure you set the correct org_id before you check the amount limits.
select * from po_control_rules where control_group_id in
(select control_group_id from po_control_groups where control_group_id in
(select control_group_id from po_position_controls where end_date is not null and job_id in (
select job_id from per_jobs where name like 'E7-G AND A' )
)
)
------------------------------
Urgent Help needed regarding a PO.
Hi
In our production system, we have a requisition which has been created and approved.
There is a PO Autocreated for this requisition with the current status being
'IN PROCESS'. After this a change was made in the requisition ,which was approved
and the change was also accepted in the PO.
However when we go to the Purchase Order Summary and open up that PO, the "Open"
button is disabled. When I look at the Action History for this PO, it has all the
action history with the latest one being having Action value NULL and performed
by field is having the buyer info.
I poked around and changed the status of the PO from 'IN PROCESS' to 'REQUIRES REAPPROVAL'
from backend. After this when I open up the PO from PO Summary, the Open button
is enabled. Then again I submitted it for approval. After it was approved by the
manager, the status again went back to 'REQUIRES REAPPROVAL'. So I am not sure what
I am supposed to do at this point. Can some one give us some pointers as to what
should be done if the PO Status is 'IN PROCESS'and the "Open" button is disabled.
How do we progress with this PO.
This is very urgent and any help is greatly appreciated.
Thanks in advance.
----------------
265498.1 to find out about the document manager failed.
Typically the document manager with error number 1 and 2 could be handled and
documented, in my case, the document manager with error number 3 was resolved
by restarting the PO document manager.
select * from po_approved_supplier_list
why is that even if everything is fine, the PO are going to BOD in dev.
If proper approval assignments and groups are not defined then even if the requisition is of
small amount, it will indefinitely go up the approval hierarchy until the appropriate person with
apporoval limits are defined.
--- Check the accounting periods open or not
select * from gl_period_statuses
where application_id =(select application_id from fnd_application where application_short_name ='PO') -- PO =201, INV=401,
and set_of_books_id = 1
and period_name like '%-06'
order by period_num
--Sourcing Rules :
/* Sourcing rule indicates where an item needs to be sourced from. That is if ever there is a PO or req
raised against a particular item, we need to go with which supplier.
So we first create a sourcing rule mentioning what are the effective dates and which supplier name.
And then assign it to an item. That is we can assign it to an item, Inventory Org,Operating unit etc.
If it is assgined to an item, then we always buy that item from that particular supplier.
If is is assigned to an Inventory Organization, then all the item in that particular inventory org, will
be sourced from that particular supplier.
If is is assigned to an Opearting Unit, then all the item in all inventory orgs of that operating unit will
be sourced from that particular supplier.*/
--At what point will the sourcing rules,come into picture. ??
SELECT * FROM mrp_sr_assignments_v
--------------------------------
/* QUOTATIONS & RFQ's : All the Quotations and RFQ' go into the po_headers_all table with
type_lookup_code ='QUOTATION'.
An RFQ(A Request for quotation is something which the company sends to the supplier
requesting them for quotation on the items. A Quotation is something which a supplier
provides us with(his quotes). A quotation could be in response to an RFQ or may not be.
*/
select * from po_headers_all where type_lookup_code ='QUOTATION'
select * from po_lines_all where po_header_id = 51604
-- Quotations
select * from po_headers_all
where type_lookup_code ='RFQ'
and po_header_id = 51608
select * from po_lines_all where po_header_id = 51608
/* Blanket Purchase Agreement (BPA's) we did see how a standard purchase order.
A typical purchase order will have a header,lines, shipments and distributions.
A Blanket Purchase Agreement is just like a purchase order with the item defined
at a pre-negotiated price and which will have the begin and end dates.
However in the blanket purchase agreement,we will not have any delivery schedules as
we dont have them yet.
from the PO screen, the moment we choose the BPA, the shipments button will not
appear,however it will be there for a PO.
As mentioned above, typically
the Purchase Order Header information will go into po_headers_all,
The purchase order lines information will go into po_lines_all,
the purchase order shipment information will go into po_line_locations_all
(i.e the time and location, need_by , the location where this needs to be shipped).
These locations are usually the inventory locations.
the purchase order distribution information will go into po_distributions_all
(code_combination etc).
However in the case of blankets, we wont have the shipments and hence no
distributions information.
*/
select * from po_headers_all
where segment1 ='19518'
and type_lookup_code ='BLANKET'
select * from po_lines_all where po_header_id = 51617
-- The following two tables will not have any records for blankets.
select * from po_line_locations_all where po_header_id = 51617
select * from po_distributions_all where po_header_id = 51617
1) What does it mean to create a purchase order only with the item category and without
mentioning the inventory item??
2)What is an ASN and ASBN ?
ASN is Advanced Shipment Notice. An ASN is sent by the supplier to the receiver indicating
that a shipment is coming or ison its way. The ASN is usually sent in an EDI or XML
message format.
ASBN is a Advanced Shipment and Billing Notice. Usually a supplier might the invoice and
tax information as well along with the other information,then it is called ASBN.
3) What are the sequence of events in ASN(Advanced Shipment Notice) ?
The sequence of events are
A shipment authorization like PO is sent to Supplier (If any errors,an app advice is
sent to supplier)
Supplier sends the ASN via EDI or XML.
ASN is entered and verified via Receiving Open Interface.
Goods arrive.
ASN vs Receipt quantities are compared(If any errors,an app advice is
sent to supplier)
4) What is EDI(Electronic Data Interface ) ?
EDI is an electronic communication mechanism between two computers(they could be different
kinds of computers as well). EDI messages will have a special format with header ,body
and trailer. The EDI software has mainly two components , translator and Mapping tool.
So EDI software needs to be implemented by both the trading partners.
The EDI example can be explained best with the following seuqnce.
A buyer downloads a PO from his ERP system into a text file.
EDI will translate the above text file into a EDI format file. During the translation
it will use the mapping rules.
EDI software control comm software and a communication is established to the supplier.
The file can be sent either to a mailbox, ftp site etc.
At the supplier end, the EDI software will re-translate into a usable format for the
supplier to upload the data into their system.
Hence think about EDI as a communication format.
5) What is Pay on Receipt functionality.
Pay on Receipt is basically a way of creating the Payables Invoice once a receipt
is made against a PO. The following steps sumarizes the required steps.
-- Ensure that supplier ,site is set up as Pay site, Primary Pay has been set,
On Purchasing tab at site level, pay on receipt is set in self billing region.
-- Run "pay on receipt autoinvoice" concurrent program and ensure that you correctly
set the Aging period parameter carefully so that the receipt is picked up.
-- After the "pay on receipt autoinvoice" completes, it creates invoices in the invoice
interfaces table and the Payables Invoice Import program is automatically kicked off which
create AP Invoices.
This is one way of creating Invoices in the Payables System.
This process is also called as Self Billing or Evaluation Receipt Settlement(ERS).
6) What is a Supplier Item Catalog?
A Supplier Item catalog is a database consisting of different kinds of information like
-Any Negotiated sources, like if there are any blanket purchase agreements,GBA, quotations etc
-Which items has been purchased from which suppliers(i.e historical purchases)
-Sourcing Rules
And the Supplier Item Catalog can be invoked from different places like Navigator
or from the PO or requisition form and then make a decision as who should be
supplier for your PO or requisition.
7). What are the key fields that you mention in the purchase orders ?
items, quantity, cost, schedule, distributions.
Based on different things that are put in, they are classified as
different orders
8) What is the difference between standard and planned purchased order?
Standard Purchase Order is when you want to make a one-time purchase of items and when you know
the items, quantity, cost, schedule, distributions.
Planned Purchase Order is a long term agreement to buy a product from a single source. You can also
have a scheduled release for a planned purchase order.
9) What is the difference between blanket purchased agreement(BPA), Contract purchase agreement & Global BPA.
Blanket Purchase Agreement is an agreement with the supplier to purchase a product/items at a
predetermined prices between two specific dates.
Global Purchase Agreement is like a BPA however this is for an enterprise-wide purpose. For ex, you can negotiate
with the supplier, based on your whole company's global purchasing requirements. That is you can
centralize the buying activity for your whole enterprise by having a global agreement.
Then the enterprise organizations can access this global agreement to create purchase order that
leverage these pre-negotiated prices and terms.
You will generally have releases(not shipments) for BPA's and Global BPA's.
Contract purchase Agreement : You create contract purchase agreements,with your suppliers to agree
on specific terms and conditions without indicating the goods and services that you will be purchasing.
You can later issue standard PO's against the contract Purchase agreements.
10). What are sourcing rules ??? write in great details .
Sourcing Rules let you specify how to replenish items in an organization.
11). What is a RFQ and quotation ?
RFQ : A RFQ is a request for quotation. RFQ's once created can be transmitted to
the supplier by either fax,phone ,isupplier portal etc.
Quotation : A quotation from a supplier is in response to a RFQ or it may not be.
A quotation typically has a line and the line will have a price break. Only after
you create a price break, the approve button is enabled.
You can approve the quotation any number of times and the approve button is enable until the status of the
quotation is closed.
A quotation can be entered manually ,say while on the phone from a supplier as the supplier provides the
information. Or, it could come from Purchasing Documents Open Interface.
Once a quotation is created, you could create a PO or BPA from this quotation.
12). What are Supplier lists and what are they used for?
Supplier lists are not ASL's. Supplier lists are basically different kinds of lists
with each list typically corresponding to a category for ex, you might create
a list like Monitor and in that list give all the suppliers who would provide
the Monitors. similary hard disk etc.
Typically once Supplier Lists are created,they are attached to an RFQ and
the RFQ is sent to all those suppliers in the list. RFQ's once created can
be transmitted to the supplier by either fax,phone Oracle iSupplier portal etc.
13). Write about the complete flow of the Pay on Receipt functionality.
Give the example of an item which is stockable ,but it is not transactable or oe transactable.
An item is stockable, if it is tangible,for ex
14). What is Approved Supplier List functionality,setup etc?
The approved supplier setup is done at the inventory item level. At the item level, you set
whether that particular item should use approved supplier or not. This is set at
Item => Purchasing options
If you set this flag, then you can only approve purchase orders , if the supplier is an approved
supplier.
15). What is the concept of importing the approved suppliers ,why would you do that.
16). What is the difference between PO Close, PO Finally Closed and PO Cancel ?
Close is reversible and prevent AP from matching any of the PO lines until
the Close is reversed.
Cancel will close the PR/PO line and it is irreversible. Cancel will also
change the Quantity to zero at line level. In a case where PO line
is partially invoiced, cancel will change the 'quantity ordered' down
to equal the 'quantity billed'.
Finally closed will close the PO and it is irreversible. Finally Closed
will not change the 'Ordered Quantity' however,it will reverse dollars back to GL.
17). What happened to the 11i Purchasing Open Document Interface in R12 ??
It has been split into two programs
Import Price Catalog => this is to import the BPA and quotations primarily.
Import Standard Purchase Orders (Why would you import standard purchase orders ,this could be
because you have first entered them into a spreadsheet and then import them into the
interface table etc).
18) what are the different interfaces that are there in Purchasing??
Purchasing Documents Open Interface :
Quotations from Supplier
Even ASL entries can be imported thru this interface,how ??
Receiving open Interface
ASN Transactions
ASBN Transactions
19) In PO, why is that shipment is related to the distributions. does that mean that each
shipment will have one distribution?
Yes. And when a PO -matched invoice is created, the same invoice distributions
will be the same as PO distributions.
20). What are the two main important reports in Purchasing and their usage?
Accrual Reconciliation Report.
Accrual Write-off Report : This report is typically run for writing off the accruals.
However you can also use this report to find the unreceipt'ed purchase orders.
21). In the receiving form,what could be the reason if you are not able to look for a PO?
One thing we can do is to check and see the what is the PO organization ,then
change to that org, and then open up the rcv trx and see if there are any receipts.
23). when you create a purchase order, does the item price comes from a price list?
No. that price is for the customer. This price is the price we need to pay to the
supplier. And it comes from the item/supplier catalog and for the catalog see above.
24). What are two important factors that need to be taken care of for PO approval.
one is document amount and the account range.
Some information
