[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
222: --
223: cursor csr_manual_feeds_exist is
224: select bf.balance_feed_id
225: from pay_balance_feeds_f bf,
226: pay_input_values_f inv,
227: pay_element_types_f elt,
228: pay_element_classifications ec
229: where bf.balance_type_id = p_balance_type_id
230: and bf.input_value_id = inv.input_value_id
303: p_input_value_id number,
304: p_pay_value_name varchar2
305: ) is
306: select iv.input_value_id
307: from pay_input_values_f iv
308: where iv.input_value_id = p_input_value_id
309: and iv.name = p_pay_value_name;
310: --
311: v_input_value_id number;
427: if v_next_bal_feed_start_date is null then
428: begin
429: select max(iv.effective_end_date)
430: into v_max_inp_val_end_date
431: from pay_input_values_f iv
432: where iv.input_value_id = p_input_value_id;
433: exception
434: when no_data_found then
435: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
589: null) legislation_code
590: ,bt.balance_name
591: ,ec.classification_name
592: ,et.element_name
593: from pay_input_values_f iv,
594: pay_element_types_f et,
595: pay_element_classifications ec,
596: pay_balance_classifications bc,
597: pay_balance_types bt,
661: ,ec.classification_name
662: ,et.element_name
663: from pay_sub_classification_rules_f scr,
664: pay_element_types_f et,
665: pay_input_values_f iv,
666: pay_element_classifications ec,
667: pay_balance_classifications bc,
668: pay_balance_types bt,
669: per_business_groups_perf ivbg,
793: and ((ec.parent_classification_id is null and
794: exists
795: (select null
796: from pay_element_types_f et,
797: pay_input_values_f iv
798: where iv.input_value_id = bf.input_value_id
799: and et.element_type_id = iv.element_type_id
800: and et.classification_id = bc.classification_id))
801: or (ec.parent_classification_id is not null and
801: or (ec.parent_classification_id is not null and
802: exists
803: (select null
804: from pay_sub_classification_rules_f scr,
805: pay_input_values_f iv
806: where iv.input_value_id = bf.input_value_id
807: and scr.element_type_id = iv.element_type_id
808: and scr.classification_id = bc.classification_id)))
809: for update;
871: null) legislation_code
872: from pay_balance_types bt,
873: pay_balance_classifications bc,
874: pay_element_types_f et,
875: pay_input_values_f iv,
876: per_business_groups_perf ivbg,
877: per_business_groups_perf btbg
878: where iv.input_value_id = p_input_value_id
879: and et.element_type_id = iv.element_type_id
930: null, nvl(iv.legislation_code,
931: nvl(scr.legislation_code,
932: bt.legislation_code)),
933: null) legislation_code
934: from pay_input_values_f iv,
935: pay_balance_classifications bc,
936: pay_balance_types bt,
937: pay_element_types_f et,
938: pay_sub_classification_rules_f scr,
1054: bt.legislation_code)),
1055: null) legislation_code
1056: from pay_sub_classification_rules_f scr,
1057: pay_element_types_f et,
1058: pay_input_values_f iv,
1059: pay_balance_classifications bc,
1060: pay_balance_types bt,
1061: per_business_groups_perf ivbg,
1062: per_business_groups_perf btbg
1330: p_pay_value_name varchar2
1331: ) is
1332: select bf.rowid row_id
1333: from pay_sub_classification_rules_f scr,
1334: pay_input_values_f iv,
1335: pay_balance_feeds_f bf,
1336: pay_balance_classifications bc,
1337: pay_balance_types bt
1338: where scr.sub_classification_rule_id = p_sub_classification_rule_id
1450: ) is
1451: select iv.input_value_id,
1452: min(iv.effective_start_date) effective_start_date,
1453: max(iv.effective_end_date) effective_end_date
1454: from pay_input_values_f iv,
1455: pay_element_types_f et,
1456: pay_element_classifications ec,
1457: pay_balance_types bt,
1458: per_business_groups_perf ivbg,
1496: scr.effective_start_date,
1497: scr.effective_end_date
1498: from pay_sub_classification_rules_f scr,
1499: pay_element_types_f et,
1500: pay_input_values_f iv,
1501: pay_element_classifications ec,
1502: pay_balance_types bt,
1503: per_business_groups_perf ivbg,
1504: per_business_groups_perf btbg
1553: and ((ec.parent_classification_id is null and
1554: exists
1555: (select null
1556: from pay_element_types_f et,
1557: pay_input_values_f iv
1558: where iv.input_value_id = bf.input_value_id
1559: and et.element_type_id = iv.element_type_id
1560: and et.classification_id = bc.classification_id))
1561: or (ec.parent_classification_id is not null and
1561: or (ec.parent_classification_id is not null and
1562: exists
1563: (select null
1564: from pay_sub_classification_rules_f scr,
1565: pay_input_values_f iv
1566: where iv.input_value_id = bf.input_value_id
1567: and scr.element_type_id = iv.element_type_id
1568: and scr.classification_id = bc.classification_id)))
1569: and exists
1609: scr.effective_start_date,
1610: scr.effective_end_date
1611: from pay_sub_classification_rules_f scr,
1612: pay_element_types_f et,
1613: pay_input_values_f iv,
1614: pay_balance_classifications bc,
1615: pay_balance_types bt,
1616: per_business_groups_perf ivbg,
1617: per_business_groups_perf btbg
1657: p_validation_end_date date
1658: ) is
1659: select bf.balance_feed_id
1660: from pay_sub_classification_rules_f scr,
1661: pay_input_values_f iv,
1662: pay_balance_feeds_f bf,
1663: pay_balance_classifications bc
1664: where scr.sub_classification_rule_id = p_sub_classification_rule_id
1665: and iv.element_type_id = scr.element_type_id