[Home] [Help]
695:
696:
697: SELECT count(*)
698: into insert_row
699: FROM pay_process_events ppe,
700: pay_event_updates peu
701: WHERE ppe.assignment_id=p_assignment_id
702: AND ppe.surrogate_key=p_surrogate_key
703: AND ppe.business_group_id=p_business_group_id
707: AND peu.dated_table_id=p_dated_table_id;
708:
709: SELECT count(*)
710: INTo upd_end_date
711: FROM pay_process_events ppe,
712: pay_event_updates peu
713: WHERE ppe.assignment_id=p_assignment_id
714: AND ppe.surrogate_key=p_surrogate_key
715: AND ppe.business_group_id=p_business_group_id
720: AND peu.dated_table_id=p_dated_table_id;
721:
722: SELECT count(*)
723: INTo upd_start_date
724: FROM pay_process_events ppe,
725: pay_event_updates peu
726: WHERE ppe.assignment_id=p_assignment_id
727: AND ppe.surrogate_key=p_surrogate_key
728: AND ppe.business_group_id=p_business_group_id
2293: Notes : PUBLIC
2294: ****************************************************************************/
2295: PROCEDURE asg_action_affected(p_assignment_action_id IN NUMBER) AS
2296:
2297: -- The following cursor selects the rows from pay_process_events where
2298: -- Change_type in DATE_PROCESSED, DATE_EARNED, GRE, PAYMENT, COST_CENTRE
2299: -- and status = 'U'
2300: CURSOR c_pay_process_events(p_assignment_id IN NUMBER) IS
2301: SELECT process_event_id ,
2296:
2297: -- The following cursor selects the rows from pay_process_events where
2298: -- Change_type in DATE_PROCESSED, DATE_EARNED, GRE, PAYMENT, COST_CENTRE
2299: -- and status = 'U'
2300: CURSOR c_pay_process_events(p_assignment_id IN NUMBER) IS
2301: SELECT process_event_id ,
2302: event_update_id ,
2303: change_type ,
2304: assignment_id ,
2303: change_type ,
2304: assignment_id ,
2305: surrogate_key ,
2306: effective_date
2307: FROM pay_process_events
2308: WHERE assignment_id = p_assignment_id
2309: AND change_type IN ('DATE_PROCESSED',
2310: 'DATE_EARNED' ,
2311: 'PAYMENT' ,
2331: FROM pay_event_updates a ,
2332: pay_dated_tables b
2333: WHERE a.dated_table_id = b.dated_table_id;
2334:
2335: l_process_event_id pay_process_events.process_event_id%TYPE ;
2336: l_change_type1 pay_process_events.change_type%TYPE ;
2337: l_assignment_id pay_process_events.assignment_id%TYPE ;
2338: l_surrogate_key pay_process_events.surrogate_key%TYPE ;
2339: l_effective_date pay_process_events.effective_date%TYPE ;
2332: pay_dated_tables b
2333: WHERE a.dated_table_id = b.dated_table_id;
2334:
2335: l_process_event_id pay_process_events.process_event_id%TYPE ;
2336: l_change_type1 pay_process_events.change_type%TYPE ;
2337: l_assignment_id pay_process_events.assignment_id%TYPE ;
2338: l_surrogate_key pay_process_events.surrogate_key%TYPE ;
2339: l_effective_date pay_process_events.effective_date%TYPE ;
2340:
2333: WHERE a.dated_table_id = b.dated_table_id;
2334:
2335: l_process_event_id pay_process_events.process_event_id%TYPE ;
2336: l_change_type1 pay_process_events.change_type%TYPE ;
2337: l_assignment_id pay_process_events.assignment_id%TYPE ;
2338: l_surrogate_key pay_process_events.surrogate_key%TYPE ;
2339: l_effective_date pay_process_events.effective_date%TYPE ;
2340:
2341: l_event_update_id pay_event_updates.event_update_id%TYPE ;
2334:
2335: l_process_event_id pay_process_events.process_event_id%TYPE ;
2336: l_change_type1 pay_process_events.change_type%TYPE ;
2337: l_assignment_id pay_process_events.assignment_id%TYPE ;
2338: l_surrogate_key pay_process_events.surrogate_key%TYPE ;
2339: l_effective_date pay_process_events.effective_date%TYPE ;
2340:
2341: l_event_update_id pay_event_updates.event_update_id%TYPE ;
2342: l_table_id pay_event_updates.dated_table_id%TYPE ;
2335: l_process_event_id pay_process_events.process_event_id%TYPE ;
2336: l_change_type1 pay_process_events.change_type%TYPE ;
2337: l_assignment_id pay_process_events.assignment_id%TYPE ;
2338: l_surrogate_key pay_process_events.surrogate_key%TYPE ;
2339: l_effective_date pay_process_events.effective_date%TYPE ;
2340:
2341: l_event_update_id pay_event_updates.event_update_id%TYPE ;
2342: l_table_id pay_event_updates.dated_table_id%TYPE ;
2343: l_column_name pay_event_updates.column_name%TYPE ;
2352: LOOP
2353: l_assignment_id := caa.assignment_id;
2354: END LOOP;
2355:
2356: FOR cppe IN c_pay_process_events(l_assignment_id)
2357: LOOP
2358: l_process_event_id := cppe.process_event_id ;
2359: l_event_update_id := cppe.event_update_id ;
2360: l_change_type1 := cppe.change_type ;
2415: CURSOR c_process_events(p_process_event_id IN NUMBER) IS
2416: SELECT change_type ,
2417: event_update_id ,
2418: effective_date
2419: FROM pay_process_events
2420: WHERE process_event_id = p_process_event_id ;
2421:
2422: CURSOR c_pay_tables(p_table_id IN NUMBER) IS
2423: SELECT table_name ,
3519: -- Get the min date of the Delete rows for this
3520: -- Date Track transaction
3521: select min(effective_date)
3522: into l_effective_date
3523: from pay_process_events
3524: where surrogate_key = p_pro_evt_rec.surrogate_key
3525: and event_update_id = p_pro_evt_rec.event_update_id
3526: and creation_date = p_pro_evt_rec.creation_date;
3527: --
3532: select count(*)
3533: into l_count
3534: from pay_dated_tables pdt,
3535: pay_event_updates peu,
3536: pay_process_events ppe
3537: where pdt.table_name = p_pro_evt_rec.table_name
3538: and pdt.dated_table_id = peu.dated_table_id
3539: and peu.event_type = 'I'
3540: and peu.change_type = p_pro_evt_rec.change_mode
3988:
3989: ---------------------------------------------------------- */
3990: PROCEDURE get_dbt_i_p_cache
3991: (
3992: p_surrogate_key IN pay_process_events.surrogate_key%type ,
3993: p_event_update_id IN pay_process_events.event_update_id%type ,
3994: p_assignment_id IN per_all_assignments.assignment_id%type,
3995: p_change_mode IN pay_event_updates.change_type%type,
3996: p_cache_number IN OUT NOCOPY NUMBER
3989: ---------------------------------------------------------- */
3990: PROCEDURE get_dbt_i_p_cache
3991: (
3992: p_surrogate_key IN pay_process_events.surrogate_key%type ,
3993: p_event_update_id IN pay_process_events.event_update_id%type ,
3994: p_assignment_id IN per_all_assignments.assignment_id%type,
3995: p_change_mode IN pay_event_updates.change_type%type,
3996: p_cache_number IN OUT NOCOPY NUMBER
3997: ) IS
4002: cp_mode in varchar2,
4003: cp_assignment_id in number) is
4004: SELECT min(creation_date),
4005: max(creation_date)
4006: FROM PAY_PROCESS_EVENTS
4007: WHERE event_update_id = cp_event_update_id
4008: AND surrogate_key = cp_base_record_id
4009: AND assignment_id is not null
4010: AND assignment_id = cp_assignment_id
4015: cp_event_update_id in number,
4016: cp_mode in varchar2) is
4017: SELECT min(creation_date),
4018: max(creation_date)
4019: FROM PAY_PROCESS_EVENTS
4020: WHERE event_update_id = cp_event_update_id
4021: AND surrogate_key = cp_base_record_id
4022: AND assignment_id is null
4023: AND change_type = nvl(cp_mode,change_type);
4978: -- This cursor will fetch the minimum efective on which a penserver event has
4979: -- occured for the employee having the creation_date in the current period.
4980: cursor csr_pen_eff_date
4981: is select min(ppe.effective_date)
4982: from pay_process_events ppe
4983: where trunc(ppe.creation_date) between p_lapp_date and p_end_date
4984: and ppe.assignment_id = p_assignment_id
4985: and ppe.business_group_id = p_business_group_id
4986: and ppe.effective_date >= ben_ext_thread.g_effective_start_date
5057: type t_event_type is table of pay_event_updates.event_type%type
5058: index by binary_integer;
5059: type t_event_update_id is table of pay_event_updates.event_update_id%type
5060: index by binary_integer;
5061: type t_effective_date is table of pay_process_events.effective_date%type
5062: index by binary_integer;
5063: type t_assignment_id is table of pay_process_events.assignment_id%type
5064: index by binary_integer;
5065: type t_surrogate_key is table of pay_process_events.surrogate_key%type
5059: type t_event_update_id is table of pay_event_updates.event_update_id%type
5060: index by binary_integer;
5061: type t_effective_date is table of pay_process_events.effective_date%type
5062: index by binary_integer;
5063: type t_assignment_id is table of pay_process_events.assignment_id%type
5064: index by binary_integer;
5065: type t_surrogate_key is table of pay_process_events.surrogate_key%type
5066: index by binary_integer;
5067: type t_process_event_id is table of pay_process_events.process_event_id%type
5061: type t_effective_date is table of pay_process_events.effective_date%type
5062: index by binary_integer;
5063: type t_assignment_id is table of pay_process_events.assignment_id%type
5064: index by binary_integer;
5065: type t_surrogate_key is table of pay_process_events.surrogate_key%type
5066: index by binary_integer;
5067: type t_process_event_id is table of pay_process_events.process_event_id%type
5068: index by binary_integer;
5069: type t_description is table of pay_process_events.description%type
5063: type t_assignment_id is table of pay_process_events.assignment_id%type
5064: index by binary_integer;
5065: type t_surrogate_key is table of pay_process_events.surrogate_key%type
5066: index by binary_integer;
5067: type t_process_event_id is table of pay_process_events.process_event_id%type
5068: index by binary_integer;
5069: type t_description is table of pay_process_events.description%type
5070: index by binary_integer;
5071: type t_calculation_date is table of pay_process_events.calculation_date%type
5065: type t_surrogate_key is table of pay_process_events.surrogate_key%type
5066: index by binary_integer;
5067: type t_process_event_id is table of pay_process_events.process_event_id%type
5068: index by binary_integer;
5069: type t_description is table of pay_process_events.description%type
5070: index by binary_integer;
5071: type t_calculation_date is table of pay_process_events.calculation_date%type
5072: index by binary_integer;
5073: type t_creation_date is table of pay_process_events.creation_date%type
5067: type t_process_event_id is table of pay_process_events.process_event_id%type
5068: index by binary_integer;
5069: type t_description is table of pay_process_events.description%type
5070: index by binary_integer;
5071: type t_calculation_date is table of pay_process_events.calculation_date%type
5072: index by binary_integer;
5073: type t_creation_date is table of pay_process_events.creation_date%type
5074: index by binary_integer;
5075: type t_change_type is table of pay_process_events.change_type%type
5069: type t_description is table of pay_process_events.description%type
5070: index by binary_integer;
5071: type t_calculation_date is table of pay_process_events.calculation_date%type
5072: index by binary_integer;
5073: type t_creation_date is table of pay_process_events.creation_date%type
5074: index by binary_integer;
5075: type t_change_type is table of pay_process_events.change_type%type
5076: index by binary_integer;
5077: type t_table_name is table of pay_dated_tables.table_name%type
5071: type t_calculation_date is table of pay_process_events.calculation_date%type
5072: index by binary_integer;
5073: type t_creation_date is table of pay_process_events.creation_date%type
5074: index by binary_integer;
5075: type t_change_type is table of pay_process_events.change_type%type
5076: index by binary_integer;
5077: type t_table_name is table of pay_dated_tables.table_name%type
5078: index by binary_integer;
5079: --
5093: -- NB Following statement has been tuned for performance purposes
5094: /*Modified the following cursor for bug 13735584
5095: The Second part of the query where group level events are picked
5096: added an extra clause to pick up the events which has the value
5097: of business group id populated as null in pay process events table */
5098: CURSOR csr_all_process_events_cre (
5099: cp_bulk_processing IN VARCHAR,
5100: cp_cstart_date IN DATE ,
5101: cp_cend_date IN DATE ) IS
5099: cp_bulk_processing IN VARCHAR,
5100: cp_cstart_date IN DATE ,
5101: cp_cend_date IN DATE ) IS
5102:
5103: SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PPE) */
5104: peu.column_name updated_column_name ,
5105: peu.event_type event_type ,
5106: peu.event_update_id event_update_id ,
5107: ppe.effective_date effective_date ,
5114: ppe.change_type change_mode,
5115: pdt.table_name table_name
5116: FROM
5117: pay_dated_tables pdt ,
5118: pay_process_events ppe ,
5119: pay_event_updates peu
5120: WHERE
5121: peu.event_update_id = ppe.event_update_id + 0
5122: AND peu.dated_table_id = pdt.dated_table_id
5158: )
5159: )
5160: )
5161: UNION ALL
5162: SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PDT) */
5163: peu.column_name updated_column_name ,
5164: peu.event_type event_type ,
5165: peu.event_update_id event_update_id ,
5166: ppe.effective_date effective_date ,
5174: pdt.table_name table_name
5175: FROM
5176: pay_dated_tables pdt ,
5177: pay_event_updates peu ,
5178: pay_process_events ppe
5179: WHERE
5180: peu.event_update_id = ppe.event_update_id + 0
5181: AND peu.dated_table_id = pdt.dated_table_id
5182: AND pdt.dated_table_id IN
5229: cp_bulk_processing IN VARCHAR2,
5230: cp_estart_date IN DATE ,
5231: cp_eend_date IN DATE ) IS
5232:
5233: SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PPE) */
5234: peu.column_name updated_column_name ,
5235: peu.event_type event_type ,
5236: peu.event_update_id event_update_id ,
5237: ppe.effective_date effective_date ,
5244: ppe.change_type change_mode,
5245: pdt.table_name table_name
5246: FROM
5247: pay_dated_tables pdt ,
5248: pay_process_events ppe ,
5249: pay_event_updates peu
5250: WHERE
5251: peu.event_update_id = ppe.event_update_id + 0
5252: AND peu.dated_table_id = pdt.dated_table_id
5290: )
5291: )
5292: )
5293: UNION ALL
5294: SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PDT) */
5295: peu.column_name updated_column_name ,
5296: peu.event_type event_type ,
5297: peu.event_update_id event_update_id ,
5298: ppe.effective_date effective_date ,
5306: pdt.table_name table_name
5307: FROM
5308: pay_dated_tables pdt ,
5309: pay_event_updates peu ,
5310: pay_process_events ppe
5311: WHERE
5312: peu.event_update_id = ppe.event_update_id + 0
5313: AND peu.dated_table_id = pdt.dated_table_id
5314: AND pdt.dated_table_id IN
5377: ppe.change_type change_mode,
5378: pdt.table_name table_name
5379: FROM
5380: pay_dated_tables pdt ,
5381: pay_process_events ppe ,
5382: pay_event_updates peu
5383: WHERE
5384: peu.event_update_id = ppe.event_update_id + 0
5385: AND peu.dated_table_id = pdt.dated_table_id
5464: ppe.change_type change_mode,
5465: pdt.table_name table_name
5466: FROM
5467: pay_dated_tables pdt ,
5468: pay_process_events ppe ,
5469: pay_event_updates peu
5470: WHERE
5471: peu.event_update_id = ppe.event_update_id + 0
5472: AND peu.dated_table_id = pdt.dated_table_id
5541: ppe.change_type change_mode,
5542: pdt.table_name table_name
5543: FROM
5544: pay_dated_tables pdt ,
5545: pay_process_events ppe ,
5546: pay_event_updates peu
5547: WHERE
5548: peu.event_update_id = ppe.event_update_id + 0
5549: AND peu.dated_table_id = pdt.dated_table_id
6084: --all this code is within record_disco_events
6085:
6086: -- EG
6087: -- PROCESS EVENTS
6088: -- pay_process_events are all events that have occurred
6089: -- DATETRACKED EVENTS
6090: -- All events the user has declared they are interested in,
6091: -- ie child of event group
6092: -- DISCO(vered) EVENTS
6362: --
6363: if ( l_business_group_id is null) then
6364: select max(business_group_id)
6365: into l_business_group_id
6366: from pay_process_events
6367: where assignment_id = p_assignment_id;
6368: end if;
6369:
6370: