Troubleshooting
Problem
SQL View / Utility for finding pipeline for a any Yantra document >how to view pipeline across process types
Symptom
PART: API-OM 5.5 Platform
PRODUCT: Distributed Order
Management
COMPONENT: unReceiveOrder
OS: Solaris - 2.8
DATABASE:
Oracle - 9.2.0.4
WEB SERVER: Websphere AE - 5.2
WEB BROWSER: Internet
Explorer - 6.0 sp1
Different Yantra documents have pipeline keys at
different places, Order_line, Release, receipts etc, Is there a utility that
will consolidated these across process types and allow for easy querying by
Doc_No
Cause
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
Historical Number
PRI49390
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21527429