Oracle Apps Functional All About Oracle Apps functional & Technical FAQs, Tutorials

Adding a Buyer

You need to be setup as a buyer in the system to raise purchasing orders. To add yourself as a buyer go to responsibility equivalent to Purchasing, Vision Operations (USA).

Go to menu setup –> personnel –> Buyers

Click on add buyers button in new browse window

Click on the search button and search for the user by the name select the username and click select button.

Now click on save button to save the buyer.

Now you can raise the purchasing orders

Sql queries to validate the data that is part of the approval processing

For purchase order status

select segment1 po#, revision_num r#,
substr(type_lookup_code,1,4) type, authorization_status auth_status,
closed_code, wf_item_type, wf_item_key, org_id
from po_headers_all
where segment1 = '&PO_NUMBER'

For Requisition Status:

select segment1 req#,authorization_status auth_status,
closed_code, wf_item_type, wf_item_key, org_id
from   po_requisition_headers_all
where segment1 = '&REQ_NUM'

Release Status:

select po.segment1 po#, rel.release_num rel#, po.revision_num po_r#,
po.authorization_status po_status, po.closed_code close_po,
po.wf_item_type po_type, po.wf_item_key po_key, po.org_id org,
rel.wf_item_type rel_type, rel.wf_item_key rel_key,
rel.po_release_id rel_id, rel.authorization_status rel_status, rel.hold_flag hold
from po_headers_all po, po_releases_all rel
where po.po_header_id = rel.po_header_id
and po.segment1 = '&PO_NUM'
and rel.release_num = 'REL_NUM'
order by 1,2

Purchase Order Action History:

select poah.sequence_num seq#, poah.action_date, poah.action_code,
poah.employee_id emp_id, fnd.user_name, substr(poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type,
poah.object_revision_num rev, pohead.org_id
from po_action_history poah, fnd_user fnd, po_headers_all pohead
where poah.object_id = pohead.po_header_id
and pohead.segment1 = '&PO_NUMBER'
and pohead.org_id = '&ORG_ID'
and   substr(poah.object_type_code,1,3) = 'PO'
and   poah.employee_id = fnd.employee_id
and   fnd.session_number != 0
order by 2,1

Requisition Action History:

select poah.sequence_num seq#, poah.action_date, poah.action_code,
poah.employee_id emp_id, fnd.user_name, substr (poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type,
poah.object_revision_num rev, pohead.org_id, poah.note
from po_action_history poah, fnd_user fnd, po_requisition_headers_all pohead
where poah.object_id = pohead.requisition_header_id
and pohead.segment1 = '&REQ_NUMBER'
and substr(poah.object_type_code,1,3) = 'REQ'
and pohead.org_id = '&ORG_ID'
and poah.employee_id = fnd.employee_id
and fnd.session_number != 0
order by 9,2,1

Position Attached to Username:

SELECT pos.name  position_name, pa.position_id,fnd.user_name
FROM PER_ALL_ASSIGNMENTS_F pa, per_positions pos,per_jobs job, fnd_user fnd
WHERE pa.POSITION_ID =  pos.POSITION_ID
and pa.job_ID = job.job_id
and sysdate between pa.EFFECTIVE_START_DATE
and pa.EFFECTIVE_END_DATE
and pa.primary_flag = 'Y'
and pa.assignment_type = 'E'
and pa.person_id = fnd.employee_id
and pa.PERSON_ID = (select employee_id
from fnd_user
where user_name = '&user_name')

Sql queries to get organization id from requisition, purchase order and purchase order release?

From Requisition:

select hr.name, prh.segment1, prh.org_id
from po_requisition_headers_all prh,
hr_all_organization_units hr
where prh.org_id = hr.organization_id and
prh.segment1 = '&Enter_Req_Number';

From Purchase Order:

select hr.name, poh.segment1, poh.org_id
from po_headers_all poh,
hr_all_organization_units hr
where poh.org_id = hr.organization_id and
poh.segment1 = '&Enter_PO_Number';

select hr.name, poh.segment1, por.release_num, por.org_id
from po_headers_all poh,
po_releases_all por,
hr_all_organization_units hr
where poh.org_id = hr.organization_id and
por.org_id = poh.org_id and
poh.po_header_id = por.po_header_id and
poh.segment1 = '&Enter_PO_Number' and
por.release_num = '&Enter_Release_Num';

How does Requisition Import determine the grouping method for incoming pieces of data?

This function groups requisitions. It first assigns values to REQUISITION_LINE_ID and REQ_DISTRIBUTION_ID; the function then groups Requisitions based on the REQ_NUMBER_SEGMENT1 column. All requisitions with the same NOT NULL REQ_NUMBER_SEGMENT1 are assigned the same REQUISITION_HEADER_ID. The function then groups Requisitions based on the GROUP_CODE column. All requisitions with the same value in the GROUP_CODE column are assigned the same REQUISITION_HEADER_ID. It then groups based on the GROUP_BY parameter, which takes on the value of DEFAULT_GROUP_BY if not provided. GROUP_BY could be one of the following: BUYER, CATEGORY, ITEM, VENDOR, LOCATION or ALL.

What is the Basic Purchasing Setup for Requisition Import?

If importing requisitions from Inventory, input a value for the profile option INV: Minmax Reorder Approval. If the value of INCOMPLETE is selected, the result will be imported Requisitions that require an approval. If the value is APPROVED, then the requisitions cannot be queried in the Requisition entry form; rather, the Requisition Summary form will have to be utilized to view information on the imported approved requisitions.

Oracle Purchasing Interview Questions

1. Can we automatically 'Close' the Purchase order without receiving the full quantity?

Ans: Yes, we can close the purchase order without receiving full quantity. Once we receive partial quantity, go to control actions and close the PO.