SQL View / Utility for finding pipeline for a any Yantra document >how to view pipeline across process types

Resolving The Problem

The following SQL view script collates pipeline info across Order, Shipments, receipts, negotiation etc.
Once the script is created in the database,
You can use Toad, or SQLplus or sql queries to access this info.
To handle trailing spaces Use 'LIKE' 'DOC_NO%' instead of '=' to fetch records.
select * from CHK_PIPELINE_VW where doc_no like 'Y1000000789%'

P.S. For versions below 5.5 , comment out the last union involving the receipt tables.

CREATE OR REPLACE VIEW CHK_PIPELINE_VW (
PROCESS_TYPE
, DOC_NO
, DOC_NO_LINE
, PIPELINE_DESC
)
as
SELECT
p.process_type_key
, s.shipment_no
,1
, p.pipeline_description
FROM
yfs_shipment s
, yfs_pipeline p
where p.pipeline_key=s.pipeline_key
group by p.process_type_key, s.shipment_no, p.pipeline_description
UNION
SELECT
p.process_type_key
, l.load_no
,1
, p.pipeline_description
FROM
yfs_load l, yfs_pipeline p
where p.pipeline_key=l.pipeline_key
group by p.process_type_key, l.load_no, p.pipeline_description
UNION
SELECT
p.process_type_key
, n.negotiation_no
, g.prime_line_no
, p.pipeline_description
FROM
yfs_negotiation_hdr n, yfs_negotiation_line g, yfs_pipeline p
where p.pipeline_key=n.pipeline_key
and g.negotiation_hdr_key=n.negotiation_hdr_key
group by p.process_type_key, n.negotiation_no, g.prime_line_no, p.pipeline_description
UNION
SELECT
p.process_type_key
, h.order_no
, l.prime_line_no
, p.pipeline_description
FROM
yfs_pipeline p
, yfs_order_header h
, yfs_order_line l
, yfs_order_release_status r
where
l.order_header_key=h.order_header_key
and
r.order_line_key=l .order_line_key
and
p.pipeline_key=r.pipeline_key
and
r.STATUS_QUANT ITY>0
group by p.process_type_key,h.order_no,l.prime_line_no,p.pipeline_description

UNIO N
SELECT
p.process_type_key
, r.receipt_no
, 1
, p.pipeline_description
FROM
yfs_pipeline p
, yfs_receipt_header r
where
p.pipeline_key=r.pipeline_key
group by p.process_type_key,r.receipt_no,p.pipeline_description