[Home] [Help]
401: a.currency_code_fc currency_code_fc,
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')
688: a.fund_amount_fc fund_amount_fc,
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
727: ,sum(decode(b.OPP_OPEN_STATUS_FLAG,'Y',decode(a.lead_rank_id,10000,1,0),0)) leads_hot
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'
744: count(distinct(decode(h.flow_status_code,'BOOKED',h.header_id,0))) -1 booked_orders,
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,
772: trunc(a.creation_date) creation_date,
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
1024: a.fund_amount_fc fund_amount_fc,
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
1096: COUNT(A.lead_id) opportunities,
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
1362: AND b.budget_source_type in ('EVEO', 'EONE')
1363: AND b.act_budget_used_by_id = a.event_offer_id
1364: GROUP BY a.event_offer_id, trunc(nvl(b.approval_date,b.last_update_date))) AD,
1365: ams_event_offers_all_b A,
1366: ams_event_headers_all_b B
1367: where a.event_header_id = b.event_header_id
1368: and a.event_start_date >= p_start_datel
1369: and a.event_start_date <= p_end_datel
1370: and ad.creation_date >= p_start_datel
1422: ,0
1423: ,'APPROVED'
1424: ,convert_currency(nvl(b.approved_in_currency,'USD'),nvl(b.approved_original_amount,0))
1425: )) budget_approved
1426: FROM ams_event_headers_all_b A,
1427: ozf_act_budgets B
1428: WHERE b.arc_act_budget_used_by = 'EVEH'
1429: AND b.transfer_type = 'REQUEST'
1430: AND b.budget_source_type ='FUND'
1439: a.event_header_id event_header_id,
1440: trunc(nvl(b.approval_date,b.last_update_date)) creation_date,
1441: 0, --budget_requested
1442: 0-SUM(convert_currency(b.approved_in_currency,nvl(b.approved_original_amount,0))) budget_approved
1443: FROM ams_event_headers_all_b A,
1444: ozf_act_budgets B
1445: WHERE b.arc_act_budget_used_by ='FUND'
1446: AND transfer_type in ('TRANSFER','REQUEST')
1447: AND status_code ='APPROVED'
1447: AND status_code ='APPROVED'
1448: AND b.budget_source_type = 'EVEH'
1449: AND b.budget_source_id = a.event_header_id
1450: GROUP BY a.event_header_id, trunc(nvl(b.approval_date,b.last_update_date))) AD,
1451: ams_event_headers_all_b B
1452: where
1453: b.system_status_code in ('ACTIVE', 'CANCELLED','COMPLETED','CLOSED')
1454: and ad.event_header_id = b.event_header_id
1455: AND b.active_from_date >= p_start_datel
3275: )
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
4041: ,0 --forecasted_revenue
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'
4078: ,0 booked_orders
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
4208: ,0 forecasted_revenue
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,
4236: ,trunc(i.creation_date)) --orders
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)