ERP Implementation Expert: Alert Query for Receipt Intimation

SELECT hre.full_name

, SUBSTR(poh.segment1,1,10) || '/' ||

SUBSTR(pol.line_num,1,4) || '/' ||

SUBSTR(por.release_num,1,4) || '/' ||

SUBSTR(pll.shipment_num,1,4)

, pov.vendor_name

, pol.item_description

INTO &AGENT

, &PO

, &VENDOR

, &LINE

FROM hr_employees_current_v hre

, po_vendors pov

, po_headers poh

, po_lines pol

, po_line_locations pll

, po_releases por

WHERE poh.type_lookup_code IN ('STANDARD','BLANKET','PLANNED')

AND NVL(pll.receipt_required_flag, 'N') = 'N'

AND NVL(pll.closed_code, 'OPEN') = 'OPEN'

AND NVL(pll.cancel_flag, 'N') = 'N'

AND NVL(poh.closed_code, 'OPEN') = 'OPEN'

AND NVL(poh.cancel_flag, 'N') = 'N'

AND NVL(por.closed_code, 'OPEN') = 'OPEN'

AND NVL(por.cancel_flag, 'N') = 'N'

AND poh.po_header_id = pol.po_header_id

AND pov.vendor_id = poh.vendor_id

AND pll.po_line_id = pol.po_line_id

AND por.po_release_id(+) = pll.po_release_id

AND hre.employee_id = poh.agent_id

AND pol.creation_date BETWEEN (sysdate - :CHECKDAYS)

AND sysdate

ORDER BY hre.full_name

, poh.segment1

, pov.vendor_name

, pol.creation_date

select rowidtochar(cust.rowid),

substr(hr.name,1,3) "ORGN_CODE",

a.receipt_num,

sysdate,

b.quantity_received,

b.line_num,

e.segment1,

c.segment1,

c.Description,

substr(k.email_address,1,55),

substr(l.email_address,1,55),

substr(m.email_address,1,55),

decode(e.org_id,'46','abc@rrr.com','42','hod@rrr.com',NULL),

nvl(j.segment1,'NULL'),

l.user_name

into &rowid,&orgn,&recvno,&recv_date,&recv_qty,

&lineno,&pono,&itemno,&item_desc1,&email1,&email2,&email3,&email4,&reqno,&username

from PO_RECEIPT_ALERT_CUSTOM cust,

rcv_shipment_headers a,

rcv_shipment_lines b,

mtl_system_items c,

ic_item_mst d,

po_headers_all e,

po_requisition_headers_all j,

po_requisition_lines_all porl,

po_line_locations_all poll,

fnd_user k,fnd_user l,fnd_user m,

hr_organization_units hr

where cust.shipment_header_id = a.shipment_header_id

and a.shipment_header_id = b.shipment_header_id

and d.item_no = c.segment1

and d.gl_class in ('GS','RM','FA','PY')

and b.po_header_id = e.po_header_id

and a.ship_to_org_id = hr.organization_id

and b.item_id = c.inventory_item_id

and a.ship_to_org_id = c.organization_id

and b.po_line_location_id = poll.line_location_id

and poll.line_location_id = porl.line_location_id(+)

and j.requisition_header_id(+) = porl.requisition_header_id

and e.created_by = k.user_id

and decode(j.created_by,null,e.created_by,j.created_by) = l.user_id

and a.created_by = m.user_id

and cust.rowid =:rowid

order by b.line_num