[Home] [Help]
688: , value.screen_entry_value planned
689: , value.screen_entry_value actual
690: FROM per_assignments_f assgn
691: , pay_element_links_f link
692: , pay_element_types_f types
693: , pay_element_entries_f entries
694: , pay_element_entry_values_f value
695: , pay_input_values_f inputs
696: WHERE assgn.assignment_id = p_assignment_id
992: ,c_element_name14 IN VARCHAR2
993: ,c_element_name15 IN VARCHAR2)
994: IS
995: SELECT MAX(entries.last_update_date)
996: FROM pay_element_types_f ele
997: , pay_element_entries_f entries
998: , per_assignments_f assgn
999: WHERE ele.element_name in (c_element_name1
1000: ,c_element_name2
1129:
1130:
1131:
1132: PROCEDURE get_element_entry_ids
1133: (p_element_type_id IN pay_element_types_f.element_type_id%TYPE
1134: ,p_effective_date IN DATE
1135: ,p_expected_entries IN NUMBER
1136: ,p_input_values OUT NOCOPY t_input_values_tab)
1137: IS
1136: ,p_input_values OUT NOCOPY t_input_values_tab)
1137: IS
1138:
1139: CURSOR csr_element_inputs
1140: (c_element_type_id IN pay_element_types_f.element_type_id%TYPE)
1141: IS
1142: SELECT inputs.name
1143: , inputs.input_value_id
1144: FROM pay_element_types_f types
1140: (c_element_type_id IN pay_element_types_f.element_type_id%TYPE)
1141: IS
1142: SELECT inputs.name
1143: , inputs.input_value_id
1144: FROM pay_element_types_f types
1145: , pay_input_values_f inputs
1146: WHERE types.element_type_id = c_element_type_id
1147: AND inputs.element_type_id = types.element_type_id
1148: AND inputs.legislation_code = g_legislation_code
1202:
1203:
1204: PROCEDURE get_entry_details
1205: (p_assignment_id IN per_assignments_f.assignment_id%TYPE
1206: ,p_element_name IN pay_element_types_f.element_name%TYPE
1207: ,p_effective_date IN DATE
1208: ,p_element_type_id OUT NOCOPY pay_element_types_f.element_type_id%TYPE
1209: ,p_element_link_id OUT NOCOPY pay_element_links_f.element_link_id%TYPE
1210: ,p_element_entry_id OUT NOCOPY pay_element_entries_f.element_entry_id%TYPE
1204: PROCEDURE get_entry_details
1205: (p_assignment_id IN per_assignments_f.assignment_id%TYPE
1206: ,p_element_name IN pay_element_types_f.element_name%TYPE
1207: ,p_effective_date IN DATE
1208: ,p_element_type_id OUT NOCOPY pay_element_types_f.element_type_id%TYPE
1209: ,p_element_link_id OUT NOCOPY pay_element_links_f.element_link_id%TYPE
1210: ,p_element_entry_id OUT NOCOPY pay_element_entries_f.element_entry_id%TYPE
1211: ,p_expected_entries IN NUMBER
1212: ,p_business_group_id OUT NOCOPY per_business_groups.business_group_id%TYPE
1454: --
1455: l_procedure VARCHAR(100);
1456: l_message VARCHAR2(250);
1457: l_input_values t_input_values_tab;
1458: l_element_type_id pay_element_types_f.element_type_id%TYPE;
1459: l_element_link_id pay_element_links_f.element_link_id%TYPE;
1460: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1461: l_business_group_id per_business_groups.business_group_id%TYPE;
1462: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
1473: l_boolean boolean;
1474:
1475: --
1476: cursor csr_element_entries(v_element_entry_id pay_element_entries_f.element_entry_id%TYPE
1477: ,v_element_type_id pay_element_types_f.element_type_id%TYPE
1478: , v_change_date DATE) is
1479: select effective_start_date,
1480: OBJECT_VERSION_NUMBER from pay_element_entries_f where
1481: assignment_id = p_assignment_id and
1486: order by effective_start_date;
1487:
1488:
1489: cursor csr_entries (v_element_entry_id pay_element_entries_f.element_entry_id%TYPE
1490: ,v_element_type_id pay_element_types_f.element_type_id%TYPE
1491: , v_date DATE) is
1492: select 1
1493: from pay_element_entries_f where
1494: assignment_id = p_assignment_id and
1496: element_entry_id = v_element_entry_id and
1497: effective_start_date = v_date;
1498:
1499: cursor csr_delete_existing_entries (v_element_entry_id pay_element_entries_f.element_entry_id%TYPE
1500: ,v_element_type_id pay_element_types_f.element_type_id%TYPE) is
1501: select effective_start_date,
1502: OBJECT_VERSION_NUMBER from pay_element_entries_f where
1503: assignment_id = p_assignment_id and
1504: element_type_id = v_element_type_id and
1875: l_message VARCHAR2(250);
1876: l_disability_proof VARCHAR2(2);
1877: l_permanent_disability_80u NUMBER;
1878: l_input_values t_input_values_tab;
1879: l_element_type_id pay_element_types_f.element_type_id%TYPE;
1880: l_element_link_id pay_element_links_f.element_link_id%TYPE;
1881: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1882: l_business_group_id per_business_groups.business_group_id%TYPE;
1883: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
2238: --
2239: l_procedure VARCHAR(100);
2240: l_message VARCHAR2(250);
2241: l_input_values t_input_values_tab;
2242: l_element_type_id pay_element_types_f.element_type_id%TYPE;
2243: l_element_link_id pay_element_links_f.element_link_id%TYPE;
2244: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2245: l_business_group_id per_business_groups.business_group_id%TYPE;
2246: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
2330: --
2331: l_procedure VARCHAR(100);
2332: l_message VARCHAR2(250);
2333: l_input_values t_input_values_tab;
2334: l_element_type_id pay_element_types_f.element_type_id%TYPE;
2335: l_element_link_id pay_element_links_f.element_link_id%TYPE;
2336: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2337: l_business_group_id per_business_groups.business_group_id%TYPE;
2338: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
2544: l_procedure VARCHAR(100);
2545: l_message VARCHAR2(250);
2546: l_warnings BOOLEAN;
2547: l_input_values t_input_values_tab;
2548: l_element_type_id pay_element_types_f.element_type_id%TYPE;
2549: l_element_link_id pay_element_links_f.element_link_id%TYPE;
2550: l_element_entry_id pay_element_entries.element_entry_id%TYPE;
2551: l_business_group_id per_business_groups.business_group_id%TYPE;
2552: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
2558: CURSOR csr_element_type_id(p_element_name VARCHAR2
2559: ,p_effective_date DATE)-- Added as a part of bug 4938573
2560: IS
2561: SELECT element_type_id
2562: FROM pay_element_types_f
2563: WHERE (legislation_code = 'IN' OR business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
2564: AND element_name = p_element_name
2565: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
2566:
2573: , links.element_link_id
2574: , assgn.business_group_id
2575: FROM per_assignments_f assgn
2576: , pay_element_links_f links
2577: , pay_element_types_f types
2578: WHERE assgn.assignment_id = c_assignment_id
2579: AND links.element_link_id = c_element_link_id
2580: AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
2581: AND links.business_group_id = assgn.business_group_id
2828: l_procedure VARCHAR(100);
2829: l_message VARCHAR2(250);
2830: l_warnings BOOLEAN;
2831: l_input_values t_input_values_tab;
2832: l_element_type_id pay_element_types_f.element_type_id%TYPE;
2833: l_element_link_id pay_element_links_f.element_link_id%TYPE;
2834: l_element_entry_id pay_element_entries.element_entry_id%TYPE;
2835: l_business_group_id per_business_groups.business_group_id%TYPE;
2836: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
2843: ,p_effective_date DATE -- Added as a part of bug 4938573
2844: )
2845: IS
2846: SELECT element_type_id
2847: FROM pay_element_types_f
2848: WHERE legislation_code = 'IN'
2849: AND element_name = p_element_name
2850: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
2851:
2858: , link.element_link_id
2859: , assgn.business_group_id
2860: FROM per_assignments_f assgn
2861: , pay_element_links_f link
2862: , pay_element_types_f types
2863: WHERE assgn.assignment_id = c_assignment_id
2864: AND link.element_link_id = c_element_link_id
2865: AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
2866: AND link.business_group_id = assgn.business_group_id
3111: l_procedure VARCHAR(100);
3112: l_message VARCHAR2(250);
3113: l_warnings BOOLEAN;
3114: l_input_values t_input_values_tab;
3115: l_element_type_id pay_element_types_f.element_type_id%TYPE;
3116: l_element_link_id pay_element_links_f.element_link_id%TYPE;
3117: l_element_entry_id pay_element_entries.element_entry_id%TYPE;
3118: l_business_group_id per_business_groups.business_group_id%TYPE;
3119: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
3130: , link.element_link_id
3131: , assgn.business_group_id
3132: FROM per_assignments_f assgn
3133: , pay_element_links_f link
3134: , pay_element_types_f types
3135: WHERE assgn.assignment_id = c_assignment_id
3136: AND link.element_link_id = c_element_link_id
3137: AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
3138: AND link.business_group_id = assgn.business_group_id
3165: ,p_effective_date DATE
3166: )
3167: IS
3168: SELECT element_type_id
3169: FROM pay_element_types_f
3170: WHERE legislation_code = 'IN'
3171: AND element_name = p_element_name
3172: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
3173: BEGIN
3407:
3408: l_procedure VARCHAR(100);
3409: l_message VARCHAR2(250);
3410: l_input_values t_input_values_tab;
3411: l_element_type_id pay_element_types_f.element_type_id%TYPE;
3412: l_element_link_id pay_element_links_f.element_link_id%TYPE;
3413: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3414: l_business_group_id per_business_groups.business_group_id%TYPE;
3415: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
3590: --
3591: l_procedure VARCHAR(100);
3592: l_message VARCHAR2(250);
3593: l_input_values t_input_values_tab;
3594: l_element_type_id pay_element_types_f.element_type_id%TYPE;
3595: l_element_link_id pay_element_links_f.element_link_id%TYPE;
3596: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3597: l_business_group_id per_business_groups.business_group_id%TYPE;
3598: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
3665: l_procedure VARCHAR(100);
3666: l_message VARCHAR2(250);
3667: l_warnings BOOLEAN;
3668: l_input_values t_input_values_tab;
3669: l_element_type_id pay_element_types_f.element_type_id%TYPE;
3670: l_element_link_id pay_element_links_f.element_link_id%TYPE;
3671: l_element_entry_id pay_element_entries.element_entry_id%TYPE;
3672: l_business_group_id per_business_groups.business_group_id%TYPE;
3673: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
3676: l_effective_end_date DATE;
3677: l_ele_entry_id pay_element_entries.element_entry_id%TYPE;
3678: l_ovn pay_element_entries_f.object_version_number%TYPE;
3679: l_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
3680: l_element_name pay_element_types_f.element_name%TYPE;
3681: l_token1 VARCHAR2(240);
3682: --
3683: CURSOR csr_element_link_details
3684: (c_assignment_id IN per_assignments_f.assignment_id%TYPE
3682: --
3683: CURSOR csr_element_link_details
3684: (c_assignment_id IN per_assignments_f.assignment_id%TYPE
3685: ,c_effective_date IN DATE
3686: ,c_element_name IN pay_element_types_f.element_name%TYPE)
3687: IS
3688: SELECT types.element_type_id
3689: , link.element_link_id
3690: , assgn.business_group_id
3689: , link.element_link_id
3690: , assgn.business_group_id
3691: FROM per_assignments_f assgn
3692: , pay_element_links_f link
3693: , pay_element_types_f types
3694: WHERE assgn.assignment_id = c_assignment_id
3695: AND link.element_link_id = pay_in_utils.get_element_link_id(c_assignment_id
3696: ,c_effective_date
3697: ,types.element_type_id
3722: AND entries.effective_end_date
3723: AND c_effective_date BETWEEN assgn.effective_start_date
3724: AND assgn.effective_end_date;
3725:
3726: CURSOR c_check_entry(c_element_name IN pay_element_types_f.element_name%TYPE
3727: ,c_effective_date IN DATE)
3728: IS
3729: SELECT entries.element_entry_id entry_id
3730: ,entries.object_version_number
3730: ,entries.object_version_number
3731: ,value2.screen_entry_value
3732: FROM per_assignments_f assgn
3733: , pay_element_links_f link
3734: , pay_element_types_f types
3735: , pay_element_entries_f entries
3736: , pay_element_entry_values_f value1
3737: , pay_input_values_f inputs1
3738: , pay_element_entry_values_f value2
4050:
4051: l_procedure VARCHAR(100);
4052: l_message VARCHAR2(250);
4053: l_input_values t_input_values_tab;
4054: l_element_type_id pay_element_types_f.element_type_id%TYPE;
4055: l_element_link_id pay_element_links_f.element_link_id%TYPE;
4056: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
4057: l_business_group_id per_business_groups.business_group_id%TYPE;
4058: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
4228:
4229: l_procedure VARCHAR(100);
4230: l_message VARCHAR2(250);
4231: l_input_values t_input_values_tab;
4232: l_element_type_id pay_element_types_f.element_type_id%TYPE;
4233: l_element_link_id pay_element_links_f.element_link_id%TYPE;
4234: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
4235: l_business_group_id per_business_groups.business_group_id%TYPE;
4236: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
4420:
4421: l_procedure VARCHAR(100);
4422: l_message VARCHAR2(250);
4423: l_input_values t_input_values_tab;
4424: l_element_type_id pay_element_types_f.element_type_id%TYPE;
4425: l_element_link_id pay_element_links_f.element_link_id%TYPE;
4426: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
4427: l_business_group_id per_business_groups.business_group_id%TYPE;
4428: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
4612: IS
4613: l_procedure VARCHAR(100);
4614: l_message VARCHAR2(250);
4615: l_input_values t_input_values_tab;
4616: l_element_type_id pay_element_types_f.element_type_id%TYPE;
4617: l_element_link_id pay_element_links_f.element_link_id%TYPE;
4618: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
4619: l_business_group_id per_business_groups.business_group_id%TYPE;
4620: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
4808:
4809: l_procedure VARCHAR(100);
4810: l_message VARCHAR2(250);
4811: l_input_values t_input_values_tab;
4812: l_element_type_id pay_element_types_f.element_type_id%TYPE;
4813: l_element_link_id pay_element_links_f.element_link_id%TYPE;
4814: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
4815: l_business_group_id per_business_groups.business_group_id%TYPE;
4816: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
5004:
5005: l_procedure VARCHAR(100);
5006: l_message VARCHAR2(250);
5007: l_input_values t_input_values_tab;
5008: l_element_type_id pay_element_types_f.element_type_id%TYPE;
5009: l_element_link_id pay_element_links_f.element_link_id%TYPE;
5010: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
5011: l_business_group_id per_business_groups.business_group_id%TYPE;
5012: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
5212:
5213: l_procedure VARCHAR(100);
5214: l_message VARCHAR2(250);
5215: l_input_values t_input_values_tab;
5216: l_element_type_id pay_element_types_f.element_type_id%TYPE;
5217: l_element_link_id pay_element_links_f.element_link_id%TYPE;
5218: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
5219: l_business_group_id per_business_groups.business_group_id%TYPE;
5220: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
5407:
5408: l_procedure VARCHAR(100);
5409: l_message VARCHAR2(250);
5410: l_input_values t_input_values_tab;
5411: l_element_type_id pay_element_types_f.element_type_id%TYPE;
5412: l_element_link_id pay_element_links_f.element_link_id%TYPE;
5413: l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
5414: l_business_group_id per_business_groups.business_group_id%TYPE;
5415: l_object_version_number pay_element_entries_f.object_version_number%TYPE;
6344:
6345: CURSOR csr_element_type_id(p_element_name VARCHAR2)
6346: IS
6347: SELECT element_type_id
6348: FROM pay_element_types_f
6349: WHERE legislation_code = 'IN'
6350: AND element_name = p_element_name
6351: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
6352:
6351: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
6352:
6353: CURSOR csr_number_of_entries
6354: (c_assignment_id IN per_assignments_f.assignment_id%TYPE
6355: ,c_element_name IN pay_element_types_f.element_name%TYPE
6356: ,c_effective_date IN DATE
6357: ,c_element_link_id IN NUMBER)
6358: IS
6359: SELECT count(entries.element_entry_id)
6358: IS
6359: SELECT count(entries.element_entry_id)
6360: FROM per_assignments_f assgn
6361: , pay_element_links_f link
6362: , pay_element_types_f types
6363: , pay_element_entries_f entries
6364: WHERE assgn.assignment_id = c_assignment_id
6365: AND link.element_link_id = c_element_link_id
6366: AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
7122: , value2.screen_entry_value Treatment_Amount
7123: , value3.screen_entry_value Disability_Percentage
7124: FROM per_assignments_f assgn
7125: , pay_element_links_f link
7126: , pay_element_types_f types
7127: , pay_element_entries_f entries
7128: , pay_element_entry_values_f value1
7129: , pay_input_values_f inputs1
7130: , pay_element_entry_values_f value2
7179: , value1.screen_entry_value Donation_Type
7180: , value2.screen_entry_value Donation_Amount
7181: FROM per_assignments_f assgn
7182: , pay_element_links_f link
7183: , pay_element_types_f types
7184: , pay_element_entries_f entries
7185: , pay_element_entry_values_f value1
7186: , pay_input_values_f inputs1
7187: , pay_element_entry_values_f value2
7228: , value3.screen_entry_value Policy_Number
7229: , value4.screen_entry_value Policy_Start_Date
7230: FROM per_assignments_f assgn
7231: , pay_element_links_f link
7232: , pay_element_types_f types
7233: , pay_element_entries_f entries
7234: , pay_element_entry_values_f value1
7235: , pay_input_values_f inputs1
7236: , pay_element_entry_values_f value2
7297: , value1.screen_entry_value Investment_Amount
7298: , value2.screen_entry_value Component_Name
7299: FROM per_assignments_f assgn
7300: , pay_element_links_f link
7301: , pay_element_types_f types
7302: , pay_element_entries_f entries
7303: , pay_element_entry_values_f value1
7304: , pay_input_values_f inputs1
7305: , pay_element_entry_values_f value2
7341: --Added as a part of bug fix 4774108
7342: CURSOR csr_element_type_id(p_element_name VARCHAR2)
7343: IS
7344: SELECT element_type_id
7345: FROM pay_element_types_f
7346: WHERE legislation_code = 'IN'
7347: AND element_name = p_element_name
7348: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
7349:
7348: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
7349:
7350: l_element_type_id NUMBER; --Added as a part of bug fix 4774108
7351: l_element_link_id NUMBER; --Added as a part of bug fix 4774108
7352: l_element_name pay_element_types_f.element_name%TYPE;--Added as a part of bug fix 4774108
7353: l_procedure VARCHAR(100);
7354: l_message VARCHAR2(250);
7355: BEGIN
7356: --