[Home] [Help]
400: START WITH activity_metric_id = p_actmetric_id_table(l_index)
401: CONNECT BY activity_metric_id = PRIOR rollup_to_metric
402: UNION ALL
403: SELECT a.activity_metric_id, a.dirty_flag
404: FROM ams_act_metrics_all a, ams_metrics_all_b m,
405: ams_metric_formulas f,
406: ams_act_metrics_all b, ams_metrics_all_b c
407: WHERE a.metric_id = m.metric_id
408: AND m.metric_id = f.metric_id
402: UNION ALL
403: SELECT a.activity_metric_id, a.dirty_flag
404: FROM ams_act_metrics_all a, ams_metrics_all_b m,
405: ams_metric_formulas f,
406: ams_act_metrics_all b, ams_metrics_all_b c
407: WHERE a.metric_id = m.metric_id
408: AND m.metric_id = f.metric_id
409: AND b.metric_id = c.metric_id
410: AND a.arc_act_metric_used_by = b.arc_act_metric_used_by
418: AND b.activity_metric_id = p_actmetric_id_table(l_index)
419: AND a.dirty_flag <> G_IS_DIRTY
420: UNION ALL
421: SELECT a.activity_metric_id, a.dirty_flag
422: FROM ams_act_metrics_all a, ams_metrics_all_b b,
423: ams_act_metrics_all c
424: WHERE a.metric_id = b.metric_id
425: AND b.accrual_type = G_VARIABLE
426: AND a.arc_act_metric_used_by = c.arc_act_metric_used_by
451:
452: cursor c_has_quotes_enabled IS
453: SELECT count(distinct b.metric_id) metric_count,
454: count(a.activity_metric_id) activity_count
455: FROM ams_metrics_all_b b, ams_act_metrics_all a
456: WHERE b.metric_id in (244,245,246,247,248,254,255,256,257,258)
457: AND enabled_flag = G_IS_ENABLED
458: AND a.metric_id = b.metric_id
459: AND nvl(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
483: total_list_price + total_adjusted_amount)) quote_amount,
484: marketing_source_code_id
485: FROM aso_quote_headers_all G
486: WHERE G.marketing_source_code_id in (select c.source_code_id
487: from ams_act_metrics_all AL, ams_metrics_all_b ALB,
488: ams_source_codes c
489: where AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
490: AND AL.act_metric_used_by_id = C.source_code_for_id
491: AND AL.metric_id = ALB.metric_id
496: and quote_version = (select max(quote_version) from aso_quote_headers_all
497: where quote_number = g.quote_number)
498: GROUP BY marketing_source_code_id
499: ) quotes,
500: ams_act_metrics_all AL, ams_metrics_all_b ALB,
501: ams_source_codes c
502: where quotes.marketing_source_code_id(+) = c.source_code_id
503: AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
504: AND AL.act_metric_used_by_id = C.source_code_for_id
537:
538: cursor c_has_leads_quotes_enabled IS
539: SELECT count(distinct b.metric_id) metric_count,
540: count(activity_metric_id) activity_count
541: FROM ams_metrics_all_b b, ams_act_metrics_all a
542: WHERE b.metric_id in (264,265,266,267,268, -- Dead leads
543: 83,84,85,88,89, -- Leads
544: 274,275,276,277,278, -- Leads Accepted
545: 284,285,286,287,288, -- Leads to Opportunities
594: FROM as_sales_leads X, as_statuses_b Y,
595: (select distinct c.source_code_id,
596: c.arc_source_code_for, c.source_code_for_id
597: FROM ams_source_codes C,
598: ams_metrics_all_b b, ams_act_metrics_all a
599: where a.metric_id = b.metric_id
600: AND c.arc_source_code_for = a.arc_act_metric_used_by
601: and c.source_code_for_id = a.act_metric_used_by_id
602: and b.enabled_flag = G_IS_ENABLED
611: AND Y.enabled_flag = G_IS_ENABLED
612: AND NVL(X.DELETED_FLAG,G_NOT_DELETED) <> G_IS_DELETED
613: AND source_promotion_id = c.source_code_id
614: ) X,
615: ams_act_metrics_all AL, ams_metrics_all_b ALB
616: WHERE AL.arc_act_metric_used_by = X.arc_source_code_for(+)
617: AND AL.act_metric_used_by_id = X.source_code_for_id(+)
618: AND AL.metric_id = ALB.metric_id
619: AND ALB.metric_id in (83,84,85,88,89, -- Leads
631: AL.func_actual_value
632: FROM
633: ams_source_codes C,
634: as_leads_all X,
635: ams_act_metrics_all AL, ams_metrics_all_b ALB
636: WHERE X.source_promotion_id(+) = C.source_code_id
637: AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
638: AND AL.metric_id = ALB.metric_id
639: AND AL.act_metric_used_by_id = C.source_code_for_id
671: l_today date := sysdate;
672: cursor c_has_orders_enabled IS
673: SELECT count(distinct a.metric_id) metric_count,
674: count(activity_metric_id) activity_count
675: FROM ams_metrics_all_b b, ams_act_metrics_all a
676: WHERE b.metric_id in (155,156,157,158,159, -- Orders
677: 103,104,105,108,109, -- Orders amount
678: 233,234,235,236,237, -- Booked Revenue
679: 220,221,222,224,225) -- Invoiced Revenue
725: I.INVOICED_QUANTITY,
726: NVL(H.TRANSACTIONAL_CURR_CODE,G_FUNC_CURRENCY) CURRENCY_CODE
727: FROM OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES_ALL I,
728: (SELECT DISTINCT ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID, SOURCE_CODE_ID
729: FROM AMS_SOURCE_CODES C, AMS_ACT_METRICS_ALL A, AMS_METRICS_ALL_B B
730: WHERE A.ARC_ACT_METRIC_USED_BY = C.ARC_SOURCE_CODE_FOR
731: AND A.ACT_METRIC_USED_BY_ID = C.SOURCE_CODE_FOR_ID
732: AND A.METRIC_ID = B.METRIC_ID
733: AND DECODE(B.METRIC_CATEGORY,G_REVENUE_ID,
756: GROUP BY H.ARC_SOURCE_CODE_FOR, H.SOURCE_CODE_FOR_ID,
757: H.HEADER_ID,H.CURRENCY_CODE
758: ) H
759: GROUP BY ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID) T,
760: AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
761: WHERE AL.ARC_ACT_METRIC_USED_BY = T.ARC_SOURCE_CODE_FOR(+)
762: AND AL.ACT_METRIC_USED_BY_ID = T.SOURCE_CODE_FOR_ID(+)
763: AND AL.METRIC_ID = ALB.METRIC_ID
764: AND DECODE(METRIC_CATEGORY,G_REVENUE_ID,
799: l_today DATE := sysdate;
800: cursor c_has_responses_enabled IS
801: SELECT count(distinct b.metric_id) metric_count,
802: count(activity_metric_id) activity_count
803: FROM ams_metrics_all_b b, ams_act_metrics_all a
804: WHERE b.metric_id in (165,166,167,168,169)
805: AND enabled_flag = G_IS_ENABLED
806: AND a.metric_id = b.metric_id
807: AND NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS;
829: AL.activity_metric_id, AL.func_actual_value
830: FROM
831: (select arc_source_code_for, source_code_for_id, party_id
832: from jtf_ih_interactions Z, ams_source_codes C,
833: ams_act_metrics_all a, ams_metrics_all_b b
834: where c.arc_source_code_for = a.arc_act_metric_used_by
835: AND c.source_code_for_id = a.act_metric_used_by_id
836: AND a.metric_id = b.metric_id
837: and b.metric_id in (168,169,165,166,167)
842: where y.result_id = z.result_id
843: and rownum = 1
844: and positive_response_flag = G_POSITIVE_RESPONSE)
845: ) Z,
846: ams_act_metrics_all AL, ams_metrics_all_b ALB
847: WHERE AL.act_metric_used_by_id = Z.source_code_for_id(+)
848: AND AL.ARC_ACT_METRIC_USED_BY = Z.arc_source_code_for(+)
849: AND AL.metric_id = ALB.metric_id
850: and ALB.metric_id in (168,169,165,166,167)
881:
882: cursor c_has_registrations_enabled IS
883: SELECT count(distinct b.metric_id) metric_count,
884: count(activity_metric_id) activity_count
885: FROM ams_metrics_all_b b, ams_act_metrics_all a
886: WHERE b.metric_id in (123,124,127, -- Registrants
887: 143,144,147, -- Cancellations
888: 133,134,137) -- Attendees
889: AND enabled_flag = G_IS_ENABLED
921: from ams_event_registrations r,
922: (select distinct act_metric_used_by_id event_offer_id,
923: act_metric_used_by_id object_id,
924: arc_act_metric_used_by object_type
925: from ams_act_metrics_all a, ams_metrics_all_b b
926: where a.metric_id = b.metric_id
927: AND b.metric_id in (123,124,133,134,143,144)
928: AND b.enabled_flag = G_IS_ENABLED
929: AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
929: AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
930: UNION ALL
931: select distinct related_event_id, act_metric_used_by_id object_id,
932: arc_act_metric_used_by object_type
933: from ams_act_metrics_all a, ams_metrics_all_b b,
934: ams_campaign_schedules_b c
935: where a.metric_id = b.metric_id
936: AND b.metric_id in (127,137,147)
937: AND b.enabled_flag = G_IS_ENABLED
941: ) A
942: where r.event_offer_id = a.event_offer_id
943: group by object_id, object_type
944: ) A,
945: AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
946: where AL.arc_act_metric_used_by = a.object_type(+)
947: AND AL.act_metric_used_by_id = a.object_id(+)
948: and al.metric_id = alb.metric_id
949: AND ALB.metric_id in (127,137,147,123,124,133,134,143,144)
983: cursor c_has_quotes_enabled(p_object_type VARCHAR2,
984: p_object_id NUMBER) IS
985: SELECT count(distinct b.metric_id) metric_count,
986: count(a.activity_metric_id) activity_count
987: FROM ams_metrics_all_b b, ams_act_metrics_all a
988: WHERE b.metric_id in (244,245,246,247,248,254,255,256,257,258)
989: AND enabled_flag = G_IS_ENABLED
990: AND arc_act_metric_used_by = p_object_type
991: AND act_metric_used_by_id = p_object_id
994: -- BUG4924982: Get source code id in a seperate step.
995: CURSOR c_get_source_code_id (p_object_type VARCHAR2,
996: p_object_id NUMBER) IS
997: select c.source_code_id
998: from ams_act_metrics_all AL, ams_metrics_all_b ALB,
999: ams_source_codes c
1000: where AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
1001: AND AL.act_metric_used_by_id = C.source_code_for_id
1002: AND AL.metric_id = ALB.metric_id
1045: from aso_quote_headers_all
1046: where quote_number = g.quote_number)
1047: GROUP BY marketing_source_code_id
1048: ) quotes,
1049: ams_act_metrics_all AL, ams_metrics_all_b ALB,
1050: ams_source_codes c
1051: where quotes.marketing_source_code_id(+) = c.source_code_id
1052: AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
1053: AND AL.act_metric_used_by_id = C.source_code_for_id
1088: cursor c_has_leads_opps_enabled(p_object_type VARCHAR2,
1089: p_object_id NUMBER) IS
1090: SELECT count(distinct b.metric_id) metric_count,
1091: count(activity_metric_id) activity_count
1092: FROM ams_metrics_all_b b, ams_act_metrics_all a
1093: WHERE b.metric_id in (264,265,266,267,268, -- Dead leads
1094: 83,84,85,88,89, -- Leads
1095: 274,275,276,277,278, -- Leads Accepted
1096: 284,285,286,287,288, -- Leads to Opportunities
1147: FROM as_sales_leads X, as_statuses_b Y,
1148: (select distinct c.source_code_id,
1149: c.arc_source_code_for, c.source_code_for_id
1150: FROM ams_source_codes C,
1151: ams_metrics_all_b b, ams_act_metrics_all a
1152: where a.metric_id = b.metric_id
1153: AND c.arc_source_code_for = a.arc_act_metric_used_by
1154: and c.source_code_for_id = a.act_metric_used_by_id
1155: and b.enabled_flag = G_IS_ENABLED
1166: AND Y.enabled_flag = G_IS_ENABLED
1167: AND NVL(X.DELETED_FLAG,G_NOT_DELETED) <> G_IS_DELETED
1168: AND X.source_promotion_id = c.source_code_id
1169: ) X,
1170: ams_act_metrics_all AL, ams_metrics_all_b ALB
1171: WHERE AL.arc_act_metric_used_by = X.arc_source_code_for(+)
1172: AND AL.act_metric_used_by_id = X.source_code_for_id(+)
1173: AND AL.metric_id = ALB.metric_id
1174: AND ALB.metric_id in (83,84,85,88,89, -- Leads
1187: AL.func_actual_value
1188: FROM
1189: ams_source_codes C,
1190: as_leads_all X,
1191: ams_act_metrics_all AL, ams_metrics_all_b ALB
1192: WHERE X.source_promotion_id(+) = C.source_code_id
1193: AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
1194: AND AL.metric_id = ALB.metric_id
1195: AND AL.act_metric_used_by_id = C.source_code_for_id
1229: cursor c_has_orders_enabled(p_object_type VARCHAR2,
1230: p_object_id NUMBER) IS
1231: SELECT count(distinct b.metric_id) metric_count,
1232: count(activity_metric_id) activity_count
1233: FROM ams_metrics_all_b b, ams_act_metrics_all a
1234: WHERE b.metric_id in (155,156,157,158,159, -- Orders
1235: 103,104,105,108,109, -- Orders amount
1236: 233,234,235,236,237, -- Booked Revenue
1237: 220,221,222,224,225) -- Invoiced Revenue
1307: GROUP BY H.arc_source_code_for, H.source_code_for_id,
1308: h.header_id,H.currency_code
1309: ) H
1310: GROUP BY arc_source_code_for, source_code_for_id) T,
1311: ams_act_metrics_all AL, ams_metrics_all_b ALB
1312: WHERE AL.ARC_ACT_METRIC_USED_BY = T.arc_source_code_for(+)
1313: AND AL.act_metric_used_by_id = T.source_code_for_id(+)
1314: AND AL.metric_id = ALB.metric_id
1315: AND ALB.metric_id in (155,156,157,158,159, -- Orders
1349: cursor c_has_responses_enabled(p_object_type VARCHAR2,
1350: p_object_id NUMBER) IS
1351: SELECT count(distinct b.metric_id) metric_count,
1352: count(activity_metric_id) activity_count
1353: FROM ams_metrics_all_b b, ams_act_metrics_all a
1354: WHERE b.metric_id in (165,166,167,168,169)
1355: AND enabled_flag = G_IS_ENABLED
1356: AND arc_act_metric_used_by = p_object_type
1357: AND act_metric_used_by_id = p_object_id
1389: and rownum = 1)
1390: AND Z.source_code_id = C.source_code_id
1391: AND C.arc_source_code_for = p_arc_act_metric_used_by
1392: AND C.source_code_for_id = p_act_metric_used_by_id) C,
1393: ams_act_metrics_all AL, ams_metrics_all_b ALB
1394: WHERE AL.act_metric_used_by_id = C.source_code_for_id(+)
1395: AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for(+)
1396: AND AL.metric_id = ALB.metric_id
1397: AND ALB.metric_id in (165,166,167,168,169)
1431: cursor c_has_registrations_enabled(p_object_type VARCHAR2,
1432: p_object_id NUMBER) IS
1433: SELECT count(distinct b.metric_id) metric_count,
1434: count(activity_metric_id) activity_count
1435: FROM ams_metrics_all_b b, ams_act_metrics_all a
1436: WHERE b.metric_id in (123,124,127, -- Registrants
1437: 143,144,147, -- Cancellations
1438: 133,134,137) -- Attendees
1439: AND enabled_flag = G_IS_ENABLED
1475: from ams_event_registrations r,
1476: (select distinct related_event_id event_offer_id,
1477: act_metric_used_by_id object_id,
1478: arc_act_metric_used_by object_type
1479: from ams_act_metrics_all a, ams_metrics_all_b b, ams_campaign_schedules_b c
1480: where a.metric_id = b.metric_id
1481: AND b.metric_id in (127,137,147)
1482: AND a.ARC_ACT_METRIC_USED_BY = G_CSCH
1483: AND b.enabled_flag = G_IS_ENABLED
1487: ) A
1488: where r.event_offer_id = a.event_offer_id
1489: group by object_id, object_type
1490: ) A,
1491: AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
1492: where AL.arc_act_metric_used_by = a.object_type(+)
1493: AND AL.act_metric_used_by_id = a.object_id(+)
1494: AND al.metric_id = alb.metric_id
1495: AND ALB.metric_id in (127,137,147)
1523: from ams_event_registrations r,
1524: (select distinct act_metric_used_by_id event_offer_id,
1525: act_metric_used_by_id object_id,
1526: arc_act_metric_used_by object_type
1527: from ams_act_metrics_all a, ams_metrics_all_b b
1528: where a.metric_id = b.metric_id
1529: AND b.metric_id in (123,124,133,134,143,144)
1530: AND a.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1531: AND b.enabled_flag = G_IS_ENABLED
1533: ) A
1534: where r.event_offer_id = a.event_offer_id
1535: group by object_id, object_type
1536: ) A,
1537: AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
1538: where AL.arc_act_metric_used_by = a.object_type(+)
1539: AND AL.act_metric_used_by_id = a.object_id(+)
1540: and al.metric_id = alb.metric_id
1541: AND ALB.metric_id in (123,124,133,134,143,144)
1640: l_today DATE := sysdate;
1641: cursor c_has_traget_enabled IS
1642: SELECT count(distinct a.metric_id) metric_count,
1643: count(activity_metric_id) activity_count
1644: FROM ams_metrics_all_b b, ams_act_metrics_all a
1645: WHERE b.metric_id in (304,305,306,307,308, -- Contact Group
1646: 314,315,316,317,318) -- Control Group
1647: AND enabled_flag = G_IS_ENABLED
1648: AND a.metric_id = b.metric_id
1651: cursor c_has_traget_enabled_obj(p_object_type VARCHAR2,
1652: p_object_id NUMBER) IS
1653: SELECT count(distinct a.metric_id) metric_count,
1654: count(activity_metric_id) activity_count
1655: FROM ams_metrics_all_b b, ams_act_metrics_all a
1656: WHERE b.metric_id in (304,305,306,307,308, -- Contact Group
1657: 314,315,316,317,318) -- Control Group
1658: AND enabled_flag = G_IS_ENABLED
1659: AND arc_act_metric_used_by = p_object_type
1689: FROM
1690: (SELECT al.activity_metric_id,
1691: alh.no_of_rows_active, alh.no_of_rows_in_ctrl_group
1692: FROM ams_list_headers_all ALH, ams_act_metrics_all al,
1693: ams_metrics_all_b alb
1694: WHERE ALH.arc_list_used_by = al.arc_act_metric_used_by
1695: AND ALH.list_used_by_id = al.act_metric_used_by_id
1696: AND al.metric_id = alb.metric_id
1697: and alb.metric_id in (304,305,306,307,308, -- Contact Group
1698: 314,315,316,317,318) -- Control Group
1699: and alb.enabled_flag = 'Y'
1700: AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1701: ) ACT,
1702: ams_act_metrics_all AL, ams_metrics_all_b ALB
1703: WHERE ACT.activity_metric_id(+) = AL.activity_metric_id
1704: AND AL.metric_id=ALB.METRIC_ID
1705: AND ALB.metric_id in (304,305,306,307,308, -- Contact Group
1706: 314,315,316,317,318) -- Control Group
1748: FROM
1749: (SELECT al.activity_metric_id,
1750: alh.no_of_rows_active, alh.no_of_rows_in_ctrl_group
1751: FROM ams_list_headers_all ALH, ams_act_metrics_all al,
1752: ams_metrics_all_b alb
1753: WHERE ALH.arc_list_used_by = al.arc_act_metric_used_by
1754: AND ALH.list_used_by_id = al.act_metric_used_by_id
1755: AND al.metric_id = alb.metric_id
1756: and alb.metric_id in (304,305,306,307,308, -- Contact Group
1755: AND al.metric_id = alb.metric_id
1756: and alb.metric_id in (304,305,306,307,308, -- Contact Group
1757: 314,315,316,317,318) -- Control Group
1758: and alb.enabled_flag = 'Y') ACT,
1759: ams_act_metrics_all AL, ams_metrics_all_b ALB
1760: WHERE ACT.activity_metric_id(+) = AL.activity_metric_id
1761: AND AL.metric_id=ALB.METRIC_ID
1762: AND ALB.metric_id in (304,305,306,307,308, -- Contact Group
1763: 314,315,316,317,318) -- Control Group
1801: l_today DATE := sysdate;
1802: cursor c_has_traget_enabled IS
1803: SELECT count(distinct b.metric_id) metric_count,
1804: count(activity_metric_id) activity_count
1805: FROM ams_metrics_all_b b, ams_act_metrics_all a
1806: WHERE b.metric_id IN (347, 348)
1807: AND enabled_flag = G_IS_ENABLED
1808: AND a.metric_id = b.metric_id
1809: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
1841: actlist.act_list_header_id
1842: FROM ams_act_lists actlist
1843: , ams_list_entries LIST
1844: , ams_act_metrics_all a
1845: , ams_metrics_all_b b
1846: WHERE LIST.list_header_id = actlist.list_header_id
1847: AND actlist.list_act_type = 'LIST'
1848: AND EXISTS (SELECT 1
1849: FROM ams_list_entries target, ams_act_lists acttarget
1867: AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1868: AND b.metric_id = 347
1869: AND b.enabled_flag = G_IS_ENABLED
1870: ) contacts
1871: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
1872: WHERE ALB.metric_id = 347
1873: AND actmet.metric_id = ALB.metric_id
1874: AND ALB.enabled_flag = G_IS_ENABLED
1875: AND contacts.act_list_header_id (+) = actmet.act_metric_used_by_id
1889: actlist.act_list_header_id
1890: FROM ams_act_lists actlist
1891: , ams_list_entries LIST
1892: , ams_act_metrics_all a
1893: , ams_metrics_all_b b
1894: WHERE LIST.list_header_id = actlist.list_header_id
1895: AND actlist.list_act_type = 'LIST'
1896: AND EXISTS (SELECT 1
1897: FROM ams_list_entries target, ams_act_lists acttarget
1915: AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1916: AND b.metric_id = 348
1917: AND b.enabled_flag = 'Y'
1918: ) controls
1919: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
1920: WHERE ALB.metric_id = 348
1921: AND actmet.metric_id = ALB.metric_id
1922: AND ALB.enabled_flag = 'Y'
1923: AND controls.act_list_header_id (+) = actmet.act_metric_used_by_id
1959: cursor c_has_metrics_enabled IS
1960: SELECT count(decode(b.metric_id,321,1,null)) response_count,
1961: count(decode(b.metric_id,326,1,null)) leads_count,
1962: count(decode(b.metric_id,331,1,null)) opportunities_count
1963: FROM ams_metrics_all_b b, ams_act_metrics_all a
1964: WHERE b.metric_id IN (321, 326, 331)
1965: AND enabled_flag = G_IS_ENABLED
1966: AND a.metric_id = b.metric_id
1967: AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
2012: AMS_LIST_ENTRIES ENTRY,
2013: AMS_SOURCE_CODES SOURCE,
2014: AMS_LIST_SRC_TYPES SRC,
2015: HZ_RELATIONSHIPS REL,
2016: AMS_METRICS_ALL_B B,
2017: AMS_ACT_METRICS_ALL A
2018: WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
2019: AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
2020: AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
2041: AND INTER.PARTY_ID = LIST_PARTIES.PARTY_ID
2042: AND ROWNUM = 1)
2043: GROUP BY OBJECT_TYPE, OBJECT_ID
2044: ) RESP
2045: , AMS_ACT_METRICS_ALL ACTMET, AMS_METRICS_ALL_B ALB
2046: WHERE ALB.METRIC_ID = 321
2047: AND ACTMET.METRIC_ID = ALB.METRIC_ID
2048: AND ALB.ENABLED_FLAG = G_IS_ENABLED
2049: AND RESP.OBJECT_ID (+) = ACTMET.ACT_METRIC_USED_BY_ID
2092: AMS_LIST_ENTRIES ENTRY,
2093: AMS_SOURCE_CODES SOURCE,
2094: AMS_LIST_SRC_TYPES SRC,
2095: HZ_RELATIONSHIPS REL,
2096: AMS_METRICS_ALL_B B,
2097: AMS_ACT_METRICS_ALL A
2098: WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
2099: AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
2100: AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
2118: AND lead.source_promotion_id = LIST_PARTIES.source_code_id
2119: AND NVL(lead.deleted_flag, 'N') <> 'Y'
2120: AND lead.customer_id = list_parties.party_id
2121: ) leads
2122: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2123: WHERE ALB.metric_id = 326 -- metric id for leads
2124: AND actmet.metric_id = ALB.metric_id
2125: AND ALB.enabled_flag = G_IS_ENABLED
2126: AND leads.activity_metric_id = actmet.activity_metric_id
2165: AMS_LIST_ENTRIES ENTRY,
2166: AMS_SOURCE_CODES SOURCE,
2167: AMS_LIST_SRC_TYPES SRC,
2168: HZ_RELATIONSHIPS REL,
2169: AMS_METRICS_ALL_B B,
2170: AMS_ACT_METRICS_ALL A
2171: WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
2172: AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
2173: AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
2187: ) LIST_PARTIES
2188: WHERE lead.source_promotion_id = LIST_PARTIES.source_code_id
2189: AND lead.customer_id = list_parties.party_id
2190: ) opps
2191: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2192: WHERE ALB.metric_id = 331 -- metric id for opportunities
2193: AND actmet.metric_id = ALB.metric_id
2194: AND ALB.enabled_flag = G_IS_ENABLED
2195: AND opps.activity_metric_id (+) = actmet.activity_metric_id
2226: l_today DATE := sysdate;
2227: cursor c_has_orders_enabled IS
2228: SELECT count(distinct b.metric_id) metric_count,
2229: count(a.activity_metric_id) activity_count
2230: FROM ams_metrics_all_b b, ams_act_metrics_all a
2231: WHERE b.metric_id IN (336, 341, 346)
2232: AND enabled_flag = G_IS_ENABLED
2233: AND a.metric_id = b.metric_id
2234: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
2301: source_code_id, 'ALIST' object_type,
2302: ACTLIST.ACT_LIST_HEADER_ID object_id,
2303: ACTLIST.LIST_HEADER_ID
2304: from ams_source_codes source, AMS_ACT_LISTS ACTLIST,
2305: ams_metrics_all_b b, ams_act_metrics_all a
2306: where a.metric_id = b.metric_id
2307: AND b.metric_id in (336,341,346)
2308: AND b.enabled_flag = G_IS_ENABLED
2309: AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2341: ) H
2342: GROUP BY H.object_type, H.object_id, H.header_id,H.currency_code
2343: ) H
2344: GROUP BY object_type, object_id) T,
2345: ams_act_metrics_all AL, ams_metrics_all_b ALB
2346: WHERE AL.ARC_ACT_METRIC_USED_BY = T.object_type(+)
2347: AND AL.act_metric_used_by_id = T.object_id(+)
2348: AND ALB.metric_id IN (336,341,346)
2349: AND AL.metric_id = ALB.metric_id
2422: l_actual_values_table num_table_type;
2423: l_activity_metric_id_table num_table_type;
2424: cursor c_has_inferred_enabled(object_type varchar2, object_id number) IS
2425: SELECT count(1)
2426: FROM ams_metrics_all_b b, ams_act_metrics_all a
2427: WHERE a.metric_id IN (361,362)
2428: AND enabled_flag = G_IS_ENABLED
2429: and a.metric_id = b.metric_id
2430: and a.arc_act_metric_used_by = object_type
2455: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2456: AMS_LIST_HEADERS_ALL LHA ,
2457: AMS_LIST_ENTRIES ENTRY ,
2458: AMS_ACT_METRICS_ALL AM ,
2459: AMS_METRICS_ALL_B MB,
2460: HZ_RELATIONSHIPS REL,
2461: AMS_LIST_SRC_TYPES SRC
2462: WHERE AM.METRIC_ID = MB.METRIC_ID
2463: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2491: and csch_parties.last_activation_date + l_inferred_period
2492: and rownum = 1)
2493: group by ACTIVITY_METRIC_ID
2494: ) resp
2495: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2496: WHERE ALB.metric_id in (361,362)
2497: AND actmet.metric_id = alb.metric_id
2498: AND ALB.enabled_flag = 'Y'
2499: and actmet.arc_act_metric_used_by = 'CSCH'
2525: l_today DATE := sysdate;
2526: cursor c_has_inferred_enabled IS
2527: SELECT count(distinct b.metric_id) metric_count,
2528: count(activity_metric_id) activity_count
2529: FROM ams_metrics_all_b b, ams_act_metrics_all a
2530: WHERE b.metric_id IN (361,362)
2531: AND enabled_flag = G_IS_ENABLED
2532: AND a.metric_id = b.metric_id
2533: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
2562: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2563: AMS_LIST_HEADERS_ALL LHA ,
2564: AMS_LIST_ENTRIES ENTRY ,
2565: AMS_ACT_METRICS_ALL AM ,
2566: AMS_METRICS_ALL_B MB,
2567: HZ_RELATIONSHIPS REL,
2568: AMS_LIST_SRC_TYPES SRC
2569: WHERE AM.METRIC_ID = MB.METRIC_ID
2570: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2598: and csch_parties.last_activation_date + l_inferred_period
2599: and rownum = 1)
2600: group by ACTIVITY_METRIC_ID
2601: ) resp
2602: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2603: WHERE ALB.metric_id in (361,362)
2604: AND actmet.metric_id = ALB.metric_id
2605: AND ALB.enabled_flag = 'Y'
2606: and l_today - G_CALC_LAG_DAYS < nvl(actmet.last_calculated_date,l_today)
2632: l_actual_values_table num_table_type;
2633: l_activity_metric_id_table num_table_type;
2634: cursor c_has_inferred_enabled(object_type varchar2, object_id number) IS
2635: SELECT count(1)
2636: FROM ams_metrics_all_b b, ams_act_metrics_all a
2637: WHERE a.metric_id IN (371,372)
2638: AND enabled_flag = G_IS_ENABLED
2639: and a.metric_id = b.metric_id
2640: and a.arc_act_metric_used_by = object_type
2665: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2666: AMS_LIST_HEADERS_ALL LHA ,
2667: AMS_LIST_ENTRIES ENTRY ,
2668: AMS_ACT_METRICS_ALL AM ,
2669: AMS_METRICS_ALL_B MB,
2670: HZ_RELATIONSHIPS REL,
2671: AMS_LIST_SRC_TYPES SRC
2672: WHERE AM.METRIC_ID = MB.METRIC_ID
2673: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2710: )
2711: and rownum = 1
2712: )
2713: group by activity_metric_id) leads,
2714: ams_act_metrics_all am, ams_metrics_all_b mb
2715: WHERE am.metric_id = mb.metric_id
2716: and am.arc_act_metric_used_by = 'CSCH'
2717: and mb.enabled_flag = 'Y'
2718: and mb.metric_id in (371,372)
2743: l_today DATE := sysdate;
2744: cursor c_has_inferred_enabled IS
2745: SELECT count(distinct b.metric_id) metric_count,
2746: count(activity_metric_id) activity_count
2747: FROM ams_metrics_all_b b, ams_act_metrics_all a
2748: WHERE b.metric_id IN (371,372)
2749: AND enabled_flag = G_IS_ENABLED
2750: AND a.metric_id = b.metric_id
2751: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
2780: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2781: AMS_LIST_HEADERS_ALL LHA ,
2782: AMS_LIST_ENTRIES ENTRY ,
2783: AMS_ACT_METRICS_ALL AM ,
2784: AMS_METRICS_ALL_B MB,
2785: HZ_RELATIONSHIPS REL,
2786: AMS_LIST_SRC_TYPES SRC
2787: WHERE AM.METRIC_ID = MB.METRIC_ID
2788: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2825: )
2826: and rownum = 1
2827: )
2828: group by activity_metric_id) leads,
2829: ams_act_metrics_all am, ams_metrics_all_b mb
2830: WHERE am.metric_id = mb.metric_id
2831: and am.arc_act_metric_used_by = 'CSCH'
2832: and mb.enabled_flag = 'Y'
2833: and mb.metric_id in (371,372)
2859: l_actual_values_table num_table_type;
2860: l_activity_metric_id_table num_table_type;
2861: cursor c_has_inferred_enabled(object_type varchar2, object_id number) IS
2862: SELECT count(1)
2863: FROM ams_metrics_all_b b, ams_act_metrics_all a
2864: WHERE a.metric_id IN (381,382,391,392)
2865: AND enabled_flag = G_IS_ENABLED
2866: and a.metric_id = b.metric_id
2867: and a.arc_act_metric_used_by = object_type
2907: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2908: AMS_LIST_HEADERS_ALL LHA ,
2909: AMS_LIST_ENTRIES ENTRY ,
2910: AMS_ACT_METRICS_ALL AM ,
2911: AMS_METRICS_ALL_B MB,
2912: HZ_RELATIONSHIPS REL,
2913: AMS_LIST_SRC_TYPES SRC
2914: WHERE AM.METRIC_ID = MB.METRIC_ID
2915: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2965: primary_party_id, contact_rel_party_id
2966: ) csch_orders
2967: GROUP BY csch_orders.activity_metric_id
2968: ) T,
2969: ams_act_metrics_all AL, ams_metrics_all_b ALB
2970: WHERE ALB.metric_id IN (391,381,392,382)
2971: AND AL.metric_id = ALB.metric_id
2972: AND ALB.enabled_flag = 'Y'
2973: AND AL.activity_metric_id = t.activity_metric_id(+)
3000: l_today DATE := sysdate;
3001: cursor c_has_inferred_enabled IS
3002: SELECT count(distinct b.metric_id) metric_count,
3003: count(activity_metric_id) activity_count
3004: FROM ams_metrics_all_b b, ams_act_metrics_all a
3005: WHERE b.metric_id IN (381,382,391,392)
3006: AND enabled_flag = G_IS_ENABLED
3007: AND a.metric_id = b.metric_id
3008: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3048: 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
3049: CONTACT_REL_PARTY_ID,
3050: CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
3051: SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3052: FROM AMS_METRICS_ALL_B MB,
3053: AMS_ACT_METRICS_ALL AM ,
3054: AMS_CAMPAIGN_SCHEDULES_B CS ,
3055: AMS_LIST_HEADERS_ALL LHA ,
3056: AMS_LIST_ENTRIES ENTRY ,
3110: primary_party_id, contact_rel_party_id
3111: ) csch_orders
3112: GROUP BY csch_orders.activity_metric_id
3113: ) T,
3114: ams_act_metrics_all AL, ams_metrics_all_b ALB
3115: WHERE ALB.metric_id IN (391,381,392,382)
3116: AND AL.metric_id = ALB.metric_id
3117: AND ALB.enabled_flag = 'Y'
3118: and l_today - G_CALC_LAG_DAYS < nvl(AL.last_calculated_date,l_today)
3177: l_today DATE := sysdate;
3178: cursor c_has_inferred_enabled IS
3179: SELECT count(distinct b.metric_id) metric_count,
3180: count(activity_metric_id) activity_count
3181: FROM ams_metrics_all_b b, ams_act_metrics_all a
3182: WHERE b.metric_id IN (361,362)
3183: AND enabled_flag = G_IS_ENABLED
3184: AND a.metric_id = b.metric_id
3185: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3213: CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
3214: SRC.SOURCE_CATEGORY
3215: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
3216: AMS_LIST_HEADERS_ALL LHA , AMS_LIST_ENTRIES ENTRY ,
3217: AMS_ACT_METRICS_ALL AM , AMS_METRICS_ALL_B MB, HZ_RELATIONSHIPS REL,
3218: AMS_LIST_SRC_TYPES SRC
3219: WHERE AM.METRIC_ID = MB.METRIC_ID
3220: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
3221: AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
3255: -- and csch_parties.last_activation_date + 90
3256: and rownum = 1)
3257: group by ACTIVITY_METRIC_ID
3258: ) resp
3259: , ams_act_metrics_all actmet, ams_metrics_all_b ALB
3260: WHERE ALB.metric_id in (361,362)
3261: AND actmet.metric_id = ALB.metric_id
3262: AND ALB.enabled_flag = 'Y'
3263: and l_today - G_CALC_LAG_DAYS < nvl(actmet.last_calculated_date,l_today)
3291: l_today DATE := sysdate;
3292: cursor c_has_inferred_enabled IS
3293: SELECT count(distinct b.metric_id) metric_count,
3294: count(activity_metric_id) activity_count
3295: FROM ams_metrics_all_b b, ams_act_metrics_all a
3296: WHERE b.metric_id IN (381,382,391,392)
3297: AND enabled_flag = G_IS_ENABLED
3298: AND a.metric_id = b.metric_id
3299: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3320: 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3321: decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
3322: 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null) CONTACT_REL_PARTY_ID,
3323: CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID, SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3324: FROM AMS_METRICS_ALL_B MB,
3325: AMS_ACT_METRICS_ALL AM ,
3326: AMS_CAMPAIGN_SCHEDULES_B CS ,
3327: AMS_LIST_HEADERS_ALL LHA ,
3328: AMS_LIST_ENTRIES ENTRY ,
3356: ''B2B'', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3357: decode(SRC.SOURCE_CATEGORY, ''B2C'', NULL,
3358: ''B2B'', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null) CONTACT_REL_PARTY_ID,
3359: CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID, SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3360: FROM AMS_METRICS_ALL_B MB,
3361: AMS_ACT_METRICS_ALL AM ,
3362: AMS_CAMPAIGN_SCHEDULES_B CS ,
3363: AMS_LIST_HEADERS_ALL LHA ,
3364: AMS_LIST_ENTRIES ENTRY ,
3441: primary_party_id, contact_rel_party_id
3442: ) csch_orders
3443: GROUP BY csch_orders.activity_metric_id
3444: ) T,
3445: ams_act_metrics_all AL, ams_metrics_all_b ALB
3446: WHERE ALB.metric_id IN (391,381,392,382)
3447: AND AL.metric_id = ALB.metric_id
3448: AND ALB.enabled_flag = 'Y'
3449: -- and sysdate - 90 < nvl(AL.last_calculated_date,sysdate)
3477: l_today DATE := sysdate;
3478: cursor c_has_inferred_enabled IS
3479: SELECT count(distinct b.metric_id) metric_count,
3480: count(activity_metric_id) activity_count
3481: FROM ams_metrics_all_b b, ams_act_metrics_all a
3482: WHERE b.metric_id IN (371,372)
3483: AND enabled_flag = G_IS_ENABLED
3484: AND a.metric_id = b.metric_id
3485: AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3511: FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
3512: AMS_LIST_HEADERS_ALL LHA ,
3513: AMS_LIST_ENTRIES ENTRY ,
3514: AMS_ACT_METRICS_ALL AM ,
3515: AMS_METRICS_ALL_B MB,
3516: HZ_RELATIONSHIPS REL,
3517: AMS_LIST_SRC_TYPES SRC
3518: WHERE AM.METRIC_ID = MB.METRIC_ID
3519: AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
3564: )
3565: and rownum = 1
3566: )
3567: group by activity_metric_id) leads,
3568: ams_act_metrics_all am, ams_metrics_all_b mb
3569: WHERE am.metric_id = mb.metric_id
3570: and am.arc_act_metric_used_by = 'CSCH'
3571: and mb.enabled_flag = 'Y'
3572: and mb.metric_id in (371,372)