[Home] [Help]
585: ,ota_act_cat_inclusions aci
586: where aci.category_usage_id = p_dm_id
587: and tav.activity_version_id = aci.activity_version_id
588: and tav.rco_id is null
589: and not exists (select 1 from ota_offerings off
590: where off.activity_version_id = tav.activity_version_id
591: and rownum =1);
592:
593:
1428: l_lang_id number := 0;
1429: l_base_lang_id fnd_languages.language_id%Type ;
1430: l_language_id number ;
1431: l_competence_element_id number;
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 ;
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;
1441:
1442: l_language_code fnd_natural_languages.language_code%type;
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;
1441:
1442: l_language_code fnd_natural_languages.language_code%type;
1443:
1492: Group by Language_id , decode(event_type,'SELFPACED','SELFPACED','SCHEDULED');
1493:
1494:
1495: CURSOR c_dup_off(p_name varchar, p_act_ver_id number) is
1496: SELECT Oft.Offering_id FROM ota_offerings off, ota_offerings_tl oft
1497: WHERE off.offering_id = oft.offering_id
1498: AND oft.name = p_name
1499: AND oft.language = USERENV('LANG')
1500: AND off.activity_version_id = p_act_ver_id;
1547: l_event_exists Varchar2(1) ;
1548: l_res_usg_cnt Number(5) ;
1549: l_cmp_cnt Number(5) ;
1550: l_evt_language_id Ota_Events.Language_id%Type ;
1551: l_learning_object_id ota_offerings.learning_object_id%type;
1552: l_iteration Number(5) ;
1553:
1554: l_err_code varchar2(72);
1555: l_err_msg varchar2(2000);
1669: -- select nvl(count(1),0)
1670: select Max(offering_id)
1671: -- into l_off_dup_lang
1672: into l_parent_offering_id
1673: from ota_offerings
1674: where language_id = l_language_id
1675: and activity_version_id =l_act_ver_id;
1676:
1677: -- enh2733966 fnd_natural_lang support --
1714: p_vendor_id => Ids.vendor_id,
1715: p_language_code => l_language_code -- enh2733966 fnd_natural_lang support --
1716: );
1717:
1718: Insert into ota_offerings_tl
1719: (offering_Id,
1720: Language,
1721: name,
1722: Description,
2229: --enh 2733966 --
2230: -- ----------------------------------------------------------------------------
2231: -- |--------------------------< Upgrade_Off_Lang_Code >-----------------------|
2232: -- ----------------------------------------------------------------------------
2233: -- This procedure populates Language_code in OTA_OFFERINGS if it is null.
2234:
2235: Procedure Upgrade_Off_Lang_Code is
2236:
2237: begin
2234:
2235: Procedure Upgrade_Off_Lang_Code is
2236:
2237: begin
2238: update ota_offerings a
2239: set a.language_code=decode(a.language_code, null, (select fnl.language_code
2240: FROM fnd_languages fl,
2241: fnd_natural_languages fnl WHERE
2242: fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
2284: --enh 2733966 --
2285: -- ----------------------------------------------------------------------------
2286: -- |--------------------------< Upgrade_Language_Code >----------------------------|
2287: -- ----------------------------------------------------------------------------
2288: -- This procedure checks if Language_Code is null in OTA_OFFERINGS, OTA_LEARNING_OBJECTS
2289: -- and OTA_COMPETENCE_LANGUAGES and populate it based on Langauge_Id
2290: Procedure Upgrade_Language_Code is
2291:
2292: cursor c_exist_off is
2290: Procedure Upgrade_Language_Code is
2291:
2292: cursor c_exist_off is
2293: select 1
2294: from ota_offerings
2295: where language_code is null;
2296:
2297: cursor c_exist_lo is
2298: select 1
2339: exception
2340: when others then
2341:
2342: l_err_code := SQLCODE;
2343: l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When upgrading Language code for OTA_OFFERINGS');
2344:
2345: add_log_entry( p_table_name => 'UPG_LANGUAGE_CODE'
2346: ,p_source_primary_key => '999'
2347: ,p_object_value => 'No Language code updated for OTA_OFFERINGS'
2343: l_err_msg := nvl(substr(SQLERRM,1,2000),'Error When upgrading Language code for OTA_OFFERINGS');
2344:
2345: add_log_entry( p_table_name => 'UPG_LANGUAGE_CODE'
2346: ,p_source_primary_key => '999'
2347: ,p_object_value => 'No Language code updated for OTA_OFFERINGS'
2348: ,p_message_text => l_err_msg
2349: ,p_upgrade_id => l_upg_id
2350: ,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
2351: ,p_log_type => LOG_TYPE_E
2551: where flt.language_code = fl.language_code
2552: and flt.language = p_language
2553: and fl.language_id = p_language_id ;
2554: l_lang_len number;
2555: l_off_name ota_offerings_tl.name%type := p_off_name;
2556: description fnd_languages_tl.description%type;
2557: begin
2558:
2559:
2882: AND RowID Between p_start_rowid and p_end_rowid ;
2883:
2884: CURSOR c_learning_object_id IS
2885: SELECT off.learning_object_id
2886: FROM ota_events evt, ota_offerings off
2887: WHERE evt.event_id = l_event_id
2888: and evt.parent_offering_id = off.offering_id;
2889:
2890: CURSOR c_less_status(p_user_id in number,p_lo_id in number) IS