[Home] [Help]
67: 23-Nov-94 rfine 40.8 Suppressed index on business_group_id
68: 16-Jul-95 D.Kerr 40.9 Modified the manual_bal_feeds_exist
69: function to support initial balance feeds.
70: 01-Mar-99 J. Moyano 115.1 MLS changes. Added references to _TL tables.
71: 10-Feb-00 A.Logue 115.3 Utf8 support : pay_input_values_f.name
72: extended to 80 characters.
73: 14-Feb-01 M.Reid 115.4 Rewrote csr_proc_run_result due to CBO
74: choosing non-optimal plan
75: 23-Mar-01 J.Tomkins 115.5 Bug. 1366796. Added Error 72033 regarding
214: --
215: cursor csr_manual_feeds_exist is
216: select bf.balance_feed_id
217: from pay_balance_feeds_f bf,
218: pay_input_values_f inv,
219: pay_element_types_f elt,
220: pay_element_classifications ec
221: where bf.balance_type_id = p_balance_type_id
222: and bf.input_value_id = inv.input_value_id
295: p_input_value_id number,
296: p_pay_value_name varchar2
297: ) is
298: select iv.input_value_id
299: from pay_input_values_f iv
300: where iv.input_value_id = p_input_value_id
301: and iv.name = p_pay_value_name;
302: --
303: v_input_value_id number;
419: if v_next_bal_feed_start_date is null then
420: begin
421: select max(iv.effective_end_date)
422: into v_max_inp_val_end_date
423: from pay_input_values_f iv
424: where iv.input_value_id = p_input_value_id;
425: exception
426: when no_data_found then
427: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
579: null) legislation_code
580: ,bt.balance_name
581: ,ec.classification_name
582: ,et.element_name
583: from pay_input_values_f iv,
584: pay_element_types_f et,
585: pay_element_classifications ec,
586: pay_balance_classifications bc,
587: pay_balance_types bt,
651: ,ec.classification_name
652: ,et.element_name
653: from pay_sub_classification_rules_f scr,
654: pay_element_types_f et,
655: pay_input_values_f iv,
656: pay_element_classifications ec,
657: pay_balance_classifications bc,
658: pay_balance_types bt,
659: per_business_groups_perf ivbg,
783: and ((ec.parent_classification_id is null and
784: exists
785: (select null
786: from pay_element_types_f et,
787: pay_input_values_f iv
788: where iv.input_value_id = bf.input_value_id
789: and et.element_type_id = iv.element_type_id
790: and et.classification_id = bc.classification_id))
791: or (ec.parent_classification_id is not null and
791: or (ec.parent_classification_id is not null and
792: exists
793: (select null
794: from pay_sub_classification_rules_f scr,
795: pay_input_values_f iv
796: where iv.input_value_id = bf.input_value_id
797: and scr.element_type_id = iv.element_type_id
798: and scr.classification_id = bc.classification_id)))
799: for update;
861: null) legislation_code
862: from pay_balance_types bt,
863: pay_balance_classifications bc,
864: pay_element_types_f et,
865: pay_input_values_f iv,
866: per_business_groups_perf ivbg,
867: per_business_groups_perf btbg
868: where iv.input_value_id = p_input_value_id
869: and et.element_type_id = iv.element_type_id
920: null, nvl(iv.legislation_code,
921: nvl(scr.legislation_code,
922: bt.legislation_code)),
923: null) legislation_code
924: from pay_input_values_f iv,
925: pay_balance_classifications bc,
926: pay_balance_types bt,
927: pay_element_types_f et,
928: pay_sub_classification_rules_f scr,
1044: bt.legislation_code)),
1045: null) legislation_code
1046: from pay_sub_classification_rules_f scr,
1047: pay_element_types_f et,
1048: pay_input_values_f iv,
1049: pay_balance_classifications bc,
1050: pay_balance_types bt,
1051: per_business_groups_perf ivbg,
1052: per_business_groups_perf btbg
1320: p_pay_value_name varchar2
1321: ) is
1322: select bf.rowid row_id
1323: from pay_sub_classification_rules_f scr,
1324: pay_input_values_f iv,
1325: pay_balance_feeds_f bf,
1326: pay_balance_classifications bc,
1327: pay_balance_types bt
1328: where scr.sub_classification_rule_id = p_sub_classification_rule_id
1440: ) is
1441: select iv.input_value_id,
1442: min(iv.effective_start_date) effective_start_date,
1443: max(iv.effective_end_date) effective_end_date
1444: from pay_input_values_f iv,
1445: pay_element_types_f et,
1446: pay_element_classifications ec,
1447: pay_balance_types bt,
1448: per_business_groups_perf ivbg,
1486: scr.effective_start_date,
1487: scr.effective_end_date
1488: from pay_sub_classification_rules_f scr,
1489: pay_element_types_f et,
1490: pay_input_values_f iv,
1491: pay_element_classifications ec,
1492: pay_balance_types bt,
1493: per_business_groups_perf ivbg,
1494: per_business_groups_perf btbg
1543: and ((ec.parent_classification_id is null and
1544: exists
1545: (select null
1546: from pay_element_types_f et,
1547: pay_input_values_f iv
1548: where iv.input_value_id = bf.input_value_id
1549: and et.element_type_id = iv.element_type_id
1550: and et.classification_id = bc.classification_id))
1551: or (ec.parent_classification_id is not null and
1551: or (ec.parent_classification_id is not null and
1552: exists
1553: (select null
1554: from pay_sub_classification_rules_f scr,
1555: pay_input_values_f iv
1556: where iv.input_value_id = bf.input_value_id
1557: and scr.element_type_id = iv.element_type_id
1558: and scr.classification_id = bc.classification_id)))
1559: and exists
1599: scr.effective_start_date,
1600: scr.effective_end_date
1601: from pay_sub_classification_rules_f scr,
1602: pay_element_types_f et,
1603: pay_input_values_f iv,
1604: pay_balance_classifications bc,
1605: pay_balance_types bt,
1606: per_business_groups_perf ivbg,
1607: per_business_groups_perf btbg
1647: p_validation_end_date date
1648: ) is
1649: select bf.balance_feed_id
1650: from pay_sub_classification_rules_f scr,
1651: pay_input_values_f iv,
1652: pay_balance_feeds_f bf,
1653: pay_balance_classifications bc
1654: where scr.sub_classification_rule_id = p_sub_classification_rule_id
1655: and iv.element_type_id = scr.element_type_id