[Home] [Help]
98: ---- forward procedure declarations ----
99:
100: ---------------------------------------------------------------
101: -- Purpose:
102: -- Insert parties into ams_dm_target_stg_gt using
103: -- different methods based on the data source
104: -- type.
105: -- Parameter:
106: --
785:
786:
787: --
788: -- NOTE
789: -- ams_dm_target_stg_gt used for calculating the summary of all the selected
790: -- data sources.
791: -- History
792: -- 16-Feb-2001 choang Created.
793: -- 06-Jun-2002 choang Alternative data source has different filter
886:
887:
888: -- nyostos - Sep 15, 2003 - Use Global Temporart Table
889: --l_insert_clause := 'INSERT INTO ams_dm_target_stg (arc_object_used_by, ';
890: l_insert_clause := 'INSERT INTO ams_dm_target_stg_gt (arc_object_used_by, ';
891: l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
892: l_insert_clause := l_insert_clause || 'object_id, party_id) SELECT ';
893:
894: IF p_seeded_data_source THEN
1019: END IF;
1020: END LOOP; -- for cursor
1021:
1022: IF l_is_b2b AND l_model_type='CUSTOMER_PROFITABILITY' THEN
1023: -- l_insert_string := 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id) ';
1024: -- l_insert_string := l_insert_string || 'SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id ';
1025: -- l_insert_string := l_insert_string || 'FROM ams_dm_orgcont_stg_gt ';
1026: -- l_insert_string := l_insert_string || 'GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id ';
1027: EXECUTE IMMEDIATE 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id)
1023: -- l_insert_string := 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id) ';
1024: -- l_insert_string := l_insert_string || 'SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id ';
1025: -- l_insert_string := l_insert_string || 'FROM ams_dm_orgcont_stg_gt ';
1026: -- l_insert_string := l_insert_string || 'GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id ';
1027: EXECUTE IMMEDIATE 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id)
1028: SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id
1029: FROM ams_dm_org_contacts GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id';
1030: END IF;
1031:
1200: -- using the from and where clauses of
1201: -- the SQL from ams_discoverer_sql.
1202: EXECUTE IMMEDIATE
1203: -- 'INSERT INTO ams_dm_target_stg ' ||
1204: 'INSERT INTO ams_dm_target_stg_gt ' ||
1205: '(arc_object_used_by, object_used_by_id, arc_object, object_id, party_id) ' ||
1206: 'SELECT :arc_object' || ', :object_id' || ', :disco_wb, :disco_id' || ', ' || l_source_pk_field ||
1207: ' ' || l_from_and_where
1208: USING p_arc_object_for, p_object_for_id, 'DIWB', p_discoverer_sql_id;
1296: end if;
1297: END IF;
1298:
1299: -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_target_stg';
1300: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_target_stg_gt';
1301:
1302: -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_inter_source_stg';
1303: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_ams_schema || '.ams_dm_int_src_stg_gt ';
1304:
1573: , 'N'
1574: , NULL
1575: , DECODE (t.target_flag, 'Y', l_target_value, '0')
1576: -- FROM ams_dm_target_stg t, HZ_PARTIES p
1577: FROM ams_dm_target_stg_gt t, HZ_PARTIES p
1578: WHERE t.arc_object_used_by = p_arc_object_for
1579: AND t.object_used_by_id = p_object_for_id
1580: AND t.arc_object = l_sources_rec.arc_incl_object_from
1581: AND t.object_id = l_sources_rec.incl_object_id
1603: , 'N'
1604: , NULL
1605: , DECODE (t.target_flag, 'Y', l_target_value, '0')
1606: -- FROM ams_dm_target_stg t , HZ_PARTIES p
1607: FROM ams_dm_target_stg_gt t , HZ_PARTIES p
1608: WHERE t.arc_object_used_by = p_arc_object_for
1609: AND t.object_used_by_id = p_object_for_id
1610: AND t.arc_object = l_sources_rec.arc_incl_object_from
1611: AND t.object_id = l_sources_rec.incl_object_id
1624: DELETE FROM ams_dm_int_src_stg_gt i
1625: WHERE arc_object_used_by = p_arc_object_for
1626: AND object_used_by_id = p_object_for_id
1627: AND EXISTS (SELECT 1
1628: FROM ams_dm_target_stg_gt t -- ams_dm_target_stg t
1629: WHERE t.arc_object_used_by = p_arc_object_for
1630: AND t.object_used_by_id = p_object_for_id
1631: AND t.arc_object = l_sources_rec.arc_incl_object_from
1632: AND t.object_id = l_sources_rec.incl_object_id
1639: WHERE arc_object_used_by = p_arc_object_for
1640: AND object_used_by_id = p_object_for_id
1641: AND NOT EXISTS (SELECT 1
1642: -- FROM ams_dm_target_stg t
1643: FROM ams_dm_target_stg_gt t
1644: WHERE t.arc_object_used_by = p_arc_object_for
1645: AND t.object_used_by_id = p_object_for_id
1646: AND t.arc_object = l_sources_rec.arc_incl_object_from
1647: AND t.object_id = l_sources_rec.incl_object_id
1669: , 'N'
1670: , NULL
1671: , DECODE (target_flag, 'Y', l_target_value, '0')
1672: -- FROM ams_dm_target_stg
1673: FROM ams_dm_target_stg_gt
1674: WHERE arc_object_used_by = p_arc_object_for
1675: AND object_used_by_id = p_object_for_id
1676: ;
1677: END IF;
1985: -- a competitor.
1986:
1987: IF l_is_b2b THEN
1988: --UPDATE ams_dm_target_stg t
1989: UPDATE ams_dm_target_stg_gt t
1990: SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
1991: FROM dual
1992: -- choang - 21-nov-2003 - bug 3275817
1993: -- changed to not exists and having clause
2012: WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2013: AND object_used_by_id = p_model_id;
2014: ELSE
2015: -- UPDATE ams_dm_target_stg t
2016: UPDATE ams_dm_target_stg_gt t
2017: SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2018: FROM dual
2019: -- choang - 21-nov-2003 - bug 3275817
2020: -- changed to not exists and having clause
2033: AND object_used_by_id = p_model_id;
2034: END IF;
2035: ELSIF l_model_rec.model_type IN ('EMAIL', 'DIRECTMAIL', 'TELEMARKETING') THEN
2036: -- UPDATE ams_dm_target_stg t
2037: UPDATE ams_dm_target_stg_gt t
2038: SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2039: FROM dual
2040: WHERE EXISTS (SELECT 1
2041: FROM ams_campaign_schedules_b c, jtf_ih_interactions i,
2072: l_positive_values_sql := ' ' || l_target_source_object || '.' || l_target_field || ' = tv.target_value';
2073: END IF;
2074:
2075: -- l_sql := 'UPDATE ams_dm_target_stg t';
2076: l_sql := 'UPDATE ams_dm_target_stg_gt t';
2077: l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
2078: l_sql := l_sql || ' FROM dual WHERE EXISTS (SELECT 1';
2079:
2080: IF l_data_source_id = l_target_source_id THEN
2143:
2144:
2145: IF l_is_b2b AND l_is_org_prod_affn = false THEN
2146: -- UPDATE ams_dm_target_stg t
2147: UPDATE ams_dm_target_stg_gt t
2148: SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2149: FROM dual
2150: WHERE EXISTS (SELECT 1
2151: FROM AMS_ACT_PRODUCTS aa, OE_ORDER_HEADERS_ALL oh, OE_ORDER_LINES_ALL ol,
2180: WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2181: AND object_used_by_id = p_model_id;
2182: ELSE
2183: -- UPDATE ams_dm_target_stg t
2184: UPDATE ams_dm_target_stg_gt t
2185: SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
2186: FROM dual
2187: WHERE EXISTS (SELECT 1
2188: FROM AMS_ACT_PRODUCTS aa,
2250: -- but the data contains alphanumeric data, a
2251: -- database error could be raised
2252: BEGIN
2253: -- l_sql := 'UPDATE ams_dm_target_stg t';
2254: l_sql := 'UPDATE ams_dm_target_stg_gt t';
2255: l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
2256: IF l_data_source_id = l_target_source_id THEN
2257: l_sql := l_sql || ' FROM ' || l_target_source_object_full;
2258: ELSE
2287: USING G_OBJECT_TYPE_MODEL, p_model_id;
2288: END IF;
2289:
2290: -- UPDATE ams_dm_target_stg
2291: UPDATE ams_dm_target_stg_gt
2292: SET target_flag = L_NEGATIVE_TARGET_VALUE
2293: WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
2294: AND object_used_by_id = p_model_id
2295: AND target_flag IS NULL;
2341: object_id,
2342: COUNT(*) total_selected, -- total selected
2343: SUM (DECODE (target_flag, 'Y', 1, 0)) total_targeted
2344: -- FROM ams_dm_target_stg
2345: FROM ams_dm_target_stg_gt
2346: WHERE arc_object_used_by = p_arc_object_for
2347: AND object_used_by_id = p_object_for_id
2348: GROUP BY arc_object, object_id
2349: ;
2394:
2395: -- Note
2396: -- Different between this procedure and populate_target
2397: -- is that this procedure updates amd_dm_source versus
2398: -- ams_dm_target_stg_gt. This procedure also populates
2399: -- 0 or 1 for the target value due to ODM limitations
2400: -- in creating a balanced data set -- they can't use
2401: -- Y or N in the calculation of the balanced data set.
2402: --