[Home] [Help]
121: balance_found boolean,
122: balance_value 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,
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,
134: source_number2 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,
134: source_number2 number,
135: organization_id 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,
134: source_number2 number,
135: organization_id number
136: );
137: --
144: run_balance_id pay_run_balances.run_balance_id%type,
145: defined_balance_id pay_defined_balances.defined_balance_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,
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,
157: organization_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,
157: organization_id number,
158: next number
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,
157: organization_id number,
158: next number
159: );
182: --
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,
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,
195: organization_id number,
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,
195: organization_id number,
196: prv_tax_unit_id pay_assignment_actions.tax_unit_id%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,
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,
193: local_unit_id pay_run_results.local_unit_id%type,
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,
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,
206: prv_organization_id number,
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,
206: prv_organization_id number,
207: tu_needed boolean,
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,
206: prv_organization_id number,
207: tu_needed boolean,
208: jc_needed boolean,
232: --
233: type t_run_result_rec is record
234: (run_result_id number
235: ,element_type_id number
236: ,jurisdiction_code pay_run_results.jurisdiction_code%type
237: ,assignment_action_id number
238: ,assignment_id number
239: ,tax_unit_id number
240: ,payroll_action_id number
237: ,assignment_action_id number
238: ,assignment_id number
239: ,tax_unit_id number
240: ,payroll_action_id number
241: ,time_def_id pay_run_results.time_definition_id%type
242: ,balance_date pay_run_results.end_date%type
243: ,local_unit_id pay_run_results.local_unit_id%type
244: ,source_number2 number
245: ,organization_id number
238: ,assignment_id number
239: ,tax_unit_id number
240: ,payroll_action_id number
241: ,time_def_id pay_run_results.time_definition_id%type
242: ,balance_date pay_run_results.end_date%type
243: ,local_unit_id pay_run_results.local_unit_id%type
244: ,source_number2 number
245: ,organization_id number
246: ,business_group_id number
239: ,tax_unit_id number
240: ,payroll_action_id number
241: ,time_def_id pay_run_results.time_definition_id%type
242: ,balance_date pay_run_results.end_date%type
243: ,local_unit_id pay_run_results.local_unit_id%type
244: ,source_number2 number
245: ,organization_id number
246: ,business_group_id number
247: ,legislation_code per_business_groups.legislation_code%type
4497: prev_def_bal_id number;
4498: l_add_balance boolean;
4499: start_def_bal_ptr number;
4500: pos_indx number_tab;
4501: bal_ju_code pay_run_results.jurisdiction_code%type;
4502: con_ju_code pay_run_results.jurisdiction_code%type;
4503: begin
4504: --
4505: -- Now get the balances
4498: l_add_balance boolean;
4499: start_def_bal_ptr number;
4500: pos_indx number_tab;
4501: bal_ju_code pay_run_results.jurisdiction_code%type;
4502: con_ju_code pay_run_results.jurisdiction_code%type;
4503: begin
4504: --
4505: -- Now get the balances
4506: --
4634: l_add_balance boolean;
4635: start_def_bal_ptr number;
4636: l_retrieval_cnt number;
4637: pos_indx number_tab;
4638: bal_ju_code pay_run_results.jurisdiction_code%type;
4639: con_ju_code pay_run_results.jurisdiction_code%type;
4640: --
4641: begin
4642: g_debug := hr_utility.debug_enabled;
4635: start_def_bal_ptr number;
4636: l_retrieval_cnt number;
4637: pos_indx number_tab;
4638: bal_ju_code pay_run_results.jurisdiction_code%type;
4639: con_ju_code pay_run_results.jurisdiction_code%type;
4640: --
4641: begin
4642: g_debug := hr_utility.debug_enabled;
4643: --
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
5953: where rrv.input_value_id = p_input_value_id
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')
5956: and nvl(rrv.result_value, '0') <> '0');
6060: ,prr.end_date
6061: ,prr.local_unit_id
6062: from
6063: pay_assignment_actions paa
6064: ,pay_run_results prr
6065: where
6066: paa.assignment_action_id = prr.assignment_action_id
6067: and prr.run_result_id = p_run_result_id
6068: ;
6565: p_sn_needed in varchar2,
6566: p_st2_needed in varchar2,
6567: p_sn2_needed in varchar2,
6568: p_org_needed in varchar2) is
6569: select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
6570: distinct
6571: paa.tax_unit_id tax_unit_id
6572: , prr.jurisdiction_code jurisdiction_code
6573: , decode(p_si_needed,
6594: , ppa.effective_date
6595: from pay_assignment_actions paa,
6596: pay_payroll_actions ppa,
6597: per_time_periods ptp,
6598: pay_run_results prr
6599: where paa.assignment_action_id = p_asg_action
6600: and ppa.payroll_action_id = paa.payroll_action_id
6601: and paa.assignment_action_id = prr.assignment_action_id
6602: and ptp.payroll_id = ppa.payroll_id
7478: p_sn_needed in varchar2,
7479: p_st2_needed in varchar2,
7480: p_sn2_needed in varchar2,
7481: p_org_needed in varchar2) is
7482: select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
7483: distinct
7484: paa.tax_unit_id tax_unit_id
7485: , prr.jurisdiction_code jurisdiction_code
7486: , decode(p_si_needed,
7506: , nvl(prr.end_date, ptp.end_date) balance_date
7507: from pay_assignment_actions paa,
7508: pay_payroll_actions ppa,
7509: per_time_periods ptp,
7510: pay_run_results prr
7511: where paa.assignment_action_id = asgact
7512: and paa.assignment_action_id = prr.assignment_action_id
7513: and ppa.payroll_action_id = paa.payroll_action_id
7514: and ppa.payroll_id = ptp.payroll_id
7763: p_bd_needed in varchar2,
7764: p_lu_needed in varchar2,
7765: p_sn2_needed in varchar2,
7766: p_org_needed in varchar2) is
7767: select /*+ ORDERED USE_NL (prr) INDEX(prr pay_run_results_n50)*/
7768: distinct
7769: decode(p_tu_needed,
7770: 'Y', paa.tax_unit_id,
7771: null) tax_unit_id
7801: null) organization_id
7802: from pay_assignment_actions paa,
7803: pay_payroll_actions ppa,
7804: per_time_periods ptp,
7805: pay_run_results prr
7806: where paa.payroll_action_id = p_pact_id
7807: and paa.assignment_action_id = prr.assignment_action_id
7808: and ppa.payroll_action_id = paa.payroll_action_id
7809: and ppa.payroll_id = ptp.payroll_id
8017: pbt.balance_type_id,
8018: pbd.balance_dimension_id
8019: from
8020: pay_assignment_actions paa,
8021: pay_run_results prr,
8022: pay_input_values_f piv,
8023: pay_balance_feeds_f pbf,
8024: pay_balance_types pbt,
8025: pay_defined_balances pdb,
8063: and prrv.input_value_id = piv.input_value_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
8095: and pbd.dimension_level = 'GRP'
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
8124: and pdb.balance_type_id in
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
8165: and pdb.balance_type_id in
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
8194: p_sn_needed in varchar2,
8195: p_st2_needed in varchar2,
8196: p_sn2_needed in varchar2,
8197: p_org_needed in varchar2) is
8198: select /*+ ORDERED USE_NL (prr) INDEX(prr pay_run_results_n50)*/
8199: distinct
8200: paa.tax_unit_id tax_unit_id
8201: , prr.jurisdiction_code jurisdiction_code
8202: , decode(p_si_needed,
8222: , prr.local_unit_id local_unit_id
8223: from pay_assignment_actions paa,
8224: pay_payroll_actions ppa,
8225: per_time_periods ptp,
8226: pay_run_results prr
8227: where paa.payroll_action_id = p_pact_id
8228: and paa.assignment_action_id = prr.assignment_action_id
8229: and ppa.payroll_action_id = paa.payroll_action_id
8230: and ppa.payroll_id = ptp.payroll_id
8572: p_bd_needed in varchar2,
8573: p_lu_needed in varchar2,
8574: p_sn2_needed in varchar2,
8575: p_org_needed in varchar2) is
8576: select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
8577: distinct
8578: decode(p_tu_needed,
8579: 'Y', paa.tax_unit_id,
8580: null) tax_unit_id
8610: null) local_unit_id
8611: from pay_assignment_actions paa,
8612: pay_payroll_actions ppa,
8613: per_time_periods ptp,
8614: pay_run_results prr
8615: where paa.assignment_action_id = asgact
8616: and paa.assignment_action_id = prr.assignment_action_id
8617: and ppa.payroll_action_id = paa.payroll_action_id
8618: and ppa.payroll_id = ptp.payroll_id
8820: pbt.jurisdiction_level,
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,
8856: and prr.run_result_id = prrv.run_result_id
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
8887: and pdb.balance_dimension_id = pbd.balance_dimension_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
8915: and p_def_bal_id is null
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
8954: -- and pbv.balance_load_date > p_eff_date)
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
8984: pdb.defined_balance_id,
8985: pdb.balance_type_id,
8986: pdb.balance_dimension_id
8987: from
8988: pay_run_results prr,
8989: pay_input_values_f piv,
8990: pay_balance_feeds_f pbf,
8991: pay_defined_balances pdb,
8992: pay_balance_attributes pba,
9033: p_sn_needed in varchar2,
9034: p_st2_needed in varchar2,
9035: p_sn2_needed in varchar2,
9036: p_org_needed in varchar2) is
9037: select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
9038: distinct
9039: paa.tax_unit_id tax_unit_id
9040: , prr.jurisdiction_code jurisdiction_code
9041: , decode(p_si_needed,
9061: , prr.local_unit_id
9062: from pay_assignment_actions paa,
9063: pay_payroll_actions ppa,
9064: per_time_periods ptp,
9065: pay_run_results prr
9066: where paa.assignment_action_id = asgact
9067: and paa.assignment_action_id = prr.assignment_action_id
9068: and ppa.payroll_action_id = paa.payroll_action_id
9069: and ppa.payroll_id = ptp.payroll_id
9607: , prr.time_definition_id
9608: , nvl(prr.end_date, ptp.end_date) balance_date
9609: , prr.local_unit_id
9610: from pay_assignment_actions paa,
9611: pay_run_results prr,
9612: pay_payroll_actions ppa,
9613: per_time_periods ptp
9614: where ppa.payroll_action_id = p_pact_id
9615: and paa.payroll_action_id = ppa.payroll_action_id
10313: 'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
10314: null) organization_id,
10315: ppa.effective_date
10316: from pay_assignment_actions paa,
10317: pay_run_results prr,
10318: pay_payroll_actions ppa
10319: where paa.assignment_action_id = revassactid
10320: and ppa.payroll_action_id = paa.payroll_action_id
10321: and paa.assignment_action_id = prr.assignment_action_id;