[Home] [Help]
42: procedure init_dyt_pkg is
43: l_index number := 0;
44: l_proc varchar2(30) := g_package||'.init_dyt_pkg';
45: begin
46: hr_utility.set_location(l_proc,10);
47:
48: -- delete all elements from package head pl/sql table.
49: l_index := g_dyt_pkg_head.first;
50: while l_index is not null loop
61: --initialize the index
62: g_dyt_pkg_hindex := 0;
63: g_dyt_pkg_bindex := 0;
64:
65: hr_utility.set_location(l_proc,900);
66: exception
67: when others then
68: hr_utility.trace('Unhandled Error: '||l_proc);
69: hr_utility.set_location(l_proc,1000);
64:
65: hr_utility.set_location(l_proc,900);
66: exception
67: when others then
68: hr_utility.trace('Unhandled Error: '||l_proc);
69: hr_utility.set_location(l_proc,1000);
70: raise;
71: end init_dyt_pkg;
72:
65: hr_utility.set_location(l_proc,900);
66: exception
67: when others then
68: hr_utility.trace('Unhandled Error: '||l_proc);
69: hr_utility.set_location(l_proc,1000);
70: raise;
71: end init_dyt_pkg;
72:
73: --
97: l_end number;
98: l_next varchar2(1);
99: l_proc varchar2(80) := g_package||'.add_to_dyt_pkg';
100: begin
101: hr_utility.set_location(l_proc,10);
102: <
103: while l_code_index is not null loop
104: l_start := 0;
105: -- read the string of the passed on code, chop it into the array element
134: end loop char254_loop;
135:
136: l_code_index := p_new_code_tbl.next(l_code_index);
137: end loop row_of_code_loop;
138: hr_utility.set_location(l_proc,900);
139: exception
140: when others then
141: hr_utility.trace('Unhandled Error: '||l_proc);
142: hr_utility.trace(l_proc ||' g_dyt_pkg_hindex - ' || g_dyt_pkg_hindex
137: end loop row_of_code_loop;
138: hr_utility.set_location(l_proc,900);
139: exception
140: when others then
141: hr_utility.trace('Unhandled Error: '||l_proc);
142: hr_utility.trace(l_proc ||' g_dyt_pkg_hindex - ' || g_dyt_pkg_hindex
143: ||' g_dyt_pkg_bindex - ' || g_dyt_pkg_bindex );
144: raise;
145: end ADD_TO_DYT_PKG;
138: hr_utility.set_location(l_proc,900);
139: exception
140: when others then
141: hr_utility.trace('Unhandled Error: '||l_proc);
142: hr_utility.trace(l_proc ||' g_dyt_pkg_hindex - ' || g_dyt_pkg_hindex
143: ||' g_dyt_pkg_bindex - ' || g_dyt_pkg_bindex );
144: raise;
145: end ADD_TO_DYT_PKG;
146: --============================================
1291: EXCEPTION
1292: WHEN OTHERS THEN
1293: --
1294: -- This shouldn't ever occur 'cos do_ddl seems to trap everything
1295: hr_utility.set_location('DROP PACKAGE '||p_name,ABS(SQLCODE));
1296: END drop_package;
1297: --
1298: FUNCTION module_ok(p_name IN VARCHAR2,p_type IN VARCHAR2) RETURN BOOLEAN IS
1299: --
1334: l_rows number;
1335: l_proc varchar2(80) := g_package||'.build_dyt_pkg_from_tbl';
1336:
1337: begin
1338: hr_utility.set_location(l_proc,10);
1339: hr_utility.trace('p_package_index - '||p_package_index);
1340:
1341: --
1342: l_csr_sql := dbms_sql.open_cursor;
1335: l_proc varchar2(80) := g_package||'.build_dyt_pkg_from_tbl';
1336:
1337: begin
1338: hr_utility.set_location(l_proc,10);
1339: hr_utility.trace('p_package_index - '||p_package_index);
1340:
1341: --
1342: l_csr_sql := dbms_sql.open_cursor;
1343: dbms_sql.parse( l_csr_sql, p_package_body,1,p_package_index,p_body, dbms_sql.v7 );
1344: l_rows := dbms_sql.execute( l_csr_sql );
1345: dbms_sql.close_cursor( l_csr_sql );
1346: --
1347:
1348: hr_utility.set_location(l_proc,900);
1349: exception
1350: when others then
1351: hr_utility.trace('Unhandled Exception: '||l_proc);
1352: raise;
1347:
1348: hr_utility.set_location(l_proc,900);
1349: exception
1350: when others then
1351: hr_utility.trace('Unhandled Exception: '||l_proc);
1352: raise;
1353: end build_dyt_pkg_from_tbl;
1354:
1355: --
1746: l_rc NUMBER;
1747: --
1748: l_proc VARCHAR2(30) := 'generate_trigger';
1749: BEGIN
1750: hr_utility.set_location(' Entering: '||l_proc,10);
1751: --Calling from SINGLE (Dynamic Triggers Form)
1752: --
1753: -- Fetch the trigger event information
1754: OPEN csr_name(p_id);
1761: -- Eg Set up to store as db triggers, as package code or both
1762: -- Added by jford 1-OCT-02 as part of cont calc
1763: --
1764: IF (l_dyn_type = 'P' or l_dyn_type = 'B') THEN
1765: hr_utility.trace(' Handle Dynamic Triggers as Package Style.');
1766: -- dyn trigger code should be handled as package
1767: -- >> GENERATE PACKAGE
1768: -- generate code FOR ALL DYT's ON TABLE eg many dyn_triggers
1769: gen_dyt_pkg_full_code(l_tab_id,p_ok);
1770: p_name := l_dyt_pkg;
1771: ELSE
1772: -- type is just original 'T' Trigger mechanism so use existing code
1773: -- >> GENERATE DBMS TRIGGER
1774: hr_utility.trace(' Handle Dynamic Triggers as Individual Database Trigger Style.');
1775: -- Get the trigger name in the standard format
1776: --if weve got an old crap dyt_pkg, then dump it
1777: if (l_dyt_pkg is not null) then
1778: drop_package(l_table,l_dyt_pkg);
1791: -- Create the trigger using the generated PL/SQL and the AOL routines
1792: create_trigger(l_trigger,l_table,l_action,l_sql);
1793: p_ok := module_ok(l_trigger,'TRIGGER');
1794: if (p_ok) then
1795: hr_utility.trace(' Database Trigger '||p_name||' created with success');
1796: else
1797: hr_utility.trace(' Database Trigger '||p_name||' created with failure');
1798: update pay_trigger_events
1799: set generated_flag = 'N', enabled_flag = 'N'
1793: p_ok := module_ok(l_trigger,'TRIGGER');
1794: if (p_ok) then
1795: hr_utility.trace(' Database Trigger '||p_name||' created with success');
1796: else
1797: hr_utility.trace(' Database Trigger '||p_name||' created with failure');
1798: update pay_trigger_events
1799: set generated_flag = 'N', enabled_flag = 'N'
1800: where event_id = p_id;
1801: l_enb_flag := 'N';
1808: --
1809: -- Otherwise, drop it to make sure it definitely doesn't exist
1810: drop_trigger(l_trigger);
1811: p_ok := TRUE;
1812: hr_utility.trace(' Database Trigger '||p_name||' dropped with success.');
1813: --
1814: END IF;
1815: END IF;
1816:
1813: --
1814: END IF;
1815: END IF;
1816:
1817: hr_utility.set_location(' Leaving: '||l_proc,900);
1818: END generate_trigger;
1819: --
1820: -- +---------------------------------------------------------------------------+
1821: -- | NAME : compile_package_indirect |
1862: );
1863: EXCEPTION
1864: WHEN OTHERS THEN
1865: -- Output a trrace message if we fail
1866: hr_utility.set_location('COMPILE_PACKAGE_INDIRECT',ABS(SQLCODE));
1867: END compile_package_indirect;
1868: --
1869: -- +---------------------------------------------------------------------------+
1870: -- | NAME : create_defaults |
2502: -- trigger name and error text to the standard Oracle Apps error logging
2503: -- mechanism
2504: p_sql := p_sql||'EXCEPTION'||g_eol;
2505: p_sql := p_sql||' WHEN OTHERS THEN'||g_eol;
2506: p_sql := p_sql||' hr_utility.set_location('''||
2507: get_trigger_name(p_id,l_table,l_type)||''',ABS(SQLCODE));'||g_eol;
2508: p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
2509: p_sql := p_sql||' RAISE;'||g_eol;
2510: p_sql := p_sql||' --'||g_eol;
2859:
2860:
2861: << ONE_PROC_PARAM_LOOP >>
2862: FOR i in 1..l_position.COUNT loop
2863: --hr_utility.trace(' Found parameter '||l_argument_name(i)||' type '||l_ --
2864: if l_datatype(i) <> c_dtype_varchar2 and
2865: l_datatype(i) <> c_dtype_number and
2866: l_datatype(i) <> c_dtype_date and
2867: l_datatype(i) <> c_dtype_boolean and
2868: l_datatype(i) <> c_dtype_long then
2869: -- Error: In a hook package procedure all the parameter datatypes
2870: -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2871: -- module will not execute until this problem has been resolved.
2872: hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2873: hr_utility.set_location(l_proc, 80);
2874: else
2875: -- Set the datatype string with the corresponding word value
2876: if l_datatype(i) = c_dtype_varchar2 then
2869: -- Error: In a hook package procedure all the parameter datatypes
2870: -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2871: -- module will not execute until this problem has been resolved.
2872: hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2873: hr_utility.set_location(l_proc, 80);
2874: else
2875: -- Set the datatype string with the corresponding word value
2876: if l_datatype(i) = c_dtype_varchar2 then
2877: l_datatype_str := 'VARCHAR2';
2897:
2898: END LOOP HOOK_MODULES;
2899: --Debug Output all the paramaters in my table form
2900: --FOR j in 0..(p_params.COUNT - 1) loop
2901: --hr_utility.trace(j||' '||p_params(j).value_name||' '||p_params(j).usage_type||' '||p_params(j).data_type);
2902: --end loop;
2903:
2904: hr_utility.trace('Total RHI params '||p_params.count);
2905:
2900: --FOR j in 0..(p_params.COUNT - 1) loop
2901: --hr_utility.trace(j||' '||p_params(j).value_name||' '||p_params(j).usage_type||' '||p_params(j).data_type);
2902: --end loop;
2903:
2904: hr_utility.trace('Total RHI params '||p_params.count);
2905:
2906: -- If we didnt get any hook params then there might not be a hook pkg!
2907: -- So just base the params on what we actually need, i.e. the param mappings
2908: if ( p_params.count = 0 ) then
2905:
2906: -- If we didnt get any hook params then there might not be a hook pkg!
2907: -- So just base the params on what we actually need, i.e. the param mappings
2908: if ( p_params.count = 0 ) then
2909: hr_utility.trace('No hook params => build params from what is required from components');
2910:
2911: get_dyt_pkg_params_tbl(p_tab_id,p_tab_name,p_params);
2912: --On top of the ones we need in dyt call, we also know we need p_datetrack
2913: --in both after_delete and after_update.
2922: p_params(i+1).value_name := 'P_DATETRACK_MODE';
2923: p_params(i+1).usage_type := 'PD';
2924: p_params(i+1).data_type := 'VARCHAR2';
2925:
2926: hr_utility.trace('Total RHI params '||p_params.count);
2927: -- Example of structure of p_params from get_dyt_pkg_params_tbl
2928: --local_form - :new.EFFECTIVE_END_DATE
2929: --param_form - p_new_EFFECTIVE_END_DATE
2930: --value_name - P_EFFECTIVE_END_DATE
3029: l_missing_rep := replace(missing_param.name,'OLD','NEW');
3030: else
3031: l_missing_rep := l_missing||'xxx'; --no change so will fall over
3032: end if;
3033: --hr_utility.trace('Missing param: '||l_missing||' replace with: '||l_missing_rep);
3034: p_sql := replace(p_sql,l_missing,l_missing_rep);
3035:
3036: end loop;
3037:
3045:
3046: --Get extract from this instance of :new to next :new instance
3047: --
3048: l_pos_next := instr(p_sql,':OLD.',l_pos+1);
3049: --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3050: if (l_pos_next <> 0 ) then
3051: l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3052: else
3053: l_extract := substr(p_sql,l_pos);
3059:
3060: -- Amend the prefix and make sure we're not over the 30 char limit
3061: l_new_col_name := substr(replace(l_col_name,':OLD.','P_OLD_'),1,30);
3062:
3063: --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3064:
3065: l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3066: p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3067:
3081:
3082: --Get extract from this instance of :new to next
3083: --
3084: l_pos_next := instr(p_sql,':NEW.',l_pos+1);
3085: --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3086: if (l_pos_next <> 0 ) then
3087: l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3088: else
3089: l_extract := substr(p_sql,l_pos);
3095:
3096: -- Amend the prefix and make sure we're not over the 30 char limit
3097: l_new_col_name := substr(replace(l_col_name,':NEW.','P_NEW_'),1,30);
3098:
3099: --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3100:
3101: l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3102: p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3103:
3134: l_proc_name := substr(p_dyt_name,1,30 - length(l_suffix));
3135: --Full version is first||suffix
3136: l_proc_name := l_proc_name||l_suffix;
3137:
3138: --hr_utility.trace(' Got DYT pkg procedure name: '||l_proc_name);
3139: return l_proc_name;
3140: END;
3141:
3142: --
3197:
3198: --
3199: l_proc varchar2(35) := 'gen_dyt_pkg_full_code';
3200: BEGIN
3201: hr_utility.set_location(' Entering: '||l_proc,10);
3202: -- The package to hold the code is created with two sections.
3203: -- a. The main trigger code, built using info from the dynamic triggers screen
3204: -- b. The after_update, after_insert, after_delete standard called by rhi,
3205: -- these act as wrappers calling (a)'s.
3271: l_dyt_pkg_body_tbl(0) := l_bsql;
3272:
3273: -- >>> 2. Get all parameters defined for this table in to table of records for future
3274: -- manipulation. This table holds the following info:
3275: hr_utility.set_location(' -Get parameters DYT : '||l_proc,20);
3276:
3277: -- i. local name --entered in form
3278: -- ii. generated pkg param version of i-used for internal pkg proc
3279: -- iii. usage type -- eg PI Insert, PU Update, PD Delete
3279: -- iii. usage type -- eg PI Insert, PU Update, PD Delete
3280: -- iv. type of col
3281: --
3282: get_dyt_pkg_params_tbl(l_tab_id, l_tab_name, l_dyt_params);
3283: hr_utility.set_location(' -Get parameters after_xxx : '||l_proc,25);
3284: get_dyt_rhi_params(l_tab_id, l_tab_name, l_hok_params);
3285:
3286: -- >>> 3. Add the dynamic trigger code as separate public procedures
3287: -- loop for each dyn trig, calling a procedure to create appropriate code
3285:
3286: -- >>> 3. Add the dynamic trigger code as separate public procedures
3287: -- loop for each dyn trig, calling a procedure to create appropriate code
3288: --
3289: hr_utility.set_location(' -Create dyt procedure code: '||l_proc,30);
3290: FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3291: gen_dyt_pkg_proc(dyt_rec.event_id,dyt_rec.short_name,
3292: l_tab_name,dyt_rec.triggering_action,
3293: dyt_rec.description,dyt_rec.info,
3303: -- Create the wrapper procedures that the rhi will call, these simply call
3304: -- the newly created, dynamic trigger code procedures
3305: -- Three times, one for each trigger type
3306: --
3307: hr_utility.set_location(' -Create hook entry point procedure code: '||l_proc,40);
3308: gen_dyt_pkg_rhi_proc(l_tab_name,'I','INSERT',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
3309: l_hs := ''; l_bs := '';
3310: --
3311: gen_dyt_pkg_rhi_proc(l_tab_name,'U','UPDATE',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
3333:
3334:
3335: -- >>> 6. Generate and compile this new dynamic package
3336: --
3337: hr_utility.set_location(' -Generate database package: '||l_proc,60);
3338: build_dyt_pkg_from_tbl( g_dyt_pkg_head,g_dyt_pkg_hindex,FALSE);
3339: build_dyt_pkg_from_tbl( g_dyt_pkg_body,g_dyt_pkg_bindex,TRUE);
3340:
3341: -- >>> 7. Create database trigger equivalent of dyn-trigger if required
3381: p_ok :=FALSE;
3382: l_flag := 'N';
3383: end if;
3384:
3385: --hr_utility.set_location(' -Status of database package: '||l_flag||l_proc,80);
3386: --Complete FOR WHOLE TABLE, so mark pkg gen, and all dyt as generated + enabled or vice versa
3387: -- All or nothing, either all dyt's are success, or mark all as failure
3388: update pay_dated_tables
3389: set dyn_trig_pkg_generated = l_flag
3404: end if;
3405:
3406: END LOOP;
3407: commit; --make sure updates are saved
3408: hr_utility.set_location(' Leaving: '||l_proc,900);
3409: END gen_dyt_pkg_full_code;
3410:
3411: -- +-----------------------------------------------------------------------------+
3412: -- | NAME : gen_dyt_pkg_proc |
3504: -- n/a
3505:
3506: p_bs := p_bs||'BEGIN'||g_eol;
3507: --
3508: p_bs := p_bs||' hr_utility.trace('' >DYT: Execute procedure version of Dynamic Trigger: '||p_dyt_name||''');'||g_eol;
3509:
3510:
3511: --
3512: -- Add the data migrator check - Bug 1885557
3529: -- trigger name and error text to the standard Oracle Apps error logging
3530: -- mechanism
3531: p_bs := p_bs||'EXCEPTION'||g_eol;
3532: p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3533: p_bs := p_bs||' hr_utility.set_location('''||
3534: l_dyt_name||''',ABS(SQLCODE));'||g_eol;
3535: p_bs := p_bs||' RAISE;'||g_eol;
3536: p_bs := p_bs||' --'||g_eol;
3537: p_bs := p_bs||'END '||l_dyt_name||';'||g_eos;
3621: p_bs := p_hs ||' IS '||g_eol;
3622: p_bs := p_bs ||' l_mode varchar2(80);'||g_eos;
3623: p_bs := p_bs ||' BEGIN'||g_eos;
3624:
3625: p_bs := p_bs||' hr_utility.trace('' >DYT: Main entry point from row handler, AFTER_'||p_dyt_info||''');'||g_eol;
3626:
3627: p_hs := p_hs||'; -- End of procedure definition for AFTER_'||upper(p_dyt_info)||g_eos;
3628:
3629: --Create main body code, i.e. call to newly created dyn-trigger procedure(s).
3691: -- Add a default exception block to catch all errors and write the
3692: -- trigger name and error text to the standard Oracle Apps error log
3693: p_bs := p_bs||'EXCEPTION'||g_eol;
3694: p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3695: p_bs := p_bs||' hr_utility.set_location('''||
3696: 'AFTER_'||upper(p_dyt_info)||''',ABS(SQLCODE));'||g_eol;
3697: p_bs := p_bs||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3698: p_bs := p_bs||' RAISE;'||g_eol;
3699: p_bs := p_bs||' --'||g_eol;
3797: -- Add a default exception block to catch all errors and write the
3798: -- trigger name and error text to the standard Oracle Apps error log
3799: l_sql := l_sql||'EXCEPTION'||g_eol;
3800: l_sql := l_sql||' WHEN OTHERS THEN'||g_eol;
3801: l_sql := l_sql||' hr_utility.set_location('''||
3802: l_dbt_name||''',ABS(SQLCODE));'||g_eol;
3803: l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3804: l_sql := l_sql||' RAISE;'||g_eol;
3805: l_sql := l_sql||' --'||g_eol;
3865: from hr_api_modules
3866: where module_name = p_table_name;
3867:
3868: begin
3869: hr_utility.trace('>>> set table '||p_table_name||' to be style '||p_dyt_type);
3870: --
3871: update pay_dated_tables
3872: set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
3873: where table_name = p_table_name;
3871: update pay_dated_tables
3872: set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
3873: where table_name = p_table_name;
3874:
3875: hr_utility.trace(' creating dyt triggers...');
3876: --
3877: for dyt_record in csr_dyt_ids(p_table_name) loop
3878: paywsdyg_pkg.generate_trigger(
3879: dyt_record.event_id,
3878: paywsdyg_pkg.generate_trigger(
3879: dyt_record.event_id,
3880: l_name,
3881: l_ok);
3882: hr_utility.trace(' just created a dynamic trigger for id: '||dyt_record.event_id||', dyt_name: '||dyt_record.short_name||', into: '||l_name);
3883: if (p_dyt_type = 'P') then exit; end if;
3884: end loop;
3885: hr_utility.trace('>>> completed trigger building for table '||p_table_name);
3886:
3881: l_ok);
3882: hr_utility.trace(' just created a dynamic trigger for id: '||dyt_record.event_id||', dyt_name: '||dyt_record.short_name||', into: '||l_name);
3883: if (p_dyt_type = 'P') then exit; end if;
3884: end loop;
3885: hr_utility.trace('>>> completed trigger building for table '||p_table_name);
3886:
3887:
3888: if (p_dyt_type = 'P') then
3889: l_hooks := 'Y';
3966: l_industry varchar2(1);
3967:
3968: l_proc varchar2(240) := g_package||'.confirm_dyt_data';
3969: BEGIN
3970: hr_utility.set_location(l_proc,10);
3971: --
3972: -- >>> PHASE 1: Decide what is the reqd format for this table for dynamic trigger
3973: --
3974: open csr_dyt_pkg_hook;
3974: open csr_dyt_pkg_hook;
3975: fetch csr_dyt_pkg_hook into l_hook_count;
3976: close csr_dyt_pkg_hook;
3977:
3978: hr_utility.trace( '- Decision on what is the required behaviour based on enabled hook count.');
3979: if (l_hook_count > 0) then
3980: l_reqd_format := 'P';
3981: else
3982: l_reqd_format := 'T';
3980: l_reqd_format := 'P';
3981: else
3982: l_reqd_format := 'T';
3983: end if;
3984: hr_utility.trace( '- Hook count is '||l_hook_count||' so REQD behaviour is '||l_reqd_format);
3985:
3986:
3987: -- >>> PHASE 2: Get the exisitng information for this table
3988: --
3988: --
3989: open csr_tab_details;
3990: fetch csr_tab_details into l_dt_id, l_dt_dyt_type, l_dt_pkg_name, l_dt_pkg_gen;
3991: close csr_tab_details;
3992: hr_utility.trace( '- Dated table id '||l_dt_id||' has SEEDED behaviour '||l_dt_dyt_type);
3993:
3994: open csr_pkg_exist(l_dt_pkg_name);
3995: fetch csr_pkg_exist into l_pkg_status;
3996: close csr_pkg_exist;
3996: close csr_pkg_exist;
3997:
3998: if (l_pkg_status is null) then l_pkg_status := 'NONE';
3999: end if;
4000: --hr_utility.trace( '- DYT_PKG '||l_dt_pkg_name||' has status '||l_pkg_status);
4001:
4002: --Now do phase 3 if l_reqd_format = T, phase 4 if its = P
4003: if (l_reqd_format = 'T') then
4004: -- >>> PHASE 3: Deal with situation where we wish for NO dyt_pkg
4012: -- Table is NOT || [C] Bad || [D] Very Bad ||
4013: -- set to T || => change table to || => change table to ||
4014: -- eg dyt_pkg || be dbms style || be dbms style ||
4015: --++===============++===============================================++
4016: hr_utility.trace(' NO HOOKS so desired DBMS style dynamic triggers.');
4017: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4018: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4019: l_need_rebuild_flag := 'N'; --odd but not terminal
4020:
4013: -- set to T || => change table to || => change table to ||
4014: -- eg dyt_pkg || be dbms style || be dbms style ||
4015: --++===============++===============================================++
4016: hr_utility.trace(' NO HOOKS so desired DBMS style dynamic triggers.');
4017: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4018: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4019: l_need_rebuild_flag := 'N'; --odd but not terminal
4020:
4021: elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4018: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4019: l_need_rebuild_flag := 'N'; --odd but not terminal
4020:
4021: elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4022: hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
4023: l_need_rebuild_flag := 'N'; --odd but not terminal
4024: --
4025: elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
4026: l_need_rebuild_flag := 'Y'; -- BAD so change to dbms triggers
4042: -- set to T || || hooks will fail ||
4043: -- eg dyt_pkg || || => change to dyt_pkg ||
4044: --++===============++===============================================++
4045:
4046: hr_utility.trace(' HOOKS EXIST so desired DYT_PKG style dynamic triggers.');
4047: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4048: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4049: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4050: --
4043: -- eg dyt_pkg || || => change to dyt_pkg ||
4044: --++===============++===============================================++
4045:
4046: hr_utility.trace(' HOOKS EXIST so desired DYT_PKG style dynamic triggers.');
4047: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4048: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4049: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4050: --
4051: elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4052: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4053: --
4054: elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
4055: l_need_rebuild_flag := 'N';
4056: hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
4057: --
4058: elsif (l_dt_dyt_type = 'P' and l_pkg_status <> 'VALID') then
4059: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4060: --
4076: p_table_name => 'PAY_ELEMENT_ENTRY_VALUES_F',
4077: p_dyt_type => l_reqd_format);
4078: end if;
4079:
4080: hr_utility.set_location(l_proc,900);
4081: END confirm_dyt_data;
4082:
4083: --
4084: --