/************************************************************************** * Fetches associated contractor information from database as XML data. * * '*assetid*' will be replaced with current propref * * '*clientid*' will be replaced with current clientid * * If more contract types are created, additional unions are required. * **************************************************************************/ SELECT 1 AS Tag, NULL AS Parent, CT.ct_conttyperef AS [Contract!1!Created!hide], CT.ct_code AS [Contract!1!type], NULL AS [Contractor!2!Type!Element], NULL AS [Contractor!2!Code!Element], NULL AS [Contractor!2!Name!Element], NULL AS [Contractor!2!Address!Element], NULL AS [Contractor!2!Tel!Element], NULL AS [Contractor!2!Fax!Element], NULL AS [Contractor!2!OOH_Tel!Element], NULL AS [Contractor!2!From!Element], NULL AS [Contractor!2!To!Element] FROM cc_property_contracts PC INNER JOIN cc_contractors C ON PC.pc_contref = C.c_contref AND PC.pc_clientref = C.c_clientref INNER JOIN cc_contract_types CT ON PC.pc_conttyperef = CT.ct_conttyperef WHERE (PC.pc_propref = N'*assetid*') AND (PC.pc_clientref = N'*clientid*') UNION ALL SELECT 2 AS Tag, 1 AS Parent, CT.ct_conttyperef AS [Contract!1!Created!hide], CT.ct_code AS [Contract!1!type], CT.ct_code AS [Contractor!2!Type!Element], C.c_contref AS [Contractor!2!Code!Element], C.c_name AS [Contractor!2!Name!Element], C.c_address AS [Contractor!2!Address!Element], C.c_telephone AS [Contractor!2!Tel!Element], C.c_fax AS [Contractor!2!Fax!Element], C.c_ooh_tel AS [Contractor!2!OOH_Tel!Element], NULL AS [Contractor!2!From!Element], NULL AS [Contractor!2!To!Element] FROM cc_properties P INNER JOIN cc_property_contracts PC ON P.p_propref = PC.pc_propref AND P.p_clientref = PC.pc_clientref INNER JOIN cc_contractors C ON PC.pc_contref = C.c_contref AND PC.pc_clientref = C.c_clientref INNER JOIN cc_contract_types CT ON PC.pc_conttyperef = CT.ct_conttyperef WHERE (P.p_propref = N'*assetid*') AND (P.p_clientref = N'*clientid*') AND (PC.pc_conttyperef = 1) UNION ALL SELECT 2 AS Tag, 1 AS Parent, CT.ct_conttyperef AS [Contract!1!Created!hide], CT.ct_code AS [Contract!1!type], CT.ct_code AS [Contractor!2!Type!Element], C.c_contref AS [Contractor!2!Code!Element], C.c_name AS [Contractor!2!Name!Element], C.c_address AS [Contractor!2!Address!Element], C.c_telephone AS [Contractor!2!Tel!Element], C.c_fax AS [Contractor!2!Fax!Element], C.c_ooh_tel AS [Contractor!2!OOH_Tel!Element], NULL AS [Contractor!2!From!Element], NULL AS [Contractor!2!To!Element] FROM cc_properties P INNER JOIN cc_property_contracts PC ON P.p_propref = PC.pc_propref AND P.p_clientref = PC.pc_clientref INNER JOIN cc_contractors C ON PC.pc_contref = C.c_contref AND PC.pc_clientref = C.c_clientref INNER JOIN cc_contract_types CT ON PC.pc_conttyperef = CT.ct_conttyperef WHERE (P.p_propref = N'*assetid*') AND (P.p_clientref = N'*clientid*') AND (PC.pc_conttyperef = 2) UNION ALL SELECT 2 AS Tag, 1 AS Parent, CT.ct_conttyperef AS [Contract!1!Created!hide], CT.ct_code AS [Contract!1!type], CT.ct_code AS [Contractor!2!Type!Element], C.c_contref AS [Contractor!2!Code!Element], C.c_name AS [Contractor!2!Name!Element], C.c_address AS [Contractor!2!Address!Element], C.c_telephone AS [Contractor!2!Tel!Element], C.c_fax AS [Contractor!2!Fax!Element], C.c_ooh_tel AS [Contractor!2!OOH_Tel!Element], NULL AS [Contractor!2!From!Element], NULL AS [Contractor!2!To!Element] FROM cc_properties P INNER JOIN cc_property_contracts PC ON P.p_propref = PC.pc_propref AND P.p_clientref = PC.pc_clientref INNER JOIN cc_contractors C ON PC.pc_contref = C.c_contref AND PC.pc_clientref = C.c_clientref INNER JOIN cc_contract_types CT ON PC.pc_conttyperef = CT.ct_conttyperef WHERE (P.p_propref = N'*assetid*') AND (P.p_clientref = N'*clientid*') AND (PC.pc_conttyperef = 3) UNION ALL SELECT 2 AS Tag, 1 AS Parent, CT.ct_conttyperef AS [Contract!1!Created!hide], CT.ct_code AS [Contract!1!type], CT.ct_code AS [Contractor!2!Type!Element], C.c_contref AS [Contractor!2!Code!Element], C.c_name AS [Contractor!2!Name!Element], C.c_address AS [Contractor!2!Address!Element], C.c_telephone AS [Contractor!2!Tel!Element], C.c_fax AS [Contractor!2!Fax!Element], C.c_ooh_tel AS [Contractor!2!OOH_Tel!Element], convert(char(10),PC.pc_indefect_from,103) AS [Contractor!2!From!Element], convert(char(10),PC.pc_indefect_to,103) AS [Contractor!2!To!Element] FROM cc_properties P INNER JOIN cc_property_contracts PC ON P.p_propref = PC.pc_propref AND P.p_clientref = PC.pc_clientref INNER JOIN cc_contractors C ON PC.pc_contref = C.c_contref AND PC.pc_clientref = C.c_clientref INNER JOIN cc_contract_types CT ON PC.pc_conttyperef = CT.ct_conttyperef WHERE (P.p_propref = N'*assetid*') AND (P.p_clientref = N'*clientid*') AND (PC.pc_conttyperef = 4) ORDER BY 3,4,5 FOR XML EXPLICIT