[Home] [Help]
248: -- Defined Balance Id
249: OPEN C_DEF_BAL;
250: FETCH C_DEF_BAL INTO L_DEFINED_BALANCE_ID;
251: CLOSE C_DEF_BAL;
252: hr_utility.trace('Balance defined id is:'||L_DEFINED_BALANCE_ID);
253: -- hr_utility.trace('Main Entry is:'||P_MAIN_ENTRY);
254: --Payroll Id
255: OPEN C_PAYROLL_ID;
256: FETCH C_PAYROLL_ID INTO L_PAYROLL_ID,L_BUSINESS_GROUP_ID;
249: OPEN C_DEF_BAL;
250: FETCH C_DEF_BAL INTO L_DEFINED_BALANCE_ID;
251: CLOSE C_DEF_BAL;
252: hr_utility.trace('Balance defined id is:'||L_DEFINED_BALANCE_ID);
253: -- hr_utility.trace('Main Entry is:'||P_MAIN_ENTRY);
254: --Payroll Id
255: OPEN C_PAYROLL_ID;
256: FETCH C_PAYROLL_ID INTO L_PAYROLL_ID,L_BUSINESS_GROUP_ID;
257: CLOSE C_PAYROLL_ID;
288: G_GLOBAL_VALUE := NULL;
289: OPEN C_GET_GLOBAL_VALUE(L_EFFECTIVE_DATE);
290: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
291: CLOSE C_GET_GLOBAL_VALUE;
292: hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
293: end if;
294: /* 16409174 - PAE THRESHOLD CHANGES end*/
295:
296:
297: ---Aggregation
298: OPEN C_AGGREGATION_CHECK(P_ASSIGNMENT_ID);
299: FETCH C_AGGREGATION_CHECK INTO L_AGGREGATION,L_MAIN_ENTRY;
300: CLOSE C_AGGREGATION_CHECK;
301: hr_utility.trace('Period Type: '||L_PERIOD_TYPE);
302: IF (NVL(L_AGGREGATION,'N') ='N') THEN
303: OPEN C_PERIOD_TYPE(L_PAYROLL_ID);
304: FETCH C_PERIOD_TYPE INTO L_PERIOD_TYPE;
305: CLOSE C_PERIOD_TYPE;
307: OPEN C_PERIOD_TYPE(L_PAYROLL_ID);
308: FETCH C_PERIOD_TYPE INTO L_PERIOD_TYPE;
309: CLOSE C_PERIOD_TYPE;
310: END IF;
311: hr_utility.trace('L_PRP_END_DATE: '||L_PRP_END_DATE);
312: hr_utility.trace('L_PAYROLL_ID: '||L_PAYROLL_ID);
313: hr_utility.trace('Period Type: '||L_PERIOD_TYPE);
314: IF NVL(L_AGGREGATION,'N') ='N' THEN
315: FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
308: FETCH C_PERIOD_TYPE INTO L_PERIOD_TYPE;
309: CLOSE C_PERIOD_TYPE;
310: END IF;
311: hr_utility.trace('L_PRP_END_DATE: '||L_PRP_END_DATE);
312: hr_utility.trace('L_PAYROLL_ID: '||L_PAYROLL_ID);
313: hr_utility.trace('Period Type: '||L_PERIOD_TYPE);
314: IF NVL(L_AGGREGATION,'N') ='N' THEN
315: FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
316: LOOP
309: CLOSE C_PERIOD_TYPE;
310: END IF;
311: hr_utility.trace('L_PRP_END_DATE: '||L_PRP_END_DATE);
312: hr_utility.trace('L_PAYROLL_ID: '||L_PAYROLL_ID);
313: hr_utility.trace('Period Type: '||L_PERIOD_TYPE);
314: IF NVL(L_AGGREGATION,'N') ='N' THEN
315: FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
316: LOOP
317: L_BALANCE_VALUE := L_BALANCE_VALUE+PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>L_DEFINED_BALANCE_ID ,P_ASSIGNMENT_ACTION_ID=>L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID ,P_GET_RR_ROUTE => FALSE ,
338: END LOOP;
339: END LOOP;
340: END IF;
341: IF L_PERIOD_TYPE IS NULL THEN
342: hr_utility.trace('No Main Entry Found for Aggregation as on '||L_PRP_END_DATE||'. Please Check');
343: END IF;
344: L_TOTAL_QUAL_EARNINGS := L_BALANCE_VALUE +NVL(P_PQE_ASG_RUN,0);
345: ------------------Earnings Calculated ----------------------------
346: IF L_AGE >= 59 THEN
362: END IF; -- l_sex
363: ELSE
364: N_STATE_PENSION_AGE := 65;
365: END IF; --Age >= 59
366: hr_utility.trace('N_STATE_PENSION_AGE: '||N_STATE_PENSION_AGE);
367: ---------------------------State Pension Age calculated--------------------------
368: IF L_PERIOD_TYPE = 'Week' THEN
369: L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_WEEKLY;
370: L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_WEEKLY_LT;
411: CLOSE C_AED_CHECK;
412: --IF L_AUTO_ENROL_DATE IS NOT NULL THEN
413: --L_AUTO_ENROL_DATE := L_AUTO_ENROL_DATE);
414: --END IF;
415: hr_utility.trace('L_EMPLOYEE_STATUS: '||L_EMPLOYEE_STATUS);
416: hr_utility.trace('L_AUTO_ENROL_DATE: '||L_AUTO_ENROL_DATE);
417: L_QUALIFYING_SCHEME_EXISTS := NVL(L_QUALIFYING_SCHEME_EXISTS,'N');
418: hr_utility.trace('L_QUALIFYING_SCHEME_EXISTS: '||L_QUALIFYING_SCHEME_EXISTS);
419: --Bug 15872415
412: --IF L_AUTO_ENROL_DATE IS NOT NULL THEN
413: --L_AUTO_ENROL_DATE := L_AUTO_ENROL_DATE);
414: --END IF;
415: hr_utility.trace('L_EMPLOYEE_STATUS: '||L_EMPLOYEE_STATUS);
416: hr_utility.trace('L_AUTO_ENROL_DATE: '||L_AUTO_ENROL_DATE);
417: L_QUALIFYING_SCHEME_EXISTS := NVL(L_QUALIFYING_SCHEME_EXISTS,'N');
418: hr_utility.trace('L_QUALIFYING_SCHEME_EXISTS: '||L_QUALIFYING_SCHEME_EXISTS);
419: --Bug 15872415
420: IF (L_ELIGIBLE_JOB_HOLDER_DATE IS NULL) then
414: --END IF;
415: hr_utility.trace('L_EMPLOYEE_STATUS: '||L_EMPLOYEE_STATUS);
416: hr_utility.trace('L_AUTO_ENROL_DATE: '||L_AUTO_ENROL_DATE);
417: L_QUALIFYING_SCHEME_EXISTS := NVL(L_QUALIFYING_SCHEME_EXISTS,'N');
418: hr_utility.trace('L_QUALIFYING_SCHEME_EXISTS: '||L_QUALIFYING_SCHEME_EXISTS);
419: --Bug 15872415
420: IF (L_ELIGIBLE_JOB_HOLDER_DATE IS NULL) then
421: return 0; -- Do Nothing
422: END IF;
445: RETURN NVL(L_ERROR,0);
446: -- RETURN L_TOTAL_QUAL_EARNINGS;
447: EXCEPTION
448: WHEN OTHERS THEN
449: hr_utility.trace('Error:'||sqlerrm);
450: -- RETURN NVL(L_ERROR,0);
451: RAISE;
452: END GET_AGGREGATED_EARNINGS;
453: ----------------------------------------------------------------------------
500: WHERE ADD_MONTHS(P_DATE,3) BETWEEN START_DATE AND END_DATE
501: AND PAYROLL_ID=G_PAYROLL_ID;
502:
503: BEGIN
504: hr_utility.trace('P_DATE: '||P_DATE);
505: hr_utility.trace('PERIOD: '||PERIOD);
506:
507: L_DATE := P_DATE;
508: if PERIOD is not null then
501: AND PAYROLL_ID=G_PAYROLL_ID;
502:
503: BEGIN
504: hr_utility.trace('P_DATE: '||P_DATE);
505: hr_utility.trace('PERIOD: '||PERIOD);
506:
507: L_DATE := P_DATE;
508: if PERIOD is not null then
509: if PERIOD = 'MAX_DEFER_PERIOD_START_DATE' then
519: else
520: L_DATE := L_DATE + TO_NUMBER(PERIOD) -1;
521: end if;
522: end if;
523: hr_utility.trace('L_DATE: '||L_DATE);
524: RETURN L_DATE;
525: END C_PAYROLL_MAX_DATE;
526:
527: PROCEDURE update_ni_category_pension(
577:
578: l_rec csr_element_entries_for_update%rowtype;
579:
580: BEGIN
581: hr_utility.trace('Entering ' || '.update_ni_category_pension');
582: hr_utility.trace('Auto Enrollment Date = ' || p_auto_enrol_date);
583: hr_utility.trace('Validate_mode' ||p_mode);
584:
585: -- Initialization
578: l_rec csr_element_entries_for_update%rowtype;
579:
580: BEGIN
581: hr_utility.trace('Entering ' || '.update_ni_category_pension');
582: hr_utility.trace('Auto Enrollment Date = ' || p_auto_enrol_date);
583: hr_utility.trace('Validate_mode' ||p_mode);
584:
585: -- Initialization
586: pen_basis('A') := 'APP not contracted out';
579:
580: BEGIN
581: hr_utility.trace('Entering ' || '.update_ni_category_pension');
582: hr_utility.trace('Auto Enrollment Date = ' || p_auto_enrol_date);
583: hr_utility.trace('Validate_mode' ||p_mode);
584:
585: -- Initialization
586: pen_basis('A') := 'APP not contracted out';
587: pen_basis('C') := 'Contracted out';
652: );
653:
654:
655:
656: hr_utility.trace('Assignment_id Element_entry_id Old NI Category New NI Category Old Pension Basis New Pension Basis Eff Date');
657: hr_utility.trace('------------- ---------------- -------------- --------------- ----------------- ----------------- --------');
658:
659: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id||' ' ||l_ni_old_category||' ' ||l_ni_new_category
660: ||' ' ||l_ni_old_pension||' ' ||l_ni_new_pension);
653:
654:
655:
656: hr_utility.trace('Assignment_id Element_entry_id Old NI Category New NI Category Old Pension Basis New Pension Basis Eff Date');
657: hr_utility.trace('------------- ---------------- -------------- --------------- ----------------- ----------------- --------');
658:
659: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id||' ' ||l_ni_old_category||' ' ||l_ni_new_category
660: ||' ' ||l_ni_old_pension||' ' ||l_ni_new_pension);
661:
655:
656: hr_utility.trace('Assignment_id Element_entry_id Old NI Category New NI Category Old Pension Basis New Pension Basis Eff Date');
657: hr_utility.trace('------------- ---------------- -------------- --------------- ----------------- ----------------- --------');
658:
659: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id||' ' ||l_ni_old_category||' ' ||l_ni_new_category
660: ||' ' ||l_ni_old_pension||' ' ||l_ni_new_pension);
661:
662:
663: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id|| ' Updated Successfully');
659: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id||' ' ||l_ni_old_category||' ' ||l_ni_new_category
660: ||' ' ||l_ni_old_pension||' ' ||l_ni_new_pension);
661:
662:
663: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id|| ' Updated Successfully');
664:
665: hr_utility.trace('Leaving ' || '.update_ni_catergory_and_pension');
666: end if;
667: if not (p_mode = 'GB_VALIDATE_COMMIT') THEN
661:
662:
663: hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id|| ' Updated Successfully');
664:
665: hr_utility.trace('Leaving ' || '.update_ni_catergory_and_pension');
666: end if;
667: if not (p_mode = 'GB_VALIDATE_COMMIT') THEN
668: ROLLBACK to update_NI_element;
669: end if;
669: end if;
670:
671: EXCEPTION
672: WHEN OTHERS THEN
673: hr_utility.trace('Error While Updating NI category');
674: hr_utility.trace('SQLERRM'||sqlerrm);
675: --RAISE_APPLICATION_ERROR(-20001, SQLCODE||' - '||SQLERRM);
676: RAISE;
677: END update_ni_category_pension;
670:
671: EXCEPTION
672: WHEN OTHERS THEN
673: hr_utility.trace('Error While Updating NI category');
674: hr_utility.trace('SQLERRM'||sqlerrm);
675: --RAISE_APPLICATION_ERROR(-20001, SQLCODE||' - '||SQLERRM);
676: RAISE;
677: END update_ni_category_pension;
678:
747: );
748: L_TEMP NUMBER;
749: BEGIN
750: ------*********Assign all global values to null before assigning values
751: hr_utility.trace('Inside fetch data');
752: G_MODE := NULL;
753: G_PAYROLL_ID := NULL;
754: G_TIME_PERIOD_ID := NULL;
755: G_PAYROLL_START_DATE := NULL;
760: G_ELIGIBLE_POSTPONEMENT_RULE:= NULL;
761: G_PAYROLL_PERIOD_TYPE := NULL;
762: G_GLOBAL_VALUE := NULL;
763: G_BUSINESS_GROUP_ID := NULL;
764: hr_utility.trace('Before Concurrent Program Info');
765: OPEN CSR_CP_INFO;
766: FETCH CSR_CP_INFO
767: INTO G_PAYROLL_ID,
768: G_TIME_PERIOD_ID,
772: --Remove Below Every Where use Global ##Just added for compiling purpose
773: P_PAYROLL_ID := G_PAYROLL_ID;
774: P_TIME_PERIOD_ID :=G_TIME_PERIOD_ID;
775: --Remove
776: hr_utility.trace('Before Defined Balance');
777: -- Defined Balance Id
778: OPEN C_DEF_BAL;
779: FETCH C_DEF_BAL INTO G_DEFINED_BALANCE_ID;
780: CLOSE C_DEF_BAL;
777: -- Defined Balance Id
778: OPEN C_DEF_BAL;
779: FETCH C_DEF_BAL INTO G_DEFINED_BALANCE_ID;
780: CLOSE C_DEF_BAL;
781: hr_utility.trace('Before payroll details');
782: -- Payroll Details
783: OPEN C_PAYROLL_DETAILS;
784: FETCH C_PAYROLL_DETAILS
785: INTO G_PAYROLL_ID,
803: G_EARN_START_DATE ,
804: G_EARN_END_DATE ,
805: G_EARN_DATE_PAID ;
806: CLOSE C_EARN_PERIOD_DETAILS;
807: hr_utility.trace('Values fetched from payroll :'||G_PAYROLL_ID||'START'||G_PAYROLL_START_DATE||'END'||G_PAYROLL_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
808: hr_utility.trace('Values fetched for PRP :'||G_PAYROLL_ID||'START'||G_PRP_START_DATE||'END'||G_PRP_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
809: hr_utility.trace('Values fetched for Earn :'||G_PAYROLL_ID||'START'||G_EARN_START_DATE||'END'||G_EARN_END_DATE||'PAYMENT DATE'||G_EARN_DATE_PAID);
810: hr_utility.trace('Values fetched from payroll:'||' G_PAYROLL_NAME :'||G_PAYROLL_NAME);
811: hr_utility.trace('Before C_PENSION_ELEMENT_INF details');
804: G_EARN_END_DATE ,
805: G_EARN_DATE_PAID ;
806: CLOSE C_EARN_PERIOD_DETAILS;
807: hr_utility.trace('Values fetched from payroll :'||G_PAYROLL_ID||'START'||G_PAYROLL_START_DATE||'END'||G_PAYROLL_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
808: hr_utility.trace('Values fetched for PRP :'||G_PAYROLL_ID||'START'||G_PRP_START_DATE||'END'||G_PRP_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
809: hr_utility.trace('Values fetched for Earn :'||G_PAYROLL_ID||'START'||G_EARN_START_DATE||'END'||G_EARN_END_DATE||'PAYMENT DATE'||G_EARN_DATE_PAID);
810: hr_utility.trace('Values fetched from payroll:'||' G_PAYROLL_NAME :'||G_PAYROLL_NAME);
811: hr_utility.trace('Before C_PENSION_ELEMENT_INF details');
812: -- Element Details
805: G_EARN_DATE_PAID ;
806: CLOSE C_EARN_PERIOD_DETAILS;
807: hr_utility.trace('Values fetched from payroll :'||G_PAYROLL_ID||'START'||G_PAYROLL_START_DATE||'END'||G_PAYROLL_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
808: hr_utility.trace('Values fetched for PRP :'||G_PAYROLL_ID||'START'||G_PRP_START_DATE||'END'||G_PRP_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
809: hr_utility.trace('Values fetched for Earn :'||G_PAYROLL_ID||'START'||G_EARN_START_DATE||'END'||G_EARN_END_DATE||'PAYMENT DATE'||G_EARN_DATE_PAID);
810: hr_utility.trace('Values fetched from payroll:'||' G_PAYROLL_NAME :'||G_PAYROLL_NAME);
811: hr_utility.trace('Before C_PENSION_ELEMENT_INF details');
812: -- Element Details
813: OPEN C_PENSION_ELEMENT_INF;
806: CLOSE C_EARN_PERIOD_DETAILS;
807: hr_utility.trace('Values fetched from payroll :'||G_PAYROLL_ID||'START'||G_PAYROLL_START_DATE||'END'||G_PAYROLL_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
808: hr_utility.trace('Values fetched for PRP :'||G_PAYROLL_ID||'START'||G_PRP_START_DATE||'END'||G_PRP_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
809: hr_utility.trace('Values fetched for Earn :'||G_PAYROLL_ID||'START'||G_EARN_START_DATE||'END'||G_EARN_END_DATE||'PAYMENT DATE'||G_EARN_DATE_PAID);
810: hr_utility.trace('Values fetched from payroll:'||' G_PAYROLL_NAME :'||G_PAYROLL_NAME);
811: hr_utility.trace('Before C_PENSION_ELEMENT_INF details');
812: -- Element Details
813: OPEN C_PENSION_ELEMENT_INF;
814: FETCH C_PENSION_ELEMENT_INF INTO G_ELEMENT_DETAILS;
807: hr_utility.trace('Values fetched from payroll :'||G_PAYROLL_ID||'START'||G_PAYROLL_START_DATE||'END'||G_PAYROLL_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
808: hr_utility.trace('Values fetched for PRP :'||G_PAYROLL_ID||'START'||G_PRP_START_DATE||'END'||G_PRP_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
809: hr_utility.trace('Values fetched for Earn :'||G_PAYROLL_ID||'START'||G_EARN_START_DATE||'END'||G_EARN_END_DATE||'PAYMENT DATE'||G_EARN_DATE_PAID);
810: hr_utility.trace('Values fetched from payroll:'||' G_PAYROLL_NAME :'||G_PAYROLL_NAME);
811: hr_utility.trace('Before C_PENSION_ELEMENT_INF details');
812: -- Element Details
813: OPEN C_PENSION_ELEMENT_INF;
814: FETCH C_PENSION_ELEMENT_INF INTO G_ELEMENT_DETAILS;
815: CLOSE C_PENSION_ELEMENT_INF;
812: -- Element Details
813: OPEN C_PENSION_ELEMENT_INF;
814: FETCH C_PENSION_ELEMENT_INF INTO G_ELEMENT_DETAILS;
815: CLOSE C_PENSION_ELEMENT_INF;
816: hr_utility.trace('G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID:'||G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID);
817: hr_utility.trace('Before C_TAX_REF_PENSION_DETAILS details');
818: --TAX Ref Details
819: OPEN C_TAX_REF_PENSION_DETAILS;
820: FETCH C_TAX_REF_PENSION_DETAILS
813: OPEN C_PENSION_ELEMENT_INF;
814: FETCH C_PENSION_ELEMENT_INF INTO G_ELEMENT_DETAILS;
815: CLOSE C_PENSION_ELEMENT_INF;
816: hr_utility.trace('G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID:'||G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID);
817: hr_utility.trace('Before C_TAX_REF_PENSION_DETAILS details');
818: --TAX Ref Details
819: OPEN C_TAX_REF_PENSION_DETAILS;
820: FETCH C_TAX_REF_PENSION_DETAILS
821: INTO G_PENSION_STAGING_DATE,
822: G_WORKER_POSTPONEMENT_RULE,
823: G_ELIGIBLE_POSTPONEMENT_RULE,
824: G_PAYROLL_PERIOD_TYPE;
825: CLOSE C_TAX_REF_PENSION_DETAILS;
826: hr_utility.trace('Tax details fetched are:'||G_PENSION_STAGING_DATE||'-'||G_PAYROLL_PERIOD_TYPE||'-'||G_WORKER_POSTPONEMENT_RULE||'-'||G_ELIGIBLE_POSTPONEMENT_RULE);
827: hr_utility.trace('Before C_GET_GLOBAL_VALUE details');
828: --Global Details
829: OPEN C_GET_GLOBAL_VALUE(G_PRP_START_DATE);
830: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
823: G_ELIGIBLE_POSTPONEMENT_RULE,
824: G_PAYROLL_PERIOD_TYPE;
825: CLOSE C_TAX_REF_PENSION_DETAILS;
826: hr_utility.trace('Tax details fetched are:'||G_PENSION_STAGING_DATE||'-'||G_PAYROLL_PERIOD_TYPE||'-'||G_WORKER_POSTPONEMENT_RULE||'-'||G_ELIGIBLE_POSTPONEMENT_RULE);
827: hr_utility.trace('Before C_GET_GLOBAL_VALUE details');
828: --Global Details
829: OPEN C_GET_GLOBAL_VALUE(G_PRP_START_DATE);
830: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
831: CLOSE C_GET_GLOBAL_VALUE;
828: --Global Details
829: OPEN C_GET_GLOBAL_VALUE(G_PRP_START_DATE);
830: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
831: CLOSE C_GET_GLOBAL_VALUE;
832: hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
833: hr_utility.trace('Before C_GET_QUALIFYING_PENSION_INFO details');
834: EXCEPTION
835: WHEN OTHERS THEN
836: hr_utility.trace('Fetch data failed:'||SQLERRM);
829: OPEN C_GET_GLOBAL_VALUE(G_PRP_START_DATE);
830: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
831: CLOSE C_GET_GLOBAL_VALUE;
832: hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
833: hr_utility.trace('Before C_GET_QUALIFYING_PENSION_INFO details');
834: EXCEPTION
835: WHEN OTHERS THEN
836: hr_utility.trace('Fetch data failed:'||SQLERRM);
837: -- END;
832: hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
833: hr_utility.trace('Before C_GET_QUALIFYING_PENSION_INFO details');
834: EXCEPTION
835: WHEN OTHERS THEN
836: hr_utility.trace('Fetch data failed:'||SQLERRM);
837: -- END;
838: -- hr_utility.trace('End of fetch data');
839: END FETCH_DATA;
840: ------------------------------------------------------------------------------------------------
834: EXCEPTION
835: WHEN OTHERS THEN
836: hr_utility.trace('Fetch data failed:'||SQLERRM);
837: -- END;
838: -- hr_utility.trace('End of fetch data');
839: END FETCH_DATA;
840: ------------------------------------------------------------------------------------------------
841: FUNCTION EXEC_FORMULA(
842: P_ASSIGNMENT_ID NUMBER,
910: BEGIN
911: -- Bug 14064422
912: OPEN C_ELEMENT_TYPE_ID;
913: FETCH C_ELEMENT_TYPE_ID INTO l_element_type_id;
914: HR_UTILITY.trace('In Exec Formula - Element Type Id :'||l_element_type_id);
915: CLOSE C_ELEMENT_TYPE_ID;
916: -- Bug 14064422
917: HR_UTILITY.trace('In Exec Formula -Element Entry Id :'||P_ELEM_ENTRY_ID);
918: -- Bug 14080693
913: FETCH C_ELEMENT_TYPE_ID INTO l_element_type_id;
914: HR_UTILITY.trace('In Exec Formula - Element Type Id :'||l_element_type_id);
915: CLOSE C_ELEMENT_TYPE_ID;
916: -- Bug 14064422
917: HR_UTILITY.trace('In Exec Formula -Element Entry Id :'||P_ELEM_ENTRY_ID);
918: -- Bug 14080693
919: OPEN C_ORIGINAL_ENTRY_ID;
920: FETCH C_ORIGINAL_ENTRY_ID INTO l_original_entry_id;
921: HR_UTILITY.trace('In Exec Formula -Original Entry Id :'||l_original_entry_id);
917: HR_UTILITY.trace('In Exec Formula -Element Entry Id :'||P_ELEM_ENTRY_ID);
918: -- Bug 14080693
919: OPEN C_ORIGINAL_ENTRY_ID;
920: FETCH C_ORIGINAL_ENTRY_ID INTO l_original_entry_id;
921: HR_UTILITY.trace('In Exec Formula -Original Entry Id :'||l_original_entry_id);
922: CLOSE C_ORIGINAL_ENTRY_ID;
923: -- Bug 14080693
924: l_result_rule_name:=P_RESULT_RULE_NAME;
925: HR_UTILITY.trace(' Inside run Element FORMULA '||P_FORMULA_ID);
921: HR_UTILITY.trace('In Exec Formula -Original Entry Id :'||l_original_entry_id);
922: CLOSE C_ORIGINAL_ENTRY_ID;
923: -- Bug 14080693
924: l_result_rule_name:=P_RESULT_RULE_NAME;
925: HR_UTILITY.trace(' Inside run Element FORMULA '||P_FORMULA_ID);
926: L_INPUTS.delete;
927: P_INPUTS.delete;
928: L_OUTPUTS.delete;
929: -- setting the contexts
957: P_INPUTS(10).name := 'ASSIGNMENT_ACTION_ID';
958: P_INPUTS(10).value := C_ACTION_IDS_ROW.ASSIGNMENT_ACTION_ID;
959: END IF;
960: IF C_ACTION_IDS%NOTFOUND THEN
961: HR_UTILITY.trace('No Action Ids Found for the Assignment');
962: P_INPUTS(9).name := 'PAYROLL_ACTION_ID';
963: P_INPUTS(9).value := G_PAYROLL_ACTION_ID;
964: P_INPUTS(10).name := 'ASSIGNMENT_ACTION_ID';
965: P_INPUTS(10).value := P_ASSACTID;
976: P_INPUTS(6).name := 'ASSIGNMENT_ACTION_ID';
977: P_INPUTS(6).value := C_ACTION_IDS_ROW.ASSIGNMENT_ACTION_ID;
978: END IF;
979: IF C_ACTION_IDS%NOTFOUND THEN
980: HR_UTILITY.trace('No Action Ids Found for the Assignment');
981: P_INPUTS(5).name := 'PAYROLL_ACTION_ID';
982: P_INPUTS(5).value := G_PAYROLL_ACTION_ID;
983: P_INPUTS(6).name := 'ASSIGNMENT_ACTION_ID';
984: P_INPUTS(6).value := P_ASSACTID;
987: Iteration := 7;
988: */
989: FOR iter IN ELE_SCREEN_VALUE (P_ELEM_ENTRY_ID)
990: LOOP
991: HR_UTILITY.trace('Iteration value'||Iteration);
992: P_INPUTS(Iteration).name := iter.name;
993: P_INPUTS(Iteration).value := iter.screen_entry_value;
994: IF (iter.name = 'PAY_VALUE') THEN
995: l_pay_value_ivid := iter.input_value_id;
999: IF l_result_rule_name IS NULL THEN
1000: OPEN get_pay_val_rslt_name(P_STAT_PROC_RULE_ID, l_pay_value_ivid);
1001: FETCH get_pay_val_rslt_name INTO l_result_rule_name;
1002: IF get_pay_val_rslt_name%notfound THEN
1003: HR_UTILITY.trace('Result Rule Name not Found');
1004: END IF;
1005: CLOSE get_pay_val_rslt_name;
1006: END IF;
1007: HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1003: HR_UTILITY.trace('Result Rule Name not Found');
1004: END IF;
1005: CLOSE get_pay_val_rslt_name;
1006: END IF;
1007: HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1008: HR_UTILITY.trace(' ASSIGNMENT_ACTION_ID '||P_INPUTS(6).value );
1009: HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1004: END IF;
1005: CLOSE get_pay_val_rslt_name;
1006: END IF;
1007: HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1008: HR_UTILITY.trace(' ASSIGNMENT_ACTION_ID '||P_INPUTS(6).value );
1009: HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012: FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1005: CLOSE get_pay_val_rslt_name;
1006: END IF;
1007: HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1008: HR_UTILITY.trace(' ASSIGNMENT_ACTION_ID '||P_INPUTS(6).value );
1009: HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012: FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1013: HR_UTILITY.trace('L_INPUTS.COUNT Count:'||L_INPUTS.COUNT);
1006: END IF;
1007: HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1008: HR_UTILITY.trace(' ASSIGNMENT_ACTION_ID '||P_INPUTS(6).value );
1009: HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012: FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1013: HR_UTILITY.trace('L_INPUTS.COUNT Count:'||L_INPUTS.COUNT);
1014: HR_UTILITY.trace('P_INPUTS.COUNT Count:'||P_INPUTS.COUNT);
1007: HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1008: HR_UTILITY.trace(' ASSIGNMENT_ACTION_ID '||P_INPUTS(6).value );
1009: HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012: FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1013: HR_UTILITY.trace('L_INPUTS.COUNT Count:'||L_INPUTS.COUNT);
1014: HR_UTILITY.trace('P_INPUTS.COUNT Count:'||P_INPUTS.COUNT);
1015: IF L_INPUTS.COUNT > 0 AND P_INPUTS.COUNT > 0 THEN
1009: HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012: FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1013: HR_UTILITY.trace('L_INPUTS.COUNT Count:'||L_INPUTS.COUNT);
1014: HR_UTILITY.trace('P_INPUTS.COUNT Count:'||P_INPUTS.COUNT);
1015: IF L_INPUTS.COUNT > 0 AND P_INPUTS.COUNT > 0 THEN
1016: FOR I IN L_INPUTS.first..L_INPUTS.last
1017: LOOP
1010: HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011: HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012: FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1013: HR_UTILITY.trace('L_INPUTS.COUNT Count:'||L_INPUTS.COUNT);
1014: HR_UTILITY.trace('P_INPUTS.COUNT Count:'||P_INPUTS.COUNT);
1015: IF L_INPUTS.COUNT > 0 AND P_INPUTS.COUNT > 0 THEN
1016: FOR I IN L_INPUTS.first..L_INPUTS.last
1017: LOOP
1018: FOR J IN P_INPUTS.first..P_INPUTS.last
1017: LOOP
1018: FOR J IN P_INPUTS.first..P_INPUTS.last
1019: LOOP
1020: IF L_INPUTS(I).name = P_INPUTS(J).name THEN
1021: HR_UTILITY.trace('Inputs Name:'||L_INPUTS(I).name);
1022: HR_UTILITY.trace('Inputs value:'||L_INPUTS(I).value);
1023: HR_UTILITY.trace('---------------------------');
1024: L_INPUTS(I).value := P_INPUTS(J).value;
1025: EXIT;
1018: FOR J IN P_INPUTS.first..P_INPUTS.last
1019: LOOP
1020: IF L_INPUTS(I).name = P_INPUTS(J).name THEN
1021: HR_UTILITY.trace('Inputs Name:'||L_INPUTS(I).name);
1022: HR_UTILITY.trace('Inputs value:'||L_INPUTS(I).value);
1023: HR_UTILITY.trace('---------------------------');
1024: L_INPUTS(I).value := P_INPUTS(J).value;
1025: EXIT;
1026: END IF;
1019: LOOP
1020: IF L_INPUTS(I).name = P_INPUTS(J).name THEN
1021: HR_UTILITY.trace('Inputs Name:'||L_INPUTS(I).name);
1022: HR_UTILITY.trace('Inputs value:'||L_INPUTS(I).value);
1023: HR_UTILITY.trace('---------------------------');
1024: L_INPUTS(I).value := P_INPUTS(J).value;
1025: EXIT;
1026: END IF;
1027: END LOOP;
1026: END IF;
1027: END LOOP;
1028: END LOOP;
1029: END IF;
1030: HR_UTILITY.trace('Inputs Count:'||L_INPUTS.count);
1031: /*
1032: FOR I IN L_INPUTS.first..L_INPUTS.last
1033: LOOP
1034: HR_UTILITY.trace(' i= '||I||' name '||L_INPUTS(I).name ||' value '||L_INPUTS(I).value);
1030: HR_UTILITY.trace('Inputs Count:'||L_INPUTS.count);
1031: /*
1032: FOR I IN L_INPUTS.first..L_INPUTS.last
1033: LOOP
1034: HR_UTILITY.trace(' i= '||I||' name '||L_INPUTS(I).name ||' value '||L_INPUTS(I).value);
1035: END LOOP;
1036: */
1037: --executing the formula
1038: HR_UTILITY.trace('Before Executing Formula');
1034: HR_UTILITY.trace(' i= '||I||' name '||L_INPUTS(I).name ||' value '||L_INPUTS(I).value);
1035: END LOOP;
1036: */
1037: --executing the formula
1038: HR_UTILITY.trace('Before Executing Formula');
1039: FF_EXEC.RUN_FORMULA(L_INPUTS,L_OUTPUTS);
1040: HR_UTILITY.trace('After Executing Formula');
1041: --UTILITY.trace(' calculated value from Element formula '||L_OUTPUTS(1).value);
1042: -- L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_OUTPUTS(1).value,0);
1036: */
1037: --executing the formula
1038: HR_UTILITY.trace('Before Executing Formula');
1039: FF_EXEC.RUN_FORMULA(L_INPUTS,L_OUTPUTS);
1040: HR_UTILITY.trace('After Executing Formula');
1041: --UTILITY.trace(' calculated value from Element formula '||L_OUTPUTS(1).value);
1042: -- L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_OUTPUTS(1).value,0);
1043: FOR iter IN L_OUTPUTS.first..L_OUTPUTS.last
1044: LOOP
1049: END LOOP;
1050: RETURN L_EARNINGS_VALUE;
1051: EXCEPTION
1052: WHEN OTHERS THEN
1053: HR_UTILITY.trace('EXEC_FORMULA Exception:'||sqlerrm);
1054: L_EARNINGS_VALUE := 0;
1055: RAISE;
1056: END EXEC_FORMULA;
1057: --------------------------------------------------------------------------------------------------
1104: L_STATUS_PROCESSING_RULE_ID NUMBER;
1105: L_RESULT_NAME pay_formula_result_rules_f.result_name%type :=NULL;
1106: L_EARNINGS NUMBER :=0;
1107: BEGIN
1108: HR_UTILITY.trace(' Information CALCULATE_EARNINGS_INFO');
1109: HR_UTILITY.trace(' P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1110: FOR I IN C_ELEMENT_DETAILS_INFO(P_EFFECTIVE_DATE,P_ASSIGNMENT_ID)
1111: LOOP
1112: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1105: L_RESULT_NAME pay_formula_result_rules_f.result_name%type :=NULL;
1106: L_EARNINGS NUMBER :=0;
1107: BEGIN
1108: HR_UTILITY.trace(' Information CALCULATE_EARNINGS_INFO');
1109: HR_UTILITY.trace(' P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1110: FOR I IN C_ELEMENT_DETAILS_INFO(P_EFFECTIVE_DATE,P_ASSIGNMENT_ID)
1111: LOOP
1112: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1113: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCESSING_RULE_ID;
1110: FOR I IN C_ELEMENT_DETAILS_INFO(P_EFFECTIVE_DATE,P_ASSIGNMENT_ID)
1111: LOOP
1112: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1113: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCESSING_RULE_ID;
1114: HR_UTILITY.trace('I.ELEMENT_TYPE_ID:: '||I.ELEMENT_TYPE_ID);
1115: HR_UTILITY.trace('L_STATUS_PROCESSING_RULE_ID:: '||L_STATUS_PROCESSING_RULE_ID);
1116: CLOSE C_PENSIONS_FORMULA_ID;
1117: IF L_STATUS_PROCESSING_RULE_ID IS NOT NULL THEN
1118: HR_UTILITY.trace('Before Indirect result rules');
1111: LOOP
1112: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1113: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCESSING_RULE_ID;
1114: HR_UTILITY.trace('I.ELEMENT_TYPE_ID:: '||I.ELEMENT_TYPE_ID);
1115: HR_UTILITY.trace('L_STATUS_PROCESSING_RULE_ID:: '||L_STATUS_PROCESSING_RULE_ID);
1116: CLOSE C_PENSIONS_FORMULA_ID;
1117: IF L_STATUS_PROCESSING_RULE_ID IS NOT NULL THEN
1118: HR_UTILITY.trace('Before Indirect result rules');
1119: FOR rec_result IN C_RESULT_RULE_NAME (L_STATUS_PROCESSING_RULE_ID,P_EFFECTIVE_DATE)
1114: HR_UTILITY.trace('I.ELEMENT_TYPE_ID:: '||I.ELEMENT_TYPE_ID);
1115: HR_UTILITY.trace('L_STATUS_PROCESSING_RULE_ID:: '||L_STATUS_PROCESSING_RULE_ID);
1116: CLOSE C_PENSIONS_FORMULA_ID;
1117: IF L_STATUS_PROCESSING_RULE_ID IS NOT NULL THEN
1118: HR_UTILITY.trace('Before Indirect result rules');
1119: FOR rec_result IN C_RESULT_RULE_NAME (L_STATUS_PROCESSING_RULE_ID,P_EFFECTIVE_DATE)
1120: LOOP
1121: HR_UTILITY.trace('Result Rule :'||rec_result.result_name);
1122: L_RESULT_NAME := rec_result.result_name;
1117: IF L_STATUS_PROCESSING_RULE_ID IS NOT NULL THEN
1118: HR_UTILITY.trace('Before Indirect result rules');
1119: FOR rec_result IN C_RESULT_RULE_NAME (L_STATUS_PROCESSING_RULE_ID,P_EFFECTIVE_DATE)
1120: LOOP
1121: HR_UTILITY.trace('Result Rule :'||rec_result.result_name);
1122: L_RESULT_NAME := rec_result.result_name;
1123: IF L_RESULT_NAME IS NOT NULL THEN
1124: HR_UTILITY.trace('L_EARNINGS:'||L_EARNINGS);
1125: L_EARNINGS:=L_EARNINGS+EXEC_FORMULA(P_ASSIGNMENT_ID,I.ELEMENT_ENTRY_ID,L_STATUS_PROCESSING_RULE_ID,P_ASSACTID,L_FORMULA_ID,L_RESULT_NAME);
1120: LOOP
1121: HR_UTILITY.trace('Result Rule :'||rec_result.result_name);
1122: L_RESULT_NAME := rec_result.result_name;
1123: IF L_RESULT_NAME IS NOT NULL THEN
1124: HR_UTILITY.trace('L_EARNINGS:'||L_EARNINGS);
1125: L_EARNINGS:=L_EARNINGS+EXEC_FORMULA(P_ASSIGNMENT_ID,I.ELEMENT_ENTRY_ID,L_STATUS_PROCESSING_RULE_ID,P_ASSACTID,L_FORMULA_ID,L_RESULT_NAME);
1126: END IF;
1127: L_RESULT_NAME := NULL; -- Bug 14255540
1128: END LOOP; -- Indirect results loop
1213: Iteration NUMBER;
1214: L_TIME_PERIOD_ID NUMBER :=0;
1215: BEGIN
1216: L_BALANCE_FLAG :='N';
1217: HR_UTILITY.trace('Inside CALCULATE_ASG_ACTIONS');
1218: FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
1219: LOOP
1220: --Fetch Value from Balance
1221: HR_UTILITY.trace('Assignment Action : '||L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID);
1217: HR_UTILITY.trace('Inside CALCULATE_ASG_ACTIONS');
1218: FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
1219: LOOP
1220: --Fetch Value from Balance
1221: HR_UTILITY.trace('Assignment Action : '||L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID);
1222: L_BALANCE_FLAG :='Y';
1223: L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE + PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>G_DEFINED_BALANCE_ID ,P_ASSIGNMENT_ACTION_ID=>L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID ,P_GET_RR_ROUTE => FALSE ,
1224: P_GET_RB_ROUTE => FALSE) ;
1225: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE Balance Value : '||L_TOTAL_EARNINGS_VALUE);
1221: HR_UTILITY.trace('Assignment Action : '||L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID);
1222: L_BALANCE_FLAG :='Y';
1223: L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE + PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>G_DEFINED_BALANCE_ID ,P_ASSIGNMENT_ACTION_ID=>L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID ,P_GET_RR_ROUTE => FALSE ,
1224: P_GET_RB_ROUTE => FALSE) ;
1225: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE Balance Value : '||L_TOTAL_EARNINGS_VALUE);
1226: IF (L_TIME_PERIOD_ID = L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID) THEN
1227: -- For multiple payroll runs in the same period this will avoid multiple calculations of missed elements
1228: hr_utility.trace('- Second time occuring of the L_TIME_PERIOD_ID - Older assignment action :'||L_TIME_PERIOD_ID);
1229: ELSE --else time period
1224: P_GET_RB_ROUTE => FALSE) ;
1225: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE Balance Value : '||L_TOTAL_EARNINGS_VALUE);
1226: IF (L_TIME_PERIOD_ID = L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID) THEN
1227: -- For multiple payroll runs in the same period this will avoid multiple calculations of missed elements
1228: hr_utility.trace('- Second time occuring of the L_TIME_PERIOD_ID - Older assignment action :'||L_TIME_PERIOD_ID);
1229: ELSE --else time period
1230: --Check for element entries that have been missed out in the latest run
1231: FOR missed_ele IN C_MISSED_ELEMENTS(L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED,P_ASSIGNMENT_ID)
1232: LOOP
1230: --Check for element entries that have been missed out in the latest run
1231: FOR missed_ele IN C_MISSED_ELEMENTS(L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED,P_ASSIGNMENT_ID)
1232: LOOP
1233: -- Call Calculation logic for those missed elements alone
1234: HR_UTILITY.trace('Missed Ele Type Id:'||missed_ele.element_type_id);
1235: FOR I IN C_ELEMENT_DETAILS(L_ASSIGNMENT_ACTIONS.DATE_EARNED,P_ASSIGNMENT_ID,missed_ele.element_type_id)
1236: LOOP
1237: L_SCREEN_ENTRY_VALUE := NULL;
1238: L_FORMULA_ID := NULL;
1246: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED);
1247: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1248: CLOSE C_PENSIONS_FORMULA_ID;
1249: IF L_FORMULA_ID IS NOT NULL THEN
1250: HR_UTILITY.trace('Formula Id Not Null');
1251: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1252: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1253: END IF;
1254: ELSE --Screen Entry Value not Null
1248: CLOSE C_PENSIONS_FORMULA_ID;
1249: IF L_FORMULA_ID IS NOT NULL THEN
1250: HR_UTILITY.trace('Formula Id Not Null');
1251: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1252: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1253: END IF;
1254: ELSE --Screen Entry Value not Null
1255: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1256: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1252: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1253: END IF;
1254: ELSE --Screen Entry Value not Null
1255: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1256: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1257: END IF;
1258: END LOOP;--calculation logic for missed ele
1259: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED,L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,missed_ele.element_type_id);
1260: HR_UTILITY.trace('Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1256: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1257: END IF;
1258: END LOOP;--calculation logic for missed ele
1259: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED,L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,missed_ele.element_type_id);
1260: HR_UTILITY.trace('Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1261: END LOOP;--missed ele
1262: END IF; --else time period
1263: L_TIME_PERIOD_ID := L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID;
1264: END LOOP;
1350: L_RESULT_RULE_NAME PAY_FORMULA_RESULT_RULES_F.RESULT_NAME%TYPE;
1351: Iteration NUMBER;
1352: L_TIME_PERIOD_ID NUMBER :=0;
1353: BEGIN
1354: hr_utility.trace('Inside CALCULATE_EARNINGS');
1355: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
1356: hr_utility.trace('Person_id '||P_PERSON_ID);
1357: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
1358: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1351: Iteration NUMBER;
1352: L_TIME_PERIOD_ID NUMBER :=0;
1353: BEGIN
1354: hr_utility.trace('Inside CALCULATE_EARNINGS');
1355: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
1356: hr_utility.trace('Person_id '||P_PERSON_ID);
1357: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
1358: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1359: ---#######################################################################################################
1352: L_TIME_PERIOD_ID NUMBER :=0;
1353: BEGIN
1354: hr_utility.trace('Inside CALCULATE_EARNINGS');
1355: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
1356: hr_utility.trace('Person_id '||P_PERSON_ID);
1357: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
1358: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1359: ---#######################################################################################################
1360: IF (P_AGGR_FLAG='N') THEN
1353: BEGIN
1354: hr_utility.trace('Inside CALCULATE_EARNINGS');
1355: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
1356: hr_utility.trace('Person_id '||P_PERSON_ID);
1357: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
1358: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1359: ---#######################################################################################################
1360: IF (P_AGGR_FLAG='N') THEN
1361: --Fetch Value from Balance
1354: hr_utility.trace('Inside CALCULATE_EARNINGS');
1355: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
1356: hr_utility.trace('Person_id '||P_PERSON_ID);
1357: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
1358: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1359: ---#######################################################################################################
1360: IF (P_AGGR_FLAG='N') THEN
1361: --Fetch Value from Balance
1362: L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
1359: ---#######################################################################################################
1360: IF (P_AGGR_FLAG='N') THEN
1361: --Fetch Value from Balance
1362: L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
1363: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
1364: hr_utility.trace('L_BALANCE_FLAG: '||L_BALANCE_FLAG);
1365: IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
1366: /*To handle Payroll Change and if the new payroll
1367: has no payroll-assg actions and if the previous payroll has payroll/assg
1360: IF (P_AGGR_FLAG='N') THEN
1361: --Fetch Value from Balance
1362: L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
1363: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
1364: hr_utility.trace('L_BALANCE_FLAG: '||L_BALANCE_FLAG);
1365: IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
1366: /*To handle Payroll Change and if the new payroll
1367: has no payroll-assg actions and if the previous payroll has payroll/assg
1368: actions within the PRP*/
1365: IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
1366: /*To handle Payroll Change and if the new payroll
1367: has no payroll-assg actions and if the previous payroll has payroll/assg
1368: actions within the PRP*/
1369: hr_utility.trace('To Fetch datepaids which donot have an assignment action');
1370: FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED
1371: LOOP
1372: hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
1373: hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
1368: actions within the PRP*/
1369: hr_utility.trace('To Fetch datepaids which donot have an assignment action');
1370: FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED
1371: LOOP
1372: hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
1373: hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
1374: FOR I IN C_ELEMENT_DETAILS(L_DATE_PAID_MISSED.END_DATE,P_ASSIGNMENT_ID,NULL)
1375: LOOP
1376: L_SCREEN_ENTRY_VALUE := NULL;
1369: hr_utility.trace('To Fetch datepaids which donot have an assignment action');
1370: FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED
1371: LOOP
1372: hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
1373: hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
1374: FOR I IN C_ELEMENT_DETAILS(L_DATE_PAID_MISSED.END_DATE,P_ASSIGNMENT_ID,NULL)
1375: LOOP
1376: L_SCREEN_ENTRY_VALUE := NULL;
1377: L_FORMULA_ID := NULL;
1385: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_DATE_PAID_MISSED.END_DATE);
1386: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1387: CLOSE C_PENSIONS_FORMULA_ID;
1388: IF L_FORMULA_ID IS NOT NULL THEN
1389: HR_UTILITY.trace('Formula Id Not Null');
1390: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1391: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1392: END IF;
1393: ELSE --Screen Entry Value not Null
1387: CLOSE C_PENSIONS_FORMULA_ID;
1388: IF L_FORMULA_ID IS NOT NULL THEN
1389: HR_UTILITY.trace('Formula Id Not Null');
1390: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1391: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1392: END IF;
1393: ELSE --Screen Entry Value not Null
1394: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1395: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1391: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1392: END IF;
1393: ELSE --Screen Entry Value not Null
1394: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1395: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1396: END IF;
1397: END LOOP;
1398: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_DATE_PAID_MISSED.END_DATE,P_assactid,NULL);
1399: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1395: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1396: END IF;
1397: END LOOP;
1398: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_DATE_PAID_MISSED.END_DATE,P_assactid,NULL);
1399: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1400: END LOOP;
1401: END IF;--Balance Found
1402: --No Balance Found
1403: IF (L_BALANCE_FLAG ='N') THEN
1415: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1416: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1417: CLOSE C_PENSIONS_FORMULA_ID;
1418: IF L_FORMULA_ID IS NOT NULL THEN
1419: HR_UTILITY.trace('Formula Id Not Null');
1420: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1421: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1422: END IF;
1423: ELSE --Screen Entry Value not Null
1417: CLOSE C_PENSIONS_FORMULA_ID;
1418: IF L_FORMULA_ID IS NOT NULL THEN
1419: HR_UTILITY.trace('Formula Id Not Null');
1420: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1421: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1422: END IF;
1423: ELSE --Screen Entry Value not Null
1424: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1425: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1421: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1422: END IF;
1423: ELSE --Screen Entry Value not Null
1424: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1425: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1426: END IF;
1427: END LOOP;
1428: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,P_EFFECTIVE_DATE,P_assactid,NULL);
1429: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1425: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1426: END IF;
1427: END LOOP;
1428: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,P_EFFECTIVE_DATE,P_assactid,NULL);
1429: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1430: END IF;--Balance Flag
1431: END IF;
1432: ---#######################################################################################################
1433: --AGGREGATION
1432: ---#######################################################################################################
1433: --AGGREGATION
1434: ---#######################################################################################################
1435: IF (P_AGGR_FLAG='Y') THEN
1436: HR_UTILITY.trace('Aggregation Case - Calculate_earnings');
1437: FOR I IN C_ASSIGNMENTS_AGG
1438: LOOP
1439: IF (I.PAYROLL_ID =G_PAYROLL_ID) THEN
1440: L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE+CALCULATE_EARNINGS(I.ASSIGNMENT_ID,'N',P_PERSON_ID,G_EARN_END_DATE,p_assactid);
1437: FOR I IN C_ASSIGNMENTS_AGG
1438: LOOP
1439: IF (I.PAYROLL_ID =G_PAYROLL_ID) THEN
1440: L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE+CALCULATE_EARNINGS(I.ASSIGNMENT_ID,'N',P_PERSON_ID,G_EARN_END_DATE,p_assactid);
1441: HR_UTILITY.trace('Same Payroll ID - L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
1442: ELSE --Main Entry and this asg are of different payrolls
1443: FOR J IN C_EARN_PERIOD_DETAILS(I.PAYROLL_ID)
1444: LOOP
1445: L_BALANCE_FLAG :='N'; --Reset Flag
1443: FOR J IN C_EARN_PERIOD_DETAILS(I.PAYROLL_ID)
1444: LOOP
1445: L_BALANCE_FLAG :='N'; --Reset Flag
1446: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE +CALCULATE_ASG_ACTIONS (P_ASSIGNMENT_ID ,P_ASSACTID ,L_BALANCE_FLAG ,J.TIME_PERIOD_ID );
1447: hr_utility.trace('BALANCE Flag:'||L_BALANCE_FLAG);
1448: IF (L_BALANCE_FLAG ='N') THEN-- If Balance not found
1449: FOR X IN C_ELEMENT_DETAILS(J.END_DATE,I.ASSIGNMENT_ID,NULL)
1450: LOOP
1451: hr_utility.trace('Inside Different Payroll Aggregated Case--and balance not Found');
1447: hr_utility.trace('BALANCE Flag:'||L_BALANCE_FLAG);
1448: IF (L_BALANCE_FLAG ='N') THEN-- If Balance not found
1449: FOR X IN C_ELEMENT_DETAILS(J.END_DATE,I.ASSIGNMENT_ID,NULL)
1450: LOOP
1451: hr_utility.trace('Inside Different Payroll Aggregated Case--and balance not Found');
1452: L_SCREEN_ENTRY_VALUE := NULL;
1453: L_FORMULA_ID :=NULL;
1454: p_pay_value_ivid :=NULL;
1455: L_STATUS_PROCES_ID :=NULL;
1462: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1463: CLOSE C_PENSIONS_FORMULA_ID;
1464: END IF;
1465: IF L_FORMULA_ID IS NOT NULL THEN
1466: HR_UTILITY.trace(' Inside run Element FORMULA '||L_FORMULA_ID);
1467: -- setting the contexts
1468: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1469: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1470: ELSE
1465: IF L_FORMULA_ID IS NOT NULL THEN
1466: HR_UTILITY.trace(' Inside run Element FORMULA '||L_FORMULA_ID);
1467: -- setting the contexts
1468: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1469: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1470: ELSE
1471: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1472: hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1473: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1468: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1469: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1470: ELSE
1471: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1472: hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1473: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1474: END IF;
1475: END LOOP;
1476: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
1469: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1470: ELSE
1471: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1472: hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1473: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1474: END IF;
1475: END LOOP;
1476: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
1477: hr_utility.trace('Information part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1473: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1474: END IF;
1475: END LOOP;
1476: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
1477: hr_utility.trace('Information part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1478: END IF;-- If Balance not found
1479: END LOOP;-- For every Date Paid in that Payroll
1480: END IF; -- Same Payroll Id as Main Entry
1481: END LOOP;
1479: END LOOP;-- For every Date Paid in that Payroll
1480: END IF; -- Same Payroll Id as Main Entry
1481: END LOOP;
1482: END IF;
1483: HR_UTILITY.trace(' Total value from all Pension Element values = '||L_TOTAL_EARNINGS_VALUE);
1484: RETURN L_TOTAL_EARNINGS_VALUE;
1485: EXCEPTION
1486: WHEN OTHERS THEN
1487: HR_UTILITY.trace(SQLERRM);
1483: HR_UTILITY.trace(' Total value from all Pension Element values = '||L_TOTAL_EARNINGS_VALUE);
1484: RETURN L_TOTAL_EARNINGS_VALUE;
1485: EXCEPTION
1486: WHEN OTHERS THEN
1487: HR_UTILITY.trace(SQLERRM);
1488: RAISE;
1489: END CALCULATE_EARNINGS;
1490: --------------------------------------------------------------------------------------------------
1491: FUNCTION CHECK_AGE_EARNINGS(
1589: open c_period_number;
1590: fetch c_period_number into l_period_number;
1591: close c_period_number;
1592:
1593: hr_utility.trace('l_period_number:'||l_period_number);
1594:
1595: hr_utility.trace('G_PAYROLL_PERIOD_TYPE before:'||G_PAYROLL_PERIOD_TYPE);
1596:
1597: IF NVL(l_period_number,0) <> 0 THEN
1591: close c_period_number;
1592:
1593: hr_utility.trace('l_period_number:'||l_period_number);
1594:
1595: hr_utility.trace('G_PAYROLL_PERIOD_TYPE before:'||G_PAYROLL_PERIOD_TYPE);
1596:
1597: IF NVL(l_period_number,0) <> 0 THEN
1598: IF l_period_number in (52,53,54,55,56) THEN
1599: G_PAYROLL_PERIOD_TYPE := 'Week';
1631: G_GLOBAL_VALUE := NULL;
1632: OPEN C_GET_GLOBAL_VALUE(L_EFFECTIVE_DATE);
1633: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
1634: CLOSE C_GET_GLOBAL_VALUE;
1635: hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
1636: end if;
1637: /* 16409174 - PAE THRESHOLD CHANGES end*/
1638:
1639:
1636: end if;
1637: /* 16409174 - PAE THRESHOLD CHANGES end*/
1638:
1639:
1640: hr_utility.trace('G_PAYROLL_PERIOD_TYPE after:'||G_PAYROLL_PERIOD_TYPE);
1641: IF G_PAYROLL_PERIOD_TYPE = 'Week' THEN
1642: G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_WEEKLY;
1643: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_WEEKLY_LT;
1644: ELSIF G_PAYROLL_PERIOD_TYPE = 'Calendar Month' THEN
1662: ELSIF G_PAYROLL_PERIOD_TYPE = 'Bi-Month' THEN -- Monthly's Threshold multiplied by 2
1663: G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
1664: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
1665: END IF;
1666: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
1667: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
1668: hr_utility.trace('L_AGE:'||L_AGE);
1669: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
1670: /*Bug 14645724: Age check criteria modified*/
1663: G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
1664: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
1665: END IF;
1666: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
1667: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
1668: hr_utility.trace('L_AGE:'||L_AGE);
1669: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
1670: /*Bug 14645724: Age check criteria modified*/
1671: IF L_AGE >= 16 AND L_AGE < 75 THEN
1664: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
1665: END IF;
1666: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
1667: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
1668: hr_utility.trace('L_AGE:'||L_AGE);
1669: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
1670: /*Bug 14645724: Age check criteria modified*/
1671: IF L_AGE >= 16 AND L_AGE < 75 THEN
1672: IF ((L_AGE BETWEEN 22 AND N_STATE_PENSION_AGE) AND (P_PENSIONABLE_EARNINGS >= G_AUTOTHRESHOLD)) THEN
1665: END IF;
1666: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
1667: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
1668: hr_utility.trace('L_AGE:'||L_AGE);
1669: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
1670: /*Bug 14645724: Age check criteria modified*/
1671: IF L_AGE >= 16 AND L_AGE < 75 THEN
1672: IF ((L_AGE BETWEEN 22 AND N_STATE_PENSION_AGE) AND (P_PENSIONABLE_EARNINGS >= G_AUTOTHRESHOLD)) THEN
1673: L_EMPLOYEE_STATUS_FOR_PENSION := 1;
1732: payroll_run c_payroll_run%ROWTYPE; -- bug 16169601
1733: L_PAY_STATUS per_assignment_status_types.pay_system_status%TYPE;-- bug 16169601
1734:
1735: BEGIN
1736: HR_UTILITY.trace(' Entered ASSESS_EMPLOYEE_PROCESS');
1737: --L_PENSIONABLE_EARNINGS := CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid); -- Bug 14015189
1738: L_PENSIONABLE_EARNINGS := TRUNC (CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid)+ 0.004 , 2);
1739: --Check Age and earnings with threshold
1740: hr_utility.trace('After calculate earnings');
1736: HR_UTILITY.trace(' Entered ASSESS_EMPLOYEE_PROCESS');
1737: --L_PENSIONABLE_EARNINGS := CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid); -- Bug 14015189
1738: L_PENSIONABLE_EARNINGS := TRUNC (CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid)+ 0.004 , 2);
1739: --Check Age and earnings with threshold
1740: hr_utility.trace('After calculate earnings');
1741: L_EMPLOYEE_STATUS_FOR_PENSION := CHECK_AGE_EARNINGS(L_PENSIONABLE_EARNINGS,P_ASSIGNMENT_ID, P_PERSON_ID,L_AGE,L_REASON);
1742: hr_utility.trace('After check earnings');
1743: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION'||L_EMPLOYEE_STATUS_FOR_PENSION);
1744: P_EARNINGS := L_PENSIONABLE_EARNINGS;
1738: L_PENSIONABLE_EARNINGS := TRUNC (CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid)+ 0.004 , 2);
1739: --Check Age and earnings with threshold
1740: hr_utility.trace('After calculate earnings');
1741: L_EMPLOYEE_STATUS_FOR_PENSION := CHECK_AGE_EARNINGS(L_PENSIONABLE_EARNINGS,P_ASSIGNMENT_ID, P_PERSON_ID,L_AGE,L_REASON);
1742: hr_utility.trace('After check earnings');
1743: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION'||L_EMPLOYEE_STATUS_FOR_PENSION);
1744: P_EARNINGS := L_PENSIONABLE_EARNINGS;
1745: P_AGE := L_AGE;
1746:
1739: --Check Age and earnings with threshold
1740: hr_utility.trace('After calculate earnings');
1741: L_EMPLOYEE_STATUS_FOR_PENSION := CHECK_AGE_EARNINGS(L_PENSIONABLE_EARNINGS,P_ASSIGNMENT_ID, P_PERSON_ID,L_AGE,L_REASON);
1742: hr_utility.trace('After check earnings');
1743: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION'||L_EMPLOYEE_STATUS_FOR_PENSION);
1744: P_EARNINGS := L_PENSIONABLE_EARNINGS;
1745: P_AGE := L_AGE;
1746:
1747: -- get pay_status of assignment -- to check if its P or D or null
1746:
1747: -- get pay_status of assignment -- to check if its P or D or null
1748: open c_get_pay_status(P_PERSON_ID,P_ASSIGNMENT_ID);
1749: fetch c_get_pay_status into L_PAY_STATUS;
1750: hr_utility.trace('P_PERSON_ID: '||P_PERSON_ID);
1751: hr_utility.trace('P_ASSIGNMENT_ID: '||P_ASSIGNMENT_ID);
1752: hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753: hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754: hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1747: -- get pay_status of assignment -- to check if its P or D or null
1748: open c_get_pay_status(P_PERSON_ID,P_ASSIGNMENT_ID);
1749: fetch c_get_pay_status into L_PAY_STATUS;
1750: hr_utility.trace('P_PERSON_ID: '||P_PERSON_ID);
1751: hr_utility.trace('P_ASSIGNMENT_ID: '||P_ASSIGNMENT_ID);
1752: hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753: hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754: hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1755: close c_get_pay_status;
1748: open c_get_pay_status(P_PERSON_ID,P_ASSIGNMENT_ID);
1749: fetch c_get_pay_status into L_PAY_STATUS;
1750: hr_utility.trace('P_PERSON_ID: '||P_PERSON_ID);
1751: hr_utility.trace('P_ASSIGNMENT_ID: '||P_ASSIGNMENT_ID);
1752: hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753: hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754: hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1755: close c_get_pay_status;
1756: hr_utility.trace('L_PAY_STATUS: '||L_PAY_STATUS);
1749: fetch c_get_pay_status into L_PAY_STATUS;
1750: hr_utility.trace('P_PERSON_ID: '||P_PERSON_ID);
1751: hr_utility.trace('P_ASSIGNMENT_ID: '||P_ASSIGNMENT_ID);
1752: hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753: hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754: hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1755: close c_get_pay_status;
1756: hr_utility.trace('L_PAY_STATUS: '||L_PAY_STATUS);
1757: if L_PAY_STATUS <> 'P' then
1750: hr_utility.trace('P_PERSON_ID: '||P_PERSON_ID);
1751: hr_utility.trace('P_ASSIGNMENT_ID: '||P_ASSIGNMENT_ID);
1752: hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753: hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754: hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1755: close c_get_pay_status;
1756: hr_utility.trace('L_PAY_STATUS: '||L_PAY_STATUS);
1757: if L_PAY_STATUS <> 'P' then
1758: -- if its not P then check if payroll run exists for that period for given assignment
1752: hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753: hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754: hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1755: close c_get_pay_status;
1756: hr_utility.trace('L_PAY_STATUS: '||L_PAY_STATUS);
1757: if L_PAY_STATUS <> 'P' then
1758: -- if its not P then check if payroll run exists for that period for given assignment
1759: open c_payroll_run(P_ASSIGNMENT_ID);
1760: fetch c_payroll_run into payroll_run;
1758: -- if its not P then check if payroll run exists for that period for given assignment
1759: open c_payroll_run(P_ASSIGNMENT_ID);
1760: fetch c_payroll_run into payroll_run;
1761: if c_payroll_run%NOTFOUND THEN
1762: hr_utility.trace('No Payroll run found');
1763: L_EMPLOYEE_STATUS_FOR_PENSION := 3;
1764: P_EARNINGS := 0;
1765: END IF;
1766: close c_payroll_run;
1765: END IF;
1766: close c_payroll_run;
1767: end if;
1768:
1769: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION is:'||L_EMPLOYEE_STATUS_FOR_PENSION);
1770: hr_utility.trace('P_EARNINGS is:'||P_EARNINGS);
1771:
1772: RETURN L_EMPLOYEE_STATUS_FOR_PENSION;
1773: END ASSESS_EMPLOYEE_PROCESS;
1766: close c_payroll_run;
1767: end if;
1768:
1769: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION is:'||L_EMPLOYEE_STATUS_FOR_PENSION);
1770: hr_utility.trace('P_EARNINGS is:'||P_EARNINGS);
1771:
1772: RETURN L_EMPLOYEE_STATUS_FOR_PENSION;
1773: END ASSESS_EMPLOYEE_PROCESS;
1774: --------------------------------------------------------------------------------------------------
1883: ---------------------------------------Location-------------------------------------------
1884: SELECT p_config_values('Location')
1885: INTO l_value
1886: FROM dual ;
1887: hr_utility.trace('Location:: l_value:'||l_value);
1888: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
1889: l_flag :=0;
1890: l_position :=0;
1891: temp_num :=0;
1904: FROM dual;
1905: END LOOP; -- '~'
1906: FOR i IN 1..l_seg_collect.count
1907: LOOP
1908: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
1909: OPEN c_location(l_seg_collect(i));
1910: FETCH c_location INTO l_test;
1911: IF c_location%found THEN
1912: l_flag :=1;
1915: IF l_flag = 1 THEN
1916: EXIT;
1917: END IF;
1918: END LOOP;
1919: hr_utility.trace('L_FLAG:Location:'||l_flag);
1920: IF l_flag =0 THEN
1921: RETURN 0;-- Stop here as the location criteria has failed
1922: END IF;
1923: END IF;--
1923: END IF;--
1924: ---------------------------------------Location-------------------------------------------
1925: IF l_flag =1 OR l_value IS NULL THEN
1926: SELECT p_config_values('Grade') INTO l_value FROM dual ;
1927: hr_utility.trace('Grade :: l_value:'||l_value);
1928: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
1929: l_flag :=0;
1930: l_position :=0;
1931: temp_num :=0;
1944: FROM dual;
1945: END LOOP; -- '~'
1946: FOR i IN 1..l_seg_collect.count
1947: LOOP
1948: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
1949: OPEN c_grade(l_seg_collect(i));
1950: FETCH c_grade INTO l_test;
1951: IF c_grade%found THEN
1952: l_flag :=1;
1955: IF l_flag = 1 THEN
1956: EXIT;
1957: END IF;
1958: END LOOP;
1959: hr_utility.trace('L_FLAG:Grade:'||l_flag);
1960: IF l_flag =0 THEN
1961: RETURN 0;-- Stop here as the location criteria has failed
1962: END IF;
1963: END IF;
1964: END IF;--
1965: ---------------------------------------Grade-------------------------------------------
1966: IF l_flag =1 OR l_value IS NULL THEN
1967: SELECT p_config_values('Job') INTO l_value FROM dual ;
1968: hr_utility.trace('Job :: l_value:'||l_value);
1969: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
1970: l_flag :=0;
1971: l_position :=0;
1972: temp_num :=0;
1985: FROM dual;
1986: END LOOP; -- '~'
1987: FOR i IN 1..l_seg_collect.count
1988: LOOP
1989: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
1990: OPEN c_job(l_seg_collect(i));
1991: FETCH c_job INTO l_test;
1992: IF c_job%found THEN
1993: l_flag :=1;
1996: IF l_flag = 1 THEN
1997: EXIT;
1998: END IF;
1999: END LOOP;
2000: hr_utility.trace('L_FLAG:Job:'||l_flag);
2001: IF l_flag =0 THEN
2002: RETURN 0;-- Stop here as the location criteria has failed
2003: END IF;
2004: END IF;
2005: END IF;--
2006: ---------------------------------------Job-------------------------------------------
2007: IF l_flag =1 OR l_value IS NULL THEN
2008: SELECT p_config_values('Organization') INTO l_value FROM dual ;
2009: hr_utility.trace('Organization :: l_value:'||l_value);
2010: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2011: l_flag :=0;
2012: l_position :=0;
2013: temp_num :=0;
2026: FROM dual;
2027: END LOOP; -- '~'
2028: FOR i IN 1..l_seg_collect.count
2029: LOOP
2030: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2031: OPEN c_organization(l_seg_collect(i));
2032: FETCH c_organization INTO l_test;
2033: IF c_organization%found THEN
2034: l_flag :=1;
2037: IF l_flag = 1 THEN
2038: EXIT;
2039: END IF;
2040: END LOOP;
2041: hr_utility.trace('L_FLAG:Organization:'||l_flag);
2042: IF l_flag =0 THEN
2043: RETURN 0;-- Stop here as the location criteria has failed
2044: END IF;
2045: END IF;
2046: END IF;--
2047: ---------------------------------------Organization-------------------------------------------
2048: IF l_flag =1 OR l_value IS NULL THEN
2049: SELECT p_config_values('Payroll') INTO l_value FROM dual ;
2050: hr_utility.trace('Payroll :: l_value:'||l_value);
2051: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2052: l_flag :=0;
2053: l_position :=0;
2054: temp_num :=0;
2067: FROM dual;
2068: END LOOP; -- '~'
2069: FOR i IN 1..l_seg_collect.count
2070: LOOP
2071: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2072: OPEN c_payroll(l_seg_collect(i));
2073: FETCH c_payroll INTO l_test;
2074: IF c_payroll%found THEN
2075: l_flag :=1;
2078: IF l_flag = 1 THEN
2079: EXIT;
2080: END IF;
2081: END LOOP;
2082: hr_utility.trace('L_FLAG:Payroll:'||l_flag);
2083: IF l_flag =0 THEN
2084: RETURN 0;-- Stop here as the location criteria has failed
2085: END IF;
2086: END IF;
2087: END IF;--
2088: ---------------------------------------Payroll-------------------------------------------
2089: IF l_flag =1 OR l_value IS NULL THEN
2090: SELECT p_config_values('Position') INTO l_value FROM dual ;
2091: hr_utility.trace('Position :: l_value:'||l_value);
2092: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2093: l_flag :=0;
2094: l_position :=0;
2095: temp_num :=0;
2108: FROM dual;
2109: END LOOP; -- '~'
2110: FOR i IN 1..l_seg_collect.count
2111: LOOP
2112: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2113: OPEN c_position(l_seg_collect(i));
2114: FETCH c_position INTO l_test;
2115: IF c_position%found THEN
2116: l_flag :=1;
2119: IF l_flag = 1 THEN
2120: EXIT;
2121: END IF;
2122: END LOOP;
2123: hr_utility.trace('L_FLAG:Position:'||l_flag);
2124: IF l_flag =0 THEN
2125: RETURN 0;-- Stop here as the location criteria has failed
2126: END IF;
2127: END IF;
2128: END IF;--
2129: ---------------------------------------Position-------------------------------------------
2130: IF l_flag =1 OR l_value IS NULL THEN
2131: SELECT p_config_values('Salary Basis') INTO l_value FROM dual ;
2132: hr_utility.trace('Salary Basis :: l_value:'||l_value);
2133: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2134: l_flag :=0;
2135: l_position :=0;
2136: temp_num :=0;
2149: FROM dual;
2150: END LOOP; -- '~'
2151: FOR i IN 1..l_seg_collect.count
2152: LOOP
2153: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2154: OPEN c_pay_basis(l_seg_collect(i));
2155: FETCH c_pay_basis INTO l_test;
2156: IF c_pay_basis%found THEN
2157: l_flag :=1;
2160: IF l_flag = 1 THEN
2161: EXIT;
2162: END IF;
2163: END LOOP;
2164: hr_utility.trace('L_FLAG:Salary Basis:'||l_flag);
2165: IF l_flag =0 THEN
2166: RETURN 0;-- Stop here as the location criteria has failed
2167: END IF;
2168: END IF;
2169: END IF;--
2170: ---------------------------------------Salary Basis-------------------------------------------
2171: IF l_flag =1 OR l_value IS NULL THEN
2172: SELECT p_config_values('Emp Category') INTO l_value FROM dual ;
2173: hr_utility.trace('Emp Category :: l_value:'||l_value);
2174: IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2175: l_flag :=0;
2176: l_position :=0;
2177: temp_num :=0;
2189: FROM dual;
2190: END LOOP; -- '~'
2191: FOR i IN 1..l_seg_collect.count
2192: LOOP
2193: hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2194: OPEN c_employment_category(l_seg_collect(i));
2195: FETCH c_employment_category INTO l_test;
2196: IF c_employment_category%found THEN
2197: l_flag :=1;
2200: IF l_flag = 1 THEN
2201: EXIT;
2202: END IF;
2203: END LOOP;
2204: hr_utility.trace('L_FLAG:Emp Category:'||l_flag);
2205: IF l_flag =0 THEN
2206: RETURN 0;-- Stop here as the location criteria has failed
2207: END IF;
2208: END IF;
2353: L_rulestab('Position') := NULL ;
2354: L_rulestab('Salary Basis') := NULL ;
2355: --Initialization
2356: temp:=temp+1;
2357: hr_utility.trace('----------------------------------------------------');
2358: hr_utility.trace('Scheme Name:: '||I.PCV_INFORMATION1 );
2359: IF I.PCV_INFORMATION4 IS NOT NULL THEN
2360: hr_utility.trace(I.PCV_INFORMATION5||'::'||I.PCV_INFORMATION4);
2361: get_values_into_table(I.PCV_INFORMATION4,I.PCV_INFORMATION5);
2354: L_rulestab('Salary Basis') := NULL ;
2355: --Initialization
2356: temp:=temp+1;
2357: hr_utility.trace('----------------------------------------------------');
2358: hr_utility.trace('Scheme Name:: '||I.PCV_INFORMATION1 );
2359: IF I.PCV_INFORMATION4 IS NOT NULL THEN
2360: hr_utility.trace(I.PCV_INFORMATION5||'::'||I.PCV_INFORMATION4);
2361: get_values_into_table(I.PCV_INFORMATION4,I.PCV_INFORMATION5);
2362: END IF;
2356: temp:=temp+1;
2357: hr_utility.trace('----------------------------------------------------');
2358: hr_utility.trace('Scheme Name:: '||I.PCV_INFORMATION1 );
2359: IF I.PCV_INFORMATION4 IS NOT NULL THEN
2360: hr_utility.trace(I.PCV_INFORMATION5||'::'||I.PCV_INFORMATION4);
2361: get_values_into_table(I.PCV_INFORMATION4,I.PCV_INFORMATION5);
2362: END IF;
2363: IF I.PCV_INFORMATION6 IS NOT NULL THEN
2364: hr_utility.trace(I.PCV_INFORMATION7||'::'||I.PCV_INFORMATION6);
2360: hr_utility.trace(I.PCV_INFORMATION5||'::'||I.PCV_INFORMATION4);
2361: get_values_into_table(I.PCV_INFORMATION4,I.PCV_INFORMATION5);
2362: END IF;
2363: IF I.PCV_INFORMATION6 IS NOT NULL THEN
2364: hr_utility.trace(I.PCV_INFORMATION7||'::'||I.PCV_INFORMATION6);
2365: get_values_into_table(I.PCV_INFORMATION6,I.PCV_INFORMATION7);
2366: END IF;
2367: IF I.PCV_INFORMATION8 IS NOT NULL THEN
2368: hr_utility.trace(I.PCV_INFORMATION9||'::'||I.PCV_INFORMATION8);
2364: hr_utility.trace(I.PCV_INFORMATION7||'::'||I.PCV_INFORMATION6);
2365: get_values_into_table(I.PCV_INFORMATION6,I.PCV_INFORMATION7);
2366: END IF;
2367: IF I.PCV_INFORMATION8 IS NOT NULL THEN
2368: hr_utility.trace(I.PCV_INFORMATION9||'::'||I.PCV_INFORMATION8);
2369: get_values_into_table(I.PCV_INFORMATION8,I.PCV_INFORMATION9);
2370: END IF;
2371: IF I.PCV_INFORMATION10 IS NOT NULL THEN
2372: hr_utility.trace(I.PCV_INFORMATION11||'::'||I.PCV_INFORMATION10);
2368: hr_utility.trace(I.PCV_INFORMATION9||'::'||I.PCV_INFORMATION8);
2369: get_values_into_table(I.PCV_INFORMATION8,I.PCV_INFORMATION9);
2370: END IF;
2371: IF I.PCV_INFORMATION10 IS NOT NULL THEN
2372: hr_utility.trace(I.PCV_INFORMATION11||'::'||I.PCV_INFORMATION10);
2373: get_values_into_table(I.PCV_INFORMATION10,I.PCV_INFORMATION11);
2374: END IF;
2375: IF I.PCV_INFORMATION12 IS NOT NULL THEN
2376: hr_utility.trace(I.PCV_INFORMATION13||'::'||I.PCV_INFORMATION12);
2372: hr_utility.trace(I.PCV_INFORMATION11||'::'||I.PCV_INFORMATION10);
2373: get_values_into_table(I.PCV_INFORMATION10,I.PCV_INFORMATION11);
2374: END IF;
2375: IF I.PCV_INFORMATION12 IS NOT NULL THEN
2376: hr_utility.trace(I.PCV_INFORMATION13||'::'||I.PCV_INFORMATION12);
2377: get_values_into_table(I.PCV_INFORMATION12,I.PCV_INFORMATION13);
2378: END IF;
2379: L_rulestab('Scheme Name') :=I.PCV_INFORMATION1;
2380: L_rulestab('Employee Element Name'):=I.PCV_INFORMATION2;
2380: L_rulestab('Employee Element Name'):=I.PCV_INFORMATION2;
2381: L_rulestab('Employer Element Name'):=I.PCV_INFORMATION3;
2382: g_collecttab(temp) :=L_rulestab;
2383: END LOOP;
2384: hr_utility.trace('value of Collective Count::'||g_collecttab.count);
2385: ---Printing Tables
2386: FOR i IN 1..g_collecttab.count
2387: LOOP
2388: hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2384: hr_utility.trace('value of Collective Count::'||g_collecttab.count);
2385: ---Printing Tables
2386: FOR i IN 1..g_collecttab.count
2387: LOOP
2388: hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2389: hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2385: ---Printing Tables
2386: FOR i IN 1..g_collecttab.count
2387: LOOP
2388: hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2389: hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2386: FOR i IN 1..g_collecttab.count
2387: LOOP
2388: hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2389: hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2387: LOOP
2388: hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2389: hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2388: hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2389: hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2389: hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2390: hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2391: hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2399: hr_utility.trace(g_collecttab(i)('Position'));
2392: hr_utility.trace(g_collecttab(i)('Emp Category'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2399: hr_utility.trace(g_collecttab(i)('Position'));
2400: hr_utility.trace(g_collecttab(i)('Salary Basis'));
2393: hr_utility.trace(g_collecttab(i)('Grade'));
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2399: hr_utility.trace(g_collecttab(i)('Position'));
2400: hr_utility.trace(g_collecttab(i)('Salary Basis'));
2401: END LOOP;
2394: hr_utility.trace(g_collecttab(i)('Group'));
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2399: hr_utility.trace(g_collecttab(i)('Position'));
2400: hr_utility.trace(g_collecttab(i)('Salary Basis'));
2401: END LOOP;
2402: --Printing Tables
2395: hr_utility.trace(g_collecttab(i)('Job'));
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2399: hr_utility.trace(g_collecttab(i)('Position'));
2400: hr_utility.trace(g_collecttab(i)('Salary Basis'));
2401: END LOOP;
2402: --Printing Tables
2403: END conv_configval_to_table;
2396: hr_utility.trace(g_collecttab(i)('Location'));
2397: hr_utility.trace(g_collecttab(i)('Organization'));
2398: hr_utility.trace(g_collecttab(i)('Payroll'));
2399: hr_utility.trace(g_collecttab(i)('Position'));
2400: hr_utility.trace(g_collecttab(i)('Salary Basis'));
2401: END LOOP;
2402: --Printing Tables
2403: END conv_configval_to_table;
2404: --------------------------------------------------------------------------------------------------
2473: SELECT instr(L_group_value,'|') INTO l_position FROM dual;
2474: SELECT trim(SUBSTR(L_group_value,1,(l_position-1)))
2475: INTO l_group_name
2476: FROM dual;
2477: hr_utility.trace('l_group_name:'||l_group_name);
2478: OPEN c_group_name_check(l_group_name);
2479: FETCH c_group_name_check INTO l_test;
2480: IF c_group_name_check%notfound THEN
2481: hr_utility.trace('People Group Name is not valid::'||l_group_name);
2477: hr_utility.trace('l_group_name:'||l_group_name);
2478: OPEN c_group_name_check(l_group_name);
2479: FETCH c_group_name_check INTO l_test;
2480: IF c_group_name_check%notfound THEN
2481: hr_utility.trace('People Group Name is not valid::'||l_group_name);
2482: RAISE_APPLICATION_ERROR(-20001,'The People Group Name '||l_group_name||' entered for Eligibility Value is not valid.');
2483: END IF;
2484: CLOSE c_group_name_check;
2485: IF l_group_name IS NOT NULL THEN -- If No GROUP NAME is entered
2504: FROM dual;
2505: END LOOP; -- '~'
2506: FOR i IN 1..l_seg_collection.count
2507: LOOP
2508: hr_utility.trace('Seg Collection:: '||i||l_seg_collection(i));
2509: END LOOP;
2510: stmt :=NULL;
2511: FOR ii IN 1..l_seg_collection.count
2512: LOOP -- Segments Collection
2541: FROM dual;
2542: END LOOP;-- '|'
2543: FOR i IN 1..l_seg_name_value_tab.count
2544: LOOP
2545: hr_utility.trace('For ii='||ii||'Seg val tab::'||l_seg_name_value_tab(i).name||'##'||l_seg_name_value_tab(i).value);
2546: END LOOP;
2547: IF ii =1 THEN -- Segment Collection First
2548: FOR i IN 1..l_seg_name_value_tab.count
2549: LOOP
2546: END LOOP;
2547: IF ii =1 THEN -- Segment Collection First
2548: FOR i IN 1..l_seg_name_value_tab.count
2549: LOOP
2550: hr_utility.trace(l_seg_name_value_tab(i).name||'---'||l_seg_name_value_tab(i).value);
2551: OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2552: FETCH c_col_name INTO l_col_name,l_flex_num;
2553: CLOSE c_col_name;
2554: hr_utility.trace('Column Name::'||l_col_name);
2550: hr_utility.trace(l_seg_name_value_tab(i).name||'---'||l_seg_name_value_tab(i).value);
2551: OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2552: FETCH c_col_name INTO l_col_name,l_flex_num;
2553: CLOSE c_col_name;
2554: hr_utility.trace('Column Name::'||l_col_name);
2555: hr_utility.trace('l_flex_num::'||l_flex_num);
2556: -- if i=2 then -- i=1 will be '$NAME$'
2557: IF l_flex_num IS NOT NULL AND l_col_name IS NOT NULL THEN
2558: IF i =1 THEN
2551: OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2552: FETCH c_col_name INTO l_col_name,l_flex_num;
2553: CLOSE c_col_name;
2554: hr_utility.trace('Column Name::'||l_col_name);
2555: hr_utility.trace('l_flex_num::'||l_flex_num);
2556: -- if i=2 then -- i=1 will be '$NAME$'
2557: IF l_flex_num IS NOT NULL AND l_col_name IS NOT NULL THEN
2558: IF i =1 THEN
2559: stmt := 'select PEOPLE_GROUP_ID from pay_people_groups where (ID_FLEX_NUM ='||l_flex_num||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2560: ELSE
2561: stmt := stmt ||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2562: END IF;
2563: ELSE
2564: hr_utility.trace('No Column found for the flexfield segment:: '||l_seg_name_value_tab(i).name);
2565: RAISE_APPLICATION_ERROR(-20001,'Segment Name: '||l_seg_name_value_tab(i).name||' entered for the People Group: '||l_group_name||' is not valid.');
2566: END IF;
2567: END LOOP;
2568: stmt := stmt||')';
2568: stmt := stmt||')';
2569: ELSE
2570: FOR i IN 1..l_seg_name_value_tab.count
2571: LOOP
2572: hr_utility.trace(l_seg_name_value_tab(i).name||'---'||l_seg_name_value_tab(i).value);
2573: OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2574: FETCH c_col_name INTO l_col_name,l_flex_num;
2575: CLOSE c_col_name;
2576: hr_utility.trace('Column Name::'||l_col_name);
2572: hr_utility.trace(l_seg_name_value_tab(i).name||'---'||l_seg_name_value_tab(i).value);
2573: OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2574: FETCH c_col_name INTO l_col_name,l_flex_num;
2575: CLOSE c_col_name;
2576: hr_utility.trace('Column Name::'||l_col_name);
2577: hr_utility.trace('l_flex_num::'||l_flex_num);
2578: IF l_col_name IS NOT NULL THEN
2579: IF i =1 THEN
2580: stmt := stmt ||' OR ('|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2573: OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2574: FETCH c_col_name INTO l_col_name,l_flex_num;
2575: CLOSE c_col_name;
2576: hr_utility.trace('Column Name::'||l_col_name);
2577: hr_utility.trace('l_flex_num::'||l_flex_num);
2578: IF l_col_name IS NOT NULL THEN
2579: IF i =1 THEN
2580: stmt := stmt ||' OR ('|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2581: ELSE
2581: ELSE
2582: stmt := stmt ||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2583: END IF;
2584: ELSE
2585: hr_utility.trace('No Column found for the flexfield segment:: '||l_seg_name_value_tab(i).name);
2586: RAISE_APPLICATION_ERROR(-20001,'Segment Name: '||l_seg_name_value_tab(i).name||' entered for the People Group: '||l_group_name||' is not valid.');
2587: END IF;
2588: END LOOP;
2589: stmt := stmt||')';
2588: END LOOP;
2589: stmt := stmt||')';
2590: END IF; -- Segment Collection First
2591: END LOOP; -- Segments Collection
2592: hr_utility.trace('Final Statement : '||stmt);
2593: OPEN tc FOR stmt;
2594: LOOP
2595: FETCH tc INTO l_people_group_id;
2596: EXIT
2594: LOOP
2595: FETCH tc INTO l_people_group_id;
2596: EXIT
2597: WHEN tc%notfound;
2598: hr_utility.trace('People_group_id::'||l_people_group_id);
2599: l_people_group_id_tab(l_people_group_id_tab.count+1) :=l_people_group_id;
2600: hr_utility.trace('PPPL GROUP ID into Table::'||l_people_group_id_tab(l_people_group_id_tab.count));
2601: END LOOP;
2602: CLOSE tc;
2596: EXIT
2597: WHEN tc%notfound;
2598: hr_utility.trace('People_group_id::'||l_people_group_id);
2599: l_people_group_id_tab(l_people_group_id_tab.count+1) :=l_people_group_id;
2600: hr_utility.trace('PPPL GROUP ID into Table::'||l_people_group_id_tab(l_people_group_id_tab.count));
2601: END LOOP;
2602: CLOSE tc;
2603: END IF; -- If No GROUP NAME is entered
2604: END IF; --If Any eligiblity criteria is set for 'GROUP'
2610: FOR i IN 1..g_collecttab.count --Every Config Value
2611: LOOP
2612: l_receive_ppl_group.delete;
2613: l_flag:=0;
2614: hr_utility.trace('--------------------------------------------------------------------');
2615: hr_utility.trace('Scheme Number::: '||i);
2616: hr_utility.trace('--------------------------------------------------------------------');
2617: IF check_all_null(g_collecttab(i))=0 THEN --If Not, All values are null
2618: IF g_collecttab(i)('Group') IS NOT NULL THEN --Check for People Group and Others also If this is true
2611: LOOP
2612: l_receive_ppl_group.delete;
2613: l_flag:=0;
2614: hr_utility.trace('--------------------------------------------------------------------');
2615: hr_utility.trace('Scheme Number::: '||i);
2616: hr_utility.trace('--------------------------------------------------------------------');
2617: IF check_all_null(g_collecttab(i))=0 THEN --If Not, All values are null
2618: IF g_collecttab(i)('Group') IS NOT NULL THEN --Check for People Group and Others also If this is true
2619: get_group_details(g_collecttab(i),l_receive_ppl_group);
2612: l_receive_ppl_group.delete;
2613: l_flag:=0;
2614: hr_utility.trace('--------------------------------------------------------------------');
2615: hr_utility.trace('Scheme Number::: '||i);
2616: hr_utility.trace('--------------------------------------------------------------------');
2617: IF check_all_null(g_collecttab(i))=0 THEN --If Not, All values are null
2618: IF g_collecttab(i)('Group') IS NOT NULL THEN --Check for People Group and Others also If this is true
2619: get_group_details(g_collecttab(i),l_receive_ppl_group);
2620: hr_utility.trace('Count::'||l_receive_ppl_group.count);
2616: hr_utility.trace('--------------------------------------------------------------------');
2617: IF check_all_null(g_collecttab(i))=0 THEN --If Not, All values are null
2618: IF g_collecttab(i)('Group') IS NOT NULL THEN --Check for People Group and Others also If this is true
2619: get_group_details(g_collecttab(i),l_receive_ppl_group);
2620: hr_utility.trace('Count::'||l_receive_ppl_group.count);
2621: FOR j IN 1..l_receive_ppl_group.count--PPL Group ID Check
2622: LOOP
2623: hr_utility.trace(l_receive_ppl_group(j));
2624: ---Loop where all PPL Group ID are getting fetched one by one
2619: get_group_details(g_collecttab(i),l_receive_ppl_group);
2620: hr_utility.trace('Count::'||l_receive_ppl_group.count);
2621: FOR j IN 1..l_receive_ppl_group.count--PPL Group ID Check
2622: LOOP
2623: hr_utility.trace(l_receive_ppl_group(j));
2624: ---Loop where all PPL Group ID are getting fetched one by one
2625: OPEN C_ASSIGNMENT_CHECK(l_receive_ppl_group(j));
2626: FETCH C_ASSIGNMENT_CHECK INTO l_test;
2627: IF C_ASSIGNMENT_CHECK%found THEN
2639: IF l_flag =1 THEN --Set Final Flag
2640: l_er_scheme_name :=g_collecttab(i)('Scheme Name');
2641: l_final_flag :=l_final_flag+1;
2642: IF (l_final_flag >1) THEN
2643: hr_utility.trace('Schemes Not Mutually Exclusive. Hence exiting group_check');
2644: RETURN 2;
2645: END IF;
2646: END IF;--Set Final Flag
2647: END IF; --If Not, All values are null
2645: END IF;
2646: END IF;--Set Final Flag
2647: END IF; --If Not, All values are null
2648: END LOOP; --Every Config Value
2649: hr_utility.trace('Final criteria Flag::'||l_final_flag);
2650: IF l_final_flag =1 THEN
2651: hr_utility.trace('Criteria Met for the Scheme-'||l_er_scheme_name);
2652: RETURN 1;
2653: END IF;
2647: END IF; --If Not, All values are null
2648: END LOOP; --Every Config Value
2649: hr_utility.trace('Final criteria Flag::'||l_final_flag);
2650: IF l_final_flag =1 THEN
2651: hr_utility.trace('Criteria Met for the Scheme-'||l_er_scheme_name);
2652: RETURN 1;
2653: END IF;
2654: IF l_final_flag>1 THEN
2655: hr_utility.trace('Not Mutually Exclusive');
2651: hr_utility.trace('Criteria Met for the Scheme-'||l_er_scheme_name);
2652: RETURN 1;
2653: END IF;
2654: IF l_final_flag>1 THEN
2655: hr_utility.trace('Not Mutually Exclusive');
2656: RETURN 2;
2657: END IF;
2658: IF l_final_flag =0 THEN
2659: hr_utility.trace('No Scheme met the Criteria for the assignment');--Should throw an error
2655: hr_utility.trace('Not Mutually Exclusive');
2656: RETURN 2;
2657: END IF;
2658: IF l_final_flag =0 THEN
2659: hr_utility.trace('No Scheme met the Criteria for the assignment');--Should throw an error
2660: RETURN 0; --No Scheme Found eligible for this assignment
2661: END IF;
2662: /*
2663: 0 -- No Match
2709: AND ELEMENT_TYPE_ID = G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
2710: AND (G_PRP_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
2711: OR EFFECTIVE_START_DATE > G_PRP_START_DATE);
2712: BEGIN
2713: hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
2714: hr_utility.trace('INput Value ID');
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2710: AND (G_PRP_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
2711: OR EFFECTIVE_START_DATE > G_PRP_START_DATE);
2712: BEGIN
2713: hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
2714: hr_utility.trace('INput Value ID');
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2711: OR EFFECTIVE_START_DATE > G_PRP_START_DATE);
2712: BEGIN
2713: hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
2714: hr_utility.trace('INput Value ID');
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2712: BEGIN
2713: hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
2714: hr_utility.trace('INput Value ID');
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2713: hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
2714: hr_utility.trace('INput Value ID');
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2714: hr_utility.trace('INput Value ID');
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2715: hr_utility.trace(P_INPUT_VALUE_ID1);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2716: hr_utility.trace(P_INPUT_VALUE_ID2);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2717: hr_utility.trace(P_INPUT_VALUE_ID3);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2718: hr_utility.trace(P_INPUT_VALUE_ID4);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2719: hr_utility.trace(P_INPUT_VALUE_ID5);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2720: hr_utility.trace(P_INPUT_VALUE_ID6);
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2721: hr_utility.trace(P_INPUT_VALUE_ID7);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2722: hr_utility.trace(P_INPUT_VALUE_ID8);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2723: hr_utility.trace(P_INPUT_VALUE_ID9);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2724: hr_utility.trace(P_INPUT_VALUE_ID10);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2725: hr_utility.trace(P_INPUT_VALUE_ID11);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2726: hr_utility.trace(P_INPUT_VALUE_ID12);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2727: hr_utility.trace(P_INPUT_VALUE_ID13);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2728: hr_utility.trace('Entry Values');
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2729: hr_utility.trace(P_ENTRY_VALUE1);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2730: hr_utility.trace(P_ENTRY_VALUE2);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2731: hr_utility.trace(P_ENTRY_VALUE3);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2732: hr_utility.trace(P_ENTRY_VALUE4);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2740: hr_utility.trace(P_ENTRY_VALUE12);
2733: hr_utility.trace(P_ENTRY_VALUE5);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2740: hr_utility.trace(P_ENTRY_VALUE12);
2741: hr_utility.trace(P_ENTRY_VALUE13);
2734: hr_utility.trace(P_ENTRY_VALUE6);
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2740: hr_utility.trace(P_ENTRY_VALUE12);
2741: hr_utility.trace(P_ENTRY_VALUE13);
2742: IF (P_ENTRY_VALUE2='Y') THEN
2735: hr_utility.trace(P_ENTRY_VALUE7);
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2740: hr_utility.trace(P_ENTRY_VALUE12);
2741: hr_utility.trace(P_ENTRY_VALUE13);
2742: IF (P_ENTRY_VALUE2='Y') THEN
2743: L_ENTRY_VALUE2 :='Yes';
2736: hr_utility.trace(P_ENTRY_VALUE8);
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2740: hr_utility.trace(P_ENTRY_VALUE12);
2741: hr_utility.trace(P_ENTRY_VALUE13);
2742: IF (P_ENTRY_VALUE2='Y') THEN
2743: L_ENTRY_VALUE2 :='Yes';
2744: ELSE
2737: hr_utility.trace(P_ENTRY_VALUE9);
2738: hr_utility.trace(P_ENTRY_VALUE10);
2739: hr_utility.trace(P_ENTRY_VALUE11);
2740: hr_utility.trace(P_ENTRY_VALUE12);
2741: hr_utility.trace(P_ENTRY_VALUE13);
2742: IF (P_ENTRY_VALUE2='Y') THEN
2743: L_ENTRY_VALUE2 :='Yes';
2744: ELSE
2745: L_ENTRY_VALUE2:='No';
2771: END LOOP;
2772: --Rollback to the savepoint 'update_element_transaction' if not in Commit Mode.
2773: EXCEPTION
2774: WHEN OTHERS THEN
2775: hr_utility.trace('ERROR'||SQLERRM);
2776: hr_utility.trace('Error While Updating Pensions Information Element');
2777: --ROLLBACK;
2778: RAISE;
2779: END UPDATE_INFO_ELEMENT;
2772: --Rollback to the savepoint 'update_element_transaction' if not in Commit Mode.
2773: EXCEPTION
2774: WHEN OTHERS THEN
2775: hr_utility.trace('ERROR'||SQLERRM);
2776: hr_utility.trace('Error While Updating Pensions Information Element');
2777: --ROLLBACK;
2778: RAISE;
2779: END UPDATE_INFO_ELEMENT;
2780: --------------------------------------------------------------------------------------------------
2784: p_message_text IN VARCHAR2 )
2785: IS
2786: PRAGMA AUTONOMOUS_TRANSACTION;
2787: BEGIN
2788: hr_utility.set_location(' Entering: populate_run_msg',111);
2789: INSERT
2790: INTO pay_message_lines
2791: (
2792: line_sequence,
2804: p_assignment_action_id ,
2805: 'A' ,
2806: SUBSTR(p_message_text,1,2000)
2807: );
2808: hr_utility.set_location(' Leaving: populate_run_msg',999);
2809: COMMIT;
2810: EXCEPTION
2811: WHEN OTHERS THEN
2812: hr_utility.trace('Error occured in populate_run_msg');
2808: hr_utility.set_location(' Leaving: populate_run_msg',999);
2809: COMMIT;
2810: EXCEPTION
2811: WHEN OTHERS THEN
2812: hr_utility.trace('Error occured in populate_run_msg');
2813: RAISE;
2814: END populate_run_msg;
2815: -----------------------------------------------------------------------------------------------------
2816: PROCEDURE populate_warn_msg
2820: )
2821: IS
2822: PRAGMA AUTONOMOUS_TRANSACTION;
2823: BEGIN
2824: hr_utility.set_location(' Entering: populate_warn_msg',111);
2825: INSERT
2826: INTO pay_message_lines
2827: (
2828: line_sequence,
2840: p_assignment_action_id ,
2841: 'A' ,
2842: SUBSTR(p_message_text,1,2000)
2843: );
2844: hr_utility.set_location(' Leaving: populate_warn_msg',999);
2845: COMMIT;
2846: EXCEPTION
2847: WHEN OTHERS THEN
2848: hr_utility.trace('Error occured in populate_run_msg');
2844: hr_utility.set_location(' Leaving: populate_warn_msg',999);
2845: COMMIT;
2846: EXCEPTION
2847: WHEN OTHERS THEN
2848: hr_utility.trace('Error occured in populate_run_msg');
2849: RAISE;
2850: END populate_warn_msg;
2851: PROCEDURE INSERT_PENSION_ELEMENT
2852: (
2919: L_PENSION_ENTRY_START DATE :=NULL;
2920: P_ELEMENT_LINK_ID NUMBER;
2921: P_ELEMENT_TYPE_ID NUMBER;
2922: BEGIN
2923: hr_utility.trace('Insert Pension Element');
2924: hr_utility.trace('G_BUSINESS_GROUP_ID :'||G_BUSINESS_GROUP_ID);
2925: hr_utility.trace('P_ELEMENT_TYPE_NAME:'||P_ELEMENT_TYPE_NAME);
2926: OPEN C_SERVICE_START(P_PERSON_ID);
2927: FETCH C_SERVICE_START INTO L_SERVICE_START;
2920: P_ELEMENT_LINK_ID NUMBER;
2921: P_ELEMENT_TYPE_ID NUMBER;
2922: BEGIN
2923: hr_utility.trace('Insert Pension Element');
2924: hr_utility.trace('G_BUSINESS_GROUP_ID :'||G_BUSINESS_GROUP_ID);
2925: hr_utility.trace('P_ELEMENT_TYPE_NAME:'||P_ELEMENT_TYPE_NAME);
2926: OPEN C_SERVICE_START(P_PERSON_ID);
2927: FETCH C_SERVICE_START INTO L_SERVICE_START;
2928: CLOSE C_SERVICE_START;
2921: P_ELEMENT_TYPE_ID NUMBER;
2922: BEGIN
2923: hr_utility.trace('Insert Pension Element');
2924: hr_utility.trace('G_BUSINESS_GROUP_ID :'||G_BUSINESS_GROUP_ID);
2925: hr_utility.trace('P_ELEMENT_TYPE_NAME:'||P_ELEMENT_TYPE_NAME);
2926: OPEN C_SERVICE_START(P_PERSON_ID);
2927: FETCH C_SERVICE_START INTO L_SERVICE_START;
2928: CLOSE C_SERVICE_START;
2929: hr_utility.trace('L_SERVICE_START : '||L_SERVICE_START);
2925: hr_utility.trace('P_ELEMENT_TYPE_NAME:'||P_ELEMENT_TYPE_NAME);
2926: OPEN C_SERVICE_START(P_PERSON_ID);
2927: FETCH C_SERVICE_START INTO L_SERVICE_START;
2928: CLOSE C_SERVICE_START;
2929: hr_utility.trace('L_SERVICE_START : '||L_SERVICE_START);
2930:
2931: OPEN c_asg_start_date;
2932: FETCH c_asg_start_date INTO l_asg_start_date;
2933: CLOSE c_asg_start_date;
2930:
2931: OPEN c_asg_start_date;
2932: FETCH c_asg_start_date INTO l_asg_start_date;
2933: CLOSE c_asg_start_date;
2934: hr_utility.trace('l_asg_start_date : '||l_asg_start_date);
2935: OPEN C_ELEMENT_INFO ;
2936: FETCH C_ELEMENT_INFO INTO P_ELEMENT_TYPE_ID;
2937: IF C_ELEMENT_INFO%NOTFOUND THEN
2938: hr_utility.trace('No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2934: hr_utility.trace('l_asg_start_date : '||l_asg_start_date);
2935: OPEN C_ELEMENT_INFO ;
2936: FETCH C_ELEMENT_INFO INTO P_ELEMENT_TYPE_ID;
2937: IF C_ELEMENT_INFO%NOTFOUND THEN
2938: hr_utility.trace('No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2939: -- RAISE_APPLICATION_ERROR(-20001,'No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2940: END IF;
2941: CLOSE C_ELEMENT_INFO;
2942: hr_utility.trace('P_AGGREGATION_FLAG::'||P_AGGREGATION_FLAG);
2938: hr_utility.trace('No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2939: -- RAISE_APPLICATION_ERROR(-20001,'No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2940: END IF;
2941: CLOSE C_ELEMENT_INFO;
2942: hr_utility.trace('P_AGGREGATION_FLAG::'||P_AGGREGATION_FLAG);
2943: --Non Aggregated
2944: IF NVL(P_AGGREGATION_FLAG,'N')='N' THEN
2945: --To get the date where the pension element is to be attached
2946: OPEN C_PENSION_ENTRY_START(G_PAYROLL_ID,L_SERVICE_START,l_asg_start_date);
2945: --To get the date where the pension element is to be attached
2946: OPEN C_PENSION_ENTRY_START(G_PAYROLL_ID,L_SERVICE_START,l_asg_start_date);
2947: FETCH C_PENSION_ENTRY_START INTO L_PENSION_ENTRY_START;
2948: CLOSE C_PENSION_ENTRY_START;
2949: hr_utility.trace('L_PENSION_ENTRY_START:'||L_PENSION_ENTRY_START);
2950: --Get Link Id
2951: P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => P_ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2952: hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2953: IF P_ELEMENT_LINK_ID IS NULL THEN
2948: CLOSE C_PENSION_ENTRY_START;
2949: hr_utility.trace('L_PENSION_ENTRY_START:'||L_PENSION_ENTRY_START);
2950: --Get Link Id
2951: P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => P_ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2952: hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2953: IF P_ELEMENT_LINK_ID IS NULL THEN
2954: hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||P_ASSIGNMENT_ID ||' as on '||L_PENSION_ENTRY_START);
2955: --raise_application_error(-20001,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||P_ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2956: populate_run_msg(P_ASSIGNMENT_ACTION_ID,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment Number ' ||NVL(get_assg_number(P_ASSIGNMENT_ID),'N/A') ||' as on '||
2950: --Get Link Id
2951: P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => P_ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2952: hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2953: IF P_ELEMENT_LINK_ID IS NULL THEN
2954: hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||P_ASSIGNMENT_ID ||' as on '||L_PENSION_ENTRY_START);
2955: --raise_application_error(-20001,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||P_ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2956: populate_run_msg(P_ASSIGNMENT_ACTION_ID,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment Number ' ||NVL(get_assg_number(P_ASSIGNMENT_ID),'N/A') ||' as on '||
2957: L_PENSION_ENTRY_START||'.');
2958: RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
2959: ELSE
2960: --Insert Pension Element
2961: --Mode if -GB_VALIDATE Then Donot Insert the Element
2962: SAVEPOINT insert_element_transaction;
2963: hr_utility.trace('VAlues Before .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
2964: HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => P_ASSIGNMENT_ID,
2965: P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
2966: hr_utility.trace('VAlues After .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
2967: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
2962: SAVEPOINT insert_element_transaction;
2963: hr_utility.trace('VAlues Before .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
2964: HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => P_ASSIGNMENT_ID,
2965: P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
2966: hr_utility.trace('VAlues After .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
2967: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
2968: ROLLBACK TO insert_element_transaction;
2969: END IF;--Rollback to the savepoint 'insert_element_transaction' if not in Commit Mode.
2970: IF (V_EFF_END_DATE
2967: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
2968: ROLLBACK TO insert_element_transaction;
2969: END IF;--Rollback to the savepoint 'insert_element_transaction' if not in Commit Mode.
2970: IF (V_EFF_END_DATE
2972: populate_warn_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' has been attached to the Assignment Number '||NVL(get_assg_number(P_ASSIGNMENT_ID),'N/A')||' effective from '||
2973: L_PENSION_ENTRY_START||' to ' ||V_EFF_END_DATE||'. Please review the reason for end date not being extended beyond '||V_EFF_END_DATE ||'.');
2974: END IF;
2975: END IF;
2983: OPEN C_PENSION_ENTRY_START(I.PAYROLL_ID,L_SERVICE_START,I.EFFECTIVE_START_DATE);
2984: FETCH C_PENSION_ENTRY_START INTO L_PENSION_ENTRY_START;
2985: CLOSE C_PENSION_ENTRY_START;
2986: --Get Link Id
2987: hr_utility.trace('Inside Aggregation Insert Pension Element');
2988: P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => I.ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2989: hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2990: IF P_ELEMENT_LINK_ID IS NULL THEN
2991: hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2985: CLOSE C_PENSION_ENTRY_START;
2986: --Get Link Id
2987: hr_utility.trace('Inside Aggregation Insert Pension Element');
2988: P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => I.ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2989: hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2990: IF P_ELEMENT_LINK_ID IS NULL THEN
2991: hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2992: -- raise_application_error(-20001,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2993: populate_run_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment Number ' ||NVL(get_assg_number(I.ASSIGNMENT_ID),'N/A')||' as on '
2987: hr_utility.trace('Inside Aggregation Insert Pension Element');
2988: P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => I.ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2989: hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2990: IF P_ELEMENT_LINK_ID IS NULL THEN
2991: hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2992: -- raise_application_error(-20001,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2993: populate_run_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment Number ' ||NVL(get_assg_number(I.ASSIGNMENT_ID),'N/A')||' as on '
2994: ||L_PENSION_ENTRY_START||'.');
2995: RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
2996: ELSE
2997: --Insert Pension Element
2998: --Mode if -GB_VALIDATE Then Donot Insert the Element
2999: SAVEPOINT insert_element_transaction_agg;
3000: hr_utility.trace('VAlues Before .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
3001: HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => I.ASSIGNMENT_ID,
3002: P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
3003: hr_utility.trace('VAlues After .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
3004: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
2999: SAVEPOINT insert_element_transaction_agg;
3000: hr_utility.trace('VAlues Before .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
3001: HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => I.ASSIGNMENT_ID,
3002: P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
3003: hr_utility.trace('VAlues After .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
3004: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
3005: ROLLBACK TO insert_element_transaction_agg;
3006: END IF;--Rollback to the savepoint 'insert_element_transaction_agg' if not in Commit Mode.
3007: IF (V_EFF_END_DATE
3004: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
3005: ROLLBACK TO insert_element_transaction_agg;
3006: END IF;--Rollback to the savepoint 'insert_element_transaction_agg' if not in Commit Mode.
3007: IF (V_EFF_END_DATE
3009: populate_warn_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' has been attached to the Assignment Number '||NVL(get_assg_number(I.ASSIGNMENT_ID),'N/A')||' effective from '||
3010: L_PENSION_ENTRY_START||' to ' ||V_EFF_END_DATE||'. Please review the reason for end date not being extended beyond '||V_EFF_END_DATE ||'.');
3011: END IF;
3012: END IF;
3014: END IF;--Aggregated
3015: --######################################################
3016: EXCEPTION
3017: WHEN OTHERS THEN
3018: hr_utility.trace('Error While Inserting Pension Element');
3019: hr_utility.trace('SQLERRM'||sqlerrm);
3020: RAISE;
3021: END INSERT_PENSION_ELEMENT;
3022: --------------------------------------------------------------------------------------------------
3015: --######################################################
3016: EXCEPTION
3017: WHEN OTHERS THEN
3018: hr_utility.trace('Error While Inserting Pension Element');
3019: hr_utility.trace('SQLERRM'||sqlerrm);
3020: RAISE;
3021: END INSERT_PENSION_ELEMENT;
3022: --------------------------------------------------------------------------------------------------
3023: /*
3066: AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
3067: AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
3068: AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
3069: BEGIN
3070: hr_utility.trace('Inside the Procedure AGGREGATED');
3071: FOR I IN C_ALL_ASSIGNMENTS_AGG(L_PERSON_ID)
3072: LOOP
3073: P_PENSION_INPUT_VALUES_DUMMY := NULL ;
3074: hr_utility.trace('---------------------------------------------------------------------------------------------');
3070: hr_utility.trace('Inside the Procedure AGGREGATED');
3071: FOR I IN C_ALL_ASSIGNMENTS_AGG(L_PERSON_ID)
3072: LOOP
3073: P_PENSION_INPUT_VALUES_DUMMY := NULL ;
3074: hr_utility.trace('---------------------------------------------------------------------------------------------');
3075: OPEN C_PENSION_INPUT_VALUES(I.ELEMENT_ENTRY_ID);
3076: FETCH C_PENSION_INPUT_VALUES INTO P_PENSION_INPUT_VALUES_DUMMY;
3077: CLOSE C_PENSION_INPUT_VALUES;
3078: UPDATE_INFO_ELEMENT( I.ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
3081: L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, P_PENSION_INPUT_VALUES_DUMMY.G_MAIN_ENTRY -- Main Entry has to be retained as before
3082: , G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS , G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID ,
3083: /*Bug 14622818*/
3084: to_char (l_eligible_jh_date ,fnd_date.name_in_mask) );
3085: hr_utility.trace('---------------------------------------------------------------------------------------------');
3086: END LOOP;
3087: END;
3088: PROCEDURE WRITE_OUTPUT
3089: IS
3240: l_act_time_period_id NUMBER;
3241: l_act_business_id NUMBER;
3242: --Bug 14370667
3243: BEGIN
3244: hr_utility.set_location('Entering '|| l_proc, 10);
3245: G_PAYROLL_ACTION_ID := p_payroll_action_id;
3246: FETCH_DATA();
3247: --Bug 14370667
3248: hr_utility.trace ('Debugging: Before 14370667 Updating');
3244: hr_utility.set_location('Entering '|| l_proc, 10);
3245: G_PAYROLL_ACTION_ID := p_payroll_action_id;
3246: FETCH_DATA();
3247: --Bug 14370667
3248: hr_utility.trace ('Debugging: Before 14370667 Updating');
3249: OPEN CSR_ACT_CP_INFO;
3250: FETCH CSR_ACT_CP_INFO
3251: INTO l_act_payroll_id,
3252: l_act_time_period_id,
3251: INTO l_act_payroll_id,
3252: l_act_time_period_id,
3253: l_act_business_id;
3254: CLOSE CSR_ACT_CP_INFO;
3255: hr_utility.trace('Debugging - l_act_payroll_id: '||l_act_payroll_id);
3256: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3257: hr_utility.trace('Debugging - l_act_business_id: '||l_act_business_id);
3258: UPDATE pay_payroll_actions
3259: SET time_period_id = l_act_time_period_id ,
3252: l_act_time_period_id,
3253: l_act_business_id;
3254: CLOSE CSR_ACT_CP_INFO;
3255: hr_utility.trace('Debugging - l_act_payroll_id: '||l_act_payroll_id);
3256: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3257: hr_utility.trace('Debugging - l_act_business_id: '||l_act_business_id);
3258: UPDATE pay_payroll_actions
3259: SET time_period_id = l_act_time_period_id ,
3260: payroll_id = l_act_payroll_id ,
3253: l_act_business_id;
3254: CLOSE CSR_ACT_CP_INFO;
3255: hr_utility.trace('Debugging - l_act_payroll_id: '||l_act_payroll_id);
3256: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3257: hr_utility.trace('Debugging - l_act_business_id: '||l_act_business_id);
3258: UPDATE pay_payroll_actions
3259: SET time_period_id = l_act_time_period_id ,
3260: payroll_id = l_act_payroll_id ,
3261: date_earned = G_PAYROLL_END_DATE ,
3261: date_earned = G_PAYROLL_END_DATE ,
3262: effective_date = G_PAYROLL_DATE_PAID
3263: WHERE payroll_action_id = p_payroll_action_id
3264: AND BUSINESS_GROUP_ID =l_act_business_id;
3265: hr_utility.trace ('Debugging: After 14370667 Updating');
3266: --Bug 14370667
3267: -- Included Payroll Level
3268: -- Get Default Pension Scheme --Payroll Level
3269: OPEN C_GET_PAYROLL_PENSION_INFO;
3268: -- Get Default Pension Scheme --Payroll Level
3269: OPEN C_GET_PAYROLL_PENSION_INFO;
3270: FETCH C_GET_PAYROLL_PENSION_INFO INTO G_DEFAULT_SCHEME_NAME;
3271: CLOSE C_GET_PAYROLL_PENSION_INFO;
3272: hr_utility.trace('PAYROLL Level Scheme Name::'||G_DEFAULT_SCHEME_NAME );
3273: --Fetch Data() would have assigned the default details to the globals
3274: --If there is any at Payroll Level it will be over-ridden here
3275: IF (G_DEFAULT_SCHEME_NAME IS NOT NULL) THEN
3276: OPEN C_GET_PENSION_ELEMENTS(G_DEFAULT_SCHEME_NAME);
3277: FETCH C_GET_PENSION_ELEMENTS
3278: INTO G_DEFAULT_SCH_ELEMENT_NAME,
3279: G_EMPLOYER_COMPONENT;
3280: CLOSE C_GET_PENSION_ELEMENTS;
3281: hr_utility.trace('PAYROLL Level Scheme Name Details ::'||G_DEFAULT_SCH_ELEMENT_NAME||' Employer::'||G_EMPLOYER_COMPONENT);
3282: END IF;
3283: -- Included Payroll Level
3284: --If Only at Default
3285: IF (G_DEFAULT_SCHEME_NAME IS NULL) THEN
3282: END IF;
3283: -- Included Payroll Level
3284: --If Only at Default
3285: IF (G_DEFAULT_SCHEME_NAME IS NULL) THEN
3286: hr_utility.trace('No Default Scheme Found for the Payroll.');
3287: --Get the Default Pension Scheme
3288: OPEN C_GET_DEFAULT_PENSION_INFO ;
3289: FETCH C_GET_DEFAULT_PENSION_INFO
3290: INTO G_DEFAULT_SCHEME_NAME,
3297: IF (G_DEF_FLAG='Y') THEN
3298: conv_configval_to_table(); -- All Configuration Values under Qualifying Schemes Will be saved into g_collecttab
3299: END IF;
3300: END IF;
3301: hr_utility.set_location('Leaving '|| l_proc, 10);
3302: EXCEPTION
3303: WHEN OTHERS THEN
3304: hr_utility.trace('Error occured in archinit');
3305: RAISE;
3300: END IF;
3301: hr_utility.set_location('Leaving '|| l_proc, 10);
3302: EXCEPTION
3303: WHEN OTHERS THEN
3304: hr_utility.trace('Error occured in archinit');
3305: RAISE;
3306: END archinit;
3307: --
3308: PROCEDURE range_cursor(
3310: sqlstr OUT NOCOPY VARCHAR2)
3311: IS
3312: l_proc CONSTANT VARCHAR2(35):= 'range_cursor';
3313: BEGIN
3314: hr_utility.set_location('Entering: '||l_proc,1);
3315: G_PAYROLL_ACTION_ID := pactid;
3316: FETCH_DATA();
3317: /* IF G_PENSION_STAGING_DATE IS NULL THEN
3318: fnd_file.put_line(fnd_file.OUTPUT,'Staging Date has not been Set.');
3331: ELSIF G_PAYROLL_ID IS NOT NULL THEN
3332: */ -- bug 14813352
3333: IF G_PAYROLL_ID IS NOT NULL THEN
3334: -- Payroll ID used in param, restrict by this.
3335: hr_utility.set_location(l_proc,20);
3336: sqlstr := 'select distinct paaf.person_id '|| 'from pay_payroll_actions ppa, '|| 'per_all_assignments_f paaf '|| 'where ppa.payroll_action_id = :payroll_action_id '||
3337: 'and paaf.business_group_id + 0 = ppa.business_group_id '|| 'and paaf.payroll_id = '||G_PAYROLL_ID|| ' order by paaf.person_id';
3338: ELSE
3339: sqlstr := 'select distinct person_id '|| 'from per_people_f ppf, '|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '|| 'and ppa.business_group_id = ppf.business_group_id '||
3338: ELSE
3339: sqlstr := 'select distinct person_id '|| 'from per_people_f ppf, '|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '|| 'and ppa.business_group_id = ppf.business_group_id '||
3340: 'order by ppf.person_id';
3341: END IF;
3342: hr_utility.trace(' Payroll Action Id : '||pactid);
3343: hr_utility.trace(' Range Cursor Statement : '||sqlstr);
3344: hr_utility.set_location(' Leaving: '||l_proc,100);
3345: /*
3346: OPEN C_GET_DEFAULT_PENSION_INFO ;
3339: sqlstr := 'select distinct person_id '|| 'from per_people_f ppf, '|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '|| 'and ppa.business_group_id = ppf.business_group_id '||
3340: 'order by ppf.person_id';
3341: END IF;
3342: hr_utility.trace(' Payroll Action Id : '||pactid);
3343: hr_utility.trace(' Range Cursor Statement : '||sqlstr);
3344: hr_utility.set_location(' Leaving: '||l_proc,100);
3345: /*
3346: OPEN C_GET_DEFAULT_PENSION_INFO ;
3347: FETCH C_GET_DEFAULT_PENSION_INFO
3340: 'order by ppf.person_id';
3341: END IF;
3342: hr_utility.trace(' Payroll Action Id : '||pactid);
3343: hr_utility.trace(' Range Cursor Statement : '||sqlstr);
3344: hr_utility.set_location(' Leaving: '||l_proc,100);
3345: /*
3346: OPEN C_GET_DEFAULT_PENSION_INFO ;
3347: FETCH C_GET_DEFAULT_PENSION_INFO
3348: INTO
3350: G_DEFAULT_SCH_ELEMENT_NAME,
3351: G_EMPLOYER_COMPONENT;
3352: IF C_GET_DEFAULT_PENSION_INFO%NOTFOUND THEN
3353: --ERROR
3354: hr_utility.trace('No Default Pension Scheme has been set up.');
3355: fnd_file.put_line(fnd_file.OUTPUT,'No Default Pension Scheme has been set up.');
3356: RAISE_APPLICATION_ERROR(-20001,'No Default Pension Scheme has been set up.');
3357: END IF;
3358: CLOSE C_GET_DEFAULT_PENSION_INFO;
3389: AND G_PRP_END_DATE >= PAAF.EFFECTIVE_START_DATE
3390: AND G_PRP_START_DATE <= PAAF.EFFECTIVE_END_DATE
3391: ORDER BY ASSIGNMENT_ID ;
3392: BEGIN
3393: hr_utility.set_location('Entering: '||l_proc,1);
3394: G_PAYROLL_ACTION_ID := pactid;
3395: FETCH_DATA();
3396: hr_utility.trace('stperson '||stperson ||'endperson '||endperson);
3397: /*
3392: BEGIN
3393: hr_utility.set_location('Entering: '||l_proc,1);
3394: G_PAYROLL_ACTION_ID := pactid;
3395: FETCH_DATA();
3396: hr_utility.trace('stperson '||stperson ||'endperson '||endperson);
3397: /*
3398: OPEN C_GET_DEFAULT_PENSION_INFO ;
3399: FETCH C_GET_DEFAULT_PENSION_INFO
3400: INTO
3401: L_DEFAULT_SCHEME_NAME,
3402: L_DEFAULT_SCH_ELEMENT_NAME,
3403: L_EMPLOYER_COMPONENT;
3404: IF C_GET_DEFAULT_PENSION_INFO%NOTFOUND THEN
3405: hr_utility.trace('No Default Scheme has been Setup.Please Set it up And then run the process again.');
3406: RAISE L_EXCEPTION;
3407: END IF;
3408: CLOSE C_GET_DEFAULT_PENSION_INFO;
3409: */
3412: -- Get Default Pension Scheme --Payroll Level
3413: OPEN C_GET_PAYROLL_PENSION_INFO;
3414: FETCH C_GET_PAYROLL_PENSION_INFO INTO L_DEFAULT_SCHEME_NAME;
3415: CLOSE C_GET_PAYROLL_PENSION_INFO;
3416: hr_utility.trace('PAYROLL Level Scheme Name::'||L_DEFAULT_SCHEME_NAME );
3417: IF (L_DEFAULT_SCHEME_NAME IS NOT NULL) THEN
3418: OPEN C_GET_PENSION_ELEMENTS(L_DEFAULT_SCHEME_NAME);
3419: FETCH C_GET_PENSION_ELEMENTS
3420: INTO
3420: INTO
3421: L_DEFAULT_SCH_ELEMENT_NAME,
3422: L_EMPLOYER_COMPONENT;
3423: CLOSE C_GET_PENSION_ELEMENTS;
3424: hr_utility.trace('PAYROLL Level Scheme Name Details ::'||L_DEFAULT_SCH_ELEMENT_NAME||' Employer::'||L_EMPLOYER_COMPONENT);
3425: END IF;
3426: ------------------------------------------
3427: IF (L_DEFAULT_SCHEME_NAME IS NULL) THEN
3428: hr_utility.trace('No Default Scheme Found for the Payroll.');
3424: hr_utility.trace('PAYROLL Level Scheme Name Details ::'||L_DEFAULT_SCH_ELEMENT_NAME||' Employer::'||L_EMPLOYER_COMPONENT);
3425: END IF;
3426: ------------------------------------------
3427: IF (L_DEFAULT_SCHEME_NAME IS NULL) THEN
3428: hr_utility.trace('No Default Scheme Found for the Payroll.');
3429: --Get the Default Pension Scheme
3430: OPEN C_GET_DEFAULT_PENSION_INFO ;
3431: FETCH C_GET_DEFAULT_PENSION_INFO
3432: INTO
3441: G_EMPLOYER_COMPONENT := L_EMPLOYER_COMPONENT;
3442: --- Moved to Arch Init*/
3443: FOR ASG_REC IN C_ALL_ASSIGNMENTS_PAYROLL
3444: LOOP
3445: hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
3446: SELECT pay_assignment_actions_s.nextval INTO l_ass_act_id FROM dual;
3447: --
3448: -- insert into pay_assignment_actions.
3449: hr_nonrun_asact.insact(l_ass_act_id, asg_rec.assignment_id, pactid, chunk, NULL);
3450: END LOOP;
3451: EXCEPTION
3452: /*
3453: WHEN L_EXCEPTION THEN
3454: hr_utility.trace('Please Check the Staging Date / Default Pension Scheme if they are properly Set up.');
3455: fnd_file.put_line(fnd_file.OUTPUT,' ');
3456: RAISE_APPLICATION_ERROR (-20001,'Please Check the Staging Date / Default Pension Scheme if they are properly Set up.') ;
3457: */
3458: WHEN OTHERS THEN
3467: l_request_id NUMBER;
3468: l_mode VARCHAR2(30);
3469: BEGIN
3470: IF G_ERROR <>'Y' THEN
3471: hr_utility.set_location('Entering: '||l_proc,1);
3472: --this is a eText report, Spawn the BI Publisher process
3473: hr_utility.trace('Spawn the BI Publisher process');
3474: WRITE_OUTPUT();
3475: /* Commented for bug 14273214 as this values are needed to be viewed in the Payroll view Form
3469: BEGIN
3470: IF G_ERROR <>'Y' THEN
3471: hr_utility.set_location('Entering: '||l_proc,1);
3472: --this is a eText report, Spawn the BI Publisher process
3473: hr_utility.trace('Spawn the BI Publisher process');
3474: WRITE_OUTPUT();
3475: /* Commented for bug 14273214 as this values are needed to be viewed in the Payroll view Form
3476: UPDATE pay_payroll_actions
3477: SET time_period_id = null
3478: , payroll_id = null
3479: , date_earned = null
3480: WHERE payroll_action_id = pactid
3481: and BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID;
3482: hr_utility.trace ('Debugging: Before 14370667 Rolling Back');
3483: */
3484: hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3480: WHERE payroll_action_id = pactid
3481: and BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID;
3482: hr_utility.trace ('Debugging: Before 14370667 Rolling Back');
3483: */
3484: hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488: hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3481: and BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID;
3482: hr_utility.trace ('Debugging: Before 14370667 Rolling Back');
3483: */
3484: hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488: hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3489: hr_utility.trace('Debugging - G_EARN_END_DATE: '||G_EARN_END_DATE);
3482: hr_utility.trace ('Debugging: Before 14370667 Rolling Back');
3483: */
3484: hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488: hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3489: hr_utility.trace('Debugging - G_EARN_END_DATE: '||G_EARN_END_DATE);
3490: ----------------------------SPAWNING--------------------------------------------
3483: */
3484: hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488: hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3489: hr_utility.trace('Debugging - G_EARN_END_DATE: '||G_EARN_END_DATE);
3490: ----------------------------SPAWNING--------------------------------------------
3491: --AUTO ENROLLED REPORT
3484: hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488: hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3489: hr_utility.trace('Debugging - G_EARN_END_DATE: '||G_EARN_END_DATE);
3490: ----------------------------SPAWNING--------------------------------------------
3491: --AUTO ENROLLED REPORT
3492: xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBPAER','en','US','PDF');
3485: hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486: hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487: hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488: hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3489: hr_utility.trace('Debugging - G_EARN_END_DATE: '||G_EARN_END_DATE);
3490: ----------------------------SPAWNING--------------------------------------------
3491: --AUTO ENROLLED REPORT
3492: xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBPAER','en','US','PDF');
3493: IF xml_layout = true THEN
3500: argument3 => fnd_date.date_to_canonical(G_PAYROLL_END_DATE) ,argument4 =>fnd_date.date_to_canonical( G_PRP_START_DATE) ,argument5 => fnd_date.date_to_canonical(G_PRP_END_DATE) ,
3501: argument6 => L_MODE ,argument7 => G_PAYROLL_NAME ,argument8 => G_PERIOD_NAME );
3502: -- Commit;
3503: IF l_request_id = 0 THEN
3504: hr_utility.set_location('Error spawning new process',1);
3505: END IF;
3506: END IF;
3507: --NOT AUTO ENROLLED REPORT
3508: xml_layout :=NULL;
3518: argument3 => fnd_date.date_to_canonical(G_PAYROLL_END_DATE) ,argument4 =>fnd_date.date_to_canonical( G_PRP_START_DATE) ,argument5 => fnd_date.date_to_canonical(G_PRP_END_DATE) ,
3519: argument6 => L_MODE ,argument7 => G_PAYROLL_NAME ,argument8 => G_PERIOD_NAME );
3520: -- Commit;
3521: IF l_request_id = 0 THEN
3522: hr_utility.set_location('Error spawning new process',1);
3523: END IF;
3524: END IF;
3525: --Already Enrolled Employees (Not By Auto Enrollment)
3526: --NOT AUTO ENROLLED REPORT
3537: argument3 => fnd_date.date_to_canonical(G_PAYROLL_END_DATE) ,argument4 =>fnd_date.date_to_canonical( G_PRP_START_DATE) ,argument5 => fnd_date.date_to_canonical(G_PRP_END_DATE) ,
3538: argument6 => L_MODE ,argument7 => G_PAYROLL_NAME ,argument8 => G_PERIOD_NAME );
3539: -- Commit;
3540: IF l_request_id = 0 THEN
3541: hr_utility.set_location('Error spawning new process',1);
3542: END IF;
3543: END IF;
3544: ----------------------------SPAWNING--------------------------------------------
3545: hr_utility.set_location('Leaving: '||l_proc,999);
3541: hr_utility.set_location('Error spawning new process',1);
3542: END IF;
3543: END IF;
3544: ----------------------------SPAWNING--------------------------------------------
3545: hr_utility.set_location('Leaving: '||l_proc,999);
3546: END IF;
3547: END deinitialization_code;
3548:
3549: FUNCTION CAL_STAGING_DATE_EARNINGS(
3638: L_PRP_START_DATE date;
3639: L_PRP_END_DATE date;
3640: L_EFFECTIVE_DATE date;
3641: BEGIN
3642: hr_utility.trace('Inside CALCULATE_EARNINGS');
3643: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
3644: hr_utility.trace('Person_id '||P_PERSON_ID);
3645: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
3646: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
3639: L_PRP_END_DATE date;
3640: L_EFFECTIVE_DATE date;
3641: BEGIN
3642: hr_utility.trace('Inside CALCULATE_EARNINGS');
3643: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
3644: hr_utility.trace('Person_id '||P_PERSON_ID);
3645: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
3646: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
3647: ---#######################################################################################################
3640: L_EFFECTIVE_DATE date;
3641: BEGIN
3642: hr_utility.trace('Inside CALCULATE_EARNINGS');
3643: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
3644: hr_utility.trace('Person_id '||P_PERSON_ID);
3645: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
3646: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
3647: ---#######################################################################################################
3648: open C_STAGING_PRP_PERIOD;
3641: BEGIN
3642: hr_utility.trace('Inside CALCULATE_EARNINGS');
3643: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
3644: hr_utility.trace('Person_id '||P_PERSON_ID);
3645: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
3646: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
3647: ---#######################################################################################################
3648: open C_STAGING_PRP_PERIOD;
3649: fetch C_STAGING_PRP_PERIOD into L_PRP_START_DATE, L_PRP_END_DATE, L_EFFECTIVE_DATE;
3642: hr_utility.trace('Inside CALCULATE_EARNINGS');
3643: hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
3644: hr_utility.trace('Person_id '||P_PERSON_ID);
3645: hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
3646: hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
3647: ---#######################################################################################################
3648: open C_STAGING_PRP_PERIOD;
3649: fetch C_STAGING_PRP_PERIOD into L_PRP_START_DATE, L_PRP_END_DATE, L_EFFECTIVE_DATE;
3650: close C_STAGING_PRP_PERIOD;
3650: close C_STAGING_PRP_PERIOD;
3651: IF (P_AGGR_FLAG='N') THEN
3652: --Fetch Value from Balance
3653: L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
3654: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
3655: hr_utility.trace('L_BALANCE_FLAG: '||L_BALANCE_FLAG);
3656: IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
3657: /*To handle Payroll Change and if the new payroll
3658: has no payroll-assg actions and if the previous payroll has payroll/assg
3651: IF (P_AGGR_FLAG='N') THEN
3652: --Fetch Value from Balance
3653: L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
3654: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
3655: hr_utility.trace('L_BALANCE_FLAG: '||L_BALANCE_FLAG);
3656: IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
3657: /*To handle Payroll Change and if the new payroll
3658: has no payroll-assg actions and if the previous payroll has payroll/assg
3659: actions within the PRP*/
3656: IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
3657: /*To handle Payroll Change and if the new payroll
3658: has no payroll-assg actions and if the previous payroll has payroll/assg
3659: actions within the PRP*/
3660: hr_utility.trace('To Fetch datepaids which donot have an assignment action');
3661: FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED(L_PRP_END_DATE,L_PRP_START_DATE)
3662: LOOP
3663: hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
3664: hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
3659: actions within the PRP*/
3660: hr_utility.trace('To Fetch datepaids which donot have an assignment action');
3661: FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED(L_PRP_END_DATE,L_PRP_START_DATE)
3662: LOOP
3663: hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
3664: hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
3665: FOR I IN C_ELEMENT_DETAILS(L_DATE_PAID_MISSED.END_DATE,P_ASSIGNMENT_ID,NULL)
3666: LOOP
3667: L_SCREEN_ENTRY_VALUE := NULL;
3660: hr_utility.trace('To Fetch datepaids which donot have an assignment action');
3661: FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED(L_PRP_END_DATE,L_PRP_START_DATE)
3662: LOOP
3663: hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
3664: hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
3665: FOR I IN C_ELEMENT_DETAILS(L_DATE_PAID_MISSED.END_DATE,P_ASSIGNMENT_ID,NULL)
3666: LOOP
3667: L_SCREEN_ENTRY_VALUE := NULL;
3668: L_FORMULA_ID := NULL;
3676: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_DATE_PAID_MISSED.END_DATE);
3677: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
3678: CLOSE C_PENSIONS_FORMULA_ID;
3679: IF L_FORMULA_ID IS NOT NULL THEN
3680: HR_UTILITY.trace('Formula Id Not Null');
3681: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3682: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3683: END IF;
3684: ELSE --Screen Entry Value not Null
3678: CLOSE C_PENSIONS_FORMULA_ID;
3679: IF L_FORMULA_ID IS NOT NULL THEN
3680: HR_UTILITY.trace('Formula Id Not Null');
3681: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3682: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3683: END IF;
3684: ELSE --Screen Entry Value not Null
3685: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3686: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3682: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3683: END IF;
3684: ELSE --Screen Entry Value not Null
3685: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3686: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3687: END IF;
3688: END LOOP;
3689: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_DATE_PAID_MISSED.END_DATE,P_assactid,NULL);
3690: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3686: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3687: END IF;
3688: END LOOP;
3689: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_DATE_PAID_MISSED.END_DATE,P_assactid,NULL);
3690: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3691: END LOOP;
3692: END IF;--Balance Found
3693: --No Balance Found
3694: IF (L_BALANCE_FLAG ='N') THEN
3706: OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_EFFECTIVE_DATE);
3707: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
3708: CLOSE C_PENSIONS_FORMULA_ID;
3709: IF L_FORMULA_ID IS NOT NULL THEN
3710: HR_UTILITY.trace('Formula Id Not Null');
3711: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3712: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3713: END IF;
3714: ELSE --Screen Entry Value not Null
3708: CLOSE C_PENSIONS_FORMULA_ID;
3709: IF L_FORMULA_ID IS NOT NULL THEN
3710: HR_UTILITY.trace('Formula Id Not Null');
3711: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3712: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3713: END IF;
3714: ELSE --Screen Entry Value not Null
3715: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3716: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3712: HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3713: END IF;
3714: ELSE --Screen Entry Value not Null
3715: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3716: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3717: END IF;
3718: END LOOP;
3719: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_EFFECTIVE_DATE,P_assactid,NULL);
3720: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3716: HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3717: END IF;
3718: END LOOP;
3719: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_EFFECTIVE_DATE,P_assactid,NULL);
3720: HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3721: END IF;--Balance Flag
3722: END IF;
3723: ---#######################################################################################################
3724: --AGGREGATION
3723: ---#######################################################################################################
3724: --AGGREGATION
3725: ---#######################################################################################################
3726: IF (P_AGGR_FLAG='Y') THEN
3727: HR_UTILITY.trace('Aggregation Case - Calculate_earnings');
3728: FOR I IN C_ASSIGNMENTS_AGG(L_PRP_END_DATE)
3729: LOOP
3730: IF (I.PAYROLL_ID =G_PAYROLL_ID) THEN
3731: L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE+CALCULATE_EARNINGS(I.ASSIGNMENT_ID,'N',P_PERSON_ID,G_EARN_END_DATE,p_assactid);
3728: FOR I IN C_ASSIGNMENTS_AGG(L_PRP_END_DATE)
3729: LOOP
3730: IF (I.PAYROLL_ID =G_PAYROLL_ID) THEN
3731: L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE+CALCULATE_EARNINGS(I.ASSIGNMENT_ID,'N',P_PERSON_ID,G_EARN_END_DATE,p_assactid);
3732: HR_UTILITY.trace('Same Payroll ID - L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
3733: ELSE --Main Entry and this asg are of different payrolls
3734: FOR J IN C_EARN_PERIOD_DETAILS(I.PAYROLL_ID)
3735: LOOP
3736: L_BALANCE_FLAG :='N'; --Reset Flag
3734: FOR J IN C_EARN_PERIOD_DETAILS(I.PAYROLL_ID)
3735: LOOP
3736: L_BALANCE_FLAG :='N'; --Reset Flag
3737: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE +CALCULATE_ASG_ACTIONS (P_ASSIGNMENT_ID ,P_ASSACTID ,L_BALANCE_FLAG ,J.TIME_PERIOD_ID );
3738: hr_utility.trace('BALANCE Flag:'||L_BALANCE_FLAG);
3739: IF (L_BALANCE_FLAG ='N') THEN-- If Balance not found
3740: FOR X IN C_ELEMENT_DETAILS(J.END_DATE,I.ASSIGNMENT_ID,NULL)
3741: LOOP
3742: hr_utility.trace('Inside Different Payroll Aggregated Case--and balance not Found');
3738: hr_utility.trace('BALANCE Flag:'||L_BALANCE_FLAG);
3739: IF (L_BALANCE_FLAG ='N') THEN-- If Balance not found
3740: FOR X IN C_ELEMENT_DETAILS(J.END_DATE,I.ASSIGNMENT_ID,NULL)
3741: LOOP
3742: hr_utility.trace('Inside Different Payroll Aggregated Case--and balance not Found');
3743: L_SCREEN_ENTRY_VALUE := NULL;
3744: L_FORMULA_ID :=NULL;
3745: p_pay_value_ivid :=NULL;
3746: L_STATUS_PROCES_ID :=NULL;
3753: FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
3754: CLOSE C_PENSIONS_FORMULA_ID;
3755: END IF;
3756: IF L_FORMULA_ID IS NOT NULL THEN
3757: HR_UTILITY.trace(' Inside run Element FORMULA '||L_FORMULA_ID);
3758: -- setting the contexts
3759: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3760: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3761: ELSE
3756: IF L_FORMULA_ID IS NOT NULL THEN
3757: HR_UTILITY.trace(' Inside run Element FORMULA '||L_FORMULA_ID);
3758: -- setting the contexts
3759: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3760: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3761: ELSE
3762: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3763: hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3764: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3759: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3760: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3761: ELSE
3762: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3763: hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3764: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3765: END IF;
3766: END LOOP;
3767: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
3760: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3761: ELSE
3762: L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3763: hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3764: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3765: END IF;
3766: END LOOP;
3767: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
3768: hr_utility.trace('Information part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3764: HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3765: END IF;
3766: END LOOP;
3767: L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
3768: hr_utility.trace('Information part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3769: END IF;-- If Balance not found
3770: END LOOP;-- For every Date Paid in that Payroll
3771: END IF; -- Same Payroll Id as Main Entry
3772: END LOOP;
3770: END LOOP;-- For every Date Paid in that Payroll
3771: END IF; -- Same Payroll Id as Main Entry
3772: END LOOP;
3773: END IF;
3774: HR_UTILITY.trace(' Total value from all Pension Element values = '||L_TOTAL_EARNINGS_VALUE);
3775: RETURN L_TOTAL_EARNINGS_VALUE;
3776: EXCEPTION
3777: WHEN OTHERS THEN
3778: HR_UTILITY.trace(SQLERRM);
3774: HR_UTILITY.trace(' Total value from all Pension Element values = '||L_TOTAL_EARNINGS_VALUE);
3775: RETURN L_TOTAL_EARNINGS_VALUE;
3776: EXCEPTION
3777: WHEN OTHERS THEN
3778: HR_UTILITY.trace(SQLERRM);
3779: RAISE;
3780: END CAL_STAGING_DATE_EARNINGS;
3781:
3782:
3880: ELSIF G_PAYROLL_PERIOD_TYPE = 'Bi-Month' THEN -- Monthly's Threshold multiplied by 2
3881: G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
3882: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
3883: END IF;
3884: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
3885: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
3886: hr_utility.trace('L_AGE:'||L_AGE);
3887: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
3888:
3881: G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
3882: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
3883: END IF;
3884: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
3885: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
3886: hr_utility.trace('L_AGE:'||L_AGE);
3887: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
3888:
3889:
3882: G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
3883: END IF;
3884: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
3885: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
3886: hr_utility.trace('L_AGE:'||L_AGE);
3887: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
3888:
3889:
3890: /* 16409174 - PAE THRESHOLD CHANGES start*/
3883: END IF;
3884: hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
3885: hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
3886: hr_utility.trace('L_AGE:'||L_AGE);
3887: hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
3888:
3889:
3890: /* 16409174 - PAE THRESHOLD CHANGES start*/
3891:
3903: G_GLOBAL_VALUE := NULL;
3904: OPEN C_GET_GLOBAL_VALUE(L_EFFECTIVE_DATE);
3905: FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
3906: CLOSE C_GET_GLOBAL_VALUE;
3907: hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
3908: end if;
3909: /* 16409174 - PAE THRESHOLD CHANGES end*/
3910:
3911: /*Bug 14645724: Age check criteria modified*/
4276: L_EXCEPTION EXCEPTION;-- Unsuccessful.
4277: L_EXCEPTION_UPDATE EXCEPTION;--Update the Pension Info Element and also archive the assignment details.
4278: L_EXCEPTION_UPDATE_INFO EXCEPTION;--Updates ONLY the Pension Info Element - Does not archive the data.
4279: BEGIN
4280: hr_utility.trace('ARCHIVE CODE');
4281: BEGIN -- Inner Section
4282: --- Get The Assignment's Details
4283: OPEN C_ASSIGNMENT_DETAILS;
4284: FETCH C_ASSIGNMENT_DETAILS
4286: L_ASSIGNMENT_ID,
4287: L_ELEMENT_ENTRY_ID,
4288: L_ASSIGNMENT_NUMBER;
4289: IF C_ASSIGNMENT_DETAILS%NOTFOUND THEN
4290: hr_utility.trace('No Data(Element Entry) Found for Pension Information for the Assignment Action ID :'||p_assactid);
4291: populate_run_msg(p_assactid,'No Data Found for Pensions Information for the Assignment.');
4292: L_FLAG_TEMP := TRUE;
4293: END IF;
4294: CLOSE C_ASSIGNMENT_DETAILS;
4301: fetch C_EMP_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4302: if C_EMP_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4303: -- employee level pensions staging date
4304: G_PENSION_STAGING_DATE := L_PENSION_STAGING_DATE_TEMP;
4305: hr_utility.trace('Employee level G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4306: else
4307: L_PENSION_STAGING_DATE_TEMP := null;
4308: open C_PAYROLL_PENSION_STAGING_DATE;
4309: fetch C_PAYROLL_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4309: fetch C_PAYROLL_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4310: if C_PAYROLL_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4311: -- payroll_level pensions staging date
4312: G_PENSION_STAGING_DATE := L_PENSION_STAGING_DATE_TEMP;
4313: hr_utility.trace('Payroll level G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4314: else
4315: open C_PAYEE_PENSION_STAGING_DATE;
4316: fetch C_PAYEE_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4317: if C_PAYEE_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4316: fetch C_PAYEE_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4317: if C_PAYEE_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4318: -- employer level pensions staging date
4319: G_PENSION_STAGING_DATE := L_PENSION_STAGING_DATE_TEMP;
4320: hr_utility.trace('Payee level G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4321: else
4322: -- no pensions staging date found
4323: G_PENSION_STAGING_DATE := null;
4324: end if;
4327: close C_PAYROLL_PENSION_STAGING_DATE;
4328: end if;
4329: close C_EMP_PENSION_STAGING_DATE;
4330:
4331: hr_utility.trace('Final G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4332:
4333: IF G_PENSION_STAGING_DATE IS NULL THEN
4334: populate_run_msg(p_assactid,'Staging Date has not been set for this employee at any level, hence this assignment is being skipped.');
4335: hr_utility.trace('Staging Date has not been Set.');
4331: hr_utility.trace('Final G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4332:
4333: IF G_PENSION_STAGING_DATE IS NULL THEN
4334: populate_run_msg(p_assactid,'Staging Date has not been set for this employee at any level, hence this assignment is being skipped.');
4335: hr_utility.trace('Staging Date has not been Set.');
4336: RAISE L_EXCEPTION_NO_ERROR;
4337: END IF;
4338: IF (G_PRP_END_DATE < FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE)) THEN-- Staging Date
4339: populate_run_msg(p_assactid,'Staging Date not yet reached for this employee, hence this assignment is being skipped.');
4336: RAISE L_EXCEPTION_NO_ERROR;
4337: END IF;
4338: IF (G_PRP_END_DATE < FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE)) THEN-- Staging Date
4339: populate_run_msg(p_assactid,'Staging Date not yet reached for this employee, hence this assignment is being skipped.');
4340: hr_utility.trace('Staging Date Not Yet Reached for this employee.');
4341: RAISE L_EXCEPTION_NO_ERROR;
4342: END IF;
4343:
4344: -- Get the Full Name of the Employee
4346: FETCH PERSON_NAME INTO L_FULL_NAME;
4347: CLOSE PERSON_NAME;
4348: --Get Entry Values for the Assignment's Pension Element
4349: P_PENSION_INPUT_VALUES := NULL ;
4350: hr_utility.trace('Outside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4351: OPEN C_PENSION_INPUT_VALUES(L_ELEMENT_ENTRY_ID);
4352: hr_utility.trace('Inside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4353: FETCH C_PENSION_INPUT_VALUES INTO P_PENSION_INPUT_VALUES;
4354: IF C_PENSION_INPUT_VALUES%NOTFOUND THEN
4348: --Get Entry Values for the Assignment's Pension Element
4349: P_PENSION_INPUT_VALUES := NULL ;
4350: hr_utility.trace('Outside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4351: OPEN C_PENSION_INPUT_VALUES(L_ELEMENT_ENTRY_ID);
4352: hr_utility.trace('Inside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4353: FETCH C_PENSION_INPUT_VALUES INTO P_PENSION_INPUT_VALUES;
4354: IF C_PENSION_INPUT_VALUES%NOTFOUND THEN
4355: hr_utility.trace('No Data( Entry Values) Found for Pension Information for the Assignment Action ID :'||p_assactid);
4356: populate_run_msg(p_assactid,'No Data Found for Pensions Information for the Assignment.');
4351: OPEN C_PENSION_INPUT_VALUES(L_ELEMENT_ENTRY_ID);
4352: hr_utility.trace('Inside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4353: FETCH C_PENSION_INPUT_VALUES INTO P_PENSION_INPUT_VALUES;
4354: IF C_PENSION_INPUT_VALUES%NOTFOUND THEN
4355: hr_utility.trace('No Data( Entry Values) Found for Pension Information for the Assignment Action ID :'||p_assactid);
4356: populate_run_msg(p_assactid,'No Data Found for Pensions Information for the Assignment.');
4357: L_FLAG_TEMP := TRUE;
4358: END IF;
4359: CLOSE C_PENSION_INPUT_VALUES;
4360: if L_FLAG_TEMP then
4361: RAISE L_EXCEPTION;
4362: end if;
4363: ---CHECK
4364: hr_utility.trace('For the Assignment Number :'||L_ASSIGNMENT_NUMBER);
4365: hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4361: RAISE L_EXCEPTION;
4362: end if;
4363: ---CHECK
4364: hr_utility.trace('For the Assignment Number :'||L_ASSIGNMENT_NUMBER);
4365: hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4362: end if;
4363: ---CHECK
4364: hr_utility.trace('For the Assignment Number :'||L_ASSIGNMENT_NUMBER);
4365: hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4363: ---CHECK
4364: hr_utility.trace('For the Assignment Number :'||L_ASSIGNMENT_NUMBER);
4365: hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4364: hr_utility.trace('For the Assignment Number :'||L_ASSIGNMENT_NUMBER);
4365: hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4365: hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4366: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4367: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4368: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4369: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4370: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4378: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4371: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4378: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4379: ---CHECK
4372: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4378: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4379: ---CHECK
4380: --Assignment to Local Variables
4373: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4378: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4379: ---CHECK
4380: --Assignment to Local Variables
4381: L_QUALIFYING_SCHEME_NAME :=P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME;
4374: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4378: hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4379: ---CHECK
4380: --Assignment to Local Variables
4381: L_QUALIFYING_SCHEME_NAME :=P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME;
4382: L_AGGREGATION_FLAG :=P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS;
4399: OPEN C_AGE_BETWEEN_PAYROLL(L_PERSON_ID) ;
4400: FETCH C_AGE_BETWEEN_PAYROLL INTO L_DATE_OF_BIRTH;
4401: CLOSE C_AGE_BETWEEN_PAYROLL;
4402: L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
4403: hr_utility.trace('Twenty Second birthday::'||L_DOB_TT );
4404: hr_utility.trace('L_ELIGIBLE_JH_DATE Value::'||L_ELIGIBLE_JH_DATE);
4405: hr_utility.trace('L_SERVICE_START Value::'||L_SERVICE_START);
4406: hr_utility.trace('L_DOB_TT Value::'||L_DOB_TT);
4407: /* Bug fix 14581138
4400: FETCH C_AGE_BETWEEN_PAYROLL INTO L_DATE_OF_BIRTH;
4401: CLOSE C_AGE_BETWEEN_PAYROLL;
4402: L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
4403: hr_utility.trace('Twenty Second birthday::'||L_DOB_TT );
4404: hr_utility.trace('L_ELIGIBLE_JH_DATE Value::'||L_ELIGIBLE_JH_DATE);
4405: hr_utility.trace('L_SERVICE_START Value::'||L_SERVICE_START);
4406: hr_utility.trace('L_DOB_TT Value::'||L_DOB_TT);
4407: /* Bug fix 14581138
4408: IF G_DEF_FLAG <>'Y' THEN*/
4401: CLOSE C_AGE_BETWEEN_PAYROLL;
4402: L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
4403: hr_utility.trace('Twenty Second birthday::'||L_DOB_TT );
4404: hr_utility.trace('L_ELIGIBLE_JH_DATE Value::'||L_ELIGIBLE_JH_DATE);
4405: hr_utility.trace('L_SERVICE_START Value::'||L_SERVICE_START);
4406: hr_utility.trace('L_DOB_TT Value::'||L_DOB_TT);
4407: /* Bug fix 14581138
4408: IF G_DEF_FLAG <>'Y' THEN*/
4409: hr_utility.trace('G_DEF_FLAG Value::'||NVL(G_DEF_FLAG,'Null'));
4402: L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
4403: hr_utility.trace('Twenty Second birthday::'||L_DOB_TT );
4404: hr_utility.trace('L_ELIGIBLE_JH_DATE Value::'||L_ELIGIBLE_JH_DATE);
4405: hr_utility.trace('L_SERVICE_START Value::'||L_SERVICE_START);
4406: hr_utility.trace('L_DOB_TT Value::'||L_DOB_TT);
4407: /* Bug fix 14581138
4408: IF G_DEF_FLAG <>'Y' THEN*/
4409: hr_utility.trace('G_DEF_FLAG Value::'||NVL(G_DEF_FLAG,'Null'));
4410: IF (NVL(G_DEF_FLAG,'N') <>'Y') THEN
4405: hr_utility.trace('L_SERVICE_START Value::'||L_SERVICE_START);
4406: hr_utility.trace('L_DOB_TT Value::'||L_DOB_TT);
4407: /* Bug fix 14581138
4408: IF G_DEF_FLAG <>'Y' THEN*/
4409: hr_utility.trace('G_DEF_FLAG Value::'||NVL(G_DEF_FLAG,'Null'));
4410: IF (NVL(G_DEF_FLAG,'N') <>'Y') THEN
4411: L_DEFAULT_SCHEME_NAME := G_DEFAULT_SCHEME_NAME;
4412: L_DEFAULT_SCH_ELEMENT_NAME := G_DEFAULT_SCH_ELEMENT_NAME;
4413: L_EMPLOYER_COMPONENT := G_EMPLOYER_COMPONENT;
4417: IF L_QUALIFYING_SCHEME_NAME IS NOT NULL THEN
4418: OPEN C_QUALIFYING_SCHEME_VALID(L_QUALIFYING_SCHEME_NAME);
4419: FETCH C_QUALIFYING_SCHEME_VALID INTO L_TEMP;
4420: IF C_QUALIFYING_SCHEME_VALID%NOTFOUND THEN
4421: hr_utility.trace('Qualifying Scheme Name entered for the assignment is not valid.');
4422: populate_run_msg(p_assactid,'Qualifying Scheme Name entered for the assignment is not valid.');
4423: RAISE L_EXCEPTION;
4424: END IF;
4425: CLOSE C_QUALIFYING_SCHEME_VALID;
4423: RAISE L_EXCEPTION;
4424: END IF;
4425: CLOSE C_QUALIFYING_SCHEME_VALID;
4426: IF (NVL(L_QUALIFYING_SCHEME_EXISTS,'N') ='N') THEN
4427: hr_utility.trace('Qualifying Scheme Name is entered for the assignment, but Qualifying Scheme Exists is not set to Yes.');
4428: populate_run_msg(p_assactid,'Qualifying Scheme Name is entered for the assignment, but Qualifying Scheme Exists is not set to Yes.');
4429: RAISE L_EXCEPTION;
4430: END IF;
4431: END IF;
4428: populate_run_msg(p_assactid,'Qualifying Scheme Name is entered for the assignment, but Qualifying Scheme Exists is not set to Yes.');
4429: RAISE L_EXCEPTION;
4430: END IF;
4431: END IF;
4432: hr_utility.trace('L_POSTPONEMENT_TYPE: '||L_POSTPONEMENT_TYPE);
4433: --Check if PP End Date is present without the PP Type
4434: IF L_POSTPONEMENT_DATE IS NOT NULL AND L_POSTPONEMENT_TYPE IS NULL THEN
4435: hr_utility.trace('Postponement End Date is entered for the assignment, but Postponement Type is not entered.');
4436: populate_run_msg(p_assactid,'Postponement End Date is entered for the assignment, but Postponement Type is not entered.');
4431: END IF;
4432: hr_utility.trace('L_POSTPONEMENT_TYPE: '||L_POSTPONEMENT_TYPE);
4433: --Check if PP End Date is present without the PP Type
4434: IF L_POSTPONEMENT_DATE IS NOT NULL AND L_POSTPONEMENT_TYPE IS NULL THEN
4435: hr_utility.trace('Postponement End Date is entered for the assignment, but Postponement Type is not entered.');
4436: populate_run_msg(p_assactid,'Postponement End Date is entered for the assignment, but Postponement Type is not entered.');
4437: RAISE L_EXCEPTION;
4438: END IF;
4439: -- Check if Postponement End Date is more than three months - if not DB Scheme Postponement
4443: L_MAX_POSTPO_DATE := L_ELIGIBLE_JH_DATE;
4444: ELSE
4445: L_MAX_POSTPO_DATE := GREATEST(L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4446: END IF;
4447: hr_utility.trace('L_MAX_POSTPO_DATE Value::'||L_MAX_POSTPO_DATE);
4448: IF (ADD_MONTHS(L_MAX_POSTPO_DATE,3) - 1 ) < L_POSTPONEMENT_DATE THEN
4449: hr_utility.trace('More than three months postponement - error');
4450: hr_utility.trace('Postponement Period is longer than 3 months. Please Check the Postponement End Date of the assignment.');
4451: populate_run_msg(p_assactid,'Postponement Period is longer than 3 months.');
4445: L_MAX_POSTPO_DATE := GREATEST(L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4446: END IF;
4447: hr_utility.trace('L_MAX_POSTPO_DATE Value::'||L_MAX_POSTPO_DATE);
4448: IF (ADD_MONTHS(L_MAX_POSTPO_DATE,3) - 1 ) < L_POSTPONEMENT_DATE THEN
4449: hr_utility.trace('More than three months postponement - error');
4450: hr_utility.trace('Postponement Period is longer than 3 months. Please Check the Postponement End Date of the assignment.');
4451: populate_run_msg(p_assactid,'Postponement Period is longer than 3 months.');
4452: RAISE L_EXCEPTION;
4453: ELSE
4446: END IF;
4447: hr_utility.trace('L_MAX_POSTPO_DATE Value::'||L_MAX_POSTPO_DATE);
4448: IF (ADD_MONTHS(L_MAX_POSTPO_DATE,3) - 1 ) < L_POSTPONEMENT_DATE THEN
4449: hr_utility.trace('More than three months postponement - error');
4450: hr_utility.trace('Postponement Period is longer than 3 months. Please Check the Postponement End Date of the assignment.');
4451: populate_run_msg(p_assactid,'Postponement Period is longer than 3 months.');
4452: RAISE L_EXCEPTION;
4453: ELSE
4454: hr_utility.trace('less than three months');
4450: hr_utility.trace('Postponement Period is longer than 3 months. Please Check the Postponement End Date of the assignment.');
4451: populate_run_msg(p_assactid,'Postponement Period is longer than 3 months.');
4452: RAISE L_EXCEPTION;
4453: ELSE
4454: hr_utility.trace('less than three months');
4455: END IF;
4456: END IF;-- if not DB Scheme Postponement
4457: --
4458: -- Check if Main Entry is set to Yes for more than one assignments for the same person
4459: IF (NVL(L_MAIN_ENTRY,'N') = 'Y') THEN
4460: OPEN C_MAIN_ENTRY_CHECK(L_PERSON_ID,L_ASSIGNMENT_ID);
4461: FETCH C_MAIN_ENTRY_CHECK INTO L_ASSIGN_NUM_CHECK;
4462: IF C_MAIN_ENTRY_CHECK%FOUND THEN
4463: hr_utility.trace('Main Entry for Aggregation is set to YES for more than one assignment for the same person.');
4464: populate_run_msg(p_assactid,'Main Entry for Aggregation is set to YES for more than one assignment for the same person.');
4465: hr_utility.trace('Assignment Number - '||L_ASSIGN_NUM_CHECK);
4466: RAISE L_EXCEPTION;
4467: END IF;
4461: FETCH C_MAIN_ENTRY_CHECK INTO L_ASSIGN_NUM_CHECK;
4462: IF C_MAIN_ENTRY_CHECK%FOUND THEN
4463: hr_utility.trace('Main Entry for Aggregation is set to YES for more than one assignment for the same person.');
4464: populate_run_msg(p_assactid,'Main Entry for Aggregation is set to YES for more than one assignment for the same person.');
4465: hr_utility.trace('Assignment Number - '||L_ASSIGN_NUM_CHECK);
4466: RAISE L_EXCEPTION;
4467: END IF;
4468: CLOSE C_MAIN_ENTRY_CHECK;
4469: IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
4466: RAISE L_EXCEPTION;
4467: END IF;
4468: CLOSE C_MAIN_ENTRY_CHECK;
4469: IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
4470: hr_utility.trace('Main Entry for Aggregation is set to YES, but Aggregate Earnings is not set to YES.');
4471: populate_run_msg(p_assactid,'Main Entry for Aggregation is set to YES, but Aggregate Earnings is not set to YES.');
4472: RAISE L_EXCEPTION;
4473: END IF;
4474: END IF;
4476:
4477: --DB Scheme Postponement validation - Only for EJH -- Bug 14649941
4478:
4479: IF (L_POSTPONEMENT_DATE IS NULL) AND (L_POSTPONEMENT_TYPE IS NOT NULL) THEN
4480: hr_utility.trace('Postponement Type is entered, but Postponement End Date is not entered.');
4481: populate_run_msg(p_assactid,'Postponement Type is entered, but Postponement End Date is not entered.');
4482: RAISE L_EXCEPTION;
4483: END IF;
4484:
4482: RAISE L_EXCEPTION;
4483: END IF;
4484:
4485: IF (L_POSTPONEMENT_DATE IS NOT NULL) AND (L_POSTPONEMENT_TYPE IS NULL) THEN
4486: hr_utility.trace('Postponement End Date is entered, but Postponement Type is not entered.');
4487: populate_run_msg(p_assactid,'Postponement End Date is entered, but Postponement Type is not entered.');
4488: RAISE L_EXCEPTION;
4489: END IF;
4490:
4488: RAISE L_EXCEPTION;
4489: END IF;
4490:
4491: IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME)= G_END_OF_TIME and L_EMPLOYEE_CLASS_FOR_PENSION = 'Eligible Job Holder' THEN
4492: hr_utility.trace('Pension Classification is set to Eligible Job Holder, but Eligible Job Holder Date is not set correctly.');
4493: populate_run_msg(p_assactid,'Pension Classification is set to Eligible Job Holder, but Eligible Job Holder Date is not set correctly.');
4494: RAISE L_EXCEPTION;
4495: END IF;
4496:
4499: IF (L_POSTPONEMENT_TYPE = 'DB Scheme Postponement') THEN --DB Postponement
4500: IF L_ELIGIBLE_JH_DATE IS NULL AND L_POSTPONEMENT_DATE >=G_PRP_END_DATE THEN -- Never assessed before--First time run
4501:
4502: IF (ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS_TEMP,L_AGE_TEMP,p_assactid))=1 THEN
4503: hr_utility.trace('DB Scheme Postponement is valid. Never assessed but Now assessed');
4504: -- update pensions classsification
4505: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4506: --L_ELIGIBLE_JH_DATE := HR_API.G_EOT;
4507: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4517: END IF;-- Never assessed before--First time run
4518:
4519: IF L_ELIGIBLE_JH_DATE IS NOT NULL AND L_POSTPONEMENT_DATE >=G_PRP_END_DATE THEN -- Already assessed atleast once
4520: IF L_EMPLOYEE_CLASS_FOR_PENSION = 'Eligible Job Holder' THEN
4521: hr_utility.trace('DB Scheme Postponement is valid. - Already Assessed');
4522: --L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4523: RAISE L_EXCEPTION;
4524: ELSE
4525: hr_utility.trace('DB Scheme Postponement is set, but it is invalid as the assignment is not an Eligible Job Holder.');
4521: hr_utility.trace('DB Scheme Postponement is valid. - Already Assessed');
4522: --L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4523: RAISE L_EXCEPTION;
4524: ELSE
4525: hr_utility.trace('DB Scheme Postponement is set, but it is invalid as the assignment is not an Eligible Job Holder.');
4526: populate_run_msg(p_assactid,'DB Scheme Postponement is set, but it is invalid as the assignment is not an Eligible Job Holder.');
4527: END IF;
4528: END IF;-- Already assessed atleast once
4529: END IF;--DB Postponement
4528: END IF;-- Already assessed atleast once
4529: END IF;--DB Postponement
4530: --DB Scheme Postponement validation - Only for EJH -- Bug 14649941
4531: ----------- Logic STARTS
4532: hr_utility.trace('L_AGGREGATION_FLAG Value::'||L_AGGREGATION_FLAG);
4533: --A.E.D. Null
4534: IF (L_AUTO_ENROLLMENT_DATE) IS NULL THEN
4535: --OPT Out date Crossed
4536: -- IF (NVL(L_OPTOUT_DATE,G_END_OF_TIME) > G_PRP_END_DATE) THEN
4534: IF (L_AUTO_ENROLLMENT_DATE) IS NULL THEN
4535: --OPT Out date Crossed
4536: -- IF (NVL(L_OPTOUT_DATE,G_END_OF_TIME) > G_PRP_END_DATE) THEN
4537: --Postponement Check
4538: hr_utility.trace('L_POSTPONEMENT_DATE Value::'||L_POSTPONEMENT_DATE);
4539: IF (NVL(L_POSTPONEMENT_DATE,G_PRP_START_DATE)
4541: hr_utility.trace('Qualifying Scheme Attached???'||L_QUALIFYING_SCHEME_EXISTS);
4542: --Check if It is a Main Entry , if not throw an warning that it has been skipped.
4537: --Postponement Check
4538: hr_utility.trace('L_POSTPONEMENT_DATE Value::'||L_POSTPONEMENT_DATE);
4539: IF (NVL(L_POSTPONEMENT_DATE,G_PRP_START_DATE)
4541: hr_utility.trace('Qualifying Scheme Attached???'||L_QUALIFYING_SCHEME_EXISTS);
4542: --Check if It is a Main Entry , if not throw an warning that it has been skipped.
4543: IF (NVL(L_AGGREGATION_FLAG,'N')='Y' AND NVL(L_MAIN_ENTRY,'N') = 'N') THEN
4544: populate_warn_msg(p_assactid,'The assignment is under Aggregation. The Main Entry for Aggregation is not set to Yes, hence this assignment is being skipped.');
4545: END IF;
4554: IF C_QUALIFYING_SCHEME_EXISTS%FOUND THEN
4555: L_QUALIFYING_SCHEME_EXISTS := 'Y';
4556: L_NOMINAL_FLAG :='Y';
4557: -- RAISE L_EXCEPTION_UPDATE; -- Remove Later as Nominal Date of Enrollment has to be reported
4558: hr_utility.trace('The Assignment already has a Qualifying Scheme Attached');
4559: --Removed the fix for 16303795
4560: ELSE
4561: L_QUALIFYING_SCHEME_EXISTS := 'N';
4562: hr_utility.trace('The Assignment already doesnot have a Qualifying Scheme Attached');
4558: hr_utility.trace('The Assignment already has a Qualifying Scheme Attached');
4559: --Removed the fix for 16303795
4560: ELSE
4561: L_QUALIFYING_SCHEME_EXISTS := 'N';
4562: hr_utility.trace('The Assignment already doesnot have a Qualifying Scheme Attached');
4563: l_qps_on_staging_date := null;
4564: --chk if he was part of a QPS on staging date
4565: open C_QPS_EXISTS_ON_STAGING_DATE(L_ASSIGNMENT_ID);
4566: fetch C_QPS_EXISTS_ON_STAGING_DATE into l_qps_on_staging_date;
4564: --chk if he was part of a QPS on staging date
4565: open C_QPS_EXISTS_ON_STAGING_DATE(L_ASSIGNMENT_ID);
4566: fetch C_QPS_EXISTS_ON_STAGING_DATE into l_qps_on_staging_date;
4567: close C_QPS_EXISTS_ON_STAGING_DATE;
4568: hr_utility.trace('chking if he was part of a QPS on staging date');
4569:
4570: --if l_staging_date_pi > 0 then
4571: if (l_qps_on_staging_date is not null) then
4572: hr_utility.trace('Qualifying Pensions Scheme exists on Staging Date');
4568: hr_utility.trace('chking if he was part of a QPS on staging date');
4569:
4570: --if l_staging_date_pi > 0 then
4571: if (l_qps_on_staging_date is not null) then
4572: hr_utility.trace('Qualifying Pensions Scheme exists on Staging Date');
4573: L_STAGING_DATE_EARNINGS := TRUNC (CAL_STAGING_DATE_EARNINGS(L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_PERSON_ID,p_assactid)+ 0.004 , 2);
4574: hr_utility.trace('L_STAGING_DATE_EARNINGS : '||L_STAGING_DATE_EARNINGS);
4575: L_PENSION_STATUS_SD := CHK_STAGING_DATE_EARNINGS(L_STAGING_DATE_EARNINGS,L_ASSIGNMENT_ID, L_PERSON_ID,L_AGE,L_REASON);
4576: hr_utility.trace('L_PENSION_STATUS_SD : '||L_PENSION_STATUS_SD);
4570: --if l_staging_date_pi > 0 then
4571: if (l_qps_on_staging_date is not null) then
4572: hr_utility.trace('Qualifying Pensions Scheme exists on Staging Date');
4573: L_STAGING_DATE_EARNINGS := TRUNC (CAL_STAGING_DATE_EARNINGS(L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_PERSON_ID,p_assactid)+ 0.004 , 2);
4574: hr_utility.trace('L_STAGING_DATE_EARNINGS : '||L_STAGING_DATE_EARNINGS);
4575: L_PENSION_STATUS_SD := CHK_STAGING_DATE_EARNINGS(L_STAGING_DATE_EARNINGS,L_ASSIGNMENT_ID, L_PERSON_ID,L_AGE,L_REASON);
4576: hr_utility.trace('L_PENSION_STATUS_SD : '||L_PENSION_STATUS_SD);
4577: if (L_PENSION_STATUS_SD = 1) then
4578: hr_utility.trace('Employee was in QPS as an EJH on staging date hence not processing employee');
4572: hr_utility.trace('Qualifying Pensions Scheme exists on Staging Date');
4573: L_STAGING_DATE_EARNINGS := TRUNC (CAL_STAGING_DATE_EARNINGS(L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_PERSON_ID,p_assactid)+ 0.004 , 2);
4574: hr_utility.trace('L_STAGING_DATE_EARNINGS : '||L_STAGING_DATE_EARNINGS);
4575: L_PENSION_STATUS_SD := CHK_STAGING_DATE_EARNINGS(L_STAGING_DATE_EARNINGS,L_ASSIGNMENT_ID, L_PERSON_ID,L_AGE,L_REASON);
4576: hr_utility.trace('L_PENSION_STATUS_SD : '||L_PENSION_STATUS_SD);
4577: if (L_PENSION_STATUS_SD = 1) then
4578: hr_utility.trace('Employee was in QPS as an EJH on staging date hence not processing employee');
4579: raise l_exception;
4580: end if;
4574: hr_utility.trace('L_STAGING_DATE_EARNINGS : '||L_STAGING_DATE_EARNINGS);
4575: L_PENSION_STATUS_SD := CHK_STAGING_DATE_EARNINGS(L_STAGING_DATE_EARNINGS,L_ASSIGNMENT_ID, L_PERSON_ID,L_AGE,L_REASON);
4576: hr_utility.trace('L_PENSION_STATUS_SD : '||L_PENSION_STATUS_SD);
4577: if (L_PENSION_STATUS_SD = 1) then
4578: hr_utility.trace('Employee was in QPS as an EJH on staging date hence not processing employee');
4579: raise l_exception;
4580: end if;
4581: end if;
4582:
4587: IF (L_NOMINAL_FLAG ='N') THEN --Nominal Check
4588: --TAX REF Level
4589: /*Bug 14622818 Modified the condition*/
4590: IF l_eligible_jh_date IS NULL then
4591: hr_utility.trace ('Eligible Job Holder Date is not already set, means employee assessed for the first time');
4592: l_eligible_jh_date := hr_api.g_eot;
4593: /*IF ((FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) OR ( L_SERVICE_START BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE))THEN*/
4594: IF(NVL(G_WORKER_POSTPONEMENT_RULE,'NONE')='NONE') THEN
4595: L_WORKER_POSTP_FLAG := 'N';
4632: END IF;
4633:
4634: END IF;
4635: */
4636: hr_utility.trace('POSTPONEMENT END_DATE NEW WORKER starting '||L_POSTPONEMENT_DATE );
4637: END IF;--TAX REF Level
4638:
4639:
4640: IF (L_WORKER_POSTP_FLAG = 'Y') THEN
4642:
4643: --------------------------- bug 15913417 : to classify employee even if worker postponment is applied--------------------------------------------------------
4644:
4645: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4646: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4647: hr_utility.trace('EARNINGS :'||L_EARNINGS);
4648: hr_utility.trace('AGE :'||L_AGE);
4649: --Employee Classififcation
4650: /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4643: --------------------------- bug 15913417 : to classify employee even if worker postponment is applied--------------------------------------------------------
4644:
4645: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4646: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4647: hr_utility.trace('EARNINGS :'||L_EARNINGS);
4648: hr_utility.trace('AGE :'||L_AGE);
4649: --Employee Classififcation
4650: /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4651: IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4644:
4645: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4646: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4647: hr_utility.trace('EARNINGS :'||L_EARNINGS);
4648: hr_utility.trace('AGE :'||L_AGE);
4649: --Employee Classififcation
4650: /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4651: IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4652: RETURN;
4650: /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4651: IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4652: RETURN;
4653: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4654: hr_utility.trace('Assignment is a Worker :');
4655: L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4656: -- RAISE L_EXCEPTION_UPDATE;
4657: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4658: L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4655: L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4656: -- RAISE L_EXCEPTION_UPDATE;
4657: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4658: L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4659: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4660: -- RAISE L_EXCEPTION_UPDATE;
4661: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4662: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4663: hr_utility.trace('Person Id'|| L_PERSON_ID);
4659: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4660: -- RAISE L_EXCEPTION_UPDATE;
4661: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4662: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4663: hr_utility.trace('Person Id'|| L_PERSON_ID);
4664: IF (L_ELIGIBLE_JH_DATE IS NULL ) THEN -- Never Found eligible
4665: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4666: L_ELIGIBLE_JH_DATE := L_DOB_TT;
4667: ELSE
4669: END IF;
4670: END IF;
4671: IF (L_ELIGIBLE_JH_DATE IS NOT NULL ) THEN -- Bug 13945549
4672: IF (G_PRP_START_DATE-1)=L_POSTPONEMENT_DATE THEN -- To check if Last Period was the period when the assignment was first assessed as EJH
4673: hr_utility.trace('One Day after the postponement end date , this PRP starts.');
4674: ELSE
4675: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4676: L_ELIGIBLE_JH_DATE := L_DOB_TT;
4677: ELSE
4686: END IF;--Nominal Check
4687: ---Assesment
4688: IF (L_WORKER_POSTP_FLAG ='N') THEN
4689: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4690: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4691: hr_utility.trace('EARNINGS :'||L_EARNINGS);
4692: hr_utility.trace('AGE :'||L_AGE);
4693: open c_ejh_history(L_ASSIGNMENT_ID);
4694: fetch c_ejh_history into l_history;
4687: ---Assesment
4688: IF (L_WORKER_POSTP_FLAG ='N') THEN
4689: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4690: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4691: hr_utility.trace('EARNINGS :'||L_EARNINGS);
4692: hr_utility.trace('AGE :'||L_AGE);
4693: open c_ejh_history(L_ASSIGNMENT_ID);
4694: fetch c_ejh_history into l_history;
4695: close c_ejh_history;
4688: IF (L_WORKER_POSTP_FLAG ='N') THEN
4689: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4690: hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4691: hr_utility.trace('EARNINGS :'||L_EARNINGS);
4692: hr_utility.trace('AGE :'||L_AGE);
4693: open c_ejh_history(L_ASSIGNMENT_ID);
4694: fetch c_ejh_history into l_history;
4695: close c_ejh_history;
4696: if (l_history > 0) then
4693: open c_ejh_history(L_ASSIGNMENT_ID);
4694: fetch c_ejh_history into l_history;
4695: close c_ejh_history;
4696: if (l_history > 0) then
4697: hr_utility.trace('Ignoring employee as he has already been in QPS as an EJH');
4698: raise l_exception;
4699: else
4700: hr_utility.trace('Processing employee as he has never been in QPS as an EJH');
4701: --Employee Classififcation
4696: if (l_history > 0) then
4697: hr_utility.trace('Ignoring employee as he has already been in QPS as an EJH');
4698: raise l_exception;
4699: else
4700: hr_utility.trace('Processing employee as he has never been in QPS as an EJH');
4701: --Employee Classififcation
4702: /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4703: IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4704: RETURN;
4702: /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4703: IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4704: RETURN;
4705: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4706: hr_utility.trace('Assignment is a Worker :');
4707: L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4708: RAISE L_EXCEPTION_UPDATE;
4709: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4710: L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4707: L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4708: RAISE L_EXCEPTION_UPDATE;
4709: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4710: L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4711: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4712: RAISE L_EXCEPTION_UPDATE;
4713: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4714: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4715: hr_utility.trace('Person Id'|| L_PERSON_ID);
4711: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4712: RAISE L_EXCEPTION_UPDATE;
4713: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4714: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4715: hr_utility.trace('Person Id'|| L_PERSON_ID);
4716: IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) = G_END_OF_TIME THEN -- Never Found eligible
4717: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4718: L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
4719: --L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4727: IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) <> G_END_OF_TIME THEN
4728: -- IF (G_PRP_START_DATE-1)=L_POSTPONEMENT_DATE THEN -- To check if Last Period was the period when the assignment was first assessed as EJH
4729: IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND L_POSTPONEMENT_TYPE = 'Eligible Job Holder Postponement' AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,
4730: L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE))-1)THEN
4731: hr_utility.trace('One Day after the postponement end date , this PRP starts.');
4732: -- ELSE
4733: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4734: L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
4735: --L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4746: /*
4747: OPEN PREV_PER_DATE;
4748: FETCH PREV_PER_DATE INTO L_PREV_PER_START_DATE ;
4749: CLOSE PREV_PER_DATE;
4750: hr_utility.trace('CAREFUL!! Previous'||L_PREV_PER_START_DATE);
4751: hr_utility.trace('CAREFUL!! Postpo'||L_POSTPONEMENT_DATE);
4752: */
4753: -- IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< L_PREV_PER_START_DATE) THEN
4754: --Greatest() -- Needed in cases where EJH is retained from the previous run itself.
4747: OPEN PREV_PER_DATE;
4748: FETCH PREV_PER_DATE INTO L_PREV_PER_START_DATE ;
4749: CLOSE PREV_PER_DATE;
4750: hr_utility.trace('CAREFUL!! Previous'||L_PREV_PER_START_DATE);
4751: hr_utility.trace('CAREFUL!! Postpo'||L_POSTPONEMENT_DATE);
4752: */
4753: -- IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< L_PREV_PER_START_DATE) THEN
4754: --Greatest() -- Needed in cases where EJH is retained from the previous run itself.
4755: hr_utility.trace('G_PRP_START_DATE:: '||G_PRP_START_DATE);
4751: hr_utility.trace('CAREFUL!! Postpo'||L_POSTPONEMENT_DATE);
4752: */
4753: -- IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< L_PREV_PER_START_DATE) THEN
4754: --Greatest() -- Needed in cases where EJH is retained from the previous run itself.
4755: hr_utility.trace('G_PRP_START_DATE:: '||G_PRP_START_DATE);
4756: hr_utility.trace('L_ELIGIBLE_JH_DATE:: '||L_ELIGIBLE_JH_DATE);
4757: hr_utility.trace('L_POSTPONEMENT_DATE:: '||L_POSTPONEMENT_DATE);
4758: IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),L_ELIGIBLE_JH_DATE)-1) THEN
4759: L_EJH_POSTP_FLAG := 'Y';
4752: */
4753: -- IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< L_PREV_PER_START_DATE) THEN
4754: --Greatest() -- Needed in cases where EJH is retained from the previous run itself.
4755: hr_utility.trace('G_PRP_START_DATE:: '||G_PRP_START_DATE);
4756: hr_utility.trace('L_ELIGIBLE_JH_DATE:: '||L_ELIGIBLE_JH_DATE);
4757: hr_utility.trace('L_POSTPONEMENT_DATE:: '||L_POSTPONEMENT_DATE);
4758: IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),L_ELIGIBLE_JH_DATE)-1) THEN
4759: L_EJH_POSTP_FLAG := 'Y';
4760: hr_utility.trace('CAREFUL!! Worker Postp applied before One Day ');
4753: -- IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< L_PREV_PER_START_DATE) THEN
4754: --Greatest() -- Needed in cases where EJH is retained from the previous run itself.
4755: hr_utility.trace('G_PRP_START_DATE:: '||G_PRP_START_DATE);
4756: hr_utility.trace('L_ELIGIBLE_JH_DATE:: '||L_ELIGIBLE_JH_DATE);
4757: hr_utility.trace('L_POSTPONEMENT_DATE:: '||L_POSTPONEMENT_DATE);
4758: IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),L_ELIGIBLE_JH_DATE)-1) THEN
4759: L_EJH_POSTP_FLAG := 'Y';
4760: hr_utility.trace('CAREFUL!! Worker Postp applied before One Day ');
4761: ELSIF L_POSTPONEMENT_DATE IS NULL THEN
4756: hr_utility.trace('L_ELIGIBLE_JH_DATE:: '||L_ELIGIBLE_JH_DATE);
4757: hr_utility.trace('L_POSTPONEMENT_DATE:: '||L_POSTPONEMENT_DATE);
4758: IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),L_ELIGIBLE_JH_DATE)-1) THEN
4759: L_EJH_POSTP_FLAG := 'Y';
4760: hr_utility.trace('CAREFUL!! Worker Postp applied before One Day ');
4761: ELSIF L_POSTPONEMENT_DATE IS NULL THEN
4762: L_EJH_POSTP_FLAG := 'Y';
4763: hr_utility.trace('No Postponement present');
4764: END IF;
4759: L_EJH_POSTP_FLAG := 'Y';
4760: hr_utility.trace('CAREFUL!! Worker Postp applied before One Day ');
4761: ELSIF L_POSTPONEMENT_DATE IS NULL THEN
4762: L_EJH_POSTP_FLAG := 'Y';
4763: hr_utility.trace('No Postponement present');
4764: END IF;
4765: --One Period gap -- Atleast One Day Gap
4766: IF (L_EJH_POSTP_FLAG = 'Y') THEN--One Month Check
4767: L_EJH_POSTP_FLAG := 'N';
4772: IF(NVL(G_ELIGIBLE_POSTPONEMENT_RULE,'NONE')='NONE') THEN
4773: L_EJH_POSTP_FLAG := 'N';
4774: ELSIF (G_ELIGIBLE_POSTPONEMENT_RULE ='MAX_DEFER_PERIOD_START_DATE') AND (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4775: L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (GREATEST(L_DOB_TT,L_ELIGIBLE_JH_DATE,l_asg_start_date),G_ELIGIBLE_POSTPONEMENT_RULE);
4776: hr_utility.trace('Twenty Second birthday Inside'||L_DOB_TT );
4777: IF l_postponement_date >= g_prp_end_date THEN
4778: L_POSTPONEMENT_TYPE := 'Eligible Job Holder Postponement';
4779: L_EJH_POSTP_FLAG := 'Y';
4780: END IF;
4800: ELSIF (G_ELIGIBLE_POSTPONEMENT_RULE ='MAX_DEFER_PERIOD_START_DATE') THEN
4801: -- L_POSTPONEMENT_DATE := ADD_MONTHS(G_PRP_START_DATE,3)-1;
4802: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4803: L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (L_DOB_TT);
4804: hr_utility.trace('Twenty Second birthday Inside'||L_DOB_TT );
4805: ELSE
4806: L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (GREATEST(G_PRP_START_DATE,L_ELIGIBLE_JH_DATE));
4807: hr_utility.trace('Twenty Second birthday Outside'||L_DOB_TT );
4808: END IF;
4803: L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (L_DOB_TT);
4804: hr_utility.trace('Twenty Second birthday Inside'||L_DOB_TT );
4805: ELSE
4806: L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (GREATEST(G_PRP_START_DATE,L_ELIGIBLE_JH_DATE));
4807: hr_utility.trace('Twenty Second birthday Outside'||L_DOB_TT );
4808: END IF;
4809: L_EJH_POSTP_FLAG := 'Y';
4810: L_POSTPONEMENT_TYPE :='Eligible Job Holder Postponement' ;
4811: ELSIF (G_ELIGIBLE_POSTPONEMENT_RULE ='NEXT_PAYROLL_PERIOD_START_DATE') THEN
4813: L_POSTPONEMENT_DATE := G_PRP_END_DATE;
4814: L_EJH_POSTP_FLAG := 'Y';
4815: END IF;
4816: */
4817: hr_utility.trace('ELIGIBLE POSTPONEMENT END DATE '||L_POSTPONEMENT_DATE );
4818: /* END IF;-- Postponement Not Already Applied
4819: CLOSE CHECK_POSTP_EJH ; */
4820: END IF;--One Month Check
4821: --Check if it has been more than a period since Worker Postponement Ended
4821: --Check if it has been more than a period since Worker Postponement Ended
4822: --EJH POSTPONEMENT RULE
4823: ---------************************************************************************************
4824: END IF; --Nominal Check
4825: hr_utility.trace('NOMINAL FLAG::'||L_NOMINAL_FLAG);
4826: IF (L_NOMINAL_FLAG ='Y') THEN
4827: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4828: L_NOMINAL_AUTO_ENROLL_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4829: ELSE
4852: L_QUALIFYING_SCHEME_EXISTS := 'Y';
4853: L_QUALIFYING_SCHEME_NAME :=L_DEFAULT_SCHEME_NAME;
4854: -- L_PENSION_ENTRY_START :=G_PAYROLL_START_DATE; -- Bug 14343133
4855: L_PENSION_ENTRY_END :=G_END_OF_TIME;
4856: hr_utility.trace('PENSION ELEMENT INSERTION LOGIC');
4857: hr_utility.trace('Assignment is an ELIGIBLE JOB HOLDER :');
4858: --ER
4859: IF G_DEF_FLAG='Y' THEN
4860: -- Only People Group Check
4853: L_QUALIFYING_SCHEME_NAME :=L_DEFAULT_SCHEME_NAME;
4854: -- L_PENSION_ENTRY_START :=G_PAYROLL_START_DATE; -- Bug 14343133
4855: L_PENSION_ENTRY_END :=G_END_OF_TIME;
4856: hr_utility.trace('PENSION ELEMENT INSERTION LOGIC');
4857: hr_utility.trace('Assignment is an ELIGIBLE JOB HOLDER :');
4858: --ER
4859: IF G_DEF_FLAG='Y' THEN
4860: -- Only People Group Check
4861: hr_utility.trace('As No defautl Scheme has been Set, Considering the eligibility criteria');
4857: hr_utility.trace('Assignment is an ELIGIBLE JOB HOLDER :');
4858: --ER
4859: IF G_DEF_FLAG='Y' THEN
4860: -- Only People Group Check
4861: hr_utility.trace('As No defautl Scheme has been Set, Considering the eligibility criteria');
4862: IF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 2 THEN
4863: hr_utility.trace('ER Criteria Not mutually exclusive for People Group');
4864: --RAISE_APPLICATION_ERROR(-20001,'This assignment is eligible for more than one Qualifying Scheme.');
4865: populate_run_msg(p_assactid,'This assignment is eligible for more than one Qualifying Scheme.');
4859: IF G_DEF_FLAG='Y' THEN
4860: -- Only People Group Check
4861: hr_utility.trace('As No defautl Scheme has been Set, Considering the eligibility criteria');
4862: IF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 2 THEN
4863: hr_utility.trace('ER Criteria Not mutually exclusive for People Group');
4864: --RAISE_APPLICATION_ERROR(-20001,'This assignment is eligible for more than one Qualifying Scheme.');
4865: populate_run_msg(p_assactid,'This assignment is eligible for more than one Qualifying Scheme.');
4866: RAISE G_EXCEPTION_NO_UPDATE; -- Just Archive for Error Cases.
4867: ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 0 THEN --No Matching criteria
4864: --RAISE_APPLICATION_ERROR(-20001,'This assignment is eligible for more than one Qualifying Scheme.');
4865: populate_run_msg(p_assactid,'This assignment is eligible for more than one Qualifying Scheme.');
4866: RAISE G_EXCEPTION_NO_UPDATE; -- Just Archive for Error Cases.
4867: ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 0 THEN --No Matching criteria
4868: hr_utility.trace('This Assignment is an Eligible Job Holder but not eligible for any of the Qualifying Schemes.');
4869: --RAISE_APPLICATION_ERROR(-20001,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4870: populate_run_msg(p_assactid,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4871: RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
4872: ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 1 THEN
4869: --RAISE_APPLICATION_ERROR(-20001,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4870: populate_run_msg(p_assactid,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4871: RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
4872: ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 1 THEN
4873: hr_utility.trace('Group Criteria Met');
4874: hr_utility.trace('ER Scheme Name:: '||L_ER_SCHEME_NAME);
4875: L_QUALIFYING_SCHEME_NAME:=L_ER_SCHEME_NAME;
4876: IF (L_ER_SCHEME_NAME IS NOT NULL) THEN
4877: OPEN C_GET_PENSION_ELEMENTS(L_ER_SCHEME_NAME);
4870: populate_run_msg(p_assactid,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4871: RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
4872: ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 1 THEN
4873: hr_utility.trace('Group Criteria Met');
4874: hr_utility.trace('ER Scheme Name:: '||L_ER_SCHEME_NAME);
4875: L_QUALIFYING_SCHEME_NAME:=L_ER_SCHEME_NAME;
4876: IF (L_ER_SCHEME_NAME IS NOT NULL) THEN
4877: OPEN C_GET_PENSION_ELEMENTS(L_ER_SCHEME_NAME);
4878: FETCH C_GET_PENSION_ELEMENTS
4883: -- Only People Group Check
4884: END IF;
4885: END IF;
4886: --ER
4887: hr_utility.trace('L_DEFAULT_SCHEME_NAME Value::'||L_DEFAULT_SCHEME_NAME);
4888: hr_utility.trace('L_DEFAULT_SCH_ELEMENT_NAME Value::'||L_DEFAULT_SCH_ELEMENT_NAME);
4889: hr_utility.trace('L_EMPLOYER_COMPONENT Value::'||L_EMPLOYER_COMPONENT);
4890:
4891: ------------------------------------- bug 14380856 -------------------------------------------------
4884: END IF;
4885: END IF;
4886: --ER
4887: hr_utility.trace('L_DEFAULT_SCHEME_NAME Value::'||L_DEFAULT_SCHEME_NAME);
4888: hr_utility.trace('L_DEFAULT_SCH_ELEMENT_NAME Value::'||L_DEFAULT_SCH_ELEMENT_NAME);
4889: hr_utility.trace('L_EMPLOYER_COMPONENT Value::'||L_EMPLOYER_COMPONENT);
4890:
4891: ------------------------------------- bug 14380856 -------------------------------------------------
4892:
4885: END IF;
4886: --ER
4887: hr_utility.trace('L_DEFAULT_SCHEME_NAME Value::'||L_DEFAULT_SCHEME_NAME);
4888: hr_utility.trace('L_DEFAULT_SCH_ELEMENT_NAME Value::'||L_DEFAULT_SCH_ELEMENT_NAME);
4889: hr_utility.trace('L_EMPLOYER_COMPONENT Value::'||L_EMPLOYER_COMPONENT);
4890:
4891: ------------------------------------- bug 14380856 -------------------------------------------------
4892:
4893: hr_utility.trace('L_QUALIFYING_SCHEME_NAME :'||L_QUALIFYING_SCHEME_NAME);
4889: hr_utility.trace('L_EMPLOYER_COMPONENT Value::'||L_EMPLOYER_COMPONENT);
4890:
4891: ------------------------------------- bug 14380856 -------------------------------------------------
4892:
4893: hr_utility.trace('L_QUALIFYING_SCHEME_NAME :'||L_QUALIFYING_SCHEME_NAME);
4894:
4895: if l_postponement_date < g_prp_end_date and g_prp_start_date < l_postponement_date then
4896: l_custom_eff_date := GREATEST(l_postponement_date+1,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4897: else
4899: end if;
4900:
4901: open C_VALIDATE_CONFIG_VALUE('PAY_GB_ENROLMENT_CUSTOM_PROC',L_QUALIFYING_SCHEME_NAME);
4902: fetch C_VALIDATE_CONFIG_VALUE into l_custom_procedure;
4903: hr_utility.trace('l_custom_procedure :'||l_custom_procedure);
4904: if C_VALIDATE_CONFIG_VALUE%found and l_custom_procedure is not null then
4905: hr_utility.trace('Running Custom Procedure');
4906: hr_utility.trace('l_custom_eff_date : '||l_custom_eff_date);
4907: hr_utility.trace('l_assignment_id :'||l_assignment_id);
4901: open C_VALIDATE_CONFIG_VALUE('PAY_GB_ENROLMENT_CUSTOM_PROC',L_QUALIFYING_SCHEME_NAME);
4902: fetch C_VALIDATE_CONFIG_VALUE into l_custom_procedure;
4903: hr_utility.trace('l_custom_procedure :'||l_custom_procedure);
4904: if C_VALIDATE_CONFIG_VALUE%found and l_custom_procedure is not null then
4905: hr_utility.trace('Running Custom Procedure');
4906: hr_utility.trace('l_custom_eff_date : '||l_custom_eff_date);
4907: hr_utility.trace('l_assignment_id :'||l_assignment_id);
4908: begin
4909: SAVEPOINT custom_proc;
4902: fetch C_VALIDATE_CONFIG_VALUE into l_custom_procedure;
4903: hr_utility.trace('l_custom_procedure :'||l_custom_procedure);
4904: if C_VALIDATE_CONFIG_VALUE%found and l_custom_procedure is not null then
4905: hr_utility.trace('Running Custom Procedure');
4906: hr_utility.trace('l_custom_eff_date : '||l_custom_eff_date);
4907: hr_utility.trace('l_assignment_id :'||l_assignment_id);
4908: begin
4909: SAVEPOINT custom_proc;
4910: l_stmt := 'BEGIN :status := '||l_custom_procedure||'( :pens_qual_scheme_name, :effective_start_date, :auto_enrollment_date, :assignment_id ); exception when others then :status := 0 ; END;' ;
4903: hr_utility.trace('l_custom_procedure :'||l_custom_procedure);
4904: if C_VALIDATE_CONFIG_VALUE%found and l_custom_procedure is not null then
4905: hr_utility.trace('Running Custom Procedure');
4906: hr_utility.trace('l_custom_eff_date : '||l_custom_eff_date);
4907: hr_utility.trace('l_assignment_id :'||l_assignment_id);
4908: begin
4909: SAVEPOINT custom_proc;
4910: l_stmt := 'BEGIN :status := '||l_custom_procedure||'( :pens_qual_scheme_name, :effective_start_date, :auto_enrollment_date, :assignment_id ); exception when others then :status := 0 ; END;' ;
4911: execute immediate l_stmt using in out l_status, l_qualifying_scheme_name, l_custom_eff_date, l_auto_enrollment_date, l_assignment_id;
4912: IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
4913: ROLLBACK TO custom_proc;
4914: END IF;
4915: exception when others then
4916: hr_utility.trace('Error occured in execute immediate');
4917: l_status := 0;
4918: ROLLBACK TO custom_proc;
4919: end;
4920: end if;
4917: l_status := 0;
4918: ROLLBACK TO custom_proc;
4919: end;
4920: end if;
4921: hr_utility.trace('l_status :'||l_status);
4922: if l_status = 0 then
4923: --error;
4924: hr_utility.trace('Error in custom procedure, hence skipping assignment');
4925: populate_run_msg(p_assactid,'Error in custom procedure, hence this assignment is being skipped.');
4920: end if;
4921: hr_utility.trace('l_status :'||l_status);
4922: if l_status = 0 then
4923: --error;
4924: hr_utility.trace('Error in custom procedure, hence skipping assignment');
4925: populate_run_msg(p_assactid,'Error in custom procedure, hence this assignment is being skipped.');
4926: elsif l_status = 2 then
4927:
4928: IF(L_DEFAULT_SCH_ELEMENT_NAME IS NOT NULL) THEN
4926: elsif l_status = 2 then
4927:
4928: IF(L_DEFAULT_SCH_ELEMENT_NAME IS NOT NULL) THEN
4929: --EMployee Element
4930: hr_utility.trace('EMPLOYEE ELEMENT INSERTION LOGIC');
4931: INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
4932: P_ELEMENT_TYPE_NAME=>L_DEFAULT_SCH_ELEMENT_NAME, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
4933: END IF;
4934: --Employer Element
4932: P_ELEMENT_TYPE_NAME=>L_DEFAULT_SCH_ELEMENT_NAME, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
4933: END IF;
4934: --Employer Element
4935: IF(L_EMPLOYER_COMPONENT IS NOT NULL) THEN
4936: hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC');
4937: INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
4938: P_ELEMENT_TYPE_NAME=>L_EMPLOYER_COMPONENT, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
4939: ELSE
4940: hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC - Employer Element is NULL');
4936: hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC');
4937: INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
4938: P_ELEMENT_TYPE_NAME=>L_EMPLOYER_COMPONENT, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
4939: ELSE
4940: hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC - Employer Element is NULL');
4941: END IF;
4942:
4943: end if;
4944:
4958: ---Assesment
4959: ------------------------------------------------------------------------------------------------------
4960: ELSIF (NVL(L_QUALIFYING_SCHEME_EXISTS,'N') ='Y') THEN --Scheme Already Exists
4961: IF (L_QUALIFYING_SCHEME_NAME IS NULL) THEN
4962: hr_utility.trace('Qualifying Scheme Exists is set to Yes, but Qualifying Scheme Name does not contain any value.');
4963: populate_run_msg(p_assactid,'Qualifying Scheme Exists is set to Yes, but Qualifying Scheme Name does not contain any value.');
4964: RAISE L_EXCEPTION;
4965: END IF;
4966: OPEN C_QUALIFYING_SCHEME_EXISTS (L_ASSIGNMENT_ID) ;
4965: END IF;
4966: OPEN C_QUALIFYING_SCHEME_EXISTS (L_ASSIGNMENT_ID) ;
4967: FETCH C_QUALIFYING_SCHEME_EXISTS INTO L_QUALIFYING_SCHEME_NAME;
4968: IF C_QUALIFYING_SCHEME_EXISTS%NOTFOUND THEN
4969: hr_utility.trace('Assignment doesnot have a Qualifying Scheme attached, yet Qualifying Scheme Exists Flag is Set to Yes.');
4970: populate_run_msg(p_assactid,'Assignment does not have a Qualifying Scheme attached, yet Qualifying Scheme Exists flag is set to Yes.');
4971: RAISE L_EXCEPTION;
4972: END IF;
4973: CLOSE C_QUALIFYING_SCHEME_EXISTS;
4973: CLOSE C_QUALIFYING_SCHEME_EXISTS;
4974: -- RAISE L_EXCEPTION_UPDATE; -- Remove Later as Nominal Date of Enrollment has to be reported
4975:
4976:
4977: hr_utility.trace('Assignment older classification to variable ');
4978: L_EMPLOYEE_PENSION_CLASS_OLD := L_EMPLOYEE_CLASS_FOR_PENSION;
4979: L_EARNINGS_OLD := L_EARNINGS ;
4980: hr_utility.trace('assessing employee again for change in status');
4981: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID,L_ASSIGNMENT_ID,
4976:
4977: hr_utility.trace('Assignment older classification to variable ');
4978: L_EMPLOYEE_PENSION_CLASS_OLD := L_EMPLOYEE_CLASS_FOR_PENSION;
4979: L_EARNINGS_OLD := L_EARNINGS ;
4980: hr_utility.trace('assessing employee again for change in status');
4981: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID,L_ASSIGNMENT_ID,
4982: NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4983: if (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4984: hr_utility.trace('Assignment is a Worker :');
4980: hr_utility.trace('assessing employee again for change in status');
4981: L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID,L_ASSIGNMENT_ID,
4982: NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4983: if (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4984: hr_utility.trace('Assignment is a Worker :');
4985: L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4986: elsif (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4987: L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4988: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4984: hr_utility.trace('Assignment is a Worker :');
4985: L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4986: elsif (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4987: L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4988: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4989: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4990: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4991: end if;
4992: hr_utility.trace('Done assessing');
4988: hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4989: ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4990: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4991: end if;
4992: hr_utility.trace('Done assessing');
4993: if(L_EMPLOYEE_PENSION_CLASS_OLD <> L_EMPLOYEE_CLASS_FOR_PENSION) or (L_EARNINGS_OLD <> L_EARNINGS) then
4994: hr_utility.trace('Employee assessed again as there is a change in pensions classification/Earnings');
4995: if (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4996: IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) = G_END_OF_TIME THEN -- Never Found eligible
4990: L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4991: end if;
4992: hr_utility.trace('Done assessing');
4993: if(L_EMPLOYEE_PENSION_CLASS_OLD <> L_EMPLOYEE_CLASS_FOR_PENSION) or (L_EARNINGS_OLD <> L_EARNINGS) then
4994: hr_utility.trace('Employee assessed again as there is a change in pensions classification/Earnings');
4995: if (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4996: IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) = G_END_OF_TIME THEN -- Never Found eligible
4997: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4998: L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),
5005: -- Found Eligible previously
5006: IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) <> G_END_OF_TIME THEN
5007: IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND L_POSTPONEMENT_TYPE = 'Eligible Job Holder Postponement' AND
5008: (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE))-1)THEN
5009: hr_utility.trace('One Day after the postponement end date , this PRP starts.');
5010: IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
5011: L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),
5012: nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
5013: ELSE
5018: end if;
5019: end if;
5020: RAISE L_EXCEPTION_UPDATE;
5021: else
5022: hr_utility.trace('Employee not assessed again as there is no change in pensions classification');
5023: RAISE L_EXCEPTION_NO_ERROR; -- Bug 14550021 -- Just Skip if the Q.S.Exists Flag is set to Yes.
5024: end if;
5025: END IF;--Scheme Already Exists
5026: -----------------------------------
5024: end if;
5025: END IF;--Scheme Already Exists
5026: -----------------------------------
5027: ELSE--Main Entry
5028: hr_utility.trace('Not a Main Entry');
5029: RAISE L_EXCEPTION_NO_ERROR;
5030: --Any Other Checks??
5031: END IF; --Main Entry
5032: ELSE --Postponement Check
5029: RAISE L_EXCEPTION_NO_ERROR;
5030: --Any Other Checks??
5031: END IF; --Main Entry
5032: ELSE --Postponement Check
5033: hr_utility.trace('Postponement End Date Check');
5034: RAISE L_EXCEPTION_NO_ERROR;
5035: --Any Other Checks??
5036: END IF;--Postponement Check
5037: -- ELSE --OPT Out date Crossed
5034: RAISE L_EXCEPTION_NO_ERROR;
5035: --Any Other Checks??
5036: END IF;--Postponement Check
5037: -- ELSE --OPT Out date Crossed
5038: -- hr_utility.trace('OPT Out date Crossed');
5039: -- RAISE L_EXCEPTION_NO_ERROR;
5040: --Any Other Checks??
5041: -- NULL;
5042: -- END IF;--OPT Out date Crossed
5040: --Any Other Checks??
5041: -- NULL;
5042: -- END IF;--OPT Out date Crossed
5043: ELSE --A.E.D. Not Null
5044: hr_utility.trace('A.E.D. Not Null. Already Enrolled');
5045: RAISE L_EXCEPTION_NO_ERROR;
5046: --Any Other Checks??
5047: END IF; --A.E.D. Null
5048: EXCEPTION
5046: --Any Other Checks??
5047: END IF; --A.E.D. Null
5048: EXCEPTION
5049: WHEN L_EXCEPTION_NO_ERROR THEN
5050: hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_NO_ERROR');
5051: WHEN L_EXCEPTION_UPDATE_INFO THEN
5052: hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE_INFO');
5053: --Update Info Element
5054: IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
5048: EXCEPTION
5049: WHEN L_EXCEPTION_NO_ERROR THEN
5050: hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_NO_ERROR');
5051: WHEN L_EXCEPTION_UPDATE_INFO THEN
5052: hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE_INFO');
5053: --Update Info Element
5054: IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
5055: hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
5056: --UPDATE_INFO_ELEMENT( L_ELEMENT_ENTRY_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME
5051: WHEN L_EXCEPTION_UPDATE_INFO THEN
5052: hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE_INFO');
5053: --Update Info Element
5054: IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
5055: hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
5056: --UPDATE_INFO_ELEMENT( L_ELEMENT_ENTRY_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME
5057: UPDATE_INFO_ELEMENT( L_ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
5058: G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
5059: L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
5064: END IF;
5065: --*****************************************************************************************
5066: --AGGREGATION
5067: IF (NVL(L_AGGREGATION_FLAG,'N')='Y') THEN
5068: hr_utility.trace('---------------------------------------------------------------------------------------------');
5069: hr_utility.trace('Exception Update-- AGGREGATED Case');
5070: -- CAll the Procedrure for Aggreagated Case
5071: AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5072: L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5065: --*****************************************************************************************
5066: --AGGREGATION
5067: IF (NVL(L_AGGREGATION_FLAG,'N')='Y') THEN
5068: hr_utility.trace('---------------------------------------------------------------------------------------------');
5069: hr_utility.trace('Exception Update-- AGGREGATED Case');
5070: -- CAll the Procedrure for Aggreagated Case
5071: AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5072: L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5073: --L_AUTO_ENROLLMENT_DATE DATE,L_DEFAULT_SCHEME_NAME VARCHAR2);
5070: -- CAll the Procedrure for Aggreagated Case
5071: AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5072: L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5073: --L_AUTO_ENROLLMENT_DATE DATE,L_DEFAULT_SCHEME_NAME VARCHAR2);
5074: hr_utility.trace('---------------------------------------------------------------------------------------------');
5075: END IF;
5076: --AGGREGATION
5077: --*****************************************************************************************
5078: WHEN L_EXCEPTION_UPDATE THEN
5075: END IF;
5076: --AGGREGATION
5077: --*****************************************************************************************
5078: WHEN L_EXCEPTION_UPDATE THEN
5079: hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE');
5080: --*****************************************************-
5081: pay_action_information_api.create_action_information( p_action_information_id => l_action_id, p_object_version_number => l_ovn, p_action_information_category => 'GB_ENROLL_PENSIONS', p_action_context_id => p_assactid,
5082: p_action_context_type => 'AAP', p_assignment_id => L_ASSIGNMENT_ID, p_effective_date =>G_PRP_END_DATE, p_action_information1 =>L_QUALIFYING_SCHEME_NAME, p_action_information2 =>L_AGGREGATION_FLAG,
5083: p_action_information3 =>L_MAIN_ENTRY, p_action_information4 =>L_QUALIFYING_SCHEME_EXISTS, p_action_information5 =>L_POSTPONEMENT_TYPE, p_action_information6 =>FND_DATE.DATE_TO_CANONICAL(L_POSTPONEMENT_DATE),
5087: p_action_information16 =>FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE), p_action_information17 =>FND_DATE.DATE_TO_CANONICAL(L_NOMINAL_AUTO_ENROLL_DATE), p_action_information18 =>floor(L_AGE) );
5088: --**************************************
5089: --Update Info Element
5090: IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
5091: hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
5092: --UPDATE_INFO_ELEMENT( L_ELEMENT_ENTRY_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME
5093: UPDATE_INFO_ELEMENT( L_ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
5094: G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
5095: L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
5100: END IF;
5101: --*****************************************************************************************
5102: --AGGREGATION
5103: IF (NVL(L_AGGREGATION_FLAG,'N')='Y') THEN
5104: hr_utility.trace('---------------------------------------------------------------------------------------------');
5105: hr_utility.trace('Exception Update-- AGGREGATED Case');
5106: -- CAll the Procedrure for Aggreagated Case
5107: AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5108: L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5101: --*****************************************************************************************
5102: --AGGREGATION
5103: IF (NVL(L_AGGREGATION_FLAG,'N')='Y') THEN
5104: hr_utility.trace('---------------------------------------------------------------------------------------------');
5105: hr_utility.trace('Exception Update-- AGGREGATED Case');
5106: -- CAll the Procedrure for Aggreagated Case
5107: AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5108: L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5109: --L_AUTO_ENROLLMENT_DATE DATE,L_DEFAULT_SCHEME_NAME VARCHAR2);
5106: -- CAll the Procedrure for Aggreagated Case
5107: AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5108: L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5109: --L_AUTO_ENROLLMENT_DATE DATE,L_DEFAULT_SCHEME_NAME VARCHAR2);
5110: hr_utility.trace('---------------------------------------------------------------------------------------------');
5111: END IF;
5112: --AGGREGATION
5113: --*****************************************************************************************
5114: END ; -- Inner Section
5114: END ; -- Inner Section
5115: EXCEPTION -- Outer Section
5116: WHEN L_EXCEPTION THEN
5117: --RAISE_APPLICATION_ERROR(-20001,'Archive Failed for the assignment');
5118: hr_utility.trace('Archive Failed for the assignment');
5119: WHEN G_EXCEPTION_NO_UPDATE THEN
5120: hr_utility.trace('INSIDE EXCEPTION: G_EXCEPTION_NO_UPDATE');
5121: --*****************************************************-
5122: pay_action_information_api.create_action_information( p_action_information_id => l_action_id, p_object_version_number => l_ovn, p_action_information_category => 'GB_ENROLL_PENSIONS', p_action_context_id => p_assactid,
5116: WHEN L_EXCEPTION THEN
5117: --RAISE_APPLICATION_ERROR(-20001,'Archive Failed for the assignment');
5118: hr_utility.trace('Archive Failed for the assignment');
5119: WHEN G_EXCEPTION_NO_UPDATE THEN
5120: hr_utility.trace('INSIDE EXCEPTION: G_EXCEPTION_NO_UPDATE');
5121: --*****************************************************-
5122: pay_action_information_api.create_action_information( p_action_information_id => l_action_id, p_object_version_number => l_ovn, p_action_information_category => 'GB_ENROLL_PENSIONS', p_action_context_id => p_assactid,
5123: p_action_context_type => 'AAP', p_assignment_id => L_ASSIGNMENT_ID, p_effective_date =>G_PRP_END_DATE, p_action_information1 =>L_QUALIFYING_SCHEME_NAME, p_action_information2 =>L_AGGREGATION_FLAG,
5124: p_action_information3 =>L_MAIN_ENTRY, p_action_information4 =>L_QUALIFYING_SCHEME_EXISTS, p_action_information5 =>L_POSTPONEMENT_TYPE, p_action_information6 =>FND_DATE.DATE_TO_CANONICAL(L_POSTPONEMENT_DATE),