[Home] [Help]
247: a batch.
248: 40.20 N.Bristow 08-MAY-1996 Bug 359005. Now caching Tax Unit Id
249: when validating. Reinstated the
250: tax unit id column on
251: pay_balance_batch_lines. Tuned several
252: statements.
253: 40.19 N.Bristow 18-MAR-1996 Now padding expired latest balance
254: columns with -9999.
255: 40.18 N.Bristow 18-MAR-1996 Bug 349583. Order by clause on
310: ,business_group_id pay_balance_batch_headers.business_group_id%type
311: ,legislation_code varchar2(30)
312: ,payroll_id pay_balance_batch_headers.payroll_id%type
313: ,consolidation_set_id pay_consolidation_sets.consolidation_set_id%type
314: ,assignment_id pay_balance_batch_lines.assignment_id%type
315: ,batch_header_status pay_balance_batch_headers.batch_status%type
316: ,batch_line_status pay_balance_batch_lines.batch_line_status%type
317: ,chunk_size number(9)
318: ,jurisdiction_iv pay_input_values_f.name%type
312: ,payroll_id pay_balance_batch_headers.payroll_id%type
313: ,consolidation_set_id pay_consolidation_sets.consolidation_set_id%type
314: ,assignment_id pay_balance_batch_lines.assignment_id%type
315: ,batch_header_status pay_balance_batch_headers.batch_status%type
316: ,batch_line_status pay_balance_batch_lines.batch_line_status%type
317: ,chunk_size number(9)
318: ,jurisdiction_iv pay_input_values_f.name%type
319: ,include_adj_rule pay_legislation_rules.rule_mode%type
320: );
412: (
413: p_batch_id number
414: ) is
415: select *
416: from pay_balance_batch_lines BL
417: where BL.batch_id = p_batch_id
418: and nvl(BL.batch_line_status, 'U') <> 'T'
419: order by BL.assignment_id,
420: BL.assignment_number
463: ,BL.jurisdiction_code
464: ,BL.original_entry_id
465: ,BL.batch_line_status) expiry_date
466: from pay_balance_batch_headers BH
467: ,pay_balance_batch_lines BL
468: where BH.batch_id = p_batch_id
469: and BL.batch_id = BH.batch_id
470: and BL.assignment_id = p_assignment_id
471: order by BL.assignment_id
494: (
495: p_batch_id number
496: ) is
497: select *
498: from pay_balance_batch_lines BL
499: where BL.batch_id = p_batch_id
500: and BL.batch_line_status = 'T'
501: order by BL.payroll_action_id;
502: --
553: procedure remove_messages(p_batch_id in number)
554: is
555: cursor pml (p_batch_id number) is
556: select batch_line_id
557: from pay_balance_batch_lines
558: where batch_id = p_batch_id;
559: --
560: begin
561: hr_utility.trace('Entering pay_balance_upload.remove_messages');
1279: -- p_batch_id - the batch header to be locked.
1280: -- USES
1281: -- NOTES
1282: -- This is used by the insert, update and delete triggers for the table
1283: -- PAY_BALANCE_BATCH_LINES. This can be used to ensure that the batch lines
1284: -- cannot be changed once another user has a row level lock on the batch
1285: -- header. This is used by the process to freeze the batch definition while
1286: -- it is being processed.
1287: -----------------------------------------------------------------------------
2120: l_tax_unit_id number;
2121: l_oee_id number;
2122: l_source_id number;
2123: l_run_type_id number;
2124: l_source_text pay_balance_batch_lines.source_text%type;
2125: l_source_number pay_balance_batch_lines.source_number%type;
2126: l_source_text2 pay_balance_batch_lines.source_text2%type;
2127: l_adj_date date;
2128: l_jurisdiction_code varchar2(30);
2121: l_oee_id number;
2122: l_source_id number;
2123: l_run_type_id number;
2124: l_source_text pay_balance_batch_lines.source_text%type;
2125: l_source_number pay_balance_batch_lines.source_number%type;
2126: l_source_text2 pay_balance_batch_lines.source_text2%type;
2127: l_adj_date date;
2128: l_jurisdiction_code varchar2(30);
2129: l_payroll_id number;
2122: l_source_id number;
2123: l_run_type_id number;
2124: l_source_text pay_balance_batch_lines.source_text%type;
2125: l_source_number pay_balance_batch_lines.source_number%type;
2126: l_source_text2 pay_balance_batch_lines.source_text2%type;
2127: l_adj_date date;
2128: l_jurisdiction_code varchar2(30);
2129: l_payroll_id number;
2130: --
2355: --
2356: for l_index in 1..p_num_lines loop
2357: hr_utility.trace('Updating Line '||p_batch_line_list(l_index)||
2358: ' With Payroll Action '||l_payroll_action_id);
2359: update pay_balance_batch_lines BL
2360: set BL.batch_line_status = 'T' -- Transferred
2361: ,BL.payroll_action_id = l_payroll_action_id
2362: where BL.batch_line_id = p_batch_line_list(l_index);
2363: end loop;
2622: (
2623: p_batch_id number
2624: ) is
2625: select distinct nvl(BL.batch_line_status, 'U')
2626: from pay_balance_batch_lines BL
2627: where BL.batch_id = p_batch_id
2628: order by decode(nvl(BL.batch_line_status, 'U'), 'U', 1
2629: , 'T', 2
2630: , 'E', 3
3131: -----------------------------------------------------------------------------
3132: --
3133: procedure ins_latest_balance
3134: (
3135: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3136: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3137: p_value in pay_balance_batch_lines.value%TYPE,
3138: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3139: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3132: --
3133: procedure ins_latest_balance
3134: (
3135: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3136: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3137: p_value in pay_balance_batch_lines.value%TYPE,
3138: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3139: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3140: p_tax_unit_id in number,
3133: procedure ins_latest_balance
3134: (
3135: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3136: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3137: p_value in pay_balance_batch_lines.value%TYPE,
3138: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3139: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3140: p_tax_unit_id in number,
3141: p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3134: (
3135: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3136: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3137: p_value in pay_balance_batch_lines.value%TYPE,
3138: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3139: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3140: p_tax_unit_id in number,
3141: p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3142: default NULL,
3137: p_value in pay_balance_batch_lines.value%TYPE,
3138: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3139: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3140: p_tax_unit_id in number,
3141: p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3142: default NULL,
3143: p_source_id in number,
3144: p_source_text in varchar2,
3145: p_source_number in number,
3144: p_source_text in varchar2,
3145: p_source_number in number,
3146: p_source_text2 in varchar2,
3147: p_run_type_id in number,
3148: p_oee_id in pay_balance_batch_lines.original_entry_id%TYPE
3149: default NULL
3150: ) is
3151: --
3152: cursor csr_get_def_bal (p_bal_type_id in number,
3422: pbl.payroll_action_id,
3423: pbl.value
3424: from
3425: pay_balance_dimensions pbd,
3426: pay_balance_batch_lines pbl
3427: where pbl.batch_id = p_batch_id
3428: and pbl.assignment_id = p_assignment_id
3429: and pbl.balance_dimension_id = pbd.balance_dimension_id
3430: and pbl.upload_date is null -- Don't consider historical loads
3462: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3463: 10);
3464: select /*+ ORDERED
3465: USE_NL(pbl ppa pbf paa rr rrv)
3466: INDEX(pbl PAY_BALANCE_BATCH_LINES_N51)
3467: INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
3468: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
3469: INDEX(rr PAY_RUN_RESULTS_N50)
3470: INDEX(rrv PAY_RUN_RESULT_VALUES_N50)
3472: to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3473: paa.assignment_action_id),16))
3474: into l_asg_act_id
3475: from
3476: pay_balance_batch_lines pbl,
3477: pay_payroll_actions ppa,
3478: pay_assignment_actions paa,
3479: pay_run_results rr,
3480: pay_run_result_values rrv,
4307: l_ele_link_id number;
4308: --
4309: -- Variables to cache details of the current balance being validated.
4310: --
4311: l_bal_id pay_balance_batch_lines.balance_type_id%type;
4312: l_bal_name pay_balance_batch_lines.balance_name%type;
4313: l_bal_invld boolean := FALSE;
4314: l_bal_invl_feed boolean := FALSE;
4315: l_bal_invl_link boolean := FALSE;
4308: --
4309: -- Variables to cache details of the current balance being validated.
4310: --
4311: l_bal_id pay_balance_batch_lines.balance_type_id%type;
4312: l_bal_name pay_balance_batch_lines.balance_name%type;
4313: l_bal_invld boolean := FALSE;
4314: l_bal_invl_feed boolean := FALSE;
4315: l_bal_invl_link boolean := FALSE;
4316: l_bal_vald_rec t_balance_validation_rec;
4530: procedure validate_assignment
4531: (
4532: p_glbl_data_rec in glbl_data_rec_type
4533: ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
4534: ,p_asg_id in out nocopy pay_balance_batch_lines.assignment_id%type
4535: ,p_asg_number in out nocopy pay_balance_batch_lines.assignment_number%type
4536: ,p_asg_invld in out nocopy boolean
4537: ,p_asg_invld_type in out nocopy boolean
4538: ,p_asg_invld_pyrl in out nocopy boolean
4531: (
4532: p_glbl_data_rec in glbl_data_rec_type
4533: ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
4534: ,p_asg_id in out nocopy pay_balance_batch_lines.assignment_id%type
4535: ,p_asg_number in out nocopy pay_balance_batch_lines.assignment_number%type
4536: ,p_asg_invld in out nocopy boolean
4537: ,p_asg_invld_type in out nocopy boolean
4538: ,p_asg_invld_pyrl in out nocopy boolean
4539: ,p_asg_too_short in out nocopy boolean
4796: -- Variables to cache details of the previously validated assignment. This
4797: -- can be used bu the validate_assignment procedure when validating future
4798: -- assignments.
4799: --
4800: l_asg_id pay_balance_batch_lines.assignment_id%type;
4801: l_asg_number pay_balance_batch_lines.assignment_number%type;
4802: l_asg_invld boolean := FALSE;
4803: l_asg_invld_type boolean := FALSE;
4804: l_asg_invld_pyrl boolean := FALSE;
4797: -- can be used bu the validate_assignment procedure when validating future
4798: -- assignments.
4799: --
4800: l_asg_id pay_balance_batch_lines.assignment_id%type;
4801: l_asg_number pay_balance_batch_lines.assignment_number%type;
4802: l_asg_invld boolean := FALSE;
4803: l_asg_invld_type boolean := FALSE;
4804: l_asg_invld_pyrl boolean := FALSE;
4805: l_asg_too_short boolean := FALSE;
4879: -- Update the batch line with information retrieved during validation
4880: -- ie. if the assignment_number was set on a batch line then the
4881: -- assiugnment_id is derived etc...
4882: --
4883: update pay_balance_batch_lines BL
4884: set BL.assignment_number = l_batch_line_rec.assignment_number
4885: ,BL.assignment_id = l_batch_line_rec.assignment_id
4886: ,BL.balance_name = l_batch_line_rec.balance_name
4887: ,BL.balance_type_id = l_batch_line_rec.balance_type_id
5217: ,p_message_token => null);
5218: --
5219: -- Mark each batch line as invalid.
5220: --
5221: update pay_balance_batch_lines BL
5222: set BL.batch_line_status = 'E' -- Error
5223: where BL.batch_line_id = l_batch_line_list(l_index);
5224: --
5225: end loop;
5258: ,p_message_token => null);
5259: --
5260: -- Mark each batch line as invalid.
5261: --
5262: update pay_balance_batch_lines BL
5263: set BL.batch_line_status = 'E' -- Error
5264: where BL.batch_line_id = l_batch_line_list(l_index);
5265: --
5266: end loop;
5300: ) is
5301: select /*+ ORDERED
5302: INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
5303: distinct ASG.assignment_id
5304: from pay_balance_batch_lines BL,
5305: per_all_assignments_f ASG
5306: where BL.batch_id = p_batch_id
5307: and BL.assignment_id = ASG.assignment_id
5308: and ASG.business_group_id = p_business_group_id
5446: -- Reset the status of the batch lines for which the payroll action
5447: -- has been rolled back.
5448: -- (#2676349) commented out the assignment condition in the
5449: -- where clause.
5450: update pay_balance_batch_lines BL
5451: set BL.batch_line_status = 'U'
5452: ,BL.payroll_action_id = null
5453: where BL.batch_id = p_glbl_data_rec.batch_id
5454: --and BL.assignment_id = l_asg_id
5511: --
5512: cursor csr_get_err_lines (p_batch_id in number)
5513: is
5514: select BL.batch_line_id
5515: from pay_balance_batch_lines BL
5516: where BL.batch_id = p_batch_id
5517: and BL.batch_line_status = 'E';
5518: --
5519: begin
5537: and ML.source_id = p_glbl_data_rec.batch_id;
5538: --
5539: -- Delete the batch lines for the batch.
5540: --
5541: delete from pay_balance_batch_lines BBL
5542: where BBL.batch_id = p_glbl_data_rec.batch_id;
5543: --
5544: -- Delete the batch header.
5545: --
5636: ) is
5637: select /*+ ORDERED
5638: INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
5639: distinct ASG.assignment_id
5640: from pay_balance_batch_lines BL,
5641: per_all_assignments_f ASG
5642: where BL.batch_id = p_batch_id
5643: and BL.assignment_id = ASG.assignment_id
5644: and BL.batch_line_status = 'T'
5738: select
5739: distinct ppa.payroll_action_id
5740: from
5741: pay_payroll_actions ppa
5742: ,pay_balance_batch_lines pbbl
5743: where
5744: ppa.action_status <> 'C'
5745: and ppa.payroll_action_id = pbbl.payroll_action_id
5746: and pbbl.batch_line_status = 'T'