[Home] [Help]
6: type con_num_array is table of ff_contexts.context_id%type
7: index by binary_integer;
8: type con_type_array is table of ff_contexts.data_type%type
9: index by binary_integer;
10: type con_val_array is table of pay_run_result_values.result_value%type
11: index by binary_integer;
12: type par_val_array is table of varchar2(80)
13: index by binary_integer;
14: type number_array is table of number
171: jurisdiction_level pay_balance_types.jurisdiction_level%type,
172: tax_unit_id pay_assignment_actions.tax_unit_id%type,
173: jurisdiction_code pay_run_results.jurisdiction_code%type,
174: source_id number,
175: source_text pay_run_result_values.result_value%type,
176: source_number number,
177: source_text2 pay_run_result_values.result_value%type,
178: time_def_id pay_run_results.time_definition_id%type,
179: balance_date pay_run_results.end_date%type,
173: jurisdiction_code pay_run_results.jurisdiction_code%type,
174: source_id number,
175: source_text pay_run_result_values.result_value%type,
176: source_number number,
177: source_text2 pay_run_result_values.result_value%type,
178: time_def_id pay_run_results.time_definition_id%type,
179: balance_date pay_run_results.end_date%type,
180: original_entry_id number,
181: local_unit_id pay_run_results.local_unit_id%type,
194: payroll_action_id pay_payroll_actions.payroll_action_id%type,
195: tax_unit_id pay_assignment_actions.tax_unit_id%type,
196: jurisdiction_code pay_run_results.jurisdiction_code%type,
197: source_id number,
198: source_text pay_run_result_values.result_value%type,
199: source_number number,
200: source_text2 pay_run_result_values.result_value%type,
201: time_def_id pay_run_results.time_definition_id%type,
202: balance_date pay_run_results.end_date%type,
196: jurisdiction_code pay_run_results.jurisdiction_code%type,
197: source_id number,
198: source_text pay_run_result_values.result_value%type,
199: source_number number,
200: source_text2 pay_run_result_values.result_value%type,
201: time_def_id pay_run_results.time_definition_id%type,
202: balance_date pay_run_results.end_date%type,
203: local_unit_id pay_run_results.local_unit_id%type,
204: source_number2 number,
231: type t_context_details_rec is record
232: (
233: tax_unit_id pay_assignment_actions.tax_unit_id%type,
234: jurisdiction_code pay_run_results.jurisdiction_code%type,
235: source_id pay_run_result_values.result_value%type,
236: source_text pay_run_result_values.result_value%type,
237: source_number pay_run_result_values.result_value%type,
238: source_text2 pay_run_result_values.result_value%type,
239: time_def_id pay_run_results.time_definition_id%type,
232: (
233: tax_unit_id pay_assignment_actions.tax_unit_id%type,
234: jurisdiction_code pay_run_results.jurisdiction_code%type,
235: source_id pay_run_result_values.result_value%type,
236: source_text pay_run_result_values.result_value%type,
237: source_number pay_run_result_values.result_value%type,
238: source_text2 pay_run_result_values.result_value%type,
239: time_def_id pay_run_results.time_definition_id%type,
240: balance_date pay_run_results.end_date%type,
233: tax_unit_id pay_assignment_actions.tax_unit_id%type,
234: jurisdiction_code pay_run_results.jurisdiction_code%type,
235: source_id pay_run_result_values.result_value%type,
236: source_text pay_run_result_values.result_value%type,
237: source_number pay_run_result_values.result_value%type,
238: source_text2 pay_run_result_values.result_value%type,
239: time_def_id pay_run_results.time_definition_id%type,
240: balance_date pay_run_results.end_date%type,
241: local_unit_id pay_run_results.local_unit_id%type,
234: jurisdiction_code pay_run_results.jurisdiction_code%type,
235: source_id pay_run_result_values.result_value%type,
236: source_text pay_run_result_values.result_value%type,
237: source_number pay_run_result_values.result_value%type,
238: source_text2 pay_run_result_values.result_value%type,
239: time_def_id pay_run_results.time_definition_id%type,
240: balance_date pay_run_results.end_date%type,
241: local_unit_id pay_run_results.local_unit_id%type,
242: source_number2 number,
242: source_number2 number,
243: organization_id number,
244: prv_tax_unit_id pay_assignment_actions.tax_unit_id%type,
245: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
246: prv_source_id pay_run_result_values.result_value%type,
247: prv_source_text pay_run_result_values.result_value%type,
248: prv_source_number pay_run_result_values.result_value%type,
249: prv_source_text2 pay_run_result_values.result_value%type,
250: prv_time_def_id pay_run_results.time_definition_id%type,
243: organization_id number,
244: prv_tax_unit_id pay_assignment_actions.tax_unit_id%type,
245: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
246: prv_source_id pay_run_result_values.result_value%type,
247: prv_source_text pay_run_result_values.result_value%type,
248: prv_source_number pay_run_result_values.result_value%type,
249: prv_source_text2 pay_run_result_values.result_value%type,
250: prv_time_def_id pay_run_results.time_definition_id%type,
251: prv_balance_date pay_run_results.end_date%type,
244: prv_tax_unit_id pay_assignment_actions.tax_unit_id%type,
245: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
246: prv_source_id pay_run_result_values.result_value%type,
247: prv_source_text pay_run_result_values.result_value%type,
248: prv_source_number pay_run_result_values.result_value%type,
249: prv_source_text2 pay_run_result_values.result_value%type,
250: prv_time_def_id pay_run_results.time_definition_id%type,
251: prv_balance_date pay_run_results.end_date%type,
252: prv_local_unit_id pay_run_results.local_unit_id%type,
245: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
246: prv_source_id pay_run_result_values.result_value%type,
247: prv_source_text pay_run_result_values.result_value%type,
248: prv_source_number pay_run_result_values.result_value%type,
249: prv_source_text2 pay_run_result_values.result_value%type,
250: prv_time_def_id pay_run_results.time_definition_id%type,
251: prv_balance_date pay_run_results.end_date%type,
252: prv_local_unit_id pay_run_results.local_unit_id%type,
253: prv_source_number2 number,
359:
360: type t_inp_value is record
361: (
362: rv_input_name pay_input_values_f.name%type,
363: rv_input_value pay_run_result_values.result_value%type
364: );
365:
366: Type typ_inp_value is table of t_inp_value index by binary_integer;
367:
366: Type typ_inp_value is table of t_inp_value index by binary_integer;
367:
368: tab_inp_value typ_inp_value;
369:
370: g_context_id pay_run_result_values.run_result_id%type;
371: g_val_cache boolean ;
372: --
373: -- Copy of Hr_General2.get_oracle_db_version to avoid a very nasty patching
374: -- issue in the c-code chain.
6248: from pay_run_balances rb
6249: where rb.defined_balance_id = pdb.defined_balance_id
6250: and rb.effective_date >= p_inv_date
6251: and exists ( select /*+ no_unnest */ 1 from pay_run_results rr,
6252: pay_run_result_values rrv
6253: where rr.assignment_action_id = rb.assignment_action_id
6254: and rr.element_type_id = p_element_type_id
6255: and rrv.run_result_id = rr.run_result_id
6256: and rr.status in ('P', 'PA')
6279: where rb.defined_balance_id = pdb.defined_balance_id
6280: and rb.effective_date >= p_inv_date
6281: and assact.payroll_action_id = rb.payroll_action_id
6282: and exists ( select /*+ no_unnest */ 1 from pay_run_results rr,
6283: pay_run_result_values rrv
6284: where rr.assignment_action_id = assact.assignment_action_id
6285: and rr.element_type_id = p_element_type_id
6286: and rrv.run_result_id = rr.run_result_id
6287: and rr.status in ('P', 'PA')
6312: from pay_run_balances rb
6313: where rb.defined_balance_id = pdb.defined_balance_id
6314: and rb.effective_date >= p_inv_date
6315: and exists ( select 1 from pay_run_results rr,
6316: pay_run_result_values rrv
6317: where rr.assignment_action_id = rb.assignment_action_id
6318: and rr.element_type_id = p_element_type_id
6319: and rrv.run_result_id = rr.run_result_id
6320: and rr.status in ('P', 'PA')
6343: where rb.defined_balance_id = pdb.defined_balance_id
6344: and rb.effective_date >= p_inv_date
6345: and assact.payroll_action_id = rb.payroll_action_id
6346: and exists ( select 1 from pay_run_results rr,
6347: pay_run_result_values rrv
6348: where rr.assignment_action_id = assact.assignment_action_id
6349: and rr.element_type_id = p_element_type_id
6350: and rrv.run_result_id = rr.run_result_id
6351: and rr.status in ('P', 'PA')
6357: cursor ivchk is
6358: select '1' from dual
6359: where exists
6360: (select 1
6361: from pay_run_result_values rrv
6362: where rrv.input_value_id = p_input_value_id);
6363:
6364: l_ivchk varchar2(2);
6365: l_rrv_found number := -1;
6591: cursor get_sval(p_rr_id in number,
6592: p_ele_id in number,
6593: p_effdate in date) is
6594: select piv.name,prrv.result_value
6595: from pay_run_result_values prrv,
6596: pay_input_values_f piv
6597: where prrv.run_result_id = p_rr_id
6598: and piv.input_value_id = prrv.input_value_id
6599: and piv.element_type_id = p_ele_id
6599: and piv.element_type_id = p_ele_id
6600: and p_effdate between piv.effective_start_date
6601: and piv.effective_end_date;
6602: --
6603: cnt_value pay_run_result_values.result_value%type;
6604: l_rr_rec t_run_result_rec;
6605: l_inp_val_name pay_input_values_f.name%type;
6606: l_found boolean;
6607:
7848: pay_input_values_f piv
7849: ,pay_balance_feeds_f pbf
7850: ,pay_defined_balances pdb
7851: ,pay_balance_dimensions pbd
7852: ,pay_run_result_values prrv
7853: where
7854: pbd.dimension_level = 'ASG'
7855: and pdb.save_run_balance = 'Y'
7856: and ( (pdb.business_group_id = p_bus_grp_id
8610: p_bal_list varchar2,
8611: p_def_bal number)
8612: is
8613: select /*+ ORDERED USE_NL(piv pbf pbt pdb pbd prrv)
8614: INDEX (prrv PAY_RUN_RESULT_VALUES_PK)*/
8615: distinct pdb.defined_balance_id,
8616: pbt.jurisdiction_level,
8617: pbt.balance_type_id,
8618: pbd.balance_dimension_id
8623: pay_balance_feeds_f pbf,
8624: pay_balance_types pbt,
8625: pay_defined_balances pdb,
8626: pay_balance_dimensions pbd,
8627: pay_run_result_values prrv
8628: where pbd.dimension_level = 'GRP'
8629: and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
8630: and pdb.balance_dimension_id = pbd.balance_dimension_id
8631: and pdb.balance_type_id = pbt.balance_type_id
8663: and prrv.input_value_id = piv.input_value_id
8664: and prrv.result_value is not null;
8665: -- and exists (select /*+ ORDERED */ ''
8666: -- from pay_run_results prr,
8667: -- pay_run_result_values prrv,
8668: -- pay_balance_feeds_f pbf
8669: -- where prr.assignment_action_id = p_asg_act_id
8670: -- and prr.run_result_id = prrv.run_result_id
8671: -- and prrv.input_value_id = pbf.input_value_id
8695: and p_def_bal_id = pba.defined_balance_id
8696: and exists (select /*+ ORDERED */ ''
8697: from pay_assignment_actions paa,
8698: pay_run_results prr,
8699: pay_run_result_values prrv,
8700: pay_balance_feeds_f pbf
8701: where paa.payroll_action_id = p_pact_id
8702: and prr.assignment_action_id = paa.assignment_action_id
8703: and prr.run_result_id = prrv.run_result_id
8726: (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
8727: distinct pbf.balance_type_id
8728: from pay_assignment_actions paa,
8729: pay_run_results prr,
8730: pay_run_result_values prrv,
8731: pay_balance_feeds_f pbf
8732: where paa.payroll_action_id = p_pact_id
8733: and prr.assignment_action_id = paa.assignment_action_id
8734: and prr.run_result_id = prrv.run_result_id
8767: (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
8768: distinct pbf.balance_type_id
8769: from pay_assignment_actions paa,
8770: pay_run_results prr,
8771: pay_run_result_values prrv,
8772: pay_balance_feeds_f pbf
8773: where paa.payroll_action_id = p_pact_id
8774: and prr.assignment_action_id = paa.assignment_action_id
8775: and prr.run_result_id = prrv.run_result_id
9478: p_bal_list varchar2,
9479: p_def_bal number)
9480: is
9481: select /*+ ORDERED USE_NL(pbf pbt pdb pbd prrv)
9482: INDEX(prrv PAY_RUN_RESULT_VALUES_N50)*/
9483: distinct pdb.defined_balance_id,
9484: pbt.jurisdiction_level,
9485: pbt.balance_type_id,
9486: pbd.balance_dimension_id
9485: pbt.balance_type_id,
9486: pbd.balance_dimension_id
9487: from
9488: pay_run_results prr,
9489: pay_run_result_values prrv,
9490: pay_balance_feeds_f pbf,
9491: pay_balance_types pbt,
9492: pay_defined_balances pdb,
9493: pay_balance_dimensions pbd
9521: and prrv.input_value_id = pbf.input_value_id
9522: and pbt.balance_type_id = pbf.balance_type_id;
9523: -- and exists (select /*+ ORDERED */ ''
9524: -- from pay_run_results prr,
9525: -- pay_run_result_values prrv,
9526: -- pay_balance_feeds_f pbf
9527: -- where prr.assignment_action_id = p_asg_act_id
9528: -- and prr.run_result_id = prrv.run_result_id
9529: -- and prrv.input_value_id = pbf.input_value_id
9552: and pbd.dimension_level = 'ASG'
9553: and p_def_bal_id = pba.defined_balance_id
9554: and exists (select /*+ ORDERED */ ''
9555: from pay_run_results prr,
9556: pay_run_result_values prrv
9557: where prr.assignment_action_id = p_asg_act_id
9558: and prr.run_result_id = prrv.run_result_id
9559: and exists ( select /*+ no_unnest */ ''
9560: from pay_balance_feeds_f pbf
9583: and pdb.balance_type_id in
9584: (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
9585: distinct pbf.balance_type_id
9586: from pay_run_results prr,
9587: pay_run_result_values prrv,
9588: pay_balance_feeds_f pbf
9589: where prr.assignment_action_id = p_asg_act_id
9590: and prr.run_result_id = prrv.run_result_id
9591: and prrv.input_value_id = pbf.input_value_id);
9622: -- and pdb.balance_type_id in
9623: -- (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
9624: -- distinct pbf.balance_type_id
9625: -- from pay_run_results prr,
9626: -- pay_run_result_values prrv,
9627: -- pay_balance_feeds_f pbf
9628: -- where prr.assignment_action_id = p_asg_act_id
9629: -- and prr.run_result_id = prrv.run_result_id
9630: -- and prrv.input_value_id = pbf.input_value_id
9658: pay_defined_balances pdb,
9659: pay_balance_attributes pba,
9660: pay_balance_dimensions pbd,
9661: pay_balance_validation pbv,
9662: pay_run_result_values prrv
9663: where
9664: prr.assignment_action_id = p_asg_act_id
9665: and piv.element_type_id = prr.element_type_id
9666: and p_eff_date between piv.effective_start_date
10789: from dual
10790: where exists (select /*+ ORDERED*/
10791: null
10792: from pay_balance_feeds_f bf
10793: , pay_run_result_values rrv
10794: where bf.balance_type_id = p_baltype_id
10795: and bf.input_value_id = rrv.input_value_id);
10796: --
10797: -- Bug 3364019 added the + 1 to get the correct balance_load_date
11413: where f.balance_type_id = p_balance_type_id
11414: and (f.business_group_id is null or f.business_group_id = p_business_group_id)
11415: and f.effective_end_date >= p_from_date
11416: and i.input_value_id = f.input_value_id
11417: and exists ( select null from pay_run_result_values rrv
11418: where rrv.input_value_id = f.input_value_id )
11419: and f.effective_start_date between i.effective_start_date and i.effective_end_date;
11420:
11421: cursor rr_run_values(p_input_value_id number,
11436: from
11437: pay_assignment_actions assact,
11438: pay_payroll_actions pact,
11439: pay_run_results rr,
11440: pay_run_result_values rrv
11441: where assact.assignment_id = p_assignment_id
11442: --and assact.assignment_action_id in
11443: -- ( select assignment_action_id from pay_assignment_actions where assignment_id = p_assignment_id
11444: -- minus select source_action_id from pay_assignment_actions where assignment_id = p_assignment_id)
11486: where f.balance_type_id = g_balance_type_id
11487: and (f.business_group_id is null or f.business_group_id = p_business_group_id)
11488: and f.effective_end_date >= p_from_date
11489: and i.input_value_id = f.input_value_id
11490: and exists ( select null from pay_run_result_values rrv
11491: where rrv.input_value_id = f.input_value_id )
11492: and f.effective_start_date between i.effective_start_date and i.effective_end_date;
11493:
11494: g_business_group_id := p_business_group_id;
11543: from
11544: pay_assignment_actions assact,
11545: pay_payroll_actions pact,
11546: pay_run_results rr,
11547: pay_run_result_values rrv,
11548: pay_balance_feeds_f feed
11549: where assact.assignment_id = g_assignment_id
11550: --and assact.assignment_action_id in
11551: -- ( select assignment_action_id from pay_assignment_actions where assignment_id = g_assignment_id
11710: where f.balance_type_id = p_balance_type_id
11711: and (f.business_group_id is null or f.business_group_id = p_business_group_id)
11712: and f.effective_end_date >= p_from_date
11713: and i.input_value_id = f.input_value_id
11714: and exists ( select null from pay_run_result_values rrv
11715: where rrv.input_value_id = f.input_value_id )
11716: and f.effective_start_date between i.effective_start_date and i.effective_end_date;
11717:
11718: cursor rr_run_values(p_input_value_id number,
11737: pay_assignment_actions gen_assact,
11738: pay_assignment_actions assact,
11739: pay_payroll_actions pact,
11740: pay_run_results rr,
11741: pay_run_result_values rrv
11742: where gen_assact.payroll_action_id = p_generate_pact
11743: and gen_assact.chunk_number = p_chunk_number
11744: and assact.assignment_id = gen_assact.assignment_id
11745: and pact.payroll_action_id = assact.payroll_action_id
11787: where f.balance_type_id = g_balance_type_id
11788: and (f.business_group_id is null or f.business_group_id = p_business_group_id)
11789: and f.effective_end_date >= p_from_date
11790: and i.input_value_id = f.input_value_id
11791: and exists ( select null from pay_run_result_values rrv
11792: where rrv.input_value_id = f.input_value_id )
11793: and f.effective_start_date between i.effective_start_date and i.effective_end_date;
11794:
11795: g_business_group_id := p_business_group_id;
11848: pay_assignment_actions gen_assact,
11849: pay_assignment_actions assact,
11850: pay_payroll_actions pact,
11851: pay_run_results rr,
11852: pay_run_result_values rrv,
11853: pay_balance_feeds_f feed
11854: where gen_assact.payroll_action_id = p_generate_pact
11855: and gen_assact.chunk_number = g_chunk_number
11856: and assact.assignment_id = gen_assact.assignment_id