[Home] [Help]
668: /* cursor to get type of object passed from the page ******/
669: cursor get_obj_type
670: is
671: select object_type
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();
3586: /* cursor to get type of object passed from the page ******/
3587: cursor get_obj_type
3588: is
3589: select object_type
3590: from bim_i_source_codes
3591: where source_code_id=replace(l_campaign_id,'''');
3592:
3593: BEGIN
3594:
5354: IF l_cat_id is null THEN
5355: /************** START OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NULL AND CAMPAIGN ID IS NOT NULL ***********/
5356:
5357: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
5358: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,BIM_I_CPB_METS_MV a';
5359:
5360: IF l_admin_status = 'N' THEN
5361: l_inner := l_inner ||',bim_i_top_objects ac ';
5362: END IF;
5573:
5574: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
5575: l_dass:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('DASS');
5576: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
5577: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,bim_i_cpb_mets_mv a,eni_denorm_hierarchies edh,mtl_default_category_sets mdcs';
5578:
5579: IF l_admin_status = 'N' THEN
5580: l_inner := l_inner ||',bim_i_top_objects ac ';
5581: END IF;
5892: ELSIF (l_view_by ='MEDIA+MEDIA') THEN
5893: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
5894:
5895: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
5896: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,BIM_I_CPB_METS_MV a';
5897:
5898: IF l_cat_id is not null then
5899: l_inner := l_inner||' , eni_denorm_hierarchies edh,mtl_default_category_sets mdcs';
5900: end if;
6122:
6123: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
6124:
6125: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
6126: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,BIM_I_CPB_METS_MV a';
6127:
6128: IF l_cat_id is not null then
6129: l_inner := l_inner||' , eni_denorm_hierarchies edh,mtl_default_category_sets mdcs';
6130: end if;
6326:
6327: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
6328:
6329: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
6330: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,BIM_I_CPB_METS_MV a';
6331:
6332: IF l_cat_id is not null then
6333: l_inner := l_inner||' , eni_denorm_hierarchies edh,mtl_default_category_sets mdcs';
6334: end if; */
8924: /* cursor to get type of object passed from the page ******/
8925: cursor get_obj_type
8926: is
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);
9281: AND ac.resource_id = :l_resource_id ';
9282: /************************************************/
9283: ELSE
9284:
9285: l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
9286: l_where := l_where ||' AND a.source_code_id=src.source_code_id
9287: AND src.object_id=ac.object_id
9288: AND src.object_type=ac.object_type
9289: AND src.object_type NOT IN (''RCAM'')
9378: l_groupby|| ' camp.name,camp.object_id,camp.object_type'|| l_select_filter_camp /* appending filter clause */
9379: ;
9380: ELSE
9381: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
9382: /* appending table in l_form and joining conditon for the bim_i_source_codes */
9383:
9384: l_where :=l_where || ' AND a.immediate_parent_id = :l_campaign_id ' ;
9385:
9386:
9944: /* cursor to get type of object passed from the page ******/
9945: cursor get_obj_type
9946: is
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);
10264: AND ac.resource_id = :l_resource_id ';
10265: /************************************************/
10266: ELSE
10267:
10268: l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
10269: l_where := l_where ||' AND a.source_code_id=src.source_code_id
10270: AND src.object_id=ac.object_id
10271: AND src.object_type=ac.object_type
10272: AND src.object_type NOT IN (''RCAM'')
10367: ;
10368:
10369: ELSE
10370: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
10371: /* appending table in l_form and joining conditon for the bim_i_source_codes */
10372: /* l_from :=l_from||' ,bim_i_source_codes b';
10373: l_where :=l_where ||
10374: ' AND a.parent_denorm_type = b.object_type
10375: AND a.parent_object_id = b.object_id
10368:
10369: ELSE
10370: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
10371: /* appending table in l_form and joining conditon for the bim_i_source_codes */
10372: /* l_from :=l_from||' ,bim_i_source_codes b';
10373: l_where :=l_where ||
10374: ' AND a.parent_denorm_type = b.object_type
10375: AND a.parent_object_id = b.object_id
10376: AND b.child_object_id = 0
10912: /* cursor to get type of object passed from the page ******/
10913: cursor get_obj_type
10914: is
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);
11094: AND ac.resource_id = :l_resource_id ';
11095: /************************************************/
11096: ELSE
11097:
11098: l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
11099: l_where := l_where ||' AND a.source_code_id=src.source_code_id
11100: AND src.object_id=ac.object_id
11101: AND src.object_type=ac.object_type
11102: AND src.object_type NOT IN (''RCAM'')
11203: ;
11204: ELSE
11205:
11206: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
11207: /* appending table in l_form and joining conditon for the bim_i_source_codes */
11208:
11209: l_where :=l_where ||' AND a.immediate_parent_id = :l_campaign_id ';
11210:
11211: -- if program is selected from the page means it may have childern as programs,campaigns,events or one off events
11447: ' AND d.customer_category_code (+) = a.cust_category
11448: AND a.object_country = :l_country ';
11449: END IF;
11450: ELSE
11451: l_from := l_from||' ,bim_obj_ld_ccat_mv a ' ; --, bim_i_source_codes b ';
11452: l_where := l_where ||
11453: ' AND a.source_code_id = :l_campaign_id
11454: AND d.customer_category_code (+) = a.cust_category
11455: AND a.object_country = :l_country' ;
11482: AND d.lookup_type (+) = ''SOURCE_SYSTEM''
11483: AND a.object_country = :l_country';
11484: END IF;
11485: ELSE
11486: l_from := l_from||' ,bim_obj_ld_src_mv a ' ; --, bim_i_source_codes b ';
11487: l_where := l_where ||
11488: ' AND a.source_code_id = :l_campaign_id
11489: AND a.lead_source = d.lookup_code(+)
11490: AND d.lookup_type (+) = ''SOURCE_SYSTEM''
11518: AND d.lookup_type (+) = ''SALES_CHANNEL''
11519: AND a.object_country = :l_country';
11520: END IF;
11521: ELSE
11522: l_from := l_from||' ,bim_obj_ld_chnl_mv a '; --, bim_i_source_codes b ';
11523: l_where := l_where ||
11524: ' AND a.source_code_id = :l_campaign_id
11525: AND a.channel_code = d.lookup_code(+)
11526: AND d.lookup_type (+) = ''SALES_CHANNEL''
11550: l_where := l_where ||
11551: ' AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
11552: END IF;
11553: ELSE
11554: l_from := l_from||' ,bim_i_obj_mets_mv a ' ; --, bim_i_source_codes b ';
11555: l_where := l_where ||
11556: ' AND a.source_code_id = :l_campaign_id
11557: AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'')';
11558: END IF;
11587: ' AND d.rank_id (+)= a.lead_rank_id
11588: AND a.object_country = :l_country';
11589: END IF;
11590: ELSE
11591: l_from := l_from||' ,bim_obj_ld_qual_mv a ' ; --, bim_i_source_codes b ';
11592: l_where := l_where ||
11593: ' AND a.source_code_id = :l_campaign_id
11594: AND d.rank_id (+)= a.lead_rank_id
11595: AND a.object_country = :l_country';
11620: ' AND d.id (+)= a.activity_id
11621: AND a.object_country = :l_country';
11622: END IF;
11623: ELSE
11624: l_from := l_from||' ,bim_obj_chnl_mv a '; --, bim_i_source_codes b ';
11625: l_where := l_where ||
11626: ' AND a.source_code_id = :l_campaign_id
11627: AND d.id (+)= a.activity_id
11628: AND a.object_country = :l_country';
11653: ' AND d.id (+)= a.object_region
11654: AND a.object_country = :l_country';
11655: END IF;
11656: ELSE
11657: l_from := l_from||' ,bim_obj_regn_mv a '; --, bim_i_source_codes b ';
11658: l_where := l_where ||
11659: ' AND a.source_code_id = :l_campaign_id
11660: AND d.id (+)= a.object_region
11661: AND a.object_country = :l_country';
11791: /* cursor to get type of object passed from the page ******/
11792: cursor get_obj_type
11793: is
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);
12015: l_where := l_where ||' AND a.source_code_id=ac.source_code_id
12016: AND ac.resource_id = :l_resource_id ';
12017: /************************************************/
12018: ELSE
12019: l_from := l_from ||',ams_act_access_denorm ac , bim_i_source_codes src ';
12020: l_where := l_where ||' AND a.source_code_id=src.source_code_id
12021: AND src.object_id=ac.object_id
12022: AND src.object_type=ac.object_type
12023: AND src.object_type NOT IN (''RCAM'')
12112: l_groupby||
12113: l_select_filter;
12114: ELSE
12115: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
12116: /* appending table in l_form and joining conditon for the bim_i_source_codes */
12117:
12118: l_where :=l_where ||' AND a.immediate_parent_id = :l_campaign_id ';
12119:
12120: -- if program is selected from the page means it may have childern as programs,campaigns,events or one off events
12321: ' AND d.customer_category_code (+) = a.cust_category
12322: AND a.object_country = :l_country ';
12323: END IF;
12324: ELSE
12325: l_from := l_from||' ,bim_obj_ld_ccat_mv a ' ; --, bim_i_source_codes b ';
12326: l_where := l_where ||
12327: ' AND a.source_code_id = :l_campaign_id
12328: AND d.customer_category_code (+) = a.cust_category
12329: AND a.object_country = :l_country' ;
12356: AND d.lookup_type (+) = ''SOURCE_SYSTEM''
12357: AND a.object_country = :l_country';
12358: END IF;
12359: ELSE
12360: l_from := l_from||' ,bim_obj_ld_src_mv a ' ; --, bim_i_source_codes b ';
12361: l_where := l_where ||
12362: ' AND a.source_code_id = :l_campaign_id
12363: AND a.lead_source = d.lookup_code(+)
12364: AND d.lookup_type (+) = ''SOURCE_SYSTEM''
12392: AND d.lookup_type (+) = ''SALES_CHANNEL''
12393: AND a.object_country = :l_country';
12394: END IF;
12395: ELSE
12396: l_from := l_from||' ,bim_obj_ld_chnl_mv a ' ; --, bim_i_source_codes b ';
12397: l_where := l_where ||
12398: ' AND a.source_code_id = :l_campaign_id
12399: AND a.channel_code = d.lookup_code(+)
12400: AND d.lookup_type (+) = ''SALES_CHANNEL''
12424: l_where := l_where ||
12425: ' AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
12426: END IF;
12427: ELSE
12428: l_from := l_from||' ,bim_i_obj_mets_mv a ' ; --, bim_i_source_codes b ';
12429: l_where := l_where ||
12430: ' AND a.source_code_id = :l_campaign_id
12431: AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
12432: END IF;
12461: ' AND d.rank_id (+)= a.lead_rank_id
12462: AND a.object_country = :l_country';
12463: END IF;
12464: ELSE
12465: l_from := l_from||' ,bim_obj_ld_qual_mv a ' ; --, bim_i_source_codes b ';
12466: l_where := l_where ||
12467: ' AND a.source_code_id = :l_campaign_id
12468: AND d.rank_id (+)= a.lead_rank_id
12469: AND a.object_country = :l_country';
12494: ' AND d.id (+)= a.activity_id
12495: AND a.object_country = :l_country';
12496: END IF;
12497: ELSE
12498: l_from := l_from||' ,bim_obj_chnl_mv a '; --, bim_i_source_codes b ';
12499: l_where := l_where ||
12500: ' AND a.source_code_id = :l_campaign_id
12501: AND d.id (+)= a.activity_id
12502: AND a.object_country = :l_country';
12527: ' AND d.id (+)= a.object_region
12528: AND a.object_country = :l_country';
12529: END IF;
12530: ELSE
12531: l_from := l_from||' ,bim_obj_regn_mv a ' ; --, bim_i_source_codes b ';
12532: l_where := l_where ||
12533: ' AND a.source_code_id = :l_campaign_id
12534: AND d.id (+)= a.object_region
12535: AND a.object_country = :l_country';
12658: /* cursor to get type of object passed from the page ******/
12659: cursor get_obj_type
12660: is
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);
12855: l_where := l_where ||' AND a.source_code_id=ac.source_code_id
12856: AND ac.resource_id = :l_resource_id ';
12857: /************************************************/
12858: ELSE
12859: l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
12860: l_where := l_where ||' AND a.source_code_id=src.source_code_id
12861: AND src.object_id=ac.object_id
12862: AND src.object_type=ac.object_type
12863: AND src.object_type NOT IN (''RCAM'')
12953: l_select_filter /* appending filter clause */
12954: ;
12955: ELSE
12956: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
12957: /* appending table in l_form and joining conditon for the bim_i_source_codes */
12958:
12959: l_where :=l_where ||' AND a.immediate_parent_id = :l_campaign_id ';
12960:
12961: -- if program is selected from the page means it may have childern as programs,campaigns,events or one off events
13167: ' AND d.customer_category_code (+) = a.cust_category
13168: AND a.object_country = :l_country ';
13169: END IF;
13170: ELSE
13171: l_from := l_from||' ,bim_obj_ld_ccat_mv a ' ; --, bim_i_source_codes b ';
13172: l_where := l_where ||
13173: ' AND a.source_code_id = :l_campaign_id
13174: AND d.customer_category_code (+) = a.cust_category
13175: AND a.object_country = :l_country' ;
13202: AND d.lookup_type (+) = ''SOURCE_SYSTEM''
13203: AND a.object_country = :l_country';
13204: END IF;
13205: ELSE
13206: l_from := l_from||' ,bim_obj_ld_src_mv a ' ; --, bim_i_source_codes b ';
13207: l_where := l_where ||
13208: ' AND a.source_code_id = :l_campaign_id
13209: AND a.lead_source = d.lookup_code(+)
13210: AND d.lookup_type (+) = ''SOURCE_SYSTEM''
13238: AND d.lookup_type (+) = ''SALES_CHANNEL''
13239: AND a.object_country = :l_country';
13240: END IF;
13241: ELSE
13242: l_from := l_from||' ,bim_obj_ld_chnl_mv a ' ; --, bim_i_source_codes b ';
13243: l_where := l_where ||
13244: ' AND a.source_code_id = :l_campaign_id
13245: AND a.channel_code = d.lookup_code(+)
13246: AND d.lookup_type (+) = ''SALES_CHANNEL''
13270: l_where := l_where ||
13271: ' AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
13272: END IF;
13273: ELSE
13274: l_from := l_from||' ,bim_i_obj_mets_mv a '; --, bim_i_source_codes b ';
13275: l_where := l_where ||
13276: ' AND a.source_code_id = :l_campaign_id
13277: AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
13278: END IF;
13307: ' AND d.rank_id (+)= a.lead_rank_id
13308: AND a.object_country = :l_country';
13309: END IF;
13310: ELSE
13311: l_from := l_from||' ,bim_obj_ld_qual_mv a ' ; --, bim_i_source_codes b ';
13312: l_where := l_where ||
13313: ' AND a.source_code_id = :l_campaign_id
13314: AND d.rank_id (+)= a.lead_rank_id
13315: AND a.object_country = :l_country';
13340: ' AND d.id (+)= a.activity_id
13341: AND a.object_country = :l_country';
13342: END IF;
13343: ELSE
13344: l_from := l_from||' ,bim_obj_chnl_mv a ' ; --, bim_i_source_codes b ';
13345: l_where := l_where ||
13346: ' AND a.source_code_id = :l_campaign_id
13347: AND d.id (+)= a.activity_id
13348: AND a.object_country = :l_country';
13373: ' AND d.id (+)= a.object_region
13374: AND a.object_country = :l_country';
13375: END IF;
13376: ELSE
13377: l_from := l_from||' ,bim_obj_regn_mv a ' ; --, bim_i_source_codes b ';
13378: l_where := l_where ||
13379: ' AND a.source_code_id = :l_campaign_id
13380: AND d.id (+)= a.object_region
13381: AND a.object_country = :l_country';
14359: mtl_default_category_sets mdcs,';
14360: IF l_prog_view='Y' then
14361: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14362: ELSE
14363: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14364: END IF;
14365: l_sqltext := l_sqltext ||' ,(select e.parent_id parent_id ,e.value value
14366: from eni_item_vbh_nodes_v e
14367: where
14417: mtl_default_category_sets mdcs,';
14418: IF l_prog_view='Y' then
14419: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14420: ELSE
14421: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14422: END IF;
14423: l_sqltext := l_sqltext||' ,(select e.parent_id parent_id ,e.value value
14424: from eni_item_vbh_nodes_v e
14425: where
14475: mtl_default_category_sets mdcs,';
14476: IF l_prog_view='Y' then
14477: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14478: ELSE
14479: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14480: END IF;
14481: l_sqltext := l_sqltext||' ,(select e.parent_id parent_id ,e.value value
14482: from eni_item_vbh_nodes_v e
14483: where
14573: mtl_default_category_sets mdcs,';
14574: IF l_prog_view='Y' then
14575: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14576: ELSE
14577: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14578: END IF;
14579: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
14580: from eni_item_vbh_nodes_v e
14581: where e.parent_id =:l_cat_id
14627: fii_time_rpt_struct_v cal,';
14628: IF l_prog_view='Y' then
14629: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14630: ELSE
14631: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14632: END IF;
14633: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value
14634: from eni_item_vbh_nodes_v e
14635: where e.parent_id = :l_cat_id
14681: mtl_default_category_sets mdcs,';
14682: IF l_prog_view='Y' then
14683: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14684: ELSE
14685: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14686: END IF;
14687: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
14688: from eni_item_vbh_nodes_v e
14689: where e.parent_id =:l_cat_id
14735: fii_time_rpt_struct_v cal,';
14736: IF l_prog_view='Y' then
14737: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14738: ELSE
14739: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14740: END IF;
14741: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value
14742: from eni_item_vbh_nodes_v e
14743: where e.parent_id = :l_cat_id
14789: mtl_default_category_sets mdcs,';
14790: IF l_prog_view='Y' then
14791: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14792: ELSE
14793: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14794: END IF;
14795: l_sqltext :=l_sqltext||' ,(select e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
14796: from eni_item_vbh_nodes_v e
14797: where e.parent_id =:l_cat_id
14844: fii_time_rpt_struct_v cal,';
14845: IF l_prog_view='Y' then
14846: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
14847: ELSE
14848: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
14849: END IF;
14850: l_sqltext := l_sqltext ||' ,(select e.id id ,e.value value
14851: from eni_item_vbh_nodes_v e
14852: where e.parent_id = :l_cat_id
14931: fii_time_rpt_struct_v cal,';
14932: IF l_prog_view='Y' then
14933: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
14934: ELSE
14935: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d, bim_i_source_codes s ';
14936: END IF;
14937: IF l_cat_id is not null then
14938: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
14939: end if;
14986: fii_time_rpt_struct_v cal,';
14987: IF l_prog_view='Y' then
14988: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
14989: ELSE
14990: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d , bim_i_source_codes s';
14991: END IF;
14992: IF l_cat_id is not null then
14993: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
14994: end if;
15040: fii_time_rpt_struct_v cal,';
15041: IF l_prog_view='Y' then
15042: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
15043: ELSE
15044: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d, bim_i_source_codes s ';
15045: END IF;
15046: IF l_cat_id is not null then
15047: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
15048: end if;
15132: fii_time_rpt_struct_v cal,';
15133: IF l_prog_view='Y' then
15134: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
15135: ELSE
15136: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d, bim_i_source_codes s ';
15137: END IF;
15138: IF l_cat_id is not null then
15139: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
15140: end if;
15193: fii_time_rpt_struct_v cal,';
15194: IF l_prog_view='Y' then
15195: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
15196: ELSE
15197: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d , bim_i_source_codes s ';
15198: END IF;
15199: IF l_cat_id is not null then
15200: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
15201: end if;
15251: fii_time_rpt_struct_v cal,';
15252: IF l_prog_view='Y' then
15253: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
15254: ELSE
15255: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d ,bim_i_source_codes s ';
15256: END IF;
15257: IF l_cat_id is not null then
15258: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
15259: end if;
16254: mtl_default_category_sets mdcs,';
16255: IF l_prog_view='Y' then
16256: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
16257: ELSE
16258: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
16259: END IF;
16260: l_sqltext := l_sqltext ||' ,(select e.parent_id parent_id ,e.value value
16261: from eni_item_vbh_nodes_v e
16262: where
16314: mtl_default_category_sets mdcs,';
16315: IF l_prog_view='Y' then
16316: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16317: ELSE
16318: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s ';
16319: END IF;
16320: l_sqltext := l_sqltext||' ,(select e.parent_id parent_id ,e.value value
16321: from eni_item_vbh_nodes_v e
16322: where
16373: mtl_default_category_sets mdcs,';
16374: IF l_prog_view='Y' then
16375: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16376: ELSE
16377: l_sqltext := l_sqltext ||' ams_act_access_denorm ac , bim_i_source_codes s ';
16378: END IF;
16379: l_sqltext := l_sqltext||' ,(select e.parent_id parent_id ,e.value value
16380: from eni_item_vbh_nodes_v e
16381: where
16472: mtl_default_category_sets mdcs,';
16473: IF l_prog_view='Y' then
16474: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16475: ELSE
16476: l_sqltext := l_sqltext ||' ams_act_access_denorm ac ,bim_i_source_codes s ';
16477: END IF;
16478: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
16479: from eni_item_vbh_nodes_v e
16480: where e.parent_id =:l_cat_id
16526: fii_time_rpt_struct_v cal,';
16527: IF l_prog_view='Y' then
16528: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16529: ELSE
16530: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bim_i_source_codes s ';
16531: END IF;
16532: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value
16533: from eni_item_vbh_nodes_v e
16534: where e.parent_id = :l_cat_id
16576: mtl_default_category_sets mdcs,';
16577: IF l_prog_view='Y' then
16578: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16579: ELSE
16580: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bim_i_source_codes s ';
16581: END IF;
16582: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
16583: from eni_item_vbh_nodes_v e
16584: where e.parent_id =:l_cat_id
16627: fii_time_rpt_struct_v cal,';
16628: IF l_prog_view='Y' then
16629: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16630: ELSE
16631: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bim_i_source_codes s ';
16632: END IF;
16633: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value
16634: from eni_item_vbh_nodes_v e
16635: where e.parent_id = :l_cat_id
16678: mtl_default_category_sets mdcs,';
16679: IF l_prog_view='Y' then
16680: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16681: ELSE
16682: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bim_i_source_codes s ';
16683: END IF;
16684: l_sqltext :=l_sqltext||' ,(select e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
16685: from eni_item_vbh_nodes_v e
16686: where e.parent_id =:l_cat_id
16730: fii_time_rpt_struct_v cal,';
16731: IF l_prog_view='Y' then
16732: l_sqltext := l_sqltext ||' bim_i_top_objects ac ';
16733: ELSE
16734: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bim_i_source_codes s ';
16735: END IF;
16736: l_sqltext := l_sqltext ||' ,(select e.id id ,e.value value
16737: from eni_item_vbh_nodes_v e
16738: where e.parent_id = :l_cat_id
16818: fii_time_rpt_struct_v cal,';
16819: IF l_prog_view='Y' then
16820: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
16821: ELSE
16822: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d,bim_i_source_codes s ';
16823: END IF;
16824: IF l_cat_id is not null then
16825: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
16826: end if;
16873: fii_time_rpt_struct_v cal,';
16874: IF l_prog_view='Y' then
16875: l_sqltext := l_sqltext ||' bim_i_top_objects ac ,bis_areas_v d ';
16876: ELSE
16877: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d,bim_i_source_codes s ';
16878: END IF;
16879: IF l_cat_id is not null then
16880: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
16881: end if;
16928: fii_time_rpt_struct_v cal,';
16929: IF l_prog_view='Y' then
16930: l_sqltext := l_sqltext ||' bim_i_top_objects ac ,bis_areas_v d ';
16931: ELSE
16932: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d,bim_i_source_codes s ';
16933: END IF;
16934: IF l_cat_id is not null then
16935: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
16936: end if;
17022: fii_time_rpt_struct_v cal,';
17023: IF l_prog_view='Y' then
17024: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
17025: ELSE
17026: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d,bim_i_source_codes s ';
17027: END IF;
17028: IF l_cat_id is not null then
17029: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc';
17030: end if;
17084: fii_time_rpt_struct_v cal,';
17085: IF l_prog_view='Y' then
17086: l_sqltext := l_sqltext ||' bim_i_top_objects ac, bis_countries_v d ';
17087: ELSE
17088: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d,bim_i_source_codes s ';
17089: END IF;
17090: IF l_cat_id is not null then
17091: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
17092: end if;
17143: fii_time_rpt_struct_v cal,';
17144: IF l_prog_view='Y' then
17145: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
17146: ELSE
17147: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d,bim_i_source_codes s ';
17148: END IF;
17149: IF l_cat_id is not null then
17150: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
17151: end if;
17358: sum(nvl(a.budget_approved'||l_curr_suffix||',0)) BIM_ATTRIBUTE4,
17359: sum(nvl(a.cost_actual'||l_curr_suffix||',0)) BIM_ATTRIBUTE5
17360: FROM
17361: bim_i_marketing_facts a,
17362: bim_i_obj_name_mv b, bim_i_source_codes c ,
17363: ams_campaigns_all_b ab';
17364:
17365: l_where :=' WHERE
17366: c.source_code_id = b.source_code_id
17443: ''EVEH'' object_type
17444: FROM bim_i_marketing_facts a,
17445: bim_i_obj_name_mv b,
17446: ams_event_headers_all_b ab,
17447: bim_i_source_codes c ';
17448:
17449: l_where1 :=' WHERE
17450: c.source_code_id = b.source_code_id
17451: AND c.object_type = ''EVEH''
17469: ''EONE'' object_type
17470: FROM bim_i_marketing_facts a,
17471: bim_i_obj_name_mv b,
17472: ams_event_offers_all_b ab,
17473: bim_i_source_codes c';
17474:
17475: l_where2 :=' WHERE
17476: c.source_code_id = a.source_code_id (+)
17477: AND c.object_type = ''EONE''
18697: fii_time_rpt_struct_v cal,';
18698: IF l_prog_view='Y' then
18699: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
18700: ELSE
18701: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d, bim_i_source_codes s ';
18702: END IF;
18703: IF l_cat_id is not null then
18704: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
18705: end if;
18754: fii_time_rpt_struct_v cal,';
18755: IF l_prog_view='Y' then
18756: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
18757: ELSE
18758: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d, bim_i_source_codes s ';
18759: END IF;
18760: IF l_cat_id is not null then
18761: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
18762: end if;
18811: fii_time_rpt_struct_v cal,';
18812: IF l_prog_view='Y' then
18813: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_countries_v d ';
18814: ELSE
18815: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_countries_v d, bim_i_source_codes s ';
18816: END IF;
18817: IF l_cat_id is not null then
18818: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
18819: end if;
18906: fii_time_rpt_struct_v cal,';
18907: IF l_prog_view='Y' then
18908: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
18909: ELSE
18910: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d, bim_i_source_codes s ';
18911: END IF;
18912: IF l_cat_id is not null then
18913: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
18914: end if;
18959: fii_time_rpt_struct_v cal,';
18960: IF l_prog_view='Y' then
18961: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
18962: ELSE
18963: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d, bim_i_source_codes s ';
18964: END IF;
18965: IF l_cat_id is not null then
18966: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
18967: end if;
19012: fii_time_rpt_struct_v cal,';
19013: IF l_prog_view='Y' then
19014: l_sqltext := l_sqltext ||' bim_i_top_objects ac,bis_areas_v d ';
19015: ELSE
19016: l_sqltext := l_sqltext ||' ams_act_access_denorm ac,bis_areas_v d, bim_i_source_codes s ';
19017: END IF;
19018: IF l_cat_id is not null then
19019: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
19020: end if;
19104: bim_obj_chnl_mv a ,';
19105: IF l_prog_view='Y' then
19106: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19107: ELSE
19108: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19109: END IF;
19110: IF l_cat_id is not null then
19111: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
19112: end if;
19159: bim_obj_chnl_mv a ,';
19160: IF l_prog_view='Y' then
19161: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19162: ELSE
19163: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19164: END IF;
19165: IF l_cat_id is not null then
19166: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
19167: end if;
19215: bim_obj_chnl_mv a ,';
19216: IF l_prog_view='Y' then
19217: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19218: ELSE
19219: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19220: END IF;
19221: IF l_cat_id is not null then
19222: l_sqltext := l_sqltext ||',eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
19223: end if;
19311: mtl_default_category_sets mdcs,';
19312: IF l_prog_view='Y' then
19313: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19314: ELSE
19315: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19316: END IF;
19317: l_sqltext := l_sqltext ||' ,(select e.parent_id parent_id ,e.value value
19318: from eni_item_vbh_nodes_v e
19319: where
19368: mtl_default_category_sets mdcs,';
19369: IF l_prog_view='Y' then
19370: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19371: ELSE
19372: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19373: END IF;
19374: l_sqltext := l_sqltext||' ,(select e.parent_id parent_id ,e.value value
19375: from eni_item_vbh_nodes_v e
19376: where
19424: mtl_default_category_sets mdcs,';
19425: IF l_prog_view='Y' then
19426: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19427: ELSE
19428: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19429: END IF;
19430: l_sqltext := l_sqltext||' ,(select e.parent_id parent_id ,e.value value
19431: from eni_item_vbh_nodes_v e
19432: where
19520: mtl_default_category_sets mdcs,';
19521: IF l_prog_view='Y' then
19522: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19523: ELSE
19524: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19525: END IF;
19526: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value , e.leaf_node_flag leaf_node_flag
19527: from eni_item_vbh_nodes_v e
19528: where e.parent_id =:l_cat_id
19572: fii_time_rpt_struct_v cal,';
19573: IF l_prog_view='Y' then
19574: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19575: ELSE
19576: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19577: END IF;
19578: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value , e.leaf_node_flag leaf_node_flag
19579: from eni_item_vbh_nodes_v e
19580: where e.parent_id = :l_cat_id
19624: mtl_default_category_sets mdcs,';
19625: IF l_prog_view='Y' then
19626: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19627: ELSE
19628: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19629: END IF;
19630: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value e.leaf_node_flag leaf_node_flag ,
19631: from eni_item_vbh_nodes_v e
19632: where e.parent_id =:l_cat_id
19677: fii_time_rpt_struct_v cal,';
19678: IF l_prog_view='Y' then
19679: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19680: ELSE
19681: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19682: END IF;
19683: l_sqltext:=l_sqltext||' ,(select e.id id ,e.value value
19684: from eni_item_vbh_nodes_v e
19685: where e.parent_id = :l_cat_id
19730: mtl_default_category_sets mdcs,';
19731: IF l_prog_view='Y' then
19732: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19733: ELSE
19734: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19735: END IF;
19736: l_sqltext :=l_sqltext||' ,(select e.id id ,e.value value , e.leaf_node_flag
19737: from eni_item_vbh_nodes_v e
19738: where e.parent_id =:l_cat_id
19784: fii_time_rpt_struct_v cal,';
19785: IF l_prog_view='Y' then
19786: l_sqltext := l_sqltext ||' bim_i_top_objects ac';
19787: ELSE
19788: l_sqltext := l_sqltext ||' ams_act_access_denorm ac, bim_i_source_codes s';
19789: END IF;
19790: l_sqltext := l_sqltext ||' ,(select e.id id ,e.value value
19791: from eni_item_vbh_nodes_v e
19792: where e.parent_id = :l_cat_id
20009: s.child_object_usage usage,
20010: sum(nvl(a.metric'||l_curr_suffix1||',0)) BIM_ATTRIBUTE4,
20011: sum(nvl(a.cost_actual'||l_curr_suffix||',0)) BIM_ATTRIBUTE5
20012: from
20013: bim_i_marketing_facts a , bim_i_source_codes s ,
20014: ams_campaign_schedules_tl b, ams_campaigns_all_tl c,
20015: ams_media_tl d
20016: ';
20017: l_where :=' WHERE
20197: l_col VARCHAR2(1000);
20198: /* cursor to get type of object passed from the page ******/
20199: cursor get_obj_type is
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);
20248: IF l_country IS NULL THEN
20249: l_country := 'N';
20250: END IF;
20251: /************Start Inner Query to get current acitve objects *************************/
20252: l_inner := ', ( select distinct codes.source_code_id from BIM_I_obj_METS_MV a,BIM_I_SOURCE_CODES codes
20253: ,fii_time_rpt_struct_v cal';
20254: IF l_admin_status = 'N' THEN
20255: l_inner := l_inner || ',bim_i_top_objects r ';
20256: end if;
20464: l_where := l_where ||' AND a.source_code_id=ac.source_code_id
20465: AND ac.resource_id = :l_resource_id ';
20466: /************************************************/
20467: ELSE
20468: l_from := l_from ||',bim_i_top_objects ac,bim_i_source_codes src ';
20469: l_where := l_where ||' AND a.source_code_id=src.source_code_id
20470: AND a.source_code_id = ac.source_code_id
20471: AND ac.resource_id = :l_resource_id
20472: AND src.object_type NOT IN (''RCAM'')';
20548: ' name.name,name.object_id,name.object_type'
20549: ||l_select_filter;
20550: ELSE
20551: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
20552: /* appending table in l_form and joining conditon for the bim_i_source_codes */
20553:
20554: IF l_object_type='CAMP' then
20555:
20556: l_sqltext := l_select_cal ||
20944: /* cursor to get type of object passed from the page ******/
20945: cursor get_obj_type
20946: is
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);
21250: l_where := l_where ||' AND a.source_code_id=ac.source_code_id
21251: AND ac.resource_id = :l_resource_id ';
21252: /************************************************/
21253: ELSE
21254: l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
21255: l_where := l_where ||' AND a.source_code_id=src.source_code_id
21256: AND src.object_id=ac.object_id
21257: AND src.object_type=ac.object_type
21258: AND ac.resource_id = :l_resource_id
21342: l_groupby|| l_select_filter /* appending filter clause */
21343: ;
21344: ELSE
21345: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
21346: /* appending table in l_form and joining conditon for the bim_i_source_codes */
21347: l_where :=l_where ||
21348: ' AND a.immediate_parent_id = :l_campaign_id ';
21349: -- if program is selected from the page means it may have childern as programs,campaigns,events or one off events
21350: IF l_object_type in ('RCAM','EVEH') THEN
21838: /* cursor to get type of object passed from the page ******/
21839: cursor get_obj_type
21840: is
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);
22057: l_where := l_where ||' AND a.source_code_id=ac.source_code_id
22058: AND ac.resource_id = :l_resource_id ';
22059: /************************************************/
22060: ELSE
22061: l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
22062: l_where := l_where ||' AND a.source_code_id=src.source_code_id
22063: AND src.object_id=ac.object_id
22064: AND src.object_type=ac.object_type
22065: AND src.object_type NOT IN (''RCAM'')
22141: l_select_filter /* appending filter clause */
22142: ;
22143: ELSE
22144: /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
22145: /* appending table in l_form and joining conditon for the bim_i_source_codes */
22146:
22147: l_where :=l_where ||' AND a.immediate_parent_id=:l_campaign_id ';
22148:
22149: -- if program is selected from the page means it may have childern as programs,campaigns,events or one off events
22350: l_where := l_where ||
22351: ' AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
22352: END IF;
22353: ELSE
22354: l_from := l_from||' ,bim_i_obj_mets_mv a ' ; --, bim_i_source_codes b ';
22355: l_where := l_where ||
22356: ' AND a.source_code_id = :l_campaign_id
22357: AND a.object_country =d.territory_code(+) AND d.language(+) = userenv(''LANG'') ';
22358: END IF;
22387: ' AND d.id (+)= a.activity_id
22388: AND a.object_country = :l_country';
22389: END IF;
22390: ELSE
22391: l_from := l_from||' ,bim_obj_chnl_mv a ' ; --, bim_i_source_codes b ';
22392: l_where := l_where ||
22393: ' AND a.source_code_id = :l_campaign_id
22394: AND d.id (+)= a.activity_id
22395: AND a.object_country = :l_country';
22420: ' AND d.id (+)= a.object_region
22421: AND a.object_country = :l_country';
22422: END IF;
22423: ELSE
22424: l_from := l_from||' ,bim_obj_regn_mv a '; --, bim_i_source_codes b ';
22425: l_where := l_where ||
22426: ' AND a.source_code_id = :l_campaign_id
22427: AND d.id (+)= a.object_region
22428: AND a.object_country = :l_country';
22553: /* cursor to get type of object passed from the page ******/
22554: cursor get_obj_type
22555: is
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);