[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
123: jurisdiction_level pay_balance_types.jurisdiction_level%type,
124: tax_unit_id pay_assignment_actions.tax_unit_id%type,
125: jurisdiction_code pay_run_results.jurisdiction_code%type,
126: source_id number,
127: source_text pay_run_result_values.result_value%type,
128: source_number number,
129: source_text2 pay_run_result_values.result_value%type,
130: time_def_id pay_run_results.time_definition_id%type,
131: balance_date pay_run_results.end_date%type,
125: jurisdiction_code pay_run_results.jurisdiction_code%type,
126: source_id number,
127: source_text pay_run_result_values.result_value%type,
128: source_number number,
129: source_text2 pay_run_result_values.result_value%type,
130: time_def_id pay_run_results.time_definition_id%type,
131: balance_date pay_run_results.end_date%type,
132: original_entry_id number,
133: local_unit_id pay_run_results.local_unit_id%type,
146: payroll_action_id pay_payroll_actions.payroll_action_id%type,
147: tax_unit_id pay_assignment_actions.tax_unit_id%type,
148: jurisdiction_code pay_run_results.jurisdiction_code%type,
149: source_id number,
150: source_text pay_run_result_values.result_value%type,
151: source_number number,
152: source_text2 pay_run_result_values.result_value%type,
153: time_def_id pay_run_results.time_definition_id%type,
154: balance_date pay_run_results.end_date%type,
148: jurisdiction_code pay_run_results.jurisdiction_code%type,
149: source_id number,
150: source_text pay_run_result_values.result_value%type,
151: source_number number,
152: source_text2 pay_run_result_values.result_value%type,
153: time_def_id pay_run_results.time_definition_id%type,
154: balance_date pay_run_results.end_date%type,
155: local_unit_id pay_run_results.local_unit_id%type,
156: source_number2 number,
183: type t_context_details_rec is record
184: (
185: tax_unit_id pay_assignment_actions.tax_unit_id%type,
186: jurisdiction_code pay_run_results.jurisdiction_code%type,
187: source_id pay_run_result_values.result_value%type,
188: source_text pay_run_result_values.result_value%type,
189: source_number pay_run_result_values.result_value%type,
190: source_text2 pay_run_result_values.result_value%type,
191: time_def_id pay_run_results.time_definition_id%type,
184: (
185: tax_unit_id pay_assignment_actions.tax_unit_id%type,
186: jurisdiction_code pay_run_results.jurisdiction_code%type,
187: source_id pay_run_result_values.result_value%type,
188: source_text pay_run_result_values.result_value%type,
189: source_number pay_run_result_values.result_value%type,
190: source_text2 pay_run_result_values.result_value%type,
191: time_def_id pay_run_results.time_definition_id%type,
192: balance_date pay_run_results.end_date%type,
185: tax_unit_id pay_assignment_actions.tax_unit_id%type,
186: jurisdiction_code pay_run_results.jurisdiction_code%type,
187: source_id pay_run_result_values.result_value%type,
188: source_text pay_run_result_values.result_value%type,
189: source_number pay_run_result_values.result_value%type,
190: source_text2 pay_run_result_values.result_value%type,
191: time_def_id pay_run_results.time_definition_id%type,
192: balance_date pay_run_results.end_date%type,
193: local_unit_id pay_run_results.local_unit_id%type,
186: jurisdiction_code pay_run_results.jurisdiction_code%type,
187: source_id pay_run_result_values.result_value%type,
188: source_text pay_run_result_values.result_value%type,
189: source_number pay_run_result_values.result_value%type,
190: source_text2 pay_run_result_values.result_value%type,
191: time_def_id pay_run_results.time_definition_id%type,
192: balance_date pay_run_results.end_date%type,
193: local_unit_id pay_run_results.local_unit_id%type,
194: source_number2 number,
194: source_number2 number,
195: organization_id number,
196: prv_tax_unit_id pay_assignment_actions.tax_unit_id%type,
197: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
198: prv_source_id pay_run_result_values.result_value%type,
199: prv_source_text pay_run_result_values.result_value%type,
200: prv_source_number pay_run_result_values.result_value%type,
201: prv_source_text2 pay_run_result_values.result_value%type,
202: prv_time_def_id pay_run_results.time_definition_id%type,
195: organization_id number,
196: prv_tax_unit_id pay_assignment_actions.tax_unit_id%type,
197: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
198: prv_source_id pay_run_result_values.result_value%type,
199: prv_source_text pay_run_result_values.result_value%type,
200: prv_source_number pay_run_result_values.result_value%type,
201: prv_source_text2 pay_run_result_values.result_value%type,
202: prv_time_def_id pay_run_results.time_definition_id%type,
203: prv_balance_date pay_run_results.end_date%type,
196: prv_tax_unit_id pay_assignment_actions.tax_unit_id%type,
197: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
198: prv_source_id pay_run_result_values.result_value%type,
199: prv_source_text pay_run_result_values.result_value%type,
200: prv_source_number pay_run_result_values.result_value%type,
201: prv_source_text2 pay_run_result_values.result_value%type,
202: prv_time_def_id pay_run_results.time_definition_id%type,
203: prv_balance_date pay_run_results.end_date%type,
204: prv_local_unit_id pay_run_results.local_unit_id%type,
197: prv_jurisdiction_code pay_run_results.jurisdiction_code%type,
198: prv_source_id pay_run_result_values.result_value%type,
199: prv_source_text pay_run_result_values.result_value%type,
200: prv_source_number pay_run_result_values.result_value%type,
201: prv_source_text2 pay_run_result_values.result_value%type,
202: prv_time_def_id pay_run_results.time_definition_id%type,
203: prv_balance_date pay_run_results.end_date%type,
204: prv_local_unit_id pay_run_results.local_unit_id%type,
205: prv_source_number2 number,
5944: -- Select if run result value exists for input value
5945: cursor ivchk is
5946: select '1' from dual
5947: where exists
5948: (select /*+ ORDERED INDEX(RRV PAY_RUN_RESULT_VALUES_PK)
5949: INDEX(PRR PAY_RUN_RESULTS_PK) */
5950: 1
5951: from pay_run_result_values rrv,
5952: pay_run_results prr
5947: where exists
5948: (select /*+ ORDERED INDEX(RRV PAY_RUN_RESULT_VALUES_PK)
5949: INDEX(PRR PAY_RUN_RESULTS_PK) */
5950: 1
5951: from pay_run_result_values rrv,
5952: pay_run_results prr
5953: where rrv.input_value_id = p_input_value_id
5954: and prr.run_result_id = rrv.run_result_id
5955: and prr.status in ('P', 'PA')
6140: p_iv_name in varchar2,
6141: p_ele_id in number,
6142: p_effdate in date) is
6143: select prrv.result_value
6144: from pay_run_result_values prrv,
6145: pay_input_values_f piv
6146: where prrv.run_result_id = p_rr_id
6147: and piv.name = p_iv_name
6148: and piv.input_value_id = prrv.input_value_id
6149: and piv.element_type_id = p_ele_id
6150: and p_effdate between piv.effective_start_date
6151: and piv.effective_end_date;
6152: --
6153: cnt_value pay_run_result_values.result_value%type;
6154: l_rr_rec t_run_result_rec;
6155: l_inp_val_name pay_input_values_f.name%type;
6156: l_found boolean;
6157: --
7274: pay_input_values_f piv
7275: ,pay_balance_feeds_f pbf
7276: ,pay_defined_balances pdb
7277: ,pay_balance_dimensions pbd
7278: ,pay_run_result_values prrv
7279: where
7280: pbd.dimension_level = 'ASG'
7281: and pdb.save_run_balance = 'Y'
7282: and ( (pdb.business_group_id = p_bus_grp_id
8010: p_bal_list varchar2,
8011: p_def_bal number)
8012: is
8013: select /*+ ORDERED USE_NL(piv pbf pbt pdb pbd prrv)
8014: INDEX (prrv PAY_RUN_RESULT_VALUES_PK)*/
8015: distinct pdb.defined_balance_id,
8016: pbt.jurisdiction_level,
8017: pbt.balance_type_id,
8018: pbd.balance_dimension_id
8023: pay_balance_feeds_f pbf,
8024: pay_balance_types pbt,
8025: pay_defined_balances pdb,
8026: pay_balance_dimensions pbd,
8027: pay_run_result_values prrv
8028: where pbd.dimension_level = 'GRP'
8029: and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
8030: and pdb.balance_dimension_id = pbd.balance_dimension_id
8031: and pdb.balance_type_id = pbt.balance_type_id
8064: and prrv.result_value is not null;
8065:
8066: -- and exists (select /*+ ORDERED */ ''
8067: -- from pay_run_results prr,
8068: -- pay_run_result_values prrv,
8069: -- pay_balance_feeds_f pbf
8070: -- where prr.assignment_action_id = p_asg_act_id
8071: -- and prr.run_result_id = prrv.run_result_id
8072: -- and prrv.input_value_id = pbf.input_value_id
8096: and p_def_bal_id = pba.defined_balance_id
8097: and exists (select /*+ ORDERED */ ''
8098: from pay_assignment_actions paa,
8099: pay_run_results prr,
8100: pay_run_result_values prrv,
8101: pay_balance_feeds_f pbf
8102: where paa.payroll_action_id = p_pact_id
8103: and prr.assignment_action_id = paa.assignment_action_id
8104: and prr.run_result_id = prrv.run_result_id
8125: (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
8126: distinct pbf.balance_type_id
8127: from pay_assignment_actions paa,
8128: pay_run_results prr,
8129: pay_run_result_values prrv,
8130: pay_balance_feeds_f pbf
8131: where paa.payroll_action_id = p_pact_id
8132: and prr.assignment_action_id = paa.assignment_action_id
8133: and prr.run_result_id = prrv.run_result_id
8166: (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
8167: distinct pbf.balance_type_id
8168: from pay_assignment_actions paa,
8169: pay_run_results prr,
8170: pay_run_result_values prrv,
8171: pay_balance_feeds_f pbf
8172: where paa.payroll_action_id = p_pact_id
8173: and prr.assignment_action_id = paa.assignment_action_id
8174: and prr.run_result_id = prrv.run_result_id
8814: p_bal_list varchar2,
8815: p_def_bal number)
8816: is
8817: select /*+ ORDERED USE_NL(pbf pbt pdb pbd prrv)
8818: INDEX(prrv PAY_RUN_RESULT_VALUES_N50)*/
8819: distinct pdb.defined_balance_id,
8820: pbt.jurisdiction_level,
8821: pbt.balance_type_id,
8822: pbd.balance_dimension_id
8821: pbt.balance_type_id,
8822: pbd.balance_dimension_id
8823: from
8824: pay_run_results prr,
8825: pay_run_result_values prrv,
8826: pay_balance_feeds_f pbf,
8827: pay_balance_types pbt,
8828: pay_defined_balances pdb,
8829: pay_balance_dimensions pbd
8857: and prrv.input_value_id = pbf.input_value_id
8858: and pbt.balance_type_id = pbf.balance_type_id;
8859: -- and exists (select /*+ ORDERED */ ''
8860: -- from pay_run_results prr,
8861: -- pay_run_result_values prrv,
8862: -- pay_balance_feeds_f pbf
8863: -- where prr.assignment_action_id = p_asg_act_id
8864: -- and prr.run_result_id = prrv.run_result_id
8865: -- and prrv.input_value_id = pbf.input_value_id
8888: and pbd.dimension_level = 'ASG'
8889: and p_def_bal_id = pba.defined_balance_id
8890: and exists (select /*+ ORDERED */ ''
8891: from pay_run_results prr,
8892: pay_run_result_values prrv,
8893: pay_balance_feeds_f pbf
8894: where prr.assignment_action_id = p_asg_act_id
8895: and prr.run_result_id = prrv.run_result_id
8896: and prrv.input_value_id = pbf.input_value_id
8916: and pdb.balance_type_id in
8917: (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
8918: distinct pbf.balance_type_id
8919: from pay_run_results prr,
8920: pay_run_result_values prrv,
8921: pay_balance_feeds_f pbf
8922: where prr.assignment_action_id = p_asg_act_id
8923: and prr.run_result_id = prrv.run_result_id
8924: and prrv.input_value_id = pbf.input_value_id);
8955: -- and pdb.balance_type_id in
8956: -- (select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
8957: -- distinct pbf.balance_type_id
8958: -- from pay_run_results prr,
8959: -- pay_run_result_values prrv,
8960: -- pay_balance_feeds_f pbf
8961: -- where prr.assignment_action_id = p_asg_act_id
8962: -- and prr.run_result_id = prrv.run_result_id
8963: -- and prrv.input_value_id = pbf.input_value_id
8991: pay_defined_balances pdb,
8992: pay_balance_attributes pba,
8993: pay_balance_dimensions pbd,
8994: pay_balance_validation pbv,
8995: pay_run_result_values prrv
8996: where
8997: prr.assignment_action_id = p_asg_act_id
8998: and piv.element_type_id = prr.element_type_id
8999: and p_eff_date between piv.effective_start_date
10068: from dual
10069: where exists (select /*+ ORDERED*/
10070: null
10071: from pay_balance_feeds_f bf
10072: , pay_run_result_values rrv
10073: where bf.balance_type_id = p_baltype_id
10074: and bf.input_value_id = rrv.input_value_id);
10075: --
10076: -- Bug 3364019 added the + 1 to get the correct balance_load_date