andy/mip
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
Files
master
mip/Documentation/pldoc/V_QUOTE_DETAILS.html
hardya 7311d92835 Seed/enst.ctl - add INVALID status 
Documentation/Design/DatabaseItemToFunctionalSpecificationReference.xls - add rule O-M5
Documentation/pldoc - regenerated
Modules/mip_helper_special_cases.pck - make reference to Tripartite rule O-M5 (handled by mip_quotation.pck and mip_tripartite.pck
Schema - removed DATA_ITEM_ROLES_IMPORT, added views of enquiries and quote statuses

git-svn-id: http://locode01.ad.dom/svn/WEBMIP/trunk@3354 248e525c-4dfb-0310-94bc-949c084e9493
2008-01-22 17:59:56 +00:00

323 lines
8.3 KiB
HTML
Raw Permalink Blame History

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<LINK REL="STYLESHEET" TYPE="text/css" HREF="plsqldoc.css">
<TITLE>View V_QUOTE_DETAILS</TITLE>
<META name="object" content="VIEW">
<META name="name" content="V_QUOTE_DETAILS">
<META name="description" content="This view shows details of each of the quotes for an enquiry.">
<META name="columns" content="ENQUIRY_ID QUOTE_ID ENTY_CODE MODULE_CODE LEAD_TIME ADDITIONAL_ITEMS BAS_CODE HOU_CODE QMAX QMIN INLET_ORIENTATION OUTLET_ORIENTATION TOTAL_COST ROW_NUMBER">
</HEAD>
<BODY>
<TABLE WIDTH="100%"><TR>
<TD><P ALIGN="LEFT"><STRONG><SMALL></SMALL></STRONG></TD>
<TD><P ALIGN="RIGHT"><STRONG><SMALL><A HREF="index.html">index</A></SMALL></STRONG></TD>
</TR></TABLE>
<TABLE CLASS="MAIN_TABLE"><TR><TD CLASS="DESC_TEXT">
<P CLASS="MAIN_TITLE">View V_QUOTE_DETAILS</P>
This view shows details of each of the quotes for an enquiry.
</TD></TR></TABLE>
<HR>
<A NAME="Columns"></A>
<TABLE CLASS="SUB_TABLE"><TR><TD CLASS="SUB_TITLE">
Columns
</TD></TR></TABLE>
<TABLE CLASS="SIMPLE_TABLE">
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM"><U>
Name
</U>
</TD>
<TD VALIGN="TOP" CLASS="LIST_ITEM"><U>
Type
</U>
</TD>
<TD VALIGN="TOP" CLASS="LIST_ITEM"><U>
Optional
</U>
</TD>
<TD VALIGN="TOP" CLASS="LIST_ITEM"><U>
Default
</U>
</TD>
<TD VALIGN="TOP" CLASS="LIST_ITEM"><U>
<A HREF="V_CURRENT_CONTACT_MECHANISMS.html">Comments</A>
</U>
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
ENQUIRY_ID
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The <A HREF="V_AICO.html">ID</A> of the enquiry this quote relates to.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
QUOTE_ID
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The unique <A HREF="V_AICO.html">ID</A> for this particular quote.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
<A HREF="V_LACO.html">ENTY_CODE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The type of enquiry quoted for.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
MODULE_CODE
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The name of the module quoted for.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
LEAD_TIME
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The lead time for installation. This is the max of any additional item's lead times.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
ADDITIONAL_ITEMS
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(4000)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Any additional items included in the quote.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
<A HREF="V_BACO.html">BAS_CODE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The module base <A HREF="ROLE_TYPES.html">code</A>.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
<A HREF="V_HOCO.html">HOU_CODE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
QMAX
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The meter module's Q max.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
QMIN
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
INLET_ORIENTATION
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The orientation of the meter inlet.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
OUTLET_ORIENTATION
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The orientatino of the meter outlet.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
TOTAL_COST
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
The total cost of the quote. This figure excludes any lifting gear or purging.
</TD>
</TR>
<TR>
<TD VALIGN="TOP" CLASS="LIST_ITEM">
ROW_NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
NUMBER
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
Y
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</TD>
</TR>
</TABLE>
<HR>
<A NAME="SQL"></A>
<TABLE CLASS="SUB_TABLE"><TR><TD CLASS="SUB_TITLE">
SQL
</TD></TR></TABLE><PRE CLASS="DECL_TEXT">
CREATE OR REPLACE VIEW V_QUOTE_DETAILS AS
SELECT "ENQUIRY_ID","QUOTE_ID","ENTY_CODE","MODULE_CODE","LEAD_TIME","ADDITIONAL_ITEMS","BAS_CODE","HOU_CODE","QMAX","QMIN","INLET_ORIENTATION","OUTLET_ORIENTATION","TOTAL_COST","ROW_NUMBER"
FROM (SELECT q.enqu_id AS enquiry_id
,t.qute_id AS quote_id
,MAX(t.enty_code) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS <A HREF="V_LACO.html">enty_code</A>
,MAX(t.modu_code) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS module_code
,MAX(t.lead_time) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A> ORDER BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS lead_time
,<A HREF="get_quote_items.html">get_quote_items</A>(<A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS additional_items
,MAX(t.bas_code) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS <A HREF="V_BACO.html">bas_code</A>
,MAX(t.hou_code) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS <A HREF="V_HOCO.html">hou_code</A>
,MAX(t.qmax) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS qmax
,MAX(t.qmin) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS qmin
,MAX(t.inlet_orientation) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS inlet_orientation
,MAX(t.outlet_orientation) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS outlet_orientation
,SUM(nvl(t.selling_price
,0) + nvl(t.delivery_price
,0)) over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A> ORDER BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS total_cost
,row_number() over(PARTITION BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A> ORDER BY <A HREF="V_CURRENT_QUOTE_STATUS.html">qute_id</A>) AS row_number
FROM quote_items t
,quotes q
WHERE t.qute_id = q.ID
and not (quit_type = 'AQI' and <A HREF="V_AICO.html">adit_code</A> in ('LIFTING GEAR','PURGING'))
ORDER BY <A HREF="V_CURRENT_ENQUIRY_STATUS.html">enqu_id</A>
,quote_id
,t.id) all_quote_items
WHERE row_number = 1;
</PRE>
<P>&nbsp;</P>
<P>&nbsp;</P>
</BODY>
</HTML>
Reference in New Issue View Git Blame Copy Permalink