Search This Blog

Sunday, November 21, 2010

Purchase Requisition without a Purchase Order

SELECT
,prh.segment1 "PR NUM"
,trunc(prh.creation_date) "CREATED ON"
,trunc(prl.creation_date) "Line Creation Date"
,prl.line_num "Seq #"
,msi.segment1 "Item Num"
,prl.item_description "Description"
,prl.quantity "Qty"
,trunc(prl.need_by_date) "Required By"
,ppf1.full_name "REQUESTOR"
,ppf2.agent_name "BUYER"
FROM
,po.po_requisition_headers_all prh
,po.po_requisition_lines_all prl
,apps.per_people_f ppf1
,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2
,po.po_req_distributions_all prd
,inv.mtl_system_items_b msi
,po.po_line_locations_all pll
,po.po_lines_all pl
,po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id and
prl.requisition_line_id = prd.requisition_line_id and
ppf1.person_id = prh.preparer_id and
prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and
ppf2.agent_id(+) = msi.buyer_id and
msi.inventory_item_id = prl.item_id and
msi.organization_id = prl.destination_organization_id and
pll.line_location_id(+) = prl.line_location_id and
pll.po_header_id = ph.po_header_id(+) and
pll.pl_line_id = pl.po_line_id(+) and
prh.authorization_status = 'APPROVED' and
pll.line_location_id is NULL and
prl.closed_code is NULL and
nvl(prl.cancel_flag,'N') <> 'Y'
ORDER BY 1,2

SELECT
prh.segment1 "PR NUM"
,prh.authorization_status
,trunc(prh.creation_date) "CREATED ON"
,trunc(prl.creation_date) "Line Creation Date"
,prl.line_num
,prl.item_description "Description"
,prl.quantity "Qty"
FROM
po.po_requisition_headers_all prh
,po.po_requisition_lines_all prl
,po.po_req_distributions_all prd
,po.po_line_locations_all pll
,po.po_lines_all pl
,po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id and
prl.requisition_line_id = prd.requisition_line_id and
pll.line_location_id(+) = prl.line_location_id and
pll.po_header_id = ph.po_header_id(+) and
pll.po_line_id = pl.po_line_id(+) and
prh.authorization_status in ('INCOMPLETE','IN PROCESS','REJECTED','RETURNED') and
pll.line_location_id is NULL and
prl.closed_code is NULL and
nvl(prl.cancel_flag,'N') <> 'Y'
ORDER BY 2,1,4

1 comment:

  1. Where they learn techniques on how to improve their patient's mobility, relieve their pain and treat temporary or permanent disabilities, through various therapeutic techniques and procedures. Remember that you are usually limited to a number of sessions so you might want to consider paying out of your own pocket. Complete rehab solutions provide services for these type people.
    Have a look at my page - depression cured at last sherry rogers

    ReplyDelete