[Home] [Help]
249: a batch.
250: 40.20 N.Bristow 08-MAY-1996 Bug 359005. Now caching Tax Unit Id
251: when validating. Reinstated the
252: tax unit id column on
253: pay_balance_batch_lines. Tuned several
254: statements.
255: 40.19 N.Bristow 18-MAR-1996 Now padding expired latest balance
256: columns with -9999.
257: 40.18 N.Bristow 18-MAR-1996 Bug 349583. Order by clause on
312: ,business_group_id pay_balance_batch_headers.business_group_id%type
313: ,legislation_code varchar2(30)
314: ,payroll_id pay_balance_batch_headers.payroll_id%type
315: ,consolidation_set_id pay_consolidation_sets.consolidation_set_id%type
316: ,assignment_id pay_balance_batch_lines.assignment_id%type
317: ,batch_header_status pay_balance_batch_headers.batch_status%type
318: ,batch_line_status pay_balance_batch_lines.batch_line_status%type
319: ,chunk_size number(9)
320: ,jurisdiction_iv pay_input_values_f.name%type
314: ,payroll_id pay_balance_batch_headers.payroll_id%type
315: ,consolidation_set_id pay_consolidation_sets.consolidation_set_id%type
316: ,assignment_id pay_balance_batch_lines.assignment_id%type
317: ,batch_header_status pay_balance_batch_headers.batch_status%type
318: ,batch_line_status pay_balance_batch_lines.batch_line_status%type
319: ,chunk_size number(9)
320: ,jurisdiction_iv pay_input_values_f.name%type
321: ,include_adj_rule pay_legislation_rules.rule_mode%type
322: );
414: (
415: p_batch_id number
416: ) is
417: select *
418: from pay_balance_batch_lines BL
419: where BL.batch_id = p_batch_id
420: and nvl(BL.batch_line_status, 'U') <> 'T'
421: order by BL.assignment_id,
422: BL.assignment_number
465: ,BL.jurisdiction_code
466: ,BL.original_entry_id
467: ,BL.batch_line_status) expiry_date
468: from pay_balance_batch_headers BH
469: ,pay_balance_batch_lines BL
470: where BH.batch_id = p_batch_id
471: and BL.batch_id = BH.batch_id
472: and BL.assignment_id = p_assignment_id
473: order by BL.assignment_id
496: (
497: p_batch_id number
498: ) is
499: select *
500: from pay_balance_batch_lines BL
501: where BL.batch_id = p_batch_id
502: and BL.batch_line_status = 'T'
503: order by BL.payroll_action_id;
504: --
555: procedure remove_messages(p_batch_id in number)
556: is
557: cursor pml (p_batch_id number) is
558: select batch_line_id
559: from pay_balance_batch_lines
560: where batch_id = p_batch_id;
561: --
562: begin
563: hr_utility.trace('Entering pay_balance_upload.remove_messages');
1281: -- p_batch_id - the batch header to be locked.
1282: -- USES
1283: -- NOTES
1284: -- This is used by the insert, update and delete triggers for the table
1285: -- PAY_BALANCE_BATCH_LINES. This can be used to ensure that the batch lines
1286: -- cannot be changed once another user has a row level lock on the batch
1287: -- header. This is used by the process to freeze the batch definition while
1288: -- it is being processed.
1289: -----------------------------------------------------------------------------
2381: l_tax_unit_id number;
2382: l_oee_id number;
2383: l_source_id number;
2384: l_run_type_id number;
2385: l_source_text pay_balance_batch_lines.source_text%type;
2386: l_source_number pay_balance_batch_lines.source_number%type;
2387: l_source_text2 pay_balance_batch_lines.source_text2%type;
2388: l_adj_date date;
2389: l_jurisdiction_code varchar2(30);
2382: l_oee_id number;
2383: l_source_id number;
2384: l_run_type_id number;
2385: l_source_text pay_balance_batch_lines.source_text%type;
2386: l_source_number pay_balance_batch_lines.source_number%type;
2387: l_source_text2 pay_balance_batch_lines.source_text2%type;
2388: l_adj_date date;
2389: l_jurisdiction_code varchar2(30);
2390: l_payroll_id number;
2383: l_source_id number;
2384: l_run_type_id number;
2385: l_source_text pay_balance_batch_lines.source_text%type;
2386: l_source_number pay_balance_batch_lines.source_number%type;
2387: l_source_text2 pay_balance_batch_lines.source_text2%type;
2388: l_adj_date date;
2389: l_jurisdiction_code varchar2(30);
2390: l_payroll_id number;
2391: --
2641: --
2642: for l_index in 1..p_num_lines loop
2643: hr_utility.trace('Updating Line '||p_batch_line_list(l_index)||
2644: ' With Payroll Action '||l_payroll_action_id);
2645: update pay_balance_batch_lines BL
2646: set BL.batch_line_status = 'T' -- Transferred
2647: ,BL.payroll_action_id = l_payroll_action_id
2648: where BL.batch_line_id = p_batch_line_list(l_index);
2649: end loop;
2915: (
2916: p_batch_id number
2917: ) is
2918: select distinct nvl(BL.batch_line_status, 'U')
2919: from pay_balance_batch_lines BL
2920: where BL.batch_id = p_batch_id
2921: order by decode(nvl(BL.batch_line_status, 'U'), 'U', 1
2922: , 'T', 2
2923: , 'E', 3
3424: -----------------------------------------------------------------------------
3425: --
3426: procedure ins_latest_balance
3427: (
3428: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3429: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3430: p_value in pay_balance_batch_lines.value%TYPE,
3431: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3432: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3425: --
3426: procedure ins_latest_balance
3427: (
3428: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3429: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3430: p_value in pay_balance_batch_lines.value%TYPE,
3431: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3432: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3433: p_tax_unit_id in number,
3426: procedure ins_latest_balance
3427: (
3428: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3429: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3430: p_value in pay_balance_batch_lines.value%TYPE,
3431: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3432: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3433: p_tax_unit_id in number,
3434: p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3427: (
3428: p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3429: p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3430: p_value in pay_balance_batch_lines.value%TYPE,
3431: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3432: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3433: p_tax_unit_id in number,
3434: p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3435: default NULL,
3430: p_value in pay_balance_batch_lines.value%TYPE,
3431: p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3432: p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3433: p_tax_unit_id in number,
3434: p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3435: default NULL,
3436: p_source_id in number,
3437: p_source_text in varchar2,
3438: p_source_number in number,
3437: p_source_text in varchar2,
3438: p_source_number in number,
3439: p_source_text2 in varchar2,
3440: p_run_type_id in number,
3441: p_oee_id in pay_balance_batch_lines.original_entry_id%TYPE
3442: default NULL
3443: ) is
3444: --
3445: cursor csr_get_def_bal (p_bal_type_id in number,
3715: pbl.payroll_action_id,
3716: pbl.value
3717: from
3718: pay_balance_dimensions pbd,
3719: pay_balance_batch_lines pbl
3720: where pbl.batch_id = p_batch_id
3721: and pbl.assignment_id = p_assignment_id
3722: and pbl.balance_dimension_id = pbd.balance_dimension_id
3723: and pbl.upload_date is null -- Don't consider historical loads
3755: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3756: 10);
3757: select /*+ ORDERED
3758: USE_NL(pbl ppa pbf paa rr rrv)
3759: INDEX(pbl PAY_BALANCE_BATCH_LINES_N51)
3760: INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
3761: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
3762: INDEX(rr PAY_RUN_RESULTS_N50)
3763: INDEX(rrv PAY_RUN_RESULT_VALUES_N50)
3765: to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3766: paa.assignment_action_id),16))
3767: into l_asg_act_id
3768: from
3769: pay_balance_batch_lines pbl,
3770: pay_payroll_actions ppa,
3771: pay_assignment_actions paa,
3772: pay_run_results rr,
3773: pay_run_result_values rrv,
4600: l_ele_link_id number;
4601: --
4602: -- Variables to cache details of the current balance being validated.
4603: --
4604: l_bal_id pay_balance_batch_lines.balance_type_id%type;
4605: l_bal_name pay_balance_batch_lines.balance_name%type;
4606: l_bal_invld boolean := FALSE;
4607: l_bal_invl_feed boolean := FALSE;
4608: l_bal_invl_link boolean := FALSE;
4601: --
4602: -- Variables to cache details of the current balance being validated.
4603: --
4604: l_bal_id pay_balance_batch_lines.balance_type_id%type;
4605: l_bal_name pay_balance_batch_lines.balance_name%type;
4606: l_bal_invld boolean := FALSE;
4607: l_bal_invl_feed boolean := FALSE;
4608: l_bal_invl_link boolean := FALSE;
4609: l_bal_vald_rec t_balance_validation_rec;
4823: procedure validate_assignment
4824: (
4825: p_glbl_data_rec in glbl_data_rec_type
4826: ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
4827: ,p_asg_id in out nocopy pay_balance_batch_lines.assignment_id%type
4828: ,p_asg_number in out nocopy pay_balance_batch_lines.assignment_number%type
4829: ,p_asg_invld in out nocopy boolean
4830: ,p_asg_invld_type in out nocopy boolean
4831: ,p_asg_invld_pyrl in out nocopy boolean
4824: (
4825: p_glbl_data_rec in glbl_data_rec_type
4826: ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
4827: ,p_asg_id in out nocopy pay_balance_batch_lines.assignment_id%type
4828: ,p_asg_number in out nocopy pay_balance_batch_lines.assignment_number%type
4829: ,p_asg_invld in out nocopy boolean
4830: ,p_asg_invld_type in out nocopy boolean
4831: ,p_asg_invld_pyrl in out nocopy boolean
4832: ,p_asg_too_short in out nocopy boolean
5089: -- Variables to cache details of the previously validated assignment. This
5090: -- can be used bu the validate_assignment procedure when validating future
5091: -- assignments.
5092: --
5093: l_asg_id pay_balance_batch_lines.assignment_id%type;
5094: l_asg_number pay_balance_batch_lines.assignment_number%type;
5095: l_asg_invld boolean := FALSE;
5096: l_asg_invld_type boolean := FALSE;
5097: l_asg_invld_pyrl boolean := FALSE;
5090: -- can be used bu the validate_assignment procedure when validating future
5091: -- assignments.
5092: --
5093: l_asg_id pay_balance_batch_lines.assignment_id%type;
5094: l_asg_number pay_balance_batch_lines.assignment_number%type;
5095: l_asg_invld boolean := FALSE;
5096: l_asg_invld_type boolean := FALSE;
5097: l_asg_invld_pyrl boolean := FALSE;
5098: l_asg_too_short boolean := FALSE;
5172: -- Update the batch line with information retrieved during validation
5173: -- ie. if the assignment_number was set on a batch line then the
5174: -- assiugnment_id is derived etc...
5175: --
5176: update pay_balance_batch_lines BL
5177: set BL.assignment_number = l_batch_line_rec.assignment_number
5178: ,BL.assignment_id = l_batch_line_rec.assignment_id
5179: ,BL.balance_name = l_batch_line_rec.balance_name
5180: ,BL.balance_type_id = l_batch_line_rec.balance_type_id
5510: ,p_message_token => null);
5511: --
5512: -- Mark each batch line as invalid.
5513: --
5514: update pay_balance_batch_lines BL
5515: set BL.batch_line_status = 'E' -- Error
5516: where BL.batch_line_id = l_batch_line_list(l_index);
5517: --
5518: end loop;
5551: ,p_message_token => null);
5552: --
5553: -- Mark each batch line as invalid.
5554: --
5555: update pay_balance_batch_lines BL
5556: set BL.batch_line_status = 'E' -- Error
5557: where BL.batch_line_id = l_batch_line_list(l_index);
5558: --
5559: end loop;
5593: ) is
5594: select /*+ ORDERED
5595: INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
5596: distinct ASG.assignment_id
5597: from pay_balance_batch_lines BL,
5598: per_all_assignments_f ASG
5599: where BL.batch_id = p_batch_id
5600: and BL.assignment_id = ASG.assignment_id
5601: and ASG.business_group_id = p_business_group_id
5739: -- Reset the status of the batch lines for which the payroll action
5740: -- has been rolled back.
5741: -- (#2676349) commented out the assignment condition in the
5742: -- where clause.
5743: update pay_balance_batch_lines BL
5744: set BL.batch_line_status = 'U'
5745: ,BL.payroll_action_id = null
5746: where BL.batch_id = p_glbl_data_rec.batch_id
5747: --and BL.assignment_id = l_asg_id
5804: --
5805: cursor csr_get_err_lines (p_batch_id in number)
5806: is
5807: select BL.batch_line_id
5808: from pay_balance_batch_lines BL
5809: where BL.batch_id = p_batch_id
5810: and BL.batch_line_status = 'E';
5811: --
5812: begin
5830: and ML.source_id = p_glbl_data_rec.batch_id;
5831: --
5832: -- Delete the batch lines for the batch.
5833: --
5834: delete from pay_balance_batch_lines BBL
5835: where BBL.batch_id = p_glbl_data_rec.batch_id;
5836: --
5837: -- Delete the batch header.
5838: --
5929: ) is
5930: select /*+ ORDERED
5931: INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
5932: distinct ASG.assignment_id
5933: from pay_balance_batch_lines BL,
5934: per_all_assignments_f ASG
5935: where BL.batch_id = p_batch_id
5936: and BL.assignment_id = ASG.assignment_id
5937: and BL.batch_line_status = 'T'
6031: select
6032: distinct ppa.payroll_action_id
6033: from
6034: pay_payroll_actions ppa
6035: ,pay_balance_batch_lines pbbl
6036: where
6037: ppa.action_status <> 'C'
6038: and ppa.payroll_action_id = pbbl.payroll_action_id
6039: and pbbl.batch_line_status = 'T'