SELECT TRUNC(rt.transaction_date) grn_date,
(SELECT MAX(TRUNC(plla.need_by_date))
FROM po.po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id) need_by_date,
rsh.attribute5 invoice_number,
TRUNC(TO_DATE(rsh.attribute6, 'YYYY/MM/DD HH24:MI:SS')) invoice_date,
rsh.packing_slip vehicle_no,
TRUNC(TO_DATE(rsh.attribute9, 'YYYY/MM/DD HH24:MI:SS')) inward_gp_date,
rt.attribute11 bags,
rsh.attribute8 inward_gp_no,
NVL(rsh.shipment_num, rsh.attribute10) challan_no,
rsh.shipped_date challan_date2,
(SELECT MAX(TRUNC(confirm_date))
FROM wsh_new_deliveries wnd,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.delivery_id =
--nvl(to_number(rsh.attribute10),1)
(CASE
WHEN REGEXP_LIKE(rsh.attribute10, '^[[:digit:]]*$') THEN
TO_NUMBER(rsh.attribute10)
ELSE
1
END)
AND wnd.organization_id = 217) AS challan_date,
rsh.receipt_num grn_no,
NVL(rt.quantity, 0) received_quantity,
NVL((SELECT SUM(NVL(rt1.quantity, 0))
FROM rcv_transactions rt1
WHERE rt1.transaction_type = 'RETURN TO RECEIVING'
AND rt1.parent_transaction_id = rt.transaction_id
AND rt1.organization_id = rt.organization_id),
0) returned_quantity,
rt.unit_of_measure,
rt.uom_code,
rt.po_unit_price unit_price,
rt.currency_code,
NVL(rt.currency_conversion_rate, 1) conversion_rate,
pv.vendor_name vendor_name,
pha.segment1 po_number,
TRUNC(pha.creation_date) po_created_on,
TRUNC(pla.creation_date) po_line_created_on,
TRUNC(rsh.creation_date) grn_created_on,
msi.segment1 item_segment1,
msi.segment2 item_segment2,
msi.segment3 item_segment3,
msi.segment1 || '.' || msi.segment2 || '.' || msi.segment3 item_code,
msi.description item_description,
pha.attribute6 for_department_code,
msi.inventory_item_id,
pla.preferred_grade cone_weight,
rt.organization_id rcv_org_id
FROM po.rcv_transactions rt,
po.po_headers_all pha,
po.po_lines_all pla,
po_vendors pv,
mtl_system_items msi,
rcv_shipment_headers rsh
WHERE transaction_type IN ('DELIVER')
AND rt.organization_id not IN (108, 237, 316)
AND rt.po_header_id = pha.po_header_id
AND rt.po_line_id = pla.po_line_id
AND pha.vendor_id = pv.vendor_id
AND pla.item_id = msi.inventory_item_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND msi.organization_id = 103
-- AND pha.org_id IN (102, 236)
--AND pha.ship_to_location_id IN (146, 56923)
AND TRUNC(rt.transaction_date) >=
'01-Jul-' || TO_CHAR(ADD_MONTHS(SYSDATE, -3 * 12), 'YYYY')
--BETWEEN '01-Aug-2020' AND '31-Aug-2020'
and pha.segment1 = '<PO_NUMBER>'