[Home] [Help]
402: a.fund_amount_fc fund_amount_fc,
403: e.source_code_id offer_source_code_id
404: FROM ams_event_offers_all_b A,
405: ams_event_headers_all_b B,
406: ams_source_codes E
407: WHERE e.source_code = a.source_code
408: AND a.event_header_id = b.event_header_id
409: AND a.system_status_code in ('ACTIVE', 'CANCELLED','COMPLETED','CLOSED')
410: AND B.active_from_date > c_start_date;
427: a.currency_code_fc currency_code_fc,
428: a.fund_amount_fc fund_amount_fc,
429: e.source_code_id offer_source_code_id
430: FROM ams_event_offers_all_b A,
431: ams_source_codes E
432: WHERE e.source_code = a.source_code
433: AND a.event_standalone_flag = 'Y'
434: AND (a.parent_type is NULL or a.parent_type = 'RCAM')
435: AND a.system_status_code in ('ACTIVE', 'CANCELLED','COMPLETED','CLOSED')
689: e.source_code_id offer_source_code_id,
690: trunc(dat.trdate) tr_date
691: FROM
692: ams_event_headers_all_b a,
693: ams_source_codes E,
694: bim_intl_dates DAT
695: WHERE dat.trdate between a.active_from_date and
696: decode(greatest(a.active_to_date,p_end_datel),a.active_to_date,p_end_datel,NULL,p_end_datel,a.active_to_date) + 0.99999
697: AND a.active_from_date+0 >= p_start_datel
728: ,sum(decode(b.OPP_OPEN_STATUS_FLAG,'Y',decode(a.status_code,'NEW',convert_currency(nvl(a.currency_code,'USD'),nvl(a.budget_amount,0)),0),0)) leads_new_amt
729: FROM as_sales_leads A,
730: as_statuses_b B,
731: ams_event_headers_all_b C,
732: ams_source_codes E
733: WHERE a.status_code = b.status_code
734: AND e.source_code = c.source_code
735: AND b.lead_flag = 'Y'
736: AND b.enabled_flag = 'Y'
745: sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD'),
746: nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) booked_orders_amt,
747: i.creation_date creation_date
748: from ams_event_headers_all_b B,
749: ams_source_codes C ,
750: as_sales_leads D,
751: as_sales_lead_opportunity A,
752: as_leads_all E,
753: aso_quote_related_objects F,
773: COUNT(A.lead_id) nooppor,
774: SUM(convert_currency(nvl(currency_code, 'USD'), nvl(A.total_amount, 0))) opportunity_amt
775: FROM as_leads_all A,
776: ams_event_headers_all_b D,
777: ams_source_codes E
778: where e.source_code = d.source_code
779: and e.source_code_id = a.source_promotion_id
780: GROUP BY d.event_header_id, trunc(a.creation_date)) G
781: WHERE f.event_header_id(+) = ad.event_header_id
1025: e.source_code_id offer_source_code_id,
1026: trunc(dat.trdate) tr_date
1027: FROM ams_event_offers_all_b A,
1028: ams_event_headers_all_b B,
1029: ams_source_codes E,
1030: bim_intl_dates DAT
1031: WHERE dat.trdate between a.event_start_date and
1032: decode(greatest(a.event_end_date,p_end_datel),a.event_end_date,p_end_datel,NULL,p_end_datel,a.event_end_date) + 0.99999
1033: AND a.event_start_date+0 >= p_start_datel
1079: ,sum(decode(b.OPP_OPEN_STATUS_FLAG,'Y',decode(a.status_code,'NEW',convert_currency(nvl(a.currency_code,'USD'),nvl(a.budget_amount,0)),0),0)) leads_new_amt
1080: FROM as_sales_leads A,
1081: as_statuses_b B,
1082: ams_event_offers_all_b C,
1083: ams_source_codes E
1084: WHERE a.status_code = b.status_code
1085: AND e.source_code = c.source_code
1086: AND b.lead_flag = 'Y'
1087: AND b.enabled_flag = 'Y'
1097: SUM(convert_currency(nvl(currency_code, 'USD'), nvl(A.total_amount, 0))) opportunity_amt
1098: FROM as_leads_all A,
1099: ams_event_offers_all_b C,
1100: ams_event_headers_all_b D,
1101: ams_source_codes E
1102: where e.source_code = c.source_code
1103: AND c.event_header_id = d.event_header_id
1104: and e.source_code_id = a.source_promotion_id
1105: GROUP BY trunc(a.creation_date),
1732: ,0 booked_orders
1733: ,0 booked_orders_amt
1734: FROM as_leads_all A,
1735: ams_event_offers_all_b C,
1736: ams_source_codes E
1737: WHERE e.source_code_for_id = c.event_offer_id
1738: AND c.event_standalone_flag = 'Y'
1739: AND (c.parent_type is null or c.parent_type ='RCAM')
1740: AND e.source_code_id = a.source_promotion_id
1770: ,0 booked_orders_amt
1771: FROM as_sales_leads A,
1772: as_statuses_b B,
1773: ams_event_offers_all_b C,
1774: ams_source_codes E
1775: WHERE e.source_code_for_id = c.event_offer_id
1776: AND c.event_standalone_flag = 'Y'
1777: AND (c.parent_type is null or c.parent_type ='RCAM')
1778: AND e.source_code_id = a.source_promotion_id
1844: ,count(distinct(decode(h.flow_status_code,'BOOKED',h.header_id,0))) -1 booked_orders
1845: ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD'),
1846: nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) booked_orders_amt
1847: FROM ams_event_offers_all_b B,
1848: ams_source_codes C ,
1849: as_sales_leads D,
1850: as_sales_lead_opportunity A,
1851: as_leads_all E,
1852: aso_quote_related_objects F,
1872: )
1873: GROUP BY event_offer_id ,creation_date
1874: ) AD,
1875: ams_event_offers_all_b A,
1876: ams_source_codes E
1877: WHERE ad.event_offer_id = a.event_offer_id
1878: AND a.event_standalone_flag = 'Y'
1879: AND (a.parent_type is null or a.parent_type = 'RCAM')
1880: AND e.source_code_for_id = a.event_offer_id
3138: ,0 booked_orders
3139: ,0 booked_orders_amt
3140: FROM as_leads_all A,
3141: ams_event_offers_all_b C,
3142: ams_source_codes E
3143: where e.source_code_for_id = c.event_offer_id
3144: and e.source_code_id = a.source_promotion_id
3145: and e.arc_source_code_for in ('EONE','EVEO')
3146: and trunc(a.creation_date) between p_start_datel and p_end_datel + 0.9999
3175: ,0 booked_orders_amt
3176: FROM as_sales_leads A,
3177: as_statuses_b B,
3178: ams_event_offers_all_b C,
3179: ams_source_codes E
3180: WHERE e.source_code_for_id = c.event_offer_id
3181: and e.source_code_id = a.source_promotion_id
3182: and a.status_code = b.status_code
3183: and e.arc_source_code_for in ('EONE','EVEO')
3247: ,count(distinct(h.header_id)) booked_orders
3248: ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD'),
3249: nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) booked_orders_amt
3250: from ams_event_offers_all_b B,
3251: ams_source_codes C ,
3252: as_sales_leads D,
3253: as_sales_lead_opportunity A,
3254: as_leads_all E,
3255: aso_quote_related_objects F,
3276: group by event_offer_id ,
3277: creation_date) AD,
3278: ams_event_offers_all_b A,
3279: ams_event_headers_all_b B,
3280: ams_source_codes E
3281: where
3282: e.source_code = a.source_code
3283: AND a.event_header_id = b.event_header_id
3284: --AND trunc(b.active_from_date) >= trunc(l_min_start_date)
3619: ,0 booked_orders
3620: ,0 booked_orders_amt
3621: FROM as_leads_all A,
3622: ams_event_offers_all_b C,
3623: ams_source_codes E
3624: WHERE e.source_code_for_id = c.event_offer_id
3625: AND c.event_standalone_flag = 'Y'
3626: AND (c.parent_type is null or c.parent_type ='RCAM')
3627: AND e.source_code_id = a.source_promotion_id
3657: ,0 booked_orders_amt
3658: FROM as_sales_leads A,
3659: as_statuses_b B,
3660: ams_event_offers_all_b C,
3661: ams_source_codes E
3662: WHERE e.source_code_for_id = c.event_offer_id
3663: AND c.event_standalone_flag = 'Y'
3664: AND (c.parent_type is null or c.parent_type ='RCAM')
3665: AND e.source_code_id = a.source_promotion_id
3731: ,count(distinct(h.header_id)) booked_orders
3732: ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD'),
3733: nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) booked_orders_amt
3734: FROM ams_event_offers_all_b B,
3735: ams_source_codes C ,
3736: as_sales_leads D,
3737: as_sales_lead_opportunity A,
3738: as_leads_all E,
3739: aso_quote_related_objects F,
3761: )
3762: GROUP BY event_offer_id ,creation_date
3763: ) AD,
3764: ams_event_offers_all_b A,
3765: ams_source_codes E
3766: WHERE ad.event_offer_id = a.event_offer_id
3767: AND a.event_standalone_flag = 'Y'
3768: AND (a.parent_type is null or a.parent_type = 'RCAM')
3769: AND e.source_code_for_id = a.event_offer_id
4042: ,0 booked_orders
4043: ,0 booked_orders_amt
4044: FROM as_leads_all A,
4045: ams_event_headers_all_b C,
4046: ams_source_codes E
4047: where e.source_code_for_id = c.event_header_id
4048: and e.source_code_id = a.source_promotion_id
4049: and e.arc_source_code_for = 'EVEH'
4050: and trunc(a.creation_date) between p_start_datel and p_end_datel + 0.9999
4079: ,0 booked_orders_amt
4080: FROM as_sales_leads A,
4081: as_statuses_b B,
4082: ams_event_headers_all_b C,
4083: ams_source_codes E
4084: WHERE e.source_code_for_id = c.event_header_id
4085: and e.source_code_id = a.source_promotion_id
4086: and a.status_code = b.status_code
4087: and e.arc_source_code_for = 'EVEH'
4209: ,count(distinct(h.header_id)) booked_orders
4210: ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD'),
4211: nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) booked_orders_amt
4212: from ams_event_headers_all_b B,
4213: ams_source_codes C ,
4214: as_sales_leads D,
4215: as_sales_lead_opportunity A,
4216: as_leads_all E,
4217: aso_quote_related_objects F,
4237: )
4238: group by event_header_id ,
4239: creation_date) AD,
4240: ams_event_headers_all_b A,
4241: ams_source_codes E
4242: where
4243: e.source_code = a.source_code
4244: --AND trunc(a.active_from_date) >= trunc(l_min_start_date)
4245: AND a.system_status_code in ('ACTIVE', 'CANCELLED','COMPLETED','CLOSED')