[Home] [Help]
298: l_person_id NUMBER;
299: BEGIN
300: SELECT person_id
301: INTO l_person_id
302: FROM per_all_assignments_f
303: WHERE assignment_id = p_assignment_id
304: AND ROWNUM < 2;
305: RETURN l_person_id;
306: EXCEPTION
2746: -- Cursor to get assignment details
2747: CURSOR csr_get_org_id(c_basic_date DATE)
2748: IS
2749: SELECT organization_id
2750: FROM per_all_assignments_f
2751: WHERE assignment_id = p_assignment_id
2752: AND c_basic_date BETWEEN effective_start_date
2753: AND effective_end_date;
2754:
2779:
2780: CURSOR csr_get_max_asg_end_date
2781: IS
2782: SELECT max(effective_end_date)
2783: FROM per_all_assignments_f
2784: WHERE assignment_id = p_assignment_id;
2785:
2786: l_max_asg_end_date DATE;
2787: l_bas_ext_dfn_id NUMBER;
2893: l_return VARCHAR2(20);
2894: CURSOR csr_get_assignment_number
2895: IS
2896: SELECT assignment_number
2897: FROM per_all_assignments_f
2898: WHERE assignment_id = g_assignment_id
2899: AND g_effective_date BETWEEN effective_start_date
2900: AND effective_end_date;
2901:
4337:
4338: IF g_asg_membership_col IS NOT NULL THEN
4339: debug('g_asg_membership_col: '||g_asg_membership_col,40);
4340: /*l_query := 'select '||g_asg_membership_col||'
4341: from per_all_assignments_f '||
4342: 'where business_group_id = '||p_business_group_id||' '||
4343: 'and assignment_id = '||p_assignment_id||' '||
4344: 'and ASS_ATTRIBUTE_CATEGORY = '||''''||g_asg_membership_context||''''||
4345: 'and to_date('||''''||TO_CHAR(p_effective_date,'dd/mm/yyyy')||''''||
4346: ',''dd/mm/yyyy'')'||' between effective_start_date '||
4347: 'and effective_end_date';*/
4348: /* commented to use bind variable instead of parameter */
4349: /* l_query := 'select '||g_asg_membership_col||' '||
4350: 'from per_all_assignments_f '||' '||
4351: 'where business_group_id = '||p_business_group_id||' '||
4352: 'and assignment_id = '||p_assignment_id||' ';
4353: IF g_asg_membership_context <> 'Global Data Elements' THEN
4354: l_query := l_query||
4362: debug('l_query: '||l_query,30);
4363: -- fetch the value of from the column name
4364: OPEN c_base_table FOR l_query; */
4365:
4366: l_query := 'select '||g_asg_membership_col||' '||'from per_all_assignments_f '||' '||
4367: 'where business_group_id = :p_business_group_id '||
4368: 'and assignment_id = :p_assignment_id '||
4369: 'and :p_effective_date between effective_start_date and effective_end_date ';
4370:
4424: (p_business_group_id IN NUMBER
4425: ,p_effective_date IN DATE
4426: ,p_assignment_id IN NUMBER
4427: ,p_person_dtl OUT NOCOPY per_all_people_f%rowtype
4428: ,p_assignment_dtl OUT NOCOPY per_all_assignments_f%rowtype
4429: ) RETURN VARCHAR2 -- Y or N
4430: IS
4431:
4432: --
4439: l_last_hire_date_indicator VARCHAR2(1) := NULL;
4440: l_person_id NUMBER;
4441:
4442: l_person_dtl per_all_people_f%rowtype;
4443: l_assignment_dtl per_all_assignments_f%rowtype;
4444: i NUMBER;
4445: l_index NUMBER;
4446: l_value NUMBER;
4447: l_pension_element_type_id NUMBER;
5408: ,asg.effective_end_date effective_end_date
5409: ,asg.creation_date creation_date
5410: ,asg.assignment_status_type_id status_type_id
5411: ,' ' status_type
5412: FROM per_all_assignments_f asg
5413:
5414: WHERE asg.assignment_id = p_assignment_id
5415: AND (( p_effective_date BETWEEN asg.effective_start_date
5416: AND asg.effective_end_date )
5534: l_per_assg_status per_assignment_status_types.per_system_status%TYPE;
5535: l_curr_person_dtls per_all_people_f%ROWTYPE;
5536: -- this contains the person details on effective date
5537:
5538: l_curr_assg_dtls per_all_assignments_f%ROWTYPE;
5539: -- this contains the person details on effective date
5540: CURSOR csr_assignment_status
5541: (
5542: p_assignment_status_type_id NUMBER
6731: ELSIF is_today_sal_end() = 'Y' THEN -- salary end => report all
6732: -- g_is_terminated := 'Y'; -- change termination status to 'Y'
6733: l_is_terminated := 'Y'; -- change termination status to 'Y'
6734: l_report_all := 'Y';
6735: ELSIF ( l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
6736: AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID') THEN
6737:
6738: -- if the event is on assignment_status_type_id
6739: -- reject the event
6996: EXIT;
6997: END IF;
6998:
6999: -- check for changes on assignment status
7000: IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7001: AND (l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
7002: OR l_update_type = 'E') THEN
7003:
7004: IF PQP_GB_PSI_FUNCTIONS.chk_is_employee_a_leaver
7035: RETURN 'Y';
7036:
7037: END IF; --IF l_assignment_status <> 'PROCESS'
7038:
7039: END IF;--IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7040:
7041: -- looping condition
7042: IF l_index = g_pay_proc_evt_tab.LAST THEN
7043: EXIT;
7069: l_chg_column_name := ben_ext_person.g_chg_pay_column;
7070: l_chg_table := ben_ext_person.g_chg_pay_table;
7071:
7072: -- check for changes on assignment status
7073: IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7074: AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID') THEN
7075:
7076: debug('Returning Y for the event on assignment_status_type_id');
7077: debug_exit(l_proc);
7076: debug('Returning Y for the event on assignment_status_type_id');
7077: debug_exit(l_proc);
7078: RETURN 'Y';
7079:
7080: END IF; --IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7081:
7082: debug('Returning N');
7083: debug_exit(l_proc);
7084: return 'N';
7118: EXIT;
7119: END IF; --IF g_effective_date
7120:
7121: -- check for changes on assignment status
7122: IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F' THEN
7123:
7124: IF l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID' THEN
7125:
7126: l_assignment_status_type_id := g_pay_proc_evt_tab(l_index).new_value;
7150: debug_exit(l_proc);
7151: RETURN 'Y';
7152: END IF;
7153:
7154: END IF; --IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7155:
7156:
7157: -- looping condition
7158: IF l_index = g_pay_proc_evt_tab.LAST THEN
7254: END IF;
7255: --Bug 7611963: End
7256: END IF;
7257:
7258: IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7259: AND (l_chg_column_name IN ('GRADE_ID','NORMAL_HOURS') OR l_chg_type = 'I'))
7260: OR (l_chg_table = 'PQP_ASSIGNMENT_ATTRIBUTES_F'
7261: AND (l_chg_column_name = 'CONTRACT_TYPE' OR l_chg_type = 'I'))
7262: OR (l_chg_table = 'PAY_ELEMENT_ENTRIES_F'
7264: OR (l_chg_table = 'PAY_ELEMENT_ENTRY_VALUES_F') THEN
7265:
7266: g_sal_chg_event_exists := 'Y';
7267:
7268: END IF; --IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7269:
7270: IF (l_chg_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
7271: AND (l_chg_column_name = 'VALUE' OR l_chg_type = 'I' )) THEN
7272:
7400: debug('l_chg_column_name:'||l_chg_column_name);
7401: debug('l_update_type:'||l_update_type);
7402: debug('l_chg_date_rev_term:'||l_chg_date_rev_term);
7403:
7404: IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7405: AND l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
7406: AND l_update_type = 'C'
7407: THEN
7408: debug('Found Reverse Term event');
7550:
7551: debug('l_chg_table: '||l_chg_table||' l_chg_date: '||l_chg_date||
7552: ' l_update_type: '||l_update_type||' l_chg_column_name: '||l_chg_column_name);
7553:
7554: IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7555: AND (l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
7556: OR l_update_type = 'E') THEN
7557:
7558: IF PQP_GB_PSI_FUNCTIONS.chk_is_employee_a_leaver
7608: l_sal_ele_end_evt_exist := 'N';
7609: END IF;
7610: --Bug 7611963: End
7611:
7612: ELSIF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7613: AND (l_chg_column_name IN ('GRADE_ID','NORMAL_HOURS') OR l_update_type = 'I'))
7614: OR (l_chg_table = 'PQP_ASSIGNMENT_ATTRIBUTES_F'
7615: AND (l_chg_column_name = 'CONTRACT_TYPE' OR l_update_type = 'I'))
7616: OR (l_chg_table = 'PAY_ELEMENT_ENTRY_VALUES_F') THEN
7661: l_chg_table_id := g_pay_proc_evt_tab(l_index).dated_table_id;
7662: l_update_type := g_pay_proc_evt_tab(l_index).update_type;
7663: l_chg_column_name := g_pay_proc_evt_tab(l_index).column_name;
7664:
7665: IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7666: AND (l_chg_column_name = 'ASSIGNMENT_STATUS_TYPE_ID'
7667: OR l_update_type = 'E') THEN
7668:
7669: IF PQP_GB_PSI_FUNCTIONS.chk_is_employee_a_leaver
7698: RETURN 'Y';
7699:
7700: END IF; --IF l_assignment_status <> 'PROCESS'
7701:
7702: END IF; -- IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7703:
7704: END IF; -- IF l_chg_date = (l_next_event_date + 1)
7705:
7706: END IF; --IF l_sal_ele_end_evt_exist = 'Y'
7801: --For bug 7013325: Added new column l_configured_dff_segment
7802: IF (l_chg_table = 'PAY_ELEMENT_ENTRIES_F'
7803: AND l_chg_type <> 'E')
7804: OR (l_chg_table = 'PAY_ELEMENT_ENTRY_VALUES_F')
7805: OR (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7806: AND (l_chg_column_name IN ('GRADE_ID','NORMAL_HOURS','EMPLOYMENT_CATEGORY',l_configured_dff_segment) OR l_chg_type = 'I'))
7807: OR (l_chg_table = 'PQP_ASSIGNMENT_ATTRIBUTES_F'
7808: AND (l_chg_column_name = 'CONTRACT_TYPE' OR l_chg_type = 'I'))THEN
7809:
7810: debug('Returning Y');
7811: debug_exit(l_proc);
7812: RETURN 'Y';
7813:
7814: END IF; --IF (l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
7815:
7816: IF (l_chg_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
7817: AND (l_chg_column_name = 'VALUE' OR l_chg_type = 'I' )) THEN
7818:
7848: debug('l_people_group_column: '||l_people_group_column);
7849:
7850: IF l_unigrade_source = 'PEOPLE_GROUP' AND l_people_group_column IS NOT NULL
7851: THEN
7852: IF(l_chg_table= 'PER_ALL_ASSIGNMENTS_F'
7853: AND l_chg_column_name= 'PEOPLE_GROUP_ID')
7854: THEN
7855: --Compare the new and old value of uni grade segment
7856: l_change_value := ben_ext_person.g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index).change_values;
7923: END IF;
7924:
7925: ELSIF l_unigrade_source = 'ASSIGNMENT' AND l_assignment_column IS NOT NULL
7926: THEN
7927: IF (l_chg_table= 'PER_ALL_ASSIGNMENTS_F' AND l_chg_column_name= trim(l_assignment_column))
7928: THEN
7929: debug('Returning Y');
7930: debug_exit(l_proc);
7931: RETURN 'Y';