Basic REQ, PO and Receipt Details
SELECT prha.segment1 req_num
, prha.creation_date req_date
, pla.line_num po_line_num
, pha.segment1 po_num
, pha.creation_date po_date
, rsh.receipt_num
, rsh.creation_date rcpt_date
, rt.quantity qty_received
, rt.transaction_type
, pda.quantity_billed qty_billed
, (pda.quantity_billed * prla.unit_price) amt_billed
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
, po.po_req_distributions_all prda
, po.po_line_locations_all plla
, po.po_lines_all pla
, po.po_headers_all pha
, po.po_distributions_all pda
, po.rcv_transactions rt
, po.rcv_shipment_headers rsh
, po.rcv_shipment_lines rsl
WHERE prha.requisition_header_id = prla.requisition_header_id
AND plla.line_location_id = prla.line_location_id
AND pla.po_line_id = plla.po_line_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = pda.po_line_id
AND prla.requisition_line_id = prda.requisition_line_id
AND pda.po_distribution_id = rt.po_distribution_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.shipment_line_id = rsl.shipment_line_id(+)
AND NVL(rt.transaction_type, 'RECEIVE') = 'RECEIVE'
AND pha.segment1 = :po;
More detailed REQ, PO and Receipt Details
SELECT DISTINCT prha.segment1 req_no
, pha.segment1 po_no
, pha.document_creation_method
, pav.agent_name buyer
, fu.description requisitioner
, papf.employee_number
, papf.full_name
, papf.email_address
, hla.location_code req_location
, haout.NAME req_org
, SUBSTR(haout.NAME, 0, 2) service
, pv.segment1 supp_no
, pv.vendor_name supp_name
, pvsa.vendor_site_code supp_site
, pv.vendor_type_lookup_code vendor_classification
, aia.invoice_num
, pha.creation_date po_creation_date
, aia.invoice_date
, aia.invoice_date - pha.creation_date calc
FROM po.po_headers_all pha
, po.po_lines_all pla
, po.po_distributions_all pda
, apps.po_agents_v pav
, apps.po_vendors pv
, apps.po_vendor_sites_all pvsa
, applsys.fnd_user fu
, po.po_requisition_lines_all prla
, po.po_requisition_headers_all prha
, po.po_req_distributions_all prda
, po.po_line_locations_all plla
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.hr_locations_all hla
, hr.hr_all_organization_units_tl haout
, po.rcv_transactions rt
, po.rcv_shipment_headers rsh
, po.rcv_shipment_lines rsl
, ap.ap_invoices_all aia
, ap.ap_invoice_distributions_all aida
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pda.po_line_id
AND pha.agent_id = pav.agent_id
AND pha.vendor_id = pv.vendor_id
AND pha.vendor_site_id = pvsa.vendor_site_id
AND pv.vendor_id = pvsa.vendor_id
AND prha.created_by = fu.user_id
AND fu.employee_id = papf.person_id
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = prda.requisition_line_id
AND prda.distribution_id = pda.req_distribution_id
AND fu.employee_id = paaf.person_id
AND paaf.location_id = hla.location_id
AND paaf.organization_id = haout.organization_id
AND aia.invoice_id = aida.invoice_id
AND aida.po_distribution_id = pda.po_distribution_id
AND pda.po_distribution_id = rt.po_distribution_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.shipment_line_id = rsl.shipment_line_id(+)
AND rsl.po_line_location_id = plla.line_location_id(+)
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND pha.creation_date > aia.invoice_date;
Another version
SELECT DISTINCT bus_gp.NAME bus_gp
, haout.NAME unit
, gcc.segment2 service
, gcc.segment3 acct
, gcc.segment4 centre
, pap.segment1 project_num
, papf.full_name requisitioner
, prha.segment1 req_num
, prha.creation_date req_date
, prha.authorization_status req_approval_status
, prha.closed_code req_closed_status
, pha.segment1 po_num
, pha.creation_date po_date
, pha.authorization_status po_approval_status
, pha.closed_code po_closed_status
, prla.closed_code line_status
, pav.agent_name po_buyer
, prla.line_num line
, prda.distribution_num req_dist_num
, prda.req_line_quantity
, prla.unit_meas_lookup_code uom
, prla.unit_price price
, prla.quantity qty
, rsh.receipt_num
, rsh.creation_date rcpt_date
, rt.quantity qty_received
, pda.quantity_billed qty_billed
, (pda.quantity_billed * prla.unit_price) amt_billed
, mcb.segment1 || '.' || mcb.segment2 purchase_category
, prla.suggested_vendor_product_code catalogue_code
, TO_CHAR(prla.need_by_date, 'DD-MON-RRRR') need_by_date
, prla.note_to_agent note_to_buyer
, prla.note_to_vendor note_to_supplier
, prla.suggested_vendor_name supplier
, prla.suggested_vendor_location site
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
, po.po_req_distributions_all prda
, po.po_line_locations_all plla
, po.po_lines_all pla
, po.po_headers_all pha
, po.po_distributions_all pda
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.hr_all_organization_units_tl haout
, hr.hr_all_organization_units_tl bus_gp
, hr.hr_locations_all_tl hlat
, inv.mtl_categories_b mcb
, gl.gl_code_combinations gcc
, pa.pa_projects_all pap
, apps.po_agents_v pav
, po.rcv_transactions rt
, po.rcv_shipment_headers rsh
, po.rcv_shipment_lines rsl
WHERE prha.requisition_header_id = prla.requisition_header_id
AND plla.line_location_id = prla.line_location_id
AND pla.po_line_id = plla.po_line_id
AND prha.preparer_id = papf.person_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = pda.po_line_id
AND prla.deliver_to_location_id = hlat.location_id
AND prla.requisition_line_id = prda.requisition_line_id
AND gcc.code_combination_id = prda.code_combination_id
AND haout.organization_id = paaf.organization_id
AND paaf.person_id = papf.person_id
AND mcb.category_id = prla.category_id
AND papf.business_group_id = bus_gp.organization_id
AND pap.project_id(+) = prda.project_id
AND pha.agent_id = pav.agent_id
AND pda.po_distribution_id = rt.po_distribution_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.shipment_line_id = rsl.shipment_line_id(+)
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND NVL(rt.transaction_type, 'RECEIVE') = 'RECEIVE'
ORDER BY prha.segment1 DESC
, prla.line_num;
No comments:
Post a Comment