andy/mip
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
Files
master
mip/Documentation/pldoc/V_CURRENT_PARTY_ADDRESSES.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

288 lines
6.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_CURRENT_PARTY_ADDRESSES</TITLE>
<META name="object" content="VIEW">
<META name="name" content="V_CURRENT_PARTY_ADDRESSES">
<META name="description" content="">
<META name="columns" content="ID ADDRESS_CODE SUB_BUILDING BUILDING STREET CITY POSTCODE START_DATE END_DATE COMMENTS RT_CODE MAX_START_DATE">
</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_CURRENT_PARTY_ADDRESSES</P>
</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">
<A HREF="V_AICO.html">ID</A>
</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">
<A HREF="V_PARTY_ADDRESSES.html">ADDRESS_CODE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(80)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</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">
<A HREF="V_PARTY_ADDRESSES.html">SUB_BUILDING</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(40)
</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">
<A HREF="V_PARTY_ADDRESSES.html">BUILDING</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(40)
</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">
<A HREF="V_PARTY_ADDRESSES.html">STREET</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(60)
</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">
<A HREF="V_PARTY_ADDRESSES.html">CITY</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(40)
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
&nbsp;
</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">
<A HREF="V_PARTY_ADDRESSES.html">POSTCODE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(8)
</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">
<A HREF="V_CURRENT_CONTACT_MECHANISMS.html">START_DATE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
DATE
</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">
<A HREF="V_CURRENT_CONTACT_MECHANISMS.html">END_DATE</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
DATE
</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">
<A HREF="V_CURRENT_CONTACT_MECHANISMS.html">COMMENTS</A>
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
VARCHAR2(255)
</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">
<A HREF="V_LAST_ENQUIRY_UPDATE.html">RT_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">
MAX_START_DATE
</TD>
<TD VALIGN="TOP" CLASS="DESC_TEXT">
DATE
</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_CURRENT_PARTY_ADDRESSES AS
SELECT "ID","ADDRESS_CODE","SUB_BUILDING","BUILDING","STREET","CITY","POSTCODE","START_DATE","END_DATE","COMMENTS","RT_CODE","MAX_START_DATE"
FROM (SELECT parties.id,
addresses.code AS <A HREF="V_PARTY_ADDRESSES.html">address_code</A>,
addresses.sub_building,
addresses.building,
addresses.street,
addresses.city,
addresses.postcode,
party_addresses.start_date,
party_addresses.end_date,
party_addresses.comments,
party_address_roles.rt_code,
MAX(party_addresses.start_date) over(PARTITION BY parties.ID, <A HREF="V_LAST_ENQUIRY_UPDATE.html">rt_code</A>) AS max_start_date
FROM addresses,
party_addresses,
party_address_roles,
parties
WHERE addresses.code = party_addresses.addr_code
AND party_addresses.start_date = party_address_roles.paddr_start_date
AND party_addresses.addr_code = party_address_roles.paddr_addr_code
AND party_addresses.prty_id = party_address_roles.paddr_prty_id
AND parties.id = party_addresses.prty_id)
WHERE <A HREF="V_CURRENT_CONTACT_MECHANISMS.html">start_date</A> = max_start_date
ORDER BY ID;
</PRE>
<P>&nbsp;</P>
<P>&nbsp;</P>
</BODY>
</HTML>
Reference in New Issue View Git Blame Copy Permalink