[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: --
1718: l_rc NUMBER;
1719: --
1720: l_proc VARCHAR2(30) := 'generate_trigger';
1721: BEGIN
1722: hr_utility.set_location(' Entering: '||l_proc,10);
1723: --Calling from SINGLE (Dynamic Triggers Form)
1724: --
1725: -- Fetch the trigger event information
1726: OPEN csr_name(p_id);
1732: -- Eg Set up to store as db triggers, as package code or both
1733: -- Added by jford 1-OCT-02 as part of cont calc
1734: --
1735: IF (l_dyn_type = 'P' or l_dyn_type = 'B') THEN
1736: hr_utility.trace(' Handle Dynamic Triggers as Package Style.');
1737: -- dyn trigger code should be handled as package
1738: -- >> GENERATE PACKAGE
1739: -- generate code FOR ALL DYT's ON TABLE eg many dyn_triggers
1740: gen_dyt_pkg_full_code(l_tab_id,p_ok);
1741: p_name := l_dyt_pkg;
1742: ELSE
1743: -- type is just original 'T' Trigger mechanism so use existing code
1744: -- >> GENERATE DBMS TRIGGER
1745: hr_utility.trace(' Handle Dynamic Triggers as Individual Database Trigger Style.');
1746: -- Get the trigger name in the standard format
1747: --if weve got an old crap dyt_pkg, then dump it
1748: if (l_dyt_pkg is not null) then
1749: drop_package(l_table,l_dyt_pkg);
1762: -- Create the trigger using the generated PL/SQL and the AOL routines
1763: create_trigger(l_trigger,l_table,l_action,l_sql);
1764: p_ok := module_ok(l_trigger,'TRIGGER');
1765: if (p_ok) then
1766: hr_utility.trace(' Database Trigger '||p_name||' created with success');
1767: else
1768: hr_utility.trace(' Database Trigger '||p_name||' created with failure');
1769: update pay_trigger_events
1770: set generated_flag = 'N', enabled_flag = 'N'
1764: p_ok := module_ok(l_trigger,'TRIGGER');
1765: if (p_ok) then
1766: hr_utility.trace(' Database Trigger '||p_name||' created with success');
1767: else
1768: hr_utility.trace(' Database Trigger '||p_name||' created with failure');
1769: update pay_trigger_events
1770: set generated_flag = 'N', enabled_flag = 'N'
1771: where event_id = p_id;
1772: l_enb_flag := 'N';
1779: --
1780: -- Otherwise, drop it to make sure it definitely doesn't exist
1781: drop_trigger(l_trigger);
1782: p_ok := TRUE;
1783: hr_utility.trace(' Database Trigger '||p_name||' dropped with success.');
1784: --
1785: END IF;
1786: END IF;
1787:
1784: --
1785: END IF;
1786: END IF;
1787:
1788: hr_utility.set_location(' Leaving: '||l_proc,900);
1789: END generate_trigger;
1790: --
1791: -- +---------------------------------------------------------------------------+
1792: -- | NAME : compile_package_indirect |
1833: );
1834: EXCEPTION
1835: WHEN OTHERS THEN
1836: -- Output a trrace message if we fail
1837: hr_utility.set_location('COMPILE_PACKAGE_INDIRECT',ABS(SQLCODE));
1838: END compile_package_indirect;
1839: --
1840: -- +---------------------------------------------------------------------------+
1841: -- | NAME : create_defaults |
2472: -- trigger name and error text to the standard Oracle Apps error logging
2473: -- mechanism
2474: p_sql := p_sql||'EXCEPTION'||g_eol;
2475: p_sql := p_sql||' WHEN OTHERS THEN'||g_eol;
2476: p_sql := p_sql||' hr_utility.set_location('''||
2477: get_trigger_name(p_id,l_table,l_type)||''',ABS(SQLCODE));'||g_eol;
2478: p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
2479: p_sql := p_sql||' RAISE;'||g_eol;
2480: p_sql := p_sql||' --'||g_eol;
2822:
2823:
2824: << ONE_PROC_PARAM_LOOP >>
2825: FOR i in 1..l_position.COUNT loop
2826: --hr_utility.trace(' Found parameter '||l_argument_name(i)||' type '||l_ --
2827: if l_datatype(i) <> c_dtype_varchar2 and
2828: l_datatype(i) <> c_dtype_number and
2829: l_datatype(i) <> c_dtype_date and
2830: l_datatype(i) <> c_dtype_boolean and
2831: l_datatype(i) <> c_dtype_long then
2832: -- Error: In a hook package procedure all the parameter datatypes
2833: -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2834: -- module will not execute until this problem has been resolved.
2835: hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2836: hr_utility.set_location(l_proc, 80);
2837: else
2838: -- Set the datatype string with the corresponding word value
2839: if l_datatype(i) = c_dtype_varchar2 then
2832: -- Error: In a hook package procedure all the parameter datatypes
2833: -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2834: -- module will not execute until this problem has been resolved.
2835: hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2836: hr_utility.set_location(l_proc, 80);
2837: else
2838: -- Set the datatype string with the corresponding word value
2839: if l_datatype(i) = c_dtype_varchar2 then
2840: l_datatype_str := 'VARCHAR2';
2860:
2861: END LOOP HOOK_MODULES;
2862: --Debug Output all the paramaters in my table form
2863: --FOR j in 0..(p_params.COUNT - 1) loop
2864: --hr_utility.trace(j||' '||p_params(j).value_name||' '||p_params(j).usage_type||' '||p_params(j).data_type);
2865: --end loop;
2866:
2867: hr_utility.trace('Total RHI params '||p_params.count);
2868:
2863: --FOR j in 0..(p_params.COUNT - 1) loop
2864: --hr_utility.trace(j||' '||p_params(j).value_name||' '||p_params(j).usage_type||' '||p_params(j).data_type);
2865: --end loop;
2866:
2867: hr_utility.trace('Total RHI params '||p_params.count);
2868:
2869: -- If we didnt get any hook params then there might not be a hook pkg!
2870: -- So just base the params on what we actually need, i.e. the param mappings
2871: if ( p_params.count = 0 ) then
2868:
2869: -- If we didnt get any hook params then there might not be a hook pkg!
2870: -- So just base the params on what we actually need, i.e. the param mappings
2871: if ( p_params.count = 0 ) then
2872: hr_utility.trace('No hook params => build params from what is required from components');
2873:
2874: get_dyt_pkg_params_tbl(p_tab_id,p_tab_name,p_params);
2875: --On top of the ones we need in dyt call, we also know we need p_datetrack
2876: --in both after_delete and after_update.
2885: p_params(i+1).value_name := 'P_DATETRACK_MODE';
2886: p_params(i+1).usage_type := 'PD';
2887: p_params(i+1).data_type := 'VARCHAR2';
2888:
2889: hr_utility.trace('Total RHI params '||p_params.count);
2890: -- Example of structure of p_params from get_dyt_pkg_params_tbl
2891: --local_form - :new.EFFECTIVE_END_DATE
2892: --param_form - p_new_EFFECTIVE_END_DATE
2893: --value_name - P_EFFECTIVE_END_DATE
2992: l_missing_rep := replace(missing_param.name,'OLD','NEW');
2993: else
2994: l_missing_rep := l_missing||'xxx'; --no change so will fall over
2995: end if;
2996: --hr_utility.trace('Missing param: '||l_missing||' replace with: '||l_missing_rep);
2997: p_sql := replace(p_sql,l_missing,l_missing_rep);
2998:
2999: end loop;
3000:
3008:
3009: --Get extract from this instance of :new to next :new instance
3010: --
3011: l_pos_next := instr(p_sql,':OLD.',l_pos+1);
3012: --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3013: if (l_pos_next <> 0 ) then
3014: l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3015: else
3016: l_extract := substr(p_sql,l_pos);
3022:
3023: -- Amend the prefix and make sure we're not over the 30 char limit
3024: l_new_col_name := substr(replace(l_col_name,':OLD.','P_OLD_'),1,30);
3025:
3026: --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3027:
3028: l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3029: p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3030:
3044:
3045: --Get extract from this instance of :new to next
3046: --
3047: l_pos_next := instr(p_sql,':NEW.',l_pos+1);
3048: --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3049: if (l_pos_next <> 0 ) then
3050: l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3051: else
3052: l_extract := substr(p_sql,l_pos);
3058:
3059: -- Amend the prefix and make sure we're not over the 30 char limit
3060: l_new_col_name := substr(replace(l_col_name,':NEW.','P_NEW_'),1,30);
3061:
3062: --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3063:
3064: l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3065: p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3066:
3097: l_proc_name := substr(p_dyt_name,1,30 - length(l_suffix));
3098: --Full version is first||suffix
3099: l_proc_name := l_proc_name||l_suffix;
3100:
3101: --hr_utility.trace(' Got DYT pkg procedure name: '||l_proc_name);
3102: return l_proc_name;
3103: END;
3104:
3105: --
3160:
3161: --
3162: l_proc varchar2(35) := 'gen_dyt_pkg_full_code';
3163: BEGIN
3164: hr_utility.set_location(' Entering: '||l_proc,10);
3165: -- The package to hold the code is created with two sections.
3166: -- a. The main trigger code, built using info from the dynamic triggers screen
3167: -- b. The after_update, after_insert, after_delete standard called by rhi,
3168: -- these act as wrappers calling (a)'s.
3233: l_dyt_pkg_body_tbl(0) := l_bsql;
3234:
3235: -- >>> 2. Get all parameters defined for this table in to table of records for future
3236: -- manipulation. This table holds the following info:
3237: hr_utility.set_location(' -Get parameters DYT : '||l_proc,20);
3238:
3239: -- i. local name --entered in form
3240: -- ii. generated pkg param version of i-used for internal pkg proc
3241: -- iii. usage type -- eg PI Insert, PU Update, PD Delete
3241: -- iii. usage type -- eg PI Insert, PU Update, PD Delete
3242: -- iv. type of col
3243: --
3244: get_dyt_pkg_params_tbl(l_tab_id, l_tab_name, l_dyt_params);
3245: hr_utility.set_location(' -Get parameters after_xxx : '||l_proc,25);
3246: get_dyt_rhi_params(l_tab_id, l_tab_name, l_hok_params);
3247:
3248: -- >>> 3. Add the dynamic trigger code as separate public procedures
3249: -- loop for each dyn trig, calling a procedure to create appropriate code
3247:
3248: -- >>> 3. Add the dynamic trigger code as separate public procedures
3249: -- loop for each dyn trig, calling a procedure to create appropriate code
3250: --
3251: hr_utility.set_location(' -Create dyt procedure code: '||l_proc,30);
3252: FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3253: gen_dyt_pkg_proc(dyt_rec.event_id,dyt_rec.short_name,
3254: l_tab_name,dyt_rec.triggering_action,
3255: dyt_rec.description,dyt_rec.info,
3265: -- Create the wrapper procedures that the rhi will call, these simply call
3266: -- the newly created, dynamic trigger code procedures
3267: -- Three times, one for each trigger type
3268: --
3269: hr_utility.set_location(' -Create hook entry point procedure code: '||l_proc,40);
3270: 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);
3271: l_hs := ''; l_bs := '';
3272: --
3273: 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);
3295:
3296:
3297: -- >>> 6. Generate and compile this new dynamic package
3298: --
3299: hr_utility.set_location(' -Generate database package: '||l_proc,60);
3300: build_dyt_pkg_from_tbl( g_dyt_pkg_head,g_dyt_pkg_hindex,FALSE);
3301: build_dyt_pkg_from_tbl( g_dyt_pkg_body,g_dyt_pkg_bindex,TRUE);
3302:
3303: -- >>> 7. Create database trigger equivalent of dyn-trigger if required
3343: p_ok :=FALSE;
3344: l_flag := 'N';
3345: end if;
3346:
3347: --hr_utility.set_location(' -Status of database package: '||l_flag||l_proc,80);
3348: --Complete FOR WHOLE TABLE, so mark pkg gen, and all dyt as generated + enabled or vice versa
3349: -- All or nothing, either all dyt's are success, or mark all as failure
3350: update pay_dated_tables
3351: set dyn_trig_pkg_generated = l_flag
3366: end if;
3367:
3368: END LOOP;
3369: commit; --make sure updates are saved
3370: hr_utility.set_location(' Leaving: '||l_proc,900);
3371: END gen_dyt_pkg_full_code;
3372:
3373: -- +-----------------------------------------------------------------------------+
3374: -- | NAME : gen_dyt_pkg_proc |
3466: -- n/a
3467:
3468: p_bs := p_bs||'BEGIN'||g_eol;
3469: --
3470: p_bs := p_bs||' hr_utility.trace('' >DYT: Execute procedure version of Dynamic Trigger: '||p_dyt_name||''');'||g_eol;
3471:
3472:
3473: --
3474: -- Add the data migrator check - Bug 1885557
3491: -- trigger name and error text to the standard Oracle Apps error logging
3492: -- mechanism
3493: p_bs := p_bs||'EXCEPTION'||g_eol;
3494: p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3495: p_bs := p_bs||' hr_utility.set_location('''||
3496: l_dyt_name||''',ABS(SQLCODE));'||g_eol;
3497: p_bs := p_bs||' RAISE;'||g_eol;
3498: p_bs := p_bs||' --'||g_eol;
3499: p_bs := p_bs||'END '||l_dyt_name||';'||g_eos;
3583: p_bs := p_hs ||' IS '||g_eol;
3584: p_bs := p_bs ||' l_mode varchar2(80);'||g_eos;
3585: p_bs := p_bs ||' BEGIN'||g_eos;
3586:
3587: p_bs := p_bs||' hr_utility.trace('' >DYT: Main entry point from row handler, AFTER_'||p_dyt_info||''');'||g_eol;
3588:
3589: p_hs := p_hs||'; -- End of procedure definition for AFTER_'||upper(p_dyt_info)||g_eos;
3590:
3591: --Create main body code, i.e. call to newly created dyn-trigger procedure(s).
3653: -- Add a default exception block to catch all errors and write the
3654: -- trigger name and error text to the standard Oracle Apps error log
3655: p_bs := p_bs||'EXCEPTION'||g_eol;
3656: p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3657: p_bs := p_bs||' hr_utility.set_location('''||
3658: 'AFTER_'||upper(p_dyt_info)||''',ABS(SQLCODE));'||g_eol;
3659: p_bs := p_bs||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3660: p_bs := p_bs||' RAISE;'||g_eol;
3661: p_bs := p_bs||' --'||g_eol;
3759: -- Add a default exception block to catch all errors and write the
3760: -- trigger name and error text to the standard Oracle Apps error log
3761: l_sql := l_sql||'EXCEPTION'||g_eol;
3762: l_sql := l_sql||' WHEN OTHERS THEN'||g_eol;
3763: l_sql := l_sql||' hr_utility.set_location('''||
3764: l_dbt_name||''',ABS(SQLCODE));'||g_eol;
3765: l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3766: l_sql := l_sql||' RAISE;'||g_eol;
3767: l_sql := l_sql||' --'||g_eol;
3827: from hr_api_modules
3828: where module_name = p_table_name;
3829:
3830: begin
3831: hr_utility.trace('>>> set table '||p_table_name||' to be style '||p_dyt_type);
3832: --
3833: update pay_dated_tables
3834: set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
3835: where table_name = p_table_name;
3833: update pay_dated_tables
3834: set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
3835: where table_name = p_table_name;
3836:
3837: hr_utility.trace(' creating dyt triggers...');
3838: --
3839: for dyt_record in csr_dyt_ids(p_table_name) loop
3840: paywsdyg_pkg.generate_trigger(
3841: dyt_record.event_id,
3840: paywsdyg_pkg.generate_trigger(
3841: dyt_record.event_id,
3842: l_name,
3843: l_ok);
3844: hr_utility.trace(' just created a dynamic trigger for id: '||dyt_record.event_id||', dyt_name: '||dyt_record.short_name||', into: '||l_name);
3845: if (p_dyt_type = 'P') then exit; end if;
3846: end loop;
3847: hr_utility.trace('>>> completed trigger building for table '||p_table_name);
3848:
3843: l_ok);
3844: hr_utility.trace(' just created a dynamic trigger for id: '||dyt_record.event_id||', dyt_name: '||dyt_record.short_name||', into: '||l_name);
3845: if (p_dyt_type = 'P') then exit; end if;
3846: end loop;
3847: hr_utility.trace('>>> completed trigger building for table '||p_table_name);
3848:
3849:
3850: if (p_dyt_type = 'P') then
3851: l_hooks := 'Y';
3928: l_industry varchar2(1);
3929:
3930: l_proc varchar2(240) := g_package||'.confirm_dyt_data';
3931: BEGIN
3932: hr_utility.set_location(l_proc,10);
3933: --
3934: -- >>> PHASE 1: Decide what is the reqd format for this table for dynamic trigger
3935: --
3936: open csr_dyt_pkg_hook;
3936: open csr_dyt_pkg_hook;
3937: fetch csr_dyt_pkg_hook into l_hook_count;
3938: close csr_dyt_pkg_hook;
3939:
3940: hr_utility.trace( '- Decision on what is the required behaviour based on enabled hook count.');
3941: if (l_hook_count > 0) then
3942: l_reqd_format := 'P';
3943: else
3944: l_reqd_format := 'T';
3942: l_reqd_format := 'P';
3943: else
3944: l_reqd_format := 'T';
3945: end if;
3946: hr_utility.trace( '- Hook count is '||l_hook_count||' so REQD behaviour is '||l_reqd_format);
3947:
3948:
3949: -- >>> PHASE 2: Get the exisitng information for this table
3950: --
3950: --
3951: open csr_tab_details;
3952: fetch csr_tab_details into l_dt_id, l_dt_dyt_type, l_dt_pkg_name, l_dt_pkg_gen;
3953: close csr_tab_details;
3954: hr_utility.trace( '- Dated table id '||l_dt_id||' has SEEDED behaviour '||l_dt_dyt_type);
3955:
3956: open csr_pkg_exist(l_dt_pkg_name);
3957: fetch csr_pkg_exist into l_pkg_status;
3958: close csr_pkg_exist;
3958: close csr_pkg_exist;
3959:
3960: if (l_pkg_status is null) then l_pkg_status := 'NONE';
3961: end if;
3962: --hr_utility.trace( '- DYT_PKG '||l_dt_pkg_name||' has status '||l_pkg_status);
3963:
3964: --Now do phase 3 if l_reqd_format = T, phase 4 if its = P
3965: if (l_reqd_format = 'T') then
3966: -- >>> PHASE 3: Deal with situation where we wish for NO dyt_pkg
3974: -- Table is NOT || [C] Bad || [D] Very Bad ||
3975: -- set to T || => change table to || => change table to ||
3976: -- eg dyt_pkg || be dbms style || be dbms style ||
3977: --++===============++===============================================++
3978: hr_utility.trace(' NO HOOKS so desired DBMS style dynamic triggers.');
3979: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
3980: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
3981: l_need_rebuild_flag := 'N'; --odd but not terminal
3982:
3975: -- set to T || => change table to || => change table to ||
3976: -- eg dyt_pkg || be dbms style || be dbms style ||
3977: --++===============++===============================================++
3978: hr_utility.trace(' NO HOOKS so desired DBMS style dynamic triggers.');
3979: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
3980: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
3981: l_need_rebuild_flag := 'N'; --odd but not terminal
3982:
3983: elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
3980: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
3981: l_need_rebuild_flag := 'N'; --odd but not terminal
3982:
3983: elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
3984: hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
3985: l_need_rebuild_flag := 'N'; --odd but not terminal
3986: --
3987: elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
3988: l_need_rebuild_flag := 'Y'; -- BAD so change to dbms triggers
4004: -- set to T || || hooks will fail ||
4005: -- eg dyt_pkg || || => change to dyt_pkg ||
4006: --++===============++===============================================++
4007:
4008: hr_utility.trace(' HOOKS EXIST so desired DYT_PKG style dynamic triggers.');
4009: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4010: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4011: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4012: --
4005: -- eg dyt_pkg || || => change to dyt_pkg ||
4006: --++===============++===============================================++
4007:
4008: hr_utility.trace(' HOOKS EXIST so desired DYT_PKG style dynamic triggers.');
4009: hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4010: if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4011: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4012: --
4013: elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4014: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4015: --
4016: elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
4017: l_need_rebuild_flag := 'N';
4018: hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
4019: --
4020: elsif (l_dt_dyt_type = 'P' and l_pkg_status <> 'VALID') then
4021: l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4022: --
4038: p_table_name => 'PAY_ELEMENT_ENTRY_VALUES_F',
4039: p_dyt_type => l_reqd_format);
4040: end if;
4041:
4042: hr_utility.set_location(l_proc,900);
4043: END confirm_dyt_data;
4044:
4045: --
4046: --