[Home] [Help]
101:
102:
103: PROCEDURE GET_KPI_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
104: x_custom_sql OUT NOCOPY VARCHAR2,
105: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
106: IS
107: l_sqltext varchar2(32766);
108: iFlag number;
109: l_period_type_hc number;
131: l_admin_clause varchar2(4000);
132: --l_cat_id NUMBER;
133: l_campaign_id VARCHAR2(50);
134: l_cat_id VARCHAR2(50);
135: l_custom_rec BIS_QUERY_ATTRIBUTES;
136: l_table_name varchar2(100);
137: l_table_name1 varchar2(100);
138: l_pc_where varchar2(500);
139: l_pc_from varchar2(100);
151: l_report_name VARCHAR2(300);
152: l_media VARCHAR2(300);
153: BEGIN
154:
155: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
156: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
157: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
158: l_as_of_date,
159: l_period_type,
611: END GET_KPI_SQL;
612:
613: PROCEDURE GET_PO_RACK_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
614: x_custom_sql OUT NOCOPY VARCHAR2,
615: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
616: IS
617: l_sqltext varchar2(32000);
618: iFlag number;
619: l_period_type_hc number;
653: l_chnl_col varchar2(10);
654: --l_cat_id NUMBER;
655: l_cat_id VARCHAR2(50):= NULL;
656: l_campaign_id VARCHAR2(50):= NULL;
657: l_custom_rec BIS_QUERY_ATTRIBUTES;
658: l_curr VARCHAR2(50);
659: l_curr_suffix VARCHAR2(50);
660: l_dass varchar2(100); -- variable to store value for directly assigned lookup value
661: --l_una varchar2(100); -- variable to store value for Unassigned lookup value
672: from bim_i_source_codes
673: where source_code_id=replace(l_campaign_id,'''');
674:
675: BEGIN
676: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
677: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
678: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
679: l_as_of_date,
680: l_period_type,
3516: END GET_PO_RACK_SQL;
3517:
3518: PROCEDURE GET_CS_RACK_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
3519: x_custom_sql OUT NOCOPY VARCHAR2,
3520: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
3521:
3522: IS
3523:
3524: l_sqltext varchar2(15000);
3554: l_url_str_type varchar2(1000);
3555: --l_cat_id NUMBER;
3556: l_cat_id VARCHAR2(50);
3557: l_campaign_id VARCHAR2(50);
3558: l_custom_rec BIS_QUERY_ATTRIBUTES;
3559: l_inner_sqltext varchar2(20000); --- to form inner query to calculate total cost
3560: l_prog_cost1 varchar2(100);
3561: l_prog_cost2 varchar2(100);
3562: l_prog_rev1 varchar2(100);
3593: BEGIN
3594:
3595:
3596:
3597: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
3598: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
3599: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
3600: l_as_of_date,
3601: l_period_type,
6632:
6633:
6634: PROCEDURE GET_TOP_LEADS_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
6635: x_custom_sql OUT NOCOPY VARCHAR2,
6636: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
6637: IS
6638: l_sqltext varchar2(4000);
6639: iFlag number;
6640: l_period_type_hc number;
6662: l_access_table varchar2(4000);
6663: --l_cat_id NUMBER;
6664: l_cat_id VARCHAR2(50);
6665: l_campaign_id VARCHAR2(50);
6666: l_custom_rec BIS_QUERY_ATTRIBUTES;
6667: l_curr VARCHAR2(50);
6668: l_curr_suffix VARCHAR2(50);
6669: l_col_id NUMBER;
6670: l_area VARCHAR2(300);
6671: l_report_name VARCHAR2(300);
6672: l_media VARCHAR2(300);
6673:
6674: BEGIN
6675: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
6676: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
6677: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
6678: l_as_of_date,
6679: l_period_type,
6860: END GET_TOP_LEADS_SQL;
6861:
6862: PROCEDURE GET_TOP_OPPS_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
6863: x_custom_sql OUT NOCOPY VARCHAR2,
6864: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
6865: IS
6866: l_sqltext varchar2(4000);
6867: iFlag number;
6868: l_period_type_hc number;
6890: l_access_table varchar2(4000);
6891: --l_cat_id NUMBER;
6892: l_cat_id VARCHAR2(50);
6893: l_campaign_id VARCHAR2(50);
6894: l_custom_rec BIS_QUERY_ATTRIBUTES;
6895: l_curr VARCHAR2(50);
6896: l_curr_suffix VARCHAR2(50);
6897: l_col_id NUMBER;
6898: l_area VARCHAR2(300);
6898: l_area VARCHAR2(300);
6899: l_report_name VARCHAR2(300);
6900: l_media VARCHAR2(300);
6901: BEGIN
6902: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
6903: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
6904: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
6905: l_as_of_date,
6906: l_period_type,
7084: END GET_TOP_OPPS_SQL;
7085:
7086: PROCEDURE GET_TOP_EVEH_OPPS_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7087: x_custom_sql OUT NOCOPY VARCHAR2,
7088: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7089: IS
7090: l_sqltext varchar2(4000);
7091: iFlag number;
7092: l_period_type_hc number;
7114: l_access_table varchar2(4000);
7115: --l_cat_id NUMBER;
7116: l_cat_id VARCHAR2(50);
7117: l_campaign_id VARCHAR2(50);
7118: l_custom_rec BIS_QUERY_ATTRIBUTES;
7119: l_curr VARCHAR2(50);
7120: l_curr_suffix VARCHAR2(50);
7121: l_col_id NUMBER;
7122: l_area VARCHAR2(300);
7122: l_area VARCHAR2(300);
7123: l_report_name VARCHAR2(300);
7124: l_media VARCHAR2(300);
7125: BEGIN
7126: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7127: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7128: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7129: l_as_of_date,
7130: l_period_type,
7267: END GET_TOP_EVEH_OPPS_SQL;
7268:
7269: PROCEDURE GET_TOP_CAMP_OPPS_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7270: x_custom_sql OUT NOCOPY VARCHAR2,
7271: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7272: IS
7273: l_sqltext varchar2(4000);
7274: iFlag number;
7275: l_period_type_hc number;
7297: l_access_table varchar2(4000);
7298: --l_cat_id NUMBER;
7299: l_cat_id VARCHAR2(50);
7300: l_campaign_id VARCHAR2(50);
7301: l_custom_rec BIS_QUERY_ATTRIBUTES;
7302: l_curr VARCHAR2(50);
7303: l_curr_suffix VARCHAR2(50);
7304: l_col_id NUMBER;
7305: l_area VARCHAR2(300);
7305: l_area VARCHAR2(300);
7306: l_report_name VARCHAR2(300);
7307: l_media VARCHAR2(300);
7308: BEGIN
7309: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7310: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7311: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7312: l_as_of_date,
7313: l_period_type,
7451: END GET_TOP_CAMP_OPPS_SQL;
7452:
7453: PROCEDURE GET_TOP_EVEH_LEAD_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7454: x_custom_sql OUT NOCOPY VARCHAR2,
7455: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7456: IS
7457: l_sqltext varchar2(4000);
7458: iFlag number;
7459: l_period_type_hc number;
7481: l_access_table varchar2(4000);
7482: --l_cat_id NUMBER;
7483: l_cat_id VARCHAR2(50);
7484: l_campaign_id VARCHAR2(50);
7485: l_custom_rec BIS_QUERY_ATTRIBUTES;
7486: l_curr VARCHAR2(50);
7487: l_curr_suffix VARCHAR2(50);
7488: l_col_id NUMBER;
7489: l_area VARCHAR2(300);
7489: l_area VARCHAR2(300);
7490: l_report_name VARCHAR2(300);
7491: l_media VARCHAR2(300);
7492: BEGIN
7493: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7494: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7495: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7496: l_as_of_date,
7497: l_period_type,
7632: END GET_TOP_EVEH_LEAD_SQL;
7633:
7634: PROCEDURE GET_TOP_CAMP_LEAD_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7635: x_custom_sql OUT NOCOPY VARCHAR2,
7636: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7637: IS
7638: l_sqltext varchar2(4000);
7639: iFlag number;
7640: l_period_type_hc number;
7662: l_access_table varchar2(4000);
7663: --l_cat_id NUMBER;
7664: l_cat_id VARCHAR2(50);
7665: l_campaign_id VARCHAR2(50);
7666: l_custom_rec BIS_QUERY_ATTRIBUTES;
7667: l_curr VARCHAR2(50);
7668: l_curr_suffix VARCHAR2(50);
7669: l_col_id NUMBER;
7670: l_area VARCHAR2(300);
7670: l_area VARCHAR2(300);
7671: l_report_name VARCHAR2(300);
7672: l_media VARCHAR2(300);
7673: BEGIN
7674: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7675: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7676: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7677: l_as_of_date,
7678: l_period_type,
7815:
7816:
7817: PROCEDURE GET_CPL_KPI(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7818: x_custom_sql OUT NOCOPY VARCHAR2,
7819: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7820: IS
7821: l_sqltext varchar2(5000);
7822: iFlag number;
7823: l_period_type_hc number;
7845: l_admin_clause varchar2(4000);
7846: --l_cat_id NUMBER;
7847: l_campaign_id VARCHAR2(50);
7848: l_cat_id VARCHAR2(50);
7849: l_custom_rec BIS_QUERY_ATTRIBUTES;
7850: l_sql_outer varchar2(5000);
7851: select1 varchar2(5000);
7852: select2 varchar2(5000);
7853: select3 varchar2(5000);
7867: l_media VARCHAR2(300);
7868:
7869:
7870: BEGIN
7871: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7872: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7873: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7874: l_as_of_date,
7875: l_period_type,
8329:
8330:
8331: PROCEDURE GET_CPL_GRAPH_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
8332: x_custom_sql OUT NOCOPY VARCHAR2,
8333: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8334: IS
8335: iFlag number;
8336: l_sqltext VARCHAR2(20000) ;
8337: l_as_of_date DATE;
8362: l_record_type varchar2(80) := NULL;
8363: --l_cat_id NUMBER;
8364: l_campaign_id VARCHAR2(50);
8365: l_cat_id VARCHAR2(50);
8366: l_custom_rec BIS_QUERY_ATTRIBUTES;
8367: l_group_by varchar2(500);
8368: l_group_by1 varchar2(500);
8369: l_curr VARCHAR2(50);
8370: l_curr_suffix VARCHAR2(50);
8372: l_area VARCHAR2(300);
8373: l_report_name VARCHAR2(300);
8374: l_media VARCHAR2(300);
8375: BEGIN
8376: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
8377: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
8378:
8379: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
8380: l_as_of_date,
8845:
8846: PROCEDURE GET_CPL_RPL_SQL (
8847: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
8848: x_custom_sql OUT NOCOPY VARCHAR2,
8849: x_custom_output OUT NOCOPY bis_query_attributes_tbl
8850: )
8851: IS
8852: l_sqltext VARCHAR2 (30000);
8853: iflag NUMBER;
8927: select object_type
8928: from bim_i_source_codes
8929: where source_code_id=replace(l_campaign_id,'''');
8930: /*********************************************************/
8931: l_custom_rec bis_query_attributes;
8932: l_object_type varchar2(30);
8933: l_url_link varchar2(200);
8934: l_url_camp1 varchar2(3000);
8935: l_url_camp2 varchar2(3000);
8940: l_table_bud VARCHAR2(300);
8941: l_where_bud VARCHAR2(300);
8942: l_prog_cost1 VARCHAR2(30);
8943: BEGIN
8944: x_custom_output := bis_query_attributes_tbl ();
8945: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
8946: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
8947: l_as_of_date,
8948: l_period_type,
9866:
9867: PROCEDURE GET_RPL_CPL_SQL (
9868: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
9869: x_custom_sql OUT NOCOPY VARCHAR2,
9870: x_custom_output OUT NOCOPY bis_query_attributes_tbl
9871: )
9872: IS
9873: l_sqltext VARCHAR2 (32000);
9874: iflag NUMBER;
9947: select object_type
9948: from bim_i_source_codes
9949: where source_code_id=replace(l_campaign_id,'''');
9950: /*********************************************************/
9951: l_custom_rec bis_query_attributes;
9952: l_object_type varchar2(30);
9953: l_url_link varchar2(200);
9954: l_url_camp1 varchar2(3000);
9955: l_url_camp2 varchar2(3000);
9962: l_table_bud VARCHAR2(300);
9963: l_where_bud VARCHAR2(300);
9964: l_prog_cost1 VARCHAR2(30);
9965: BEGIN
9966: x_custom_output := bis_query_attributes_tbl ();
9967: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
9968: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
9969: l_as_of_date,
9970: l_period_type,
10851:
10852: PROCEDURE GET_LEAD_OPTY_CONV_SQL (
10853: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
10854: x_custom_sql OUT NOCOPY VARCHAR2,
10855: x_custom_output OUT NOCOPY bis_query_attributes_tbl
10856: )
10857: IS
10858: l_sqltext VARCHAR2 (20000);
10859: iflag NUMBER;
10915: select object_type
10916: from bim_i_source_codes
10917: where source_code_id=replace(l_campaign_id,'''');
10918: /*********************************************************/
10919: l_custom_rec bis_query_attributes;
10920: l_object_type varchar2(30);
10921: l_url_link varchar2(200) ;
10922: l_url_camp1 varchar2(3000);
10923: l_url_camp2 varchar2(3000);
10930: l_area VARCHAR2(300);
10931: l_report_name VARCHAR2(300);
10932: l_media VARCHAR2(300);
10933: BEGIN
10934: x_custom_output := bis_query_attributes_tbl ();
10935: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
10936: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
10937: l_as_of_date,
10938: l_period_type,
11731:
11732: PROCEDURE GET_MKTG_A_LEADS_SQL (
11733: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
11734: x_custom_sql OUT NOCOPY VARCHAR2,
11735: x_custom_output OUT NOCOPY bis_query_attributes_tbl
11736: )
11737: IS
11738: l_sqltext VARCHAR2 (20000);
11739: iflag NUMBER;
11794: select object_type
11795: from bim_i_source_codes
11796: where source_code_id=replace(l_campaign_id,'''');
11797: /*********************************************************/
11798: l_custom_rec bis_query_attributes;
11799: l_object_type varchar2(30);
11800: l_url_link varchar2(200);
11801: l_url_camp1 varchar2(3000);
11802: l_url_camp2 varchar2(3000);
11811: l_media VARCHAR2(300);
11812: l_jtf varchar2(300);
11813:
11814: BEGIN
11815: x_custom_output := bis_query_attributes_tbl ();
11816: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
11817: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
11818: l_as_of_date,
11819: l_period_type,
12599:
12600: PROCEDURE GET_MKTG_NEW_LEADS_SQL (
12601: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
12602: x_custom_sql OUT NOCOPY VARCHAR2,
12603: x_custom_output OUT NOCOPY bis_query_attributes_tbl
12604: )
12605: IS
12606: l_sqltext VARCHAR2 (20000);
12607: iflag NUMBER;
12661: select object_type
12662: from bim_i_source_codes
12663: where source_code_id=replace(l_campaign_id,'''');
12664: /*********************************************************/
12665: l_custom_rec bis_query_attributes;
12666: l_object_type varchar2(30);
12667: l_url_link varchar2(200);
12668: l_url_camp1 varchar2(3000);
12669: l_url_camp2 varchar2(3000);
12677: l_report_name VARCHAR2(300);
12678: l_media VARCHAR2(300);
12679: l_jtf varchar2(300);
12680: BEGIN
12681: x_custom_output := bis_query_attributes_tbl ();
12682: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
12683:
12684: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
12685: l_as_of_date,
13446:
13447:
13448: PROCEDURE GET_CAMP_START_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
13449: x_custom_sql OUT NOCOPY VARCHAR2,
13450: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
13451: IS
13452: l_sqltext varchar2(15000);
13453: iFlag number;
13454: l_period_type_hc number;
13479: l_col_id NUMBER;
13480: l_area VARCHAR2(300);
13481: l_report_name VARCHAR2(300);
13482: l_campaign_id VARCHAR2(50);
13483: l_custom_rec BIS_QUERY_ATTRIBUTES;
13484: l_dass varchar2(100); -- variable to store value for directly assigned lookup value
13485: l_una varchar2(100); -- variable to store value for Unassigned lookup value
13486: l_curr VARCHAR2(50);
13487: l_curr_suffix VARCHAR2(50);
13486: l_curr VARCHAR2(50);
13487: l_curr_suffix VARCHAR2(50);
13488: l_media VARCHAR2(300);
13489: BEGIN
13490: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
13491: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
13492: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
13493: l_as_of_date,
13494: l_period_type,
15347:
15348:
15349: PROCEDURE GET_EVEH_START_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
15350: x_custom_sql OUT NOCOPY VARCHAR2,
15351: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
15352: IS
15353: l_sqltext varchar2(15000);
15354: iFlag number;
15355: l_period_type_hc number;
15380: l_col_id NUMBER;
15381: l_area VARCHAR2(300);
15382: l_report_name VARCHAR2(300);
15383: l_campaign_id VARCHAR2(50);
15384: l_custom_rec BIS_QUERY_ATTRIBUTES;
15385: l_dass varchar2(100); -- variable to store value for directly assigned lookup value
15386: l_una varchar2(100); -- variable to store value for Unassigned lookup value
15387: l_curr VARCHAR2(50);
15388: l_curr_suffix VARCHAR2(50);
15387: l_curr VARCHAR2(50);
15388: l_curr_suffix VARCHAR2(50);
15389: l_media VARCHAR2(300);
15390: BEGIN
15391: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
15392: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
15393: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
15394: l_as_of_date,
15395: l_period_type,
17240: --campaign detail
17241:
17242: PROCEDURE GET_CAMP_DETL_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
17243: x_custom_sql OUT NOCOPY VARCHAR2,
17244: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
17245: IS
17246: l_sqltext varchar2(15000);
17247: l_period_type_hc number;
17248: l_as_of_date DATE;
17259: l_admin_status VARCHAR2(20);
17260: l_admin_flag VARCHAR2(1);
17261: l_cat_id VARCHAR2(50);
17262: l_campaign_id VARCHAR2(50);
17263: l_custom_rec BIS_QUERY_ATTRIBUTES;
17264: l_curr VARCHAR2(50);
17265: l_curr_suffix VARCHAR2(50);
17266: l_una VARCHAR2(100);
17267: l_url_str VARCHAR2(1000);
17284: l_group_by2 varchar2(5000);
17285: l_group_by varchar2(5000);
17286:
17287: BEGIN
17288: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
17289: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
17290:
17291: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
17292: l_as_of_date,
17607:
17608:
17609: PROCEDURE GET_CSCH_START_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
17610: x_custom_sql OUT NOCOPY VARCHAR2,
17611: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
17612: IS
17613: l_sqltext varchar2(15000);
17614: iFlag number;
17615: l_period_type_hc number;
17640: l_col_id NUMBER;
17641: l_view_by_name VARCHAR2(50);
17642: l_area VARCHAR2(50);
17643: l_campaign_id VARCHAR2(50);
17644: l_custom_rec BIS_QUERY_ATTRIBUTES;
17645: l_dass varchar2(100); -- variable to store value for directly assigned lookup value
17646: l_una varchar2(100); -- variable to store value for Unassigned lookup value
17647: l_eve varchar2(100); -- variable to store value for Events lookup value
17648: l_curr VARCHAR2(50);
17659: l_url_str2_r VARCHAR2(1000);
17660: l_url_str3_r VARCHAR2(1000);
17661:
17662: BEGIN
17663: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
17664: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
17665: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
17666: l_as_of_date,
17667: l_period_type,
19884: END GET_CSCH_START_SQL;
19885:
19886: PROCEDURE GET_CSCH_DETL_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
19887: x_custom_sql OUT NOCOPY VARCHAR2,
19888: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
19889: IS
19890: l_sqltext varchar2(15000);
19891: iFlag number;
19892: l_period_type_hc number;
19913: l_access_table varchar2(4000);
19914: --l_cat_id NUMBER;
19915: l_cat_id VARCHAR2(50);
19916: l_campaign_id VARCHAR2(50);
19917: l_custom_rec BIS_QUERY_ATTRIBUTES;
19918: l_dass varchar2(100); -- variable to store value for directly assigned lookup value
19919: l_una varchar2(100); -- variable to store value for Unassigned lookup value
19920: l_url_str VARCHAR2(1000);
19921: l_url_str_csch VARCHAR2(1000);
19936: l_report_name varchar2(50);
19937: l_curr_suffix1 VARCHAR2(50);
19938:
19939: BEGIN
19940: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
19941: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
19942: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
19943: l_as_of_date,
19944: l_period_type,
20127:
20128:
20129: PROCEDURE GET_RESP_SUM_SQL(p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
20130: x_custom_sql OUT NOCOPY VARCHAR2,
20131: x_custom_output OUT NOCOPY bis_query_attributes_tbl) IS
20132: l_sqltext VARCHAR2(32000);
20133: iflag NUMBER;
20134: l_period_type_hc NUMBER;
20135: l_as_of_date DATE;
20200: select object_type
20201: from bim_i_source_codes
20202: where source_code_id = replace(l_campaign_id, '''');
20203: /*********************************************************/
20204: l_custom_rec bis_query_attributes;
20205: l_object_type varchar2(30);
20206: l_url_link varchar2(200);
20207: l_url_camp1 varchar2(3000);
20208: l_url_camp2 varchar2(3000);
20218: l_where_bud VARCHAR2(300);
20219: l_prog_cost1 VARCHAR2(30);
20220:
20221: BEGIN
20222: x_custom_output := bis_query_attributes_tbl();
20223: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
20224: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
20225: l_as_of_date,
20226: l_period_type,
20883:
20884: PROCEDURE GET_RESP_RATE_SQL (
20885: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
20886: x_custom_sql OUT NOCOPY VARCHAR2,
20887: x_custom_output OUT NOCOPY bis_query_attributes_tbl
20888: )
20889: IS
20890: l_sqltext VARCHAR2 (20000);
20891: iflag NUMBER;
20947: select object_type
20948: from bim_i_source_codes
20949: where source_code_id=replace(l_campaign_id,'''');
20950: /*********************************************************/
20951: l_custom_rec bis_query_attributes;
20952: l_object_type varchar2(30);
20953: l_inner varchar2(5000);
20954: l_inr_cond varchar2(5000);
20955: l_p_inner varchar2(5000);
20972: l_area VARCHAR2(300);
20973: l_report_name VARCHAR2(300);
20974: l_media VARCHAR2(300);
20975: BEGIN
20976: x_custom_output := bis_query_attributes_tbl ();
20977: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
20978: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
20979: l_as_of_date,
20980: l_period_type,
21778:
21779: PROCEDURE GET_WON_OPTY_SQL (
21780: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
21781: x_custom_sql OUT NOCOPY VARCHAR2,
21782: x_custom_output OUT NOCOPY bis_query_attributes_tbl
21783: )
21784: IS
21785: l_sqltext VARCHAR2 (20000);
21786: iflag NUMBER;
21841: select object_type
21842: from bim_i_source_codes
21843: where source_code_id=replace(l_campaign_id,'''');
21844: /*********************************************************/
21845: l_custom_rec bis_query_attributes;
21846: l_object_type varchar2(30);
21847: l_url_link varchar2(200) ;
21848: l_url_camp1 varchar2(3000);
21849: l_url_camp2 varchar2(3000);
21855: l_area VARCHAR2(300);
21856: l_report_name VARCHAR2(300);
21857: l_media VARCHAR2(300);
21858: BEGIN
21859: x_custom_output := bis_query_attributes_tbl ();
21860: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
21861: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
21862: l_as_of_date,
21863: l_period_type,
22493:
22494: PROCEDURE BIM_MKTG_LEAD_ACT_SQL (
22495: p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
22496: x_custom_sql OUT NOCOPY VARCHAR2,
22497: x_custom_output OUT NOCOPY bis_query_attributes_tbl
22498: )
22499: IS
22500: l_sqltext VARCHAR2 (20000);
22501: iflag NUMBER;
22556: select object_type
22557: from bim_i_source_codes
22558: where source_code_id=replace(l_campaign_id,'''');
22559: /*********************************************************/
22560: l_custom_rec bis_query_attributes;
22561: l_object_type varchar2(30);
22562: l_url_link varchar2(200);
22563: l_url_camp1 varchar2(500);
22564: l_url_camp2 varchar2(500);