Introduction:
This script will provide all Requisitions Details from backend for without Po’s.
Script:
SELECT (SELECT NVL (short_code, NAME)
FROM apps.hr_operating_units
WHERE organization_id = r.org_id) org_code, r.segment1 “Req number”,
r.authorization_status req_status, rl.line_num “Req line”,
r.creation_date,
TO_CHAR (r.approved_date, ‘dd-MON-RR HH24:MI:SS’) “Req Approved Date”,
–,ass.vendor_name “SUPPLIER NAME”,aps.vendor_site_code “VENDOR SITE CODE”,
(SELECT DISTINCT segment1 || ‘.’
|| segment2
FROM apps.mtl_categories
WHERE category_id =
rl.category_id)
“Requisition Line Category”,
NULL buyer, NULL note_buyer,
rl.suggested_vendor_product_code supplier_item,
(SELECT vendor_name
FROM apps.ap_suppliers
WHERE vendor_id = rl.vendor_id AND ROWNUM = 1) “Supplier Name”,
(SELECT vendor_site_code
FROM apps.ap_supplier_sites_all
WHERE vendor_site_id = rl.vendor_site_id) “Supplier SIte Code”,
rl.item_description, rl.unit_meas_lookup_code, rl.unit_price,
rl.quantity, (rl.unit_price * rl.quantity) amount,
(SELECT full_name
FROM apps.per_all_people_f
WHERE person_id = r.preparer_id AND ROWNUM = 1) preparer,
(SELECT full_name
FROM apps.per_all_people_f
WHERE person_id = rl.to_person_id AND ROWNUM = 1) requester
–r.org_id org_id,
–aps.vendor_name
FROM
–apps.ap_suppliers ass,
–apps.ap_supplier_sites_all aps,
apps.po_requisition_headers_all r,
apps.po_requisition_lines_all rl,
apps.po_req_distributions_all rd
— apps.ap_suppliers aps
WHERE 1 = 1
–and ass.vendor_id=aps.vendor_id
–and r.segment1=’101009927000′
–and ass.vendor_id = rl.vendor_id
–and aps.vendor_site_id= rl.vendor_site_id
AND rl.requisition_header_id = r.requisition_header_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NOT EXISTS (SELECT req_distribution_id
FROM apps.po_distributions_all
WHERE req_distribution_id = rd.distribution_id)
AND r.org_id IN (14700, 1800)
AND r.authorization_status = ‘APPROVED’
AND TRUNC (r.approved_date) >= ’15-JUN-2019′
AND TRUNC (r.approved_date) <= ’21-JUN-2019′
Got any queries?
Do drop a note by writing us at yeswanth.r@doyensys.com or use the comment section below to ask your questions
