eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_CLASSIC_UPGRADE dependencies on OTA_CATEGORY_USAGES

Line 75: l_category_usage_id ota_category_usages.category_usage_id%Type ;

71: -- ----------------------------------------------------------------------------
72: Procedure Create_Default_Dm(p_business_group_id in number,
73: p_dm_id IN OUT NOCOPY Number,
74: p_update_id in number default 1 ) is
75: l_category_usage_id ota_category_usages.category_usage_id%Type ;
76: l_object_version_number ota_category_usages.object_version_number%Type ;
77: l_dm_name ota_category_usages_tl.category%Type;
78: l_err_code varchar2(72);
79: l_err_msg varchar2(2000);

Line 76: l_object_version_number ota_category_usages.object_version_number%Type ;

72: Procedure Create_Default_Dm(p_business_group_id in number,
73: p_dm_id IN OUT NOCOPY Number,
74: p_update_id in number default 1 ) is
75: l_category_usage_id ota_category_usages.category_usage_id%Type ;
76: l_object_version_number ota_category_usages.object_version_number%Type ;
77: l_dm_name ota_category_usages_tl.category%Type;
78: l_err_code varchar2(72);
79: l_err_msg varchar2(2000);
80: l_course_min_st_dt date;

Line 77: l_dm_name ota_category_usages_tl.category%Type;

73: p_dm_id IN OUT NOCOPY Number,
74: p_update_id in number default 1 ) is
75: l_category_usage_id ota_category_usages.category_usage_id%Type ;
76: l_object_version_number ota_category_usages.object_version_number%Type ;
77: l_dm_name ota_category_usages_tl.category%Type;
78: l_err_code varchar2(72);
79: l_err_msg varchar2(2000);
80: l_course_min_st_dt date;
81: Cursor csr_check_dm(p_dm in varchar2) is

Line 83: From Ota_category_usages_vl ocu

79: l_err_msg varchar2(2000);
80: l_course_min_st_dt date;
81: Cursor csr_check_dm(p_dm in varchar2) is
82: Select Ocu.Category_usage_id
83: From Ota_category_usages_vl ocu
84: Where ocu.Business_group_id = p_business_group_id
85: and ocu.Category = p_dm
86: and ocu.Type = 'DM' ;
87: l_delivery_mode_id ota_category_usages.category_usage_id%Type;

Line 87: l_delivery_mode_id ota_category_usages.category_usage_id%Type;

83: From Ota_category_usages_vl ocu
84: Where ocu.Business_group_id = p_business_group_id
85: and ocu.Category = p_dm
86: and ocu.Type = 'DM' ;
87: l_delivery_mode_id ota_category_usages.category_usage_id%Type;
88:
89: Begin
90:
91: Select lkp.Meaning into l_dm_name

Line 103: (SELECT ACI.ACTIVITY_VERSION_ID FROM OTA_ACT_CAT_INCLUSIONS ACI,OTA_CATEGORY_USAGES CTU

99: begin
100: SELECT MIN(TAV.START_DATE) INTO l_course_min_st_dt
101: FROM OTA_ACTIVITY_VERSIONS TAV
102: WHERE NOT EXISTS
103: (SELECT ACI.ACTIVITY_VERSION_ID FROM OTA_ACT_CAT_INCLUSIONS ACI,OTA_CATEGORY_USAGES CTU
104: WHERE ACI.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
105: AND ACI.CATEGORY_USAGE_ID = CTU.CATEGORY_USAGE_ID
106: AND CTU.TYPE = 'DM') ;
107: If l_course_min_st_dt is NULL then

Line 124: insert into ota_category_usages_tl

120: ,p_category_usage_id => l_category_usage_id
121: ,p_object_version_number => l_object_version_number
122: );
123: p_dm_id := l_category_usage_id;
124: insert into ota_category_usages_tl
125: ( CATEGORY_USAGE_ID,
126: LANGUAGE ,
127: CATEGORY ,
128: DESCRIPTION ,

Line 174: Select ctu.Category_usage_id from ota_Category_usages ctu, ota_Category_usages ct

170: p_root_cat_usage_id in out NOCOPY Number,
171: p_update_id in number default 1 ) IS
172:
173: Cursor Csr_chk_root_ctg_exists Is
174: Select ctu.Category_usage_id from ota_Category_usages ctu, ota_Category_usages ct
175: where ctu.parent_cat_usage_id is NULL
176: and ctu.category_usage_id = ct.parent_cat_usage_id
177: and ctu.business_group_id = p_business_group_id
178: and ctu.business_group_id = ct.business_group_id

Line 184: FROM ota_category_usages ocu

180: and ct.type = 'C' ;
181:
182: CURSOR csr_Category_present is
183: SELECT ocu.category_usage_id,ocu.start_date_active
184: FROM ota_category_usages ocu
185: WHERE ocu.category = p_business_group_name -- business_group_id AND name is unique
186: AND ocu.business_group_id = p_business_group_id
187: AND type = 'C' ;
188: l_category_usage_id ota_category_usages.category_usage_id%Type;

Line 188: l_category_usage_id ota_category_usages.category_usage_id%Type;

184: FROM ota_category_usages ocu
185: WHERE ocu.category = p_business_group_name -- business_group_id AND name is unique
186: AND ocu.business_group_id = p_business_group_id
187: AND type = 'C' ;
188: l_category_usage_id ota_category_usages.category_usage_id%Type;
189: l_parent_cat_usage_id ota_category_usages.parent_cat_usage_id%Type;
190: l_start_date ota_category_usages.start_date_active%Type;
191: l_object_version_number ota_category_usages.object_version_number%Type;
192: l_type Varchar2(1) := 'C' ;

Line 189: l_parent_cat_usage_id ota_category_usages.parent_cat_usage_id%Type;

185: WHERE ocu.category = p_business_group_name -- business_group_id AND name is unique
186: AND ocu.business_group_id = p_business_group_id
187: AND type = 'C' ;
188: l_category_usage_id ota_category_usages.category_usage_id%Type;
189: l_parent_cat_usage_id ota_category_usages.parent_cat_usage_id%Type;
190: l_start_date ota_category_usages.start_date_active%Type;
191: l_object_version_number ota_category_usages.object_version_number%Type;
192: l_type Varchar2(1) := 'C' ;
193: l_err_code varchar2(72);

Line 190: l_start_date ota_category_usages.start_date_active%Type;

186: AND ocu.business_group_id = p_business_group_id
187: AND type = 'C' ;
188: l_category_usage_id ota_category_usages.category_usage_id%Type;
189: l_parent_cat_usage_id ota_category_usages.parent_cat_usage_id%Type;
190: l_start_date ota_category_usages.start_date_active%Type;
191: l_object_version_number ota_category_usages.object_version_number%Type;
192: l_type Varchar2(1) := 'C' ;
193: l_err_code varchar2(72);
194: l_err_msg varchar2(2000);

Line 191: l_object_version_number ota_category_usages.object_version_number%Type;

187: AND type = 'C' ;
188: l_category_usage_id ota_category_usages.category_usage_id%Type;
189: l_parent_cat_usage_id ota_category_usages.parent_cat_usage_id%Type;
190: l_start_date ota_category_usages.start_date_active%Type;
191: l_object_version_number ota_category_usages.object_version_number%Type;
192: l_type Varchar2(1) := 'C' ;
193: l_err_code varchar2(72);
194: l_err_msg varchar2(2000);
195:

Line 223: Update Ota_category_usages

219: );
220:
221: -- To bypass the ota_ctu_bus.chk_root_cat validation, created the root category with parent_cat_usage_id as '-1'
222: -- Updating the dummy value to NULL
223: Update Ota_category_usages
224: Set Parent_cat_usage_id = Null
225: Where Category_usage_id = l_category_usage_id
226: and Business_group_id = p_business_group_id
227: and Parent_cat_usage_id = -1 ;

Line 229: Insert into ota_category_usages_tl

225: Where Category_usage_id = l_category_usage_id
226: and Business_group_id = p_business_group_id
227: and Parent_cat_usage_id = -1 ;
228:
229: Insert into ota_category_usages_tl
230: (Category_usage_Id,
231: Language,
232: Category,
233: Description,

Line 255: From OTA_CATEGORY_USAGES_TL T

251: Where orgtl.organization_id = p_business_group_id
252: and org.organization_id = org.business_group_id
253: and org.organization_id = orgtl.organization_id
254: And Not Exists (Select '1'
255: From OTA_CATEGORY_USAGES_TL T
256: Where T.Category_usage_Id = l_Category_usage_Id
257: And T.Language = Orgtl.Language ) ;
258:
259:

Line 288: Update Ota_category_usages

284: -- There can be only one category exist wih parent_cat_usage_id as NULL
285: -- It will be the root category for that BG. And hence updating the
286: -- other categories belongs to that BG to have Category as Parent.
287:
288: Update Ota_category_usages
289: Set parent_cat_usage_id = l_parent_cat_usage_id
290: Where parent_cat_usage_id is null
291: and Type = 'C'
292: and Business_group_id = p_business_group_id

Line 321: from ota_category_usages_vl

317: return number
318: is
319: cursor c_duplicate_dm(p_cat_name varchar2,p_bg_id number) is
320: select category_usage_id
321: from ota_category_usages_vl
322: where category = p_cat_name
323: and type = 'DM'
324: and business_group_id = p_bg_id;
325:

Line 326: l_ovn ota_category_usages.object_version_number%type;

322: where category = p_cat_name
323: and type = 'DM'
324: and business_group_id = p_bg_id;
325:
326: l_ovn ota_category_usages.object_version_number%type;
327: l_category_usage_id ota_category_usages.category_usage_id%type;
328:
329: begin
330: open c_duplicate_dm(p_name,p_business_group_id);

Line 327: l_category_usage_id ota_category_usages.category_usage_id%type;

323: and type = 'DM'
324: and business_group_id = p_bg_id;
325:
326: l_ovn ota_category_usages.object_version_number%type;
327: l_category_usage_id ota_category_usages.category_usage_id%type;
328:
329: begin
330: open c_duplicate_dm(p_name,p_business_group_id);
331: fetch c_duplicate_dm into l_category_usage_id;

Line 362: from ota_category_usages_vl ctu

358: ,ctu.category
359: ,ctu.online_flag
360: ,ctu.synchronous_flag
361: ,ctu.business_group_id
362: from ota_category_usages_vl ctu
363: where ctu.type ='DM'
364: and nvl(ctu.online_flag,'N') = 'N'
365: and nvl(ctu.synchronous_flag,'Y')= 'Y'
366: and ctu.category in (select lkp.meaning

Line 373: from ota_category_usages ctu

369: and lkp.lookup_code = 'INCLASS');
370:
371: cursor c_online_courses(p_dm_id number) is
372: select tav.activity_version_id
373: from ota_category_usages ctu
374: ,ota_activity_versions tav
375: ,ota_act_cat_inclusions aci
376: where aci.category_usage_id = ctu.category_usage_id
377: and tav.activity_version_id = aci.activity_version_id

Line 393: l_online_flag ota_category_usages.online_flag%type;

389: from ota_events evt
390: where evt.activity_version_id = p_act_ver_id;
391:
392: l_found number;
393: l_online_flag ota_category_usages.online_flag%type;
394: l_sync_flag ota_category_usages.synchronous_flag%type;
395:
396: l_sp_count number;
397: l_sched_count number;

Line 394: l_sync_flag ota_category_usages.synchronous_flag%type;

390: where evt.activity_version_id = p_act_ver_id;
391:
392: l_found number;
393: l_online_flag ota_category_usages.online_flag%type;
394: l_sync_flag ota_category_usages.synchronous_flag%type;
395:
396: l_sp_count number;
397: l_sched_count number;
398:

Line 462: from ota_category_usages_vl ctu

458: ,ctu.category
459: ,ctu.online_flag
460: ,ctu.synchronous_flag
461: ,ctu.business_group_id
462: from ota_category_usages_vl ctu
463: where ctu.type = 'DM'
464: and nvl(ctu.online_flag,'N') = 'N'
465: and nvl(ctu.synchronous_flag,'Y')= 'Y'
466: and ctu.category not in (select lkp.meaning

Line 473: from ota_category_usages ctu

469: and lkp.lookup_code = 'INCLASS');
470:
471: cursor c_online_courses(p_dm_id number) is
472: select tav.activity_version_id
473: from ota_category_usages ctu
474: ,ota_activity_versions tav
475: ,ota_act_cat_inclusions aci
476: where aci.category_usage_id = ctu.category_usage_id
477: and tav.activity_version_id = aci.activity_version_id

Line 491: l_online_flag ota_category_usages.online_flag%type;

487: from ota_events evt
488: where evt.activity_version_id = p_act_ver_id;
489:
490: l_found number;
491: l_online_flag ota_category_usages.online_flag%type;
492: l_sync_flag ota_category_usages.synchronous_flag%type;
493:
494: l_sp_count number;
495: l_sched_count number;

Line 492: l_sync_flag ota_category_usages.synchronous_flag%type;

488: where evt.activity_version_id = p_act_ver_id;
489:
490: l_found number;
491: l_online_flag ota_category_usages.online_flag%type;
492: l_sync_flag ota_category_usages.synchronous_flag%type;
493:
494: l_sp_count number;
495: l_sched_count number;
496:

Line 533: from ota_Category_usages ctu

529: update ota_act_cat_inclusions aci
530: set primary_flag = decode(category_usage_id, c_ol_dms.category_usage_id,'Y','N') --'N'
531: where aci.activity_version_id = c_ol_tav.activity_version_id
532: and aci.category_usage_id in (select ctu.category_usage_id
533: from ota_Category_usages ctu
534: where ctu.type = 'DM' ) ;
535:
536: exception
537: when others then

Line 555: update ota_category_usages

551:
552:
553: end loop;
554:
555: update ota_category_usages
556: set online_flag = l_online_flag
557: ,synchronous_flag =nvl(l_sync_flag,'N')
558: where category_usage_id = c_ol_dms.category_usage_id;
559:

Line 577: from ota_category_usages_vl ctu

573: cursor c_online_dms is
574: select ctu.category_usage_id
575: ,ctu.category
576: ,ctu.business_group_id
577: from ota_category_usages_vl ctu
578: where ctu.online_flag = 'Y'
579: and ctu.type = 'DM';
580:
581: cursor c_attach_online_dms(p_dm_id in number)is

Line 596: l_category_usage_id ota_category_usages.category_usage_id%type;

592:
593:
594: l_activity_version_id ota_activity_versions.activity_version_id%type;
595: l_dm_start_date date;
596: l_category_usage_id ota_category_usages.category_usage_id%type;
597: l_primary_flag ota_act_cat_inclusions.primary_flag%type;
598: l_found number;
599:
600: l_err_code varchar2(72);

Line 665: From Ota_Category_Usages ctu

661:
662: procedure create_root_ctg_and_dms is
663: Cursor Csr_Category_BG is
664: Select ctu.business_group_id
665: From Ota_Category_Usages ctu
666: Group by ctu.business_group_id ;
667:
668: Cursor Csr_Bg_name(p_bg_id number) is
669: Select hou.name Bg_name

Line 674: l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;

670: From hr_organization_units hou
671: Where hou.business_group_id = p_bg_id
672: and hou.business_group_id = hou.organization_id ;
673:
674: l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
675: l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
676: l_bg_name hr_organization_units.name%Type ;
677:
678: begin

Line 675: l_root_cat_usage_id ota_category_usages.category_usage_id%Type;

671: Where hou.business_group_id = p_bg_id
672: and hou.business_group_id = hou.organization_id ;
673:
674: l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
675: l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
676: l_bg_name hr_organization_units.name%Type ;
677:
678: begin
679:

Line 696: -- 2. Updates ota_category_usages.Category with Meaning from

692: -- |---------------------------< Upgrade_Category >----------------------------|
693: -- ----------------------------------------------------------------------------
694: -- This procedure does the following :
695: -- 1. Updates ota_booking_deals.category with category_usage_id
696: -- 2. Updates ota_category_usages.Category with Meaning from
697: -- lookup table (earlier it stores lookup code)
698: -- 3. Populates the category translation table ota_category_usages_tl
699: -- 4. Migrates the customer defined lookup codes(not seeded) from FREQUNCY to
700: -- OTA_DURATION_UNITS

Line 698: -- 3. Populates the category translation table ota_category_usages_tl

694: -- This procedure does the following :
695: -- 1. Updates ota_booking_deals.category with category_usage_id
696: -- 2. Updates ota_category_usages.Category with Meaning from
697: -- lookup table (earlier it stores lookup code)
698: -- 3. Populates the category translation table ota_category_usages_tl
699: -- 4. Migrates the customer defined lookup codes(not seeded) from FREQUNCY to
700: -- OTA_DURATION_UNITS
701:
702: PROCEDURE Upgrade_Category(

Line 739: from ota_category_usages ocu

735: --Should this check be included here?
736: begin
737: UPDATE ota_booking_deals tbd
738: SET tbd.category = (select ocu.category_usage_id
739: from ota_category_usages ocu
740: where tbd.category = ocu.category
741: and tbd.business_group_id = ocu.business_group_id
742: and ocu.type = 'D')
743: Where exists (select ocu.category_usage_id

Line 744: from ota_category_usages ocu

740: where tbd.category = ocu.category
741: and tbd.business_group_id = ocu.business_group_id
742: and ocu.type = 'D')
743: Where exists (select ocu.category_usage_id
744: from ota_category_usages ocu
745: where tbd.category = ocu.category
746: and tbd.business_group_id = ocu.business_group_id
747: and ocu.type = 'D');
748: Exception

Line 766: update ota_category_usages ocu

762: end;
763:
764: ota_mls_utility.set_session_language_code( l_userenv_language_code );
765:
766: update ota_category_usages ocu
767: set ocu.category = (SELECT lkp.meaning
768: FROM hr_lookups lkp
769: WHERE lkp.lookup_code = ocu.category
770: AND lkp.lookup_type = 'ACTIVITY_CATEGORY')

Line 799: Insert into OTA_CATEGORY_USAGES_TL

795: /*
796: ** Insert the TL rows.
797: */
798:
799: Insert into OTA_CATEGORY_USAGES_TL
800: (Category_usage_Id,
801: Language,
802: Category,
803: Description,

Line 821: From OTA_CATEGORY_USAGES M

817: M.Creation_date,
818: M.Last_Updated_By,
819: M.Last_Update_Date,
820: M.Last_Update_Login
821: From OTA_CATEGORY_USAGES M
822: Where M.Category_usage_id Between P_start_pkid AND P_end_pkid
823: And Not Exists (Select '1'
824: From OTA_CATEGORY_USAGES_TL T
825: Where T.Category_usage_Id = M.Category_usage_Id

Line 824: From OTA_CATEGORY_USAGES_TL T

820: M.Last_Update_Login
821: From OTA_CATEGORY_USAGES M
822: Where M.Category_usage_id Between P_start_pkid AND P_end_pkid
823: And Not Exists (Select '1'
824: From OTA_CATEGORY_USAGES_TL T
825: Where T.Category_usage_Id = M.Category_usage_Id
826: And T.Language = L_Current_Language ) ;
827:
828:

Line 848: Select Nvl(Count(Category_usage_id),0) into l_rows_processed from ota_category_usages

844: end loop;
845:
846: ota_mls_utility.set_session_language_code( l_userenv_language_code );
847:
848: Select Nvl(Count(Category_usage_id),0) into l_rows_processed from ota_category_usages
849: Where Category_usage_id between p_start_pkid and p_end_pkid ;
850:
851: p_rows_processed := l_rows_processed;
852:

Line 869: -- Ota_category_usages table.

865: -- ----------------------------------------------------------------------------
866: -- |---------------------< Upgrade_delivery_mode >----------------------------|
867: -- ----------------------------------------------------------------------------
868: -- This procedure creates the Activity typ for each category exists in
869: -- Ota_category_usages table.
870: PROCEDURE Upgrade_Delivery_Mode(p_update_id in number default 1 ) is
871:
872: CURSOR csr_del_mode is
873: select ocu.category_usage_id, ocu.type,ocu.business_group_id,ocu.start_date_active,ocu.end_date_active

Line 874: from ota_category_usages ocu

870: PROCEDURE Upgrade_Delivery_Mode(p_update_id in number default 1 ) is
871:
872: CURSOR csr_del_mode is
873: select ocu.category_usage_id, ocu.type,ocu.business_group_id,ocu.start_date_active,ocu.end_date_active
874: from ota_category_usages ocu
875: Where type = 'DM' or parent_cat_usage_id is not null ;
876: l_course_min_st_dt date;
877: l_course_max_end_dt date;
878: l_course_end_dt_has_null Varchar2(1) := 'N' ;

Line 900: Update Ota_Category_Usages

896: l_course_min_st_dt := least (trunc(sysdate),nvl(dm.end_date_active,trunc(sysdate)));
897: end if;
898:
899: If l_course_min_st_dt <= nvl(dm.start_date_active,l_course_min_st_dt) then
900: Update Ota_Category_Usages
901: Set Start_date_active = l_course_min_st_dt
902: Where Category_usage_id = dm.category_usage_id
903: and Business_group_id = dm.business_group_id ;
904: End If;

Line 907: Update Ota_Category_Usages

903: and Business_group_id = dm.business_group_id ;
904: End If;
905:
906: If (l_course_max_end_dt is NULL and dm.end_date_active is NOT NULL) or (l_course_max_end_dt > dm.end_date_active) then
907: Update Ota_Category_Usages
908: Set End_date_active = l_course_max_end_dt
909: Where Category_usage_id = dm.category_usage_id
910: and Business_group_id = dm.business_group_id ;
911: End If;

Line 938: ota_category_usages ctu

934:
935: CURSOR csr_primary_present (p_act_ver_id number) is
936: SELECT ctu.category_usage_id
937: FROM ota_act_cat_inclusions cat,
938: ota_category_usages ctu
939: WHERE ctu.category_usage_id = cat.category_usage_id
940: AND ctu.type = 'C'
941: AND cat.primary_flag='Y'
942: AND cat.activity_version_id = p_act_ver_id;

Line 957: l_equivalent_ctu ota_category_usages.category_usage_id%type;

953: AND cat.activity_version_id = p_act_ver_id;
954:
955:
956: l_equivalent_tad ota_activity_definitions.activity_id%type;
957: l_equivalent_ctu ota_category_usages.category_usage_id%type;
958: l_act_ver_id ota_activity_versions.activity_version_id%type;
959: l_count number;
960: l_err_code varchar2(72);
961: l_err_msg varchar2(2000);

Line 1069: -- Ota_category_usages table.

1065: -- ----------------------------------------------------------------------------
1066: -- |---------------------< Create_Activity_For_Category >---------------------|
1067: -- ----------------------------------------------------------------------------
1068: -- This procedure creates the Activity typ for each category exists in
1069: -- Ota_category_usages table.
1070: PROCEDURE Create_Activity_For_Category(
1071: p_process_control IN varchar2,
1072: p_start_pkid IN number,
1073: p_end_pkid IN number,

Line 1081: from ota_category_usages ocu, ota_category_usages_tl oct

1077:
1078:
1079: CURSOR c_activity_def is
1080: select ocu.category_usage_id, oct.category, ocu.business_group_id,oct.description
1081: from ota_category_usages ocu, ota_category_usages_tl oct
1082: where ocu.category_usage_id = oct.category_usage_id
1083: and oct.language = Userenv('LANG')
1084: and ocu.type = 'C'
1085: and ocu.category_usage_id between p_start_pkid and p_end_pkid

Line 1093: l_category ota_category_usages_tl.category%type;

1089:
1090: l_object_version_number number;
1091: l_category_usage_id number;
1092: l_activity_id number;
1093: l_category ota_category_usages_tl.category%type;
1094: l_business_group_id number;
1095: l_description ota_category_usages_tl.description%Type;
1096: l_rows_processed number := 0;
1097: l_err_code varchar2(72);

Line 1095: l_description ota_category_usages_tl.description%Type;

1091: l_category_usage_id number;
1092: l_activity_id number;
1093: l_category ota_category_usages_tl.category%type;
1094: l_business_group_id number;
1095: l_description ota_category_usages_tl.description%Type;
1096: l_rows_processed number := 0;
1097: l_err_code varchar2(72);
1098: l_err_msg varchar2(2000);
1099:

Line 1176: -- and ota_category_usages. And this new category will be the

1172: -- |--------------------< Create_Category_for_Activity >----------------------|
1173: -- ----------------------------------------------------------------------------
1174: -- This procedure does the following
1175: -- 1. Creates a Category for each BG in ota_activity_definitions
1176: -- and ota_category_usages. And this new category will be the
1177: -- parent category for other categories(belongs to that BG).
1178: -- 2. Creates Category for each Activity types, which are not
1179: -- as part of step 3. Attaches the newly created category to
1180: -- Activity versions exist under the equivalent Activity type.

Line 1232: FROM ota_category_usages ocu, ota_category_usages_tl oct

1228: WHERE lng.installed_flag in ('I', 'B');
1229:
1230: CURSOR csr_duplicate_category (p_act_name varchar2, p_business_group_id number) is
1231: SELECT ocu.category_usage_id
1232: FROM ota_category_usages ocu, ota_category_usages_tl oct
1233: WHERE oct.category = p_act_name
1234: AND ocu.type ='C'
1235: AND ocu.category_usage_id = oct.category_usage_id
1236: AND ocu.business_group_id = p_business_group_id;

Line 1265: --l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;

1261: l_err_msg varchar2(2000);
1262: l_min_course_start_date date;
1263: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
1264: hr_dflex_utility.l_ignore_dfcode_varray();
1265: --l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
1266: --l_default_dm_name Hr_lookups.Meaning%Type ;
1267: --l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
1268: --l_bg_name hr_organization_units.name%Type ;
1269: Begin

Line 1267: --l_root_cat_usage_id ota_category_usages.category_usage_id%Type;

1263: l_add_struct_d hr_dflex_utility.l_ignore_dfcode_varray :=
1264: hr_dflex_utility.l_ignore_dfcode_varray();
1265: --l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
1266: --l_default_dm_name Hr_lookups.Meaning%Type ;
1267: --l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
1268: --l_bg_name hr_organization_units.name%Type ;
1269: Begin
1270:
1271: -- Ignore Category Dff Validation while creating Category from Activity.

Line 1273: l_add_struct_d(l_add_struct_d.count) := 'OTA_CATEGORY_USAGES';

1269: Begin
1270:
1271: -- Ignore Category Dff Validation while creating Category from Activity.
1272: l_add_struct_d.extend(1);
1273: l_add_struct_d(l_add_struct_d.count) := 'OTA_CATEGORY_USAGES';
1274: hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
1275:
1276: for c_get_act in csr_activity_type loop
1277:

Line 1284: From Ota_category_usages

1280: l_act_name := c_get_act.name;
1281:
1282: Begin
1283: Select Category_usage_id into l_parent_category_usage_id
1284: From Ota_category_usages
1285: Where business_group_id = l_business_group_id
1286: and parent_cat_usage_id is NULL
1287: and type = 'C';
1288: Exception

Line 1347: Insert into ota_category_usages_tl

1343: ,P_ATTRIBUTE20 => c_get_act.tad_information20
1344:
1345: );
1346:
1347: Insert into ota_category_usages_tl
1348: (Category_usage_Id,
1349: Language,
1350: Category,
1351: Description,

Line 1436: l_default_dm ota_category_usages.category_usage_id%Type ;

1432: l_parent_offering_id ota_offerings.Offering_id%type;
1433: l_vendor_id number;
1434: l_supplier_id number;
1435: l_rows_processed number := 0;
1436: l_default_dm ota_category_usages.category_usage_id%Type ;
1437: l_resource_usage_id number;
1438: l_duration_units ota_offerings.duration_units%Type;
1439: l_duration ota_offerings.duration%Type;
1440: l_default_language Fnd_languages.Language_id%Type;

Line 1471: OTA_CATEGORY_USAGES TCU

1467:
1468: CURSOR c_get_dm(p_act_ver_id number)is
1469: SELECT aci.CATEGORY_USAGE_ID
1470: FROM ota_act_cat_inclusions aci ,
1471: OTA_CATEGORY_USAGES TCU
1472: WHERE ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID
1473: AND TCU.TYPE = 'DM'
1474: AND primary_flag = 'Y'
1475: AND activity_version_id = p_act_ver_id;

Line 1480: OTA_CATEGORY_USAGES TCU

1476:
1477: CURSOR c_get_dm1(p_act_ver_id number)is
1478: SELECT aci.CATEGORY_USAGE_ID
1479: FROM ota_act_cat_inclusions aci ,
1480: OTA_CATEGORY_USAGES TCU
1481: WHERE ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID
1482: AND rownum = 1
1483: AND TCU.TYPE = 'DM'
1484: And Aci.activity_Version_id = p_act_ver_id;

Line 1504: Ota_category_usages_vl ocu, hr_lookups lkp

1500: AND off.activity_version_id = p_act_ver_id;
1501:
1502: cursor c_def_dm_for_bg(l_bg_id number) is
1503: Select ocu.category_usage_id from
1504: Ota_category_usages_vl ocu, hr_lookups lkp
1505: Where Ocu.Category = Meaning
1506: and lkp.Lookup_type = 'ACTIVITY_CATEGORY'
1507: and lkp.lookup_code = 'INCLASS'
1508: and ocu.type = 'DM'

Line 1512: from ota_category_usages

1508: and ocu.type = 'DM'
1509: and ocu.business_group_id = l_bg_id;
1510: cursor c_dm_name (l_dm_id number) is
1511: select category
1512: from ota_category_usages
1513: where type='DM'
1514: and category_usage_id = l_dm_id;
1515:
1516: cursor c_get_tav_dms(p_tav_id number) is

Line 1520: ota_category_usages_vl ctu

1516: cursor c_get_tav_dms(p_tav_id number) is
1517: select ctu.category_usage_id,
1518: ctu.category
1519: from ota_act_cat_inclusions aci ,
1520: ota_category_usages_vl ctu
1521: where aci.activity_version_id = p_tav_id
1522: and ctu.category_usage_id = aci.category_usage_id
1523: and ctu.type = 'DM';
1524:

Line 1537: l_new_dm_name ota_category_usages.category%type;

1533: and a.language_id = p_lang_id;
1534:
1535: l_fnd_lang_code fnd_natural_languages.language_code%type;
1536:
1537: l_new_dm_name ota_category_usages.category%type;
1538:
1539:
1540: -- Changed Table type declaration for 3389869 as multidimesion table declaration is not supported in 8.1.7
1541: -- Type Event_tab is Table of c_get_lang%RowType INDEX BY BINARY_INTEGER;

Line 1963: from ota_category_usages

1959: -- Delete Delivery Mode
1960: delete from ota_act_cat_inclusions
1961: where activity_version_id = l_act_ver_id
1962: and category_usage_id in (select category_usage_id
1963: from ota_category_usages
1964: where type = 'DM');
1965:
1966: --3) Trainer Competences
1967: delete from per_competence_elements

Line 2517: l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;

2513: From hr_organization_units hou
2514: Where hou.business_group_id = p_bg_id
2515: and hou.business_group_id = hou.organization_id ;
2516:
2517: l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
2518: l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
2519: l_bg_name hr_organization_units.name%Type ;
2520:
2521: begin

Line 2518: l_root_cat_usage_id ota_category_usages.category_usage_id%Type;

2514: Where hou.business_group_id = p_bg_id
2515: and hou.business_group_id = hou.organization_id ;
2516:
2517: l_default_dm Ota_Category_usages_tl.Category_usage_id%Type;
2518: l_root_cat_usage_id ota_category_usages.category_usage_id%Type;
2519: l_bg_name hr_organization_units.name%Type ;
2520:
2521: begin
2522:

Line 2588: from ota_category_usages

2584: procedure upgrade_root_category_dates is
2585:
2586: cursor min_ctg_start_date(parent_ctg number) is
2587: select nvl(min(start_date_active),trunc(sysdate))
2588: from ota_category_usages
2589: where parent_cat_usage_id = parent_ctg
2590: and type = 'C';
2591:
2592: cursor c_root_ctgs is

Line 2594: from ota_category_usages

2590: and type = 'C';
2591:
2592: cursor c_root_ctgs is
2593: select category_usage_id,start_date_active
2594: from ota_category_usages
2595: where type = 'C'
2596: and parent_cat_usage_id is null;
2597:
2598: l_start_date date;

Line 2610: update ota_category_usages

2606: close min_ctg_start_date;
2607:
2608: l_min_start_date := least(nvl(c_roots.start_date_active,l_start_date),l_start_date);
2609:
2610: update ota_category_usages
2611: set start_date_active = l_min_start_date
2612: where category_usage_id = c_roots.category_usage_id;
2613:
2614:

Line 2727: and cat_fcu.Descriptive_FlexField_Name = 'OTA_CATEGORY_USAGES'

2723: and fcu.flex_value_set_id = fvs.flex_value_set_id(+)
2724: and Not exists (SELECT 'Y'
2725: From Fnd_Descr_Flex_Col_Usage_Vl cat_fcu
2726: Where cat_fcu.Application_id = fcu.application_id
2727: and cat_fcu.Descriptive_FlexField_Name = 'OTA_CATEGORY_USAGES'
2728: and cat_fcu.Descriptive_Flex_Context_code = fcu.Descriptive_Flex_Context_code
2729: and cat_fcu.end_user_column_name = fcu.end_user_column_name );
2730:
2731: l_segrec FND_DESCR_FLEX_COLUMN_USAGES%RowType ;

Line 2762: Where Descriptive_Flexfield_name = 'OTA_CATEGORY_USAGES'

2758: begin
2759: l_context_exists := NULL ;
2760: Select Max('Y') into l_context_exists
2761: From FND_DESCR_FLEX_CONTEXTS_vl
2762: Where Descriptive_Flexfield_name = 'OTA_CATEGORY_USAGES'
2763: and Descriptive_Flex_COntext_Code = dff_context.Descriptive_Flex_Context_code ;
2764: -- If there is a definition already existis and it is a Global context
2765: -- then NO context will be created but the strucre will be added to the existing one.
2766:

Line 2771: flexfield_name => 'OTA_CATEGORY_USAGES',

2767: If (NOT (dff_context.Global_Flag = 'Y' OR l_context_exists is NOT NULL)) then
2768:
2769: fnd_flex_dsc_api.create_context(
2770: appl_short_name => 'OTA' ,
2771: flexfield_name => 'OTA_CATEGORY_USAGES',
2772: context_code => Dff_context.Descriptive_flex_context_code,
2773: context_name => Nvl(dff_context.DESCRiptive_FLEX_CONTEXT_NAME,'*-*'),
2774: description => dff_context.DESCRIPTION,
2775: enabled => dff_context.ENABLED_FLAG );

Line 2783: flexfield_name => 'OTA_CATEGORY_USAGES', --'Add''l Category Information',

2779: Loop
2780: begin
2781: fnd_flex_dsc_api.create_segment(
2782: appl_short_name => 'OTA' ,
2783: flexfield_name => 'OTA_CATEGORY_USAGES', --'Add''l Category Information',
2784: context_name => Nvl(dff_context.DESCRiptive_FLEX_CONTEXT_NAME,'*-*'),
2785: name => I.END_USER_COLUMN_NAME,
2786: column => Replace(I.APPLICATION_COLUMN_NAME,'TAD_INFORMATION','ATTRIBUTE'),
2787: description => I.DESCRIPTION,