[Home] [Help]
189: l_budgeted_entity_cd varchar2(30) := null;
190: --
191: Begin
192: --
193: hr_utility.set_location('Entering :'||l_proc,5);
194: --
195: -- VALIDATE IF THIS IS A VALID BUDGET IN PQH_BUDGETS
196: --
197: Open csr_bdgt;
241: End if;
242: --
243: Close csr_bdgt_cal_freq;
244: --
245: hr_utility.set_location('Leaving :'||l_proc,10);
246: --
247: EXCEPTION
248: WHEN OTHERS THEN
249: p_budget_id := null;
331: l_proc varchar2(72) := g_package || 'Validate_entity';
332: --
333: Begin
334: --
335: hr_utility.set_location('Entering :'||l_proc,5);
336: hr_utility.set_location('p_budgeted_entity_cd'||p_budgeted_entity_cd||'p_entity_id'||p_entity_id,5);
337:
338: --
339:
332: --
333: Begin
334: --
335: hr_utility.set_location('Entering :'||l_proc,5);
336: hr_utility.set_location('p_budgeted_entity_cd'||p_budgeted_entity_cd||'p_entity_id'||p_entity_id,5);
337:
338: --
339:
340: If p_budgeted_entity_cd ='POSITION' then
372: Loop
373: --
374: Fetch csr_positions_in_bdgt into g_budget_entities(rec_no).entity_id;
375: --
376: hr_utility.set_location(' Fetch Position:'||l_proc,10);
377: --
378: If csr_positions_in_bdgt%notfound then
379: --
380: Exit;
387: Close csr_positions_in_bdgt;
388: --
389: rec_no := rec_no - 1;
390: --
391: hr_utility.set_location(' Check rowcount :'||l_proc,15);
392: --
393: If rec_no = 0 then
394: --
395: If p_entity_id IS NULL then
445: Loop
446: --
447: Fetch csr_orgs_in_bdgt into g_budget_entities(rec_no).entity_id;
448: --
449: hr_utility.set_location(' Fetch Position:'||l_proc,10);
450: --
451: If csr_orgs_in_bdgt%notfound then
452: --
453: Exit;
460: Close csr_orgs_in_bdgt;
461: --
462: rec_no := rec_no - 1;
463: --
464: hr_utility.set_location(' Check rowcount :'||l_proc,15);
465: --
466: If rec_no = 0 then
467: --
468: If p_entity_id IS NULL then
483: End if; -- p_budgeted_entity_cd='ORGANIZATION'
484: --
485: If p_budgeted_entity_cd ='JOB' then
486:
487: hr_utility.set_location(' Job Strated...:'||l_proc,10);
488:
489: If p_entity_id IS NOT NULL then
490: --
491: -- VALIDATE IF THIS IS A VALID JOB IN PER_JOBS
492: --
493: Open csr_job(p_entity_id);
494: --
495: Fetch csr_job into p_entity_name;
496: hr_utility.set_location(' Job Name :'||p_entity_name,10);
497: --
498: If csr_job%notfound then
499: --
500: --Raise exception
505: --
506: End if;
507: --
508: Close csr_job;
509: hr_utility.set_location(' exited out nocopy .... csr_job :'||p_entity_name,10);
510: --
511: End if; -- p_entity_id is not null
512: --
513: -- DETERMINE IF THE JOB BELONGS TO THE BUDGET VERSION
521: Loop
522: --
523: Fetch csr_jobs_in_bdgt into g_budget_entities(rec_no).entity_id;
524: --
525: hr_utility.set_location(' Fetch Position:'||l_proc,10);
526: --
527: If csr_jobs_in_bdgt%notfound then
528: --
529: Exit;
536: Close csr_jobs_in_bdgt;
537: --
538: rec_no := rec_no - 1;
539: --
540: hr_utility.set_location(' Check rowcount :'||l_proc,15);
541: --
542: If rec_no = 0 then
543: --
544: If p_entity_id IS NULL then
595: Loop
596: --
597: Fetch csr_grades_in_bdgt into g_budget_entities(rec_no).entity_id;
598: --
599: hr_utility.set_location(' Fetch Position:'||l_proc,10);
600: --
601: If csr_grades_in_bdgt%notfound then
602: --
603: Exit;
610: Close csr_grades_in_bdgt;
611: --
612: rec_no := rec_no - 1;
613: --
614: hr_utility.set_location(' Check rowcount :'||l_proc,15);
615: --
616: If rec_no = 0 then
617: --
618: If p_entity_id IS NULL then
631: --
632: --
633: End if; -- p_budgeted_entity_cd='GRADE'
634: --
635: hr_utility.set_location('Leaving :'||l_proc,20);
636: --
637: EXCEPTION
638: WHEN OTHERS THEN
639: p_entity_name := null;
636: --
637: EXCEPTION
638: WHEN OTHERS THEN
639: p_entity_name := null;
640: hr_utility.set_location('Exception :'||l_proc,25);
641: raise;
642: --
643: End Validate_entity;
644: --
654: l_proc varchar2(72) := g_package || 'get_table_route';
655: --
656: BEGIN
657: --
658: hr_utility.set_location('Entering:'||l_proc, 5);
659:
660: -- get table_route_id for all the tables
661:
662: -- table_route_id for per_budgets
668: OPEN csr_table_route (p_table_alias => 'P_BVR');
669: FETCH csr_table_route INTO g_table_route_id_p_bdt;
670: CLOSE csr_table_route;
671: --
672: hr_utility.set_location('Leaving:'||l_proc, 10);
673: --
674: End;
675: --
676: -----------------------------------------------------------------------
688: from per_time_period_types tp
689: where tp.period_type = p_proc_period_type;
690: --
691: begin
692: hr_utility.set_location('Entering:'||proc_name, 5);
693: --
694: Open csr_period_types;
695: --
696: Fetch csr_period_types into no_periods;
731: FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_PERIOD_TYPE');
732: APP_EXCEPTION.RAISE_EXCEPTION;
733: end if;
734: --
735: hr_utility.set_location('Leaving:'||proc_name, 10);
736: --
737: Exception When others then
738: p_base_period_type := null;
739: p_multiple := null;
736: --
737: Exception When others then
738: p_base_period_type := null;
739: p_multiple := null;
740: hr_utility.set_location('Exception:'||proc_name, 15);
741: raise;
742: end get_period_details;
743: --
744: ---------------------------------------------------------------------------------
756: --
757: func_name CONSTANT varchar2(50) := 'next_semi_month';
758: begin
759: --
760: hr_utility.set_location(func_name, 1);
761: --
762: day_of_month := to_char(p_fpe_date,'DD');
763: --
764: if (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) then
818: temp_date date;
819: --
820: begin
821: --
822: hr_utility.set_location('Entering:'||l_proc, 5);
823: --
824: -- Errors can occur when performing date manipulation.
825: if p_base_period_type = WEEKLY then
826: --
823: --
824: -- Errors can occur when performing date manipulation.
825: if p_base_period_type = WEEKLY then
826: --
827: hr_utility.set_location('Base Type is Weekly :'||l_proc, 10);
828: return (p_target_date + (7 * p_multiple));
829: --
830: elsif p_base_period_type = MONTHLY then
831: --
828: return (p_target_date + (7 * p_multiple));
829: --
830: elsif p_base_period_type = MONTHLY then
831: --
832: hr_utility.set_location('Base Type is Monthly :'||l_proc, 15);
833: return (add_months(p_target_date, p_multiple));
834: --
835: else
836: --
835: else
836: --
837: -- Addition of one semi-month.
838: --
839: hr_utility.set_location('Base Type is Semi-Month :'||l_proc, 20);
840: return(next_semi_month(p_target_date, p_fpe_date));
841: --
842: end if;
843: --
886: l_periods_rec csr_bdgt_time_periods%rowtype;
887: --
888: Begin
889: --
890: hr_utility.set_location('Entering :'||l_proc,5);
891:
892: hr_utility.set_location('period set name :'||p_period_set_name,5);
893: hr_utility.set_location('p_period_frequency: '||p_period_frequency,5);
894: hr_utility.set_location('p_budget_cal_freqe :'||p_budget_cal_freq,5);
888: Begin
889: --
890: hr_utility.set_location('Entering :'||l_proc,5);
891:
892: hr_utility.set_location('period set name :'||p_period_set_name,5);
893: hr_utility.set_location('p_period_frequency: '||p_period_frequency,5);
894: hr_utility.set_location('p_budget_cal_freqe :'||p_budget_cal_freq,5);
895: --
896: -- Step 1: GENERATE SUB-PERIODS FOR THE ENTIRE BUDGET FISCAL YEAR AND
889: --
890: hr_utility.set_location('Entering :'||l_proc,5);
891:
892: hr_utility.set_location('period set name :'||p_period_set_name,5);
893: hr_utility.set_location('p_period_frequency: '||p_period_frequency,5);
894: hr_utility.set_location('p_budget_cal_freqe :'||p_budget_cal_freq,5);
895: --
896: -- Step 1: GENERATE SUB-PERIODS FOR THE ENTIRE BUDGET FISCAL YEAR AND
897: -- STORE IT IN A DUMMY PL/SQL TABLE.
890: hr_utility.set_location('Entering :'||l_proc,5);
891:
892: hr_utility.set_location('period set name :'||p_period_set_name,5);
893: hr_utility.set_location('p_period_frequency: '||p_period_frequency,5);
894: hr_utility.set_location('p_budget_cal_freqe :'||p_budget_cal_freq,5);
895: --
896: -- Step 1: GENERATE SUB-PERIODS FOR THE ENTIRE BUDGET FISCAL YEAR AND
897: -- STORE IT IN A DUMMY PL/SQL TABLE.
898: --
959: --
960: -- Check if the periods start date is equal to the commitment start date.
961: -- If so , then the input commitment start date is valid.
962: --
963: hr_utility.set_location('Period :'||to_char(l_all_periods(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' - ' ||to_char(l_all_periods(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),10);
964: --
965: If p_cmmtmnt_start_dt between l_all_periods(cnt).cmmtmnt_start_dt and
966: l_all_periods(cnt).cmmtmnt_end_dt then
967: --
964: --
965: If p_cmmtmnt_start_dt between l_all_periods(cnt).cmmtmnt_start_dt and
966: l_all_periods(cnt).cmmtmnt_end_dt then
967: --
968: hr_utility.set_location('Valid Commitment Start Date',15);
969: --
970: -- The supplied commitment_start_dt is valid
971: --
972: l_start_dt_valid := 'Y' ;
1010: g_cmmtmnt_calc_dates(rec_no).actual_cmmtmnt_end_dt := l_all_periods(cnt).cmmtmnt_end_dt;
1011: --
1012: Else
1013: --
1014: hr_utility.set_location('Valid Commitment End Date',20);
1015: --
1016: l_end_dt_valid := 'Y';
1017: g_cmmtmnt_calc_dates(rec_no).actual_cmmtmnt_end_dt := p_cmmtmnt_end_dt;
1018: Exit;
1037: APP_EXCEPTION.RAISE_EXCEPTION;
1038: --
1039: End if;
1040: --
1041: hr_utility.set_location('Leaving :'||l_proc,25);
1042: --
1043: EXCEPTION
1044: WHEN OTHERS THEN
1045: --
1042: --
1043: EXCEPTION
1044: WHEN OTHERS THEN
1045: --
1046: hr_utility.set_location('Exception :'||l_proc,30);
1047: raise;
1048: --
1049: End generate_cmmtmnt_calc_dates;
1050: --
1064: l_proc varchar2(72) := g_package||'Validate_commitment_dates';
1065: --
1066: Begin
1067: --
1068: hr_utility.set_location('Entering:'||l_proc, 5);
1069: --
1070: -- 1) Check if p_cmmtmnt_end_dt > p_cmmtmnt_start_dt.
1071: --
1072: If p_cmmtmnt_end_dt < p_cmmtmnt_start_dt then
1117: p_period_frequency => p_period_frequency,
1118: p_cmmtmnt_start_dt=> p_cmmtmnt_start_dt,
1119: p_cmmtmnt_end_dt => p_cmmtmnt_end_dt);
1120: --
1121: hr_utility.set_location('Leaving:'||l_proc, 10);
1122: --
1123: EXCEPTION
1124: WHEN OTHERS THEN
1125: raise;
1143: l_proc varchar2(72) := g_package||'get_payroll_period_type';
1144: --
1145: Begin
1146: --
1147: hr_utility.set_location('Entering:'||l_proc,5);
1148: Open csr_period_type;
1149: Fetch csr_period_type into l_period_type;
1150: Close csr_period_type;
1151: hr_utility.set_location('period type is:'||l_period_type,8);
1147: hr_utility.set_location('Entering:'||l_proc,5);
1148: Open csr_period_type;
1149: Fetch csr_period_type into l_period_type;
1150: Close csr_period_type;
1151: hr_utility.set_location('period type is:'||l_period_type,8);
1152: hr_utility.set_location('Leaving:'||l_proc, 10);
1153: RETURN l_period_type;
1154: End;
1155: --
1148: Open csr_period_type;
1149: Fetch csr_period_type into l_period_type;
1150: Close csr_period_type;
1151: hr_utility.set_location('period type is:'||l_period_type,8);
1152: hr_utility.set_location('Leaving:'||l_proc, 10);
1153: RETURN l_period_type;
1154: End;
1155: --
1156: -----------------------get_number_per_fiscal_year--------------------------------
1169:
1170: l_check hr_lookups.meaning%type;
1171: l_proc varchar2(72) := g_package||'get_number_per_fiscal_year';
1172: Begin
1173: hr_utility.set_location('Entering:'||l_proc,5);
1174: Open csr_period_type;
1175: Fetch csr_period_type into l_number_per_fiscal_year;
1176: If csr_period_type%notfound then
1177: hr_utility.set_location('not a valid period type:'||l_proc,10);
1173: hr_utility.set_location('Entering:'||l_proc,5);
1174: Open csr_period_type;
1175: Fetch csr_period_type into l_number_per_fiscal_year;
1176: If csr_period_type%notfound then
1177: hr_utility.set_location('not a valid period type:'||l_proc,10);
1178: Close csr_period_type;
1179: -- Frequency passed is not a valid period type, checking the lookups
1180: l_check := hr_general.decode_lookup(p_lookup_type =>'PAY_BASIS',
1181: p_lookup_code => p_frequency);
1179: -- Frequency passed is not a valid period type, checking the lookups
1180: l_check := hr_general.decode_lookup(p_lookup_type =>'PAY_BASIS',
1181: p_lookup_code => p_frequency);
1182: If l_check is null then
1183: hr_utility.set_location('not a valid sal basis :'||l_proc,20);
1184: l_check := hr_general.decode_lookup(p_lookup_type =>'FREQUENCY',
1185: p_lookup_code => p_frequency);
1186: If l_check is null then
1187: hr_utility.set_location('not a valid assignment frequency :'||l_proc,30);
1183: hr_utility.set_location('not a valid sal basis :'||l_proc,20);
1184: l_check := hr_general.decode_lookup(p_lookup_type =>'FREQUENCY',
1185: p_lookup_code => p_frequency);
1186: If l_check is null then
1187: hr_utility.set_location('not a valid assignment frequency :'||l_proc,30);
1188: RETURN -1;
1189: Else
1190: if p_frequency ='W' then
1191: return 52;
1209: end if;
1210: End if;
1211: End if;
1212: Close csr_period_type;
1213: hr_utility.set_location('Leaving:'||l_proc, 10);
1214: RETURN l_number_per_fiscal_year;
1215: End;
1216: --
1217: --------------------get_commitment_from_formula-------------------------------
1286: --
1287: l_input_count number;
1288: BEGIN
1289: --
1290: hr_utility.set_location ('Entering: '||l_proc,05);
1291: --
1292: begin
1293: /*
1294: * Insert row into fnd_sessions to allow use of global values
1310: -- array locations we put the values into, this is because of the caching
1311: -- mechanism that formula uses.
1312: -- Set the Context for the formula.
1313: --
1314: hr_utility.set_location ('Set Context '||l_proc,10);
1315: --
1316: for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
1317: --
1318: if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
1471: end if;
1472: --
1473: end loop;
1474: --
1475: hr_utility.set_location ('Run formula: '||l_proc,15);
1476: --
1477: -- We have loaded the input record . Now run the formula.
1478: --
1479: ff_exec.run_formula(p_inputs => l_inputs,
1495: end if;
1496: --
1497: end loop;
1498: --
1499: hr_utility.set_location ('Entering: '||l_proc,20);
1500: --
1501: Return l_commitment;
1502: --
1503: End;
1606: --
1607: --
1608: Begin
1609: --
1610: hr_utility.set_location('Entering:'||l_proc, 5);
1611: hr_utility.set_location('p_commit_calculation_dt:'||p_commit_calculation_dt, 5);
1612: hr_utility.set_location('p_actual_cmmtmnt_start_dt:'||p_actual_cmmtmnt_start_dt, 5);
1613: hr_utility.set_location('p_commit_calculation_end_dt:'||p_commit_calculation_end_dt, 5);
1614: hr_utility.set_location('p_actual_cmmtmnt_end_dt:'||p_actual_cmmtmnt_end_dt, 5);
1607: --
1608: Begin
1609: --
1610: hr_utility.set_location('Entering:'||l_proc, 5);
1611: hr_utility.set_location('p_commit_calculation_dt:'||p_commit_calculation_dt, 5);
1612: hr_utility.set_location('p_actual_cmmtmnt_start_dt:'||p_actual_cmmtmnt_start_dt, 5);
1613: hr_utility.set_location('p_commit_calculation_end_dt:'||p_commit_calculation_end_dt, 5);
1614: hr_utility.set_location('p_actual_cmmtmnt_end_dt:'||p_actual_cmmtmnt_end_dt, 5);
1615:
1608: Begin
1609: --
1610: hr_utility.set_location('Entering:'||l_proc, 5);
1611: hr_utility.set_location('p_commit_calculation_dt:'||p_commit_calculation_dt, 5);
1612: hr_utility.set_location('p_actual_cmmtmnt_start_dt:'||p_actual_cmmtmnt_start_dt, 5);
1613: hr_utility.set_location('p_commit_calculation_end_dt:'||p_commit_calculation_end_dt, 5);
1614: hr_utility.set_location('p_actual_cmmtmnt_end_dt:'||p_actual_cmmtmnt_end_dt, 5);
1615:
1616: --
1609: --
1610: hr_utility.set_location('Entering:'||l_proc, 5);
1611: hr_utility.set_location('p_commit_calculation_dt:'||p_commit_calculation_dt, 5);
1612: hr_utility.set_location('p_actual_cmmtmnt_start_dt:'||p_actual_cmmtmnt_start_dt, 5);
1613: hr_utility.set_location('p_commit_calculation_end_dt:'||p_commit_calculation_end_dt, 5);
1614: hr_utility.set_location('p_actual_cmmtmnt_end_dt:'||p_actual_cmmtmnt_end_dt, 5);
1615:
1616: --
1617:
1610: hr_utility.set_location('Entering:'||l_proc, 5);
1611: hr_utility.set_location('p_commit_calculation_dt:'||p_commit_calculation_dt, 5);
1612: hr_utility.set_location('p_actual_cmmtmnt_start_dt:'||p_actual_cmmtmnt_start_dt, 5);
1613: hr_utility.set_location('p_commit_calculation_end_dt:'||p_commit_calculation_end_dt, 5);
1614: hr_utility.set_location('p_actual_cmmtmnt_end_dt:'||p_actual_cmmtmnt_end_dt, 5);
1615:
1616: --
1617:
1618: -- GET ALL ELEMENT ENTRIES FOR THIS ELEMENT TYPE , AND THIS
1631: Close csr_assignment_entries;
1632: Exit;
1633: End if;
1634:
1635: hr_utility.set_location('Entry exist for Assgnt '||l_proc,10);
1636: -- SELECT THE COMMITMENT AMOUNT FOR THIS ELEMENT ENTRY
1637: l_entry_value := 0;
1638: hr_utility.set_location('p_input_value_id '||p_element_input_value_id,11);
1639: hr_utility.set_location('l_element_entry_id '||l_element_entry_id,11);
1634:
1635: hr_utility.set_location('Entry exist for Assgnt '||l_proc,10);
1636: -- SELECT THE COMMITMENT AMOUNT FOR THIS ELEMENT ENTRY
1637: l_entry_value := 0;
1638: hr_utility.set_location('p_input_value_id '||p_element_input_value_id,11);
1639: hr_utility.set_location('l_element_entry_id '||l_element_entry_id,11);
1640: hr_utility.set_location('p_commit_calculation_dt '||to_char(p_commit_calculation_dt),11);
1641: Open csr_elmnt_entry_value
1642: (p_commit_calculation_dt => l_entry_effective_start_date,
1635: hr_utility.set_location('Entry exist for Assgnt '||l_proc,10);
1636: -- SELECT THE COMMITMENT AMOUNT FOR THIS ELEMENT ENTRY
1637: l_entry_value := 0;
1638: hr_utility.set_location('p_input_value_id '||p_element_input_value_id,11);
1639: hr_utility.set_location('l_element_entry_id '||l_element_entry_id,11);
1640: hr_utility.set_location('p_commit_calculation_dt '||to_char(p_commit_calculation_dt),11);
1641: Open csr_elmnt_entry_value
1642: (p_commit_calculation_dt => l_entry_effective_start_date,
1643: p_element_entry_id => l_element_entry_id,
1636: -- SELECT THE COMMITMENT AMOUNT FOR THIS ELEMENT ENTRY
1637: l_entry_value := 0;
1638: hr_utility.set_location('p_input_value_id '||p_element_input_value_id,11);
1639: hr_utility.set_location('l_element_entry_id '||l_element_entry_id,11);
1640: hr_utility.set_location('p_commit_calculation_dt '||to_char(p_commit_calculation_dt),11);
1641: Open csr_elmnt_entry_value
1642: (p_commit_calculation_dt => l_entry_effective_start_date,
1643: p_element_entry_id => l_element_entry_id,
1644: p_input_value_id => p_element_input_value_id,
1644: p_input_value_id => p_element_input_value_id,
1645: p_commit_calculation_end_dt => l_entry_effective_end_date);
1646: Fetch csr_elmnt_entry_value into l_entry_value;
1647:
1648: hr_utility.set_location('Entry Value '||l_entry_value,11);
1649: --
1650: If csr_elmnt_entry_value%found then
1651: --
1652: -- Only if there is entry value , there is any point in getting the
1651: --
1652: -- Only if there is entry value , there is any point in getting the
1653: -- frequency for which it is available and converting it to reqd frequency.
1654: --
1655: hr_utility.set_location('Value is there for this entry',15);
1656:
1657: Close csr_elmnt_entry_value;
1658: --
1659: -- SELECT THE FREQUENCY FOR THE ELEMENT ENTRY based on
1662: -- we will use budget element frequency as the elements frequency
1663: --
1664: -- Comment added by sgoyal
1665: -- We don't ask for frequency input value anymore on form, hence dflt_elmnt_freq will be used.
1666: hr_utility.set_location('Frequency is dflt Elmnt '||p_dflt_elmnt_frequency,30);
1667: --
1668: -- l_element_frequency := p_dflt_elmnt_frequency;
1669: --
1670: if p_dflt_elmnt_frequency is null then
1689: else
1690: l_element_frequency := p_dflt_elmnt_frequency;
1691: end if;
1692:
1693: hr_utility.set_location('Figure :' || l_entry_value,31);
1694: hr_utility.set_location('From :' || l_element_frequency,32);
1695: hr_utility.set_location('To :' || p_commitment_calc_frequency,33);
1696: --
1697: l_converted_amt := Convert_Period_Type(
1690: l_element_frequency := p_dflt_elmnt_frequency;
1691: end if;
1692:
1693: hr_utility.set_location('Figure :' || l_entry_value,31);
1694: hr_utility.set_location('From :' || l_element_frequency,32);
1695: hr_utility.set_location('To :' || p_commitment_calc_frequency,33);
1696: --
1697: l_converted_amt := Convert_Period_Type(
1698: p_bus_grp_id => p_business_group_id,
1691: end if;
1692:
1693: hr_utility.set_location('Figure :' || l_entry_value,31);
1694: hr_utility.set_location('From :' || l_element_frequency,32);
1695: hr_utility.set_location('To :' || p_commitment_calc_frequency,33);
1696: --
1697: l_converted_amt := Convert_Period_Type(
1698: p_bus_grp_id => p_business_group_id,
1699: p_payroll_id => p_payroll_id,
1706: p_asst_std_freq => p_asst_frequency,
1707: p_dflt_elmnt_frequency => p_dflt_elmnt_frequency,
1708: p_budget_calendar_frequency => p_budget_calendar_frequency
1709: );
1710: hr_utility.set_location('converted amt is'||l_converted_amt,34);
1711: --
1712: -- If the period for which we need to calculate commitment is lesser
1713: -- than the input period type , we have to further prorate the
1714: -- calculated commitment value for the no of days in the commitment
1726: Else
1727: l_adjusted_end_dt := p_actual_cmmtmnt_end_dt;
1728: End if;
1729: --
1730: hr_utility.set_location('Before Proration - Converted Amount: '||l_converted_amt,34);
1731: l_converted_amt := nvl(l_converted_amt,0) *
1732: (l_adjusted_end_dt - l_adjusted_start_dt + 1)/
1733: (p_commit_calculation_end_dt - p_commit_calculation_dt + 1);
1734: hr_utility.set_location('After Proration - Converted Amount: '||l_converted_amt,35);
1730: hr_utility.set_location('Before Proration - Converted Amount: '||l_converted_amt,34);
1731: l_converted_amt := nvl(l_converted_amt,0) *
1732: (l_adjusted_end_dt - l_adjusted_start_dt + 1)/
1733: (p_commit_calculation_end_dt - p_commit_calculation_dt + 1);
1734: hr_utility.set_location('After Proration - Converted Amount: '||l_converted_amt,35);
1735: l_entry_commitment := nvl(l_entry_commitment,0) + nvl(l_converted_amt,0);
1736: Else
1737: Close csr_elmnt_entry_value;
1738: End if; /** There is a entry value,that needs to be converted **/
1736: Else
1737: Close csr_elmnt_entry_value;
1738: End if; /** There is a entry value,that needs to be converted **/
1739: End loop; /** element entry commitment calculation **/
1740: hr_utility.set_location('Leaving:'||l_proc, 40);
1741: RETURN l_entry_commitment;
1742: End;
1743: --
1744: ----------------------get_commitment_from_sal_basis--------------------------
1824: l_inter_amt number;
1825: Begin
1826: --
1827: --
1828: hr_utility.set_location('Entering:'||l_proc, 5);
1829: --
1830:
1831: hr_utility.set_location('p_pay_basis_id'||p_pay_basis_id, 5);
1832: hr_utility.set_location('p_assignment_id'||p_assignment_id,5);
1827: --
1828: hr_utility.set_location('Entering:'||l_proc, 5);
1829: --
1830:
1831: hr_utility.set_location('p_pay_basis_id'||p_pay_basis_id, 5);
1832: hr_utility.set_location('p_assignment_id'||p_assignment_id,5);
1833: hr_utility.set_location('p_commit_calculation_dt'||p_commit_calculation_dt,5);
1834: hr_utility.set_location('p_element_type_id'||p_element_type_id,5);
1835: l_entry_value := NULL;
1828: hr_utility.set_location('Entering:'||l_proc, 5);
1829: --
1830:
1831: hr_utility.set_location('p_pay_basis_id'||p_pay_basis_id, 5);
1832: hr_utility.set_location('p_assignment_id'||p_assignment_id,5);
1833: hr_utility.set_location('p_commit_calculation_dt'||p_commit_calculation_dt,5);
1834: hr_utility.set_location('p_element_type_id'||p_element_type_id,5);
1835: l_entry_value := NULL;
1836: l_pay_basis := NULL;
1829: --
1830:
1831: hr_utility.set_location('p_pay_basis_id'||p_pay_basis_id, 5);
1832: hr_utility.set_location('p_assignment_id'||p_assignment_id,5);
1833: hr_utility.set_location('p_commit_calculation_dt'||p_commit_calculation_dt,5);
1834: hr_utility.set_location('p_element_type_id'||p_element_type_id,5);
1835: l_entry_value := NULL;
1836: l_pay_basis := NULL;
1837: --
1830:
1831: hr_utility.set_location('p_pay_basis_id'||p_pay_basis_id, 5);
1832: hr_utility.set_location('p_assignment_id'||p_assignment_id,5);
1833: hr_utility.set_location('p_commit_calculation_dt'||p_commit_calculation_dt,5);
1834: hr_utility.set_location('p_element_type_id'||p_element_type_id,5);
1835: l_entry_value := NULL;
1836: l_pay_basis := NULL;
1837: --
1838: Open csr_pay_basis(p_pay_basis_id => p_pay_basis_id,
1840: p_commit_calculation_end_dt => p_actual_cmmtmnt_end_dt) ;
1841: Fetch csr_pay_basis into l_pay_basis,l_input_value_id;
1842: Close csr_pay_basis;
1843:
1844: hr_utility.set_location('PB Input value is'||l_input_value_id,8);
1845: if l_input_value_id is null then
1846: return null;
1847: end if;
1848: Open csr_salary_basis(p_assignment_id => p_assignment_id,
1856: --
1857: --
1858: -- CONVERT AMOUNT TO BUDGET_CALENDAR FREQUENCY
1859: --
1860: hr_utility.set_location('Figure :' || to_char(l_entry_value),10);
1861: hr_utility.set_location('From :' || l_pay_basis,15);
1862: hr_utility.set_location('To :' || p_commitment_calc_frequency,20);
1863:
1864: l_inter_amt := Convert_Period_Type(
1857: --
1858: -- CONVERT AMOUNT TO BUDGET_CALENDAR FREQUENCY
1859: --
1860: hr_utility.set_location('Figure :' || to_char(l_entry_value),10);
1861: hr_utility.set_location('From :' || l_pay_basis,15);
1862: hr_utility.set_location('To :' || p_commitment_calc_frequency,20);
1863:
1864: l_inter_amt := Convert_Period_Type(
1865: p_bus_grp_id => p_business_group_id,
1858: -- CONVERT AMOUNT TO BUDGET_CALENDAR FREQUENCY
1859: --
1860: hr_utility.set_location('Figure :' || to_char(l_entry_value),10);
1861: hr_utility.set_location('From :' || l_pay_basis,15);
1862: hr_utility.set_location('To :' || p_commitment_calc_frequency,20);
1863:
1864: l_inter_amt := Convert_Period_Type(
1865: p_bus_grp_id => p_business_group_id,
1866: p_payroll_id => p_payroll_id,
1892: l_inter_amt := l_inter_amt * (ad_edDt - ad_stDt + 1)/365;
1893: --
1894: l_converted_amt := nvl(l_converted_amt,0) + nvl(l_inter_amt,0) ;
1895: --
1896: hr_utility.set_location('~~NS:Adjusted Start Date- End Date: '||ad_stDt||' - '||
1897: ad_edDt||' - '||l_inter_amt,8);
1898: hr_utility.set_location('~~NS:l_converted_amt: '||l_converted_amt,8);
1899:
1900: End Loop;
1894: l_converted_amt := nvl(l_converted_amt,0) + nvl(l_inter_amt,0) ;
1895: --
1896: hr_utility.set_location('~~NS:Adjusted Start Date- End Date: '||ad_stDt||' - '||
1897: ad_edDt||' - '||l_inter_amt,8);
1898: hr_utility.set_location('~~NS:l_converted_amt: '||l_converted_amt,8);
1899:
1900: End Loop;
1901: Close csr_salary_basis;
1902:
1930: (p_commit_calculation_end_dt - p_commit_calculation_dt + 1);
1931: --
1932: l_converted_amt := round(l_converted_amt,2);
1933: --
1934: hr_utility.set_location('~~NS:Converted amount: '||l_converted_amt,8);
1935: hr_utility.set_location('Leaving:'||l_proc, 25);
1936: --
1937: RETURN l_converted_amt;
1938: --
1931: --
1932: l_converted_amt := round(l_converted_amt,2);
1933: --
1934: hr_utility.set_location('~~NS:Converted amount: '||l_converted_amt,8);
1935: hr_utility.set_location('Leaving:'||l_proc, 25);
1936: --
1937: RETURN l_converted_amt;
1938: --
1939: End;
1951: l_proc varchar2(72) := g_package||'calculate_overhead';
1952: --
1953: Begin
1954: --
1955: hr_utility.set_location('Entering:'||l_proc, 5);
1956: --
1957: If p_overhead_percentage < 0 then
1958: -- Log error
1959: -- get message text for PQH_NEG_OVERHEAD_PERCENT
1972: Else
1973: p_element_overhead := (p_overhead_percentage/100)*p_element_commitment;
1974: End if;
1975: --
1976: hr_utility.set_location('Leaving:'||l_proc, 10);
1977: --
1978: RETURN 0;
1979: --
1980: exception when others then
2014: l_proc varchar2(72) := g_package||'fetch_bdgt_cmmtmnt_elmnts';
2015: --
2016: Begin
2017: --
2018: hr_utility.set_location('Entering:'||l_proc, 5);
2019: --
2020: Open csr_bdgt_commit_elmnts(p_budget_id => p_budget_id);
2021: --
2022: -- CALCULATE COMMITMENT FOR EACH ELEMENT TYPE OF A POSITION
2067: APP_EXCEPTION.RAISE_EXCEPTION;
2068: --
2069: End if;
2070: --
2071: hr_utility.set_location('Leaving:'||l_proc, 10);
2072: --
2073: EXCEPTION
2074: WHEN OTHERS THEN
2075: raise;
2093: l_is_not_susp boolean := false;
2094: l_proc varchar2(72) := g_package||'check_non_susp_assignment';
2095: --
2096: Begin
2097: hr_utility.set_location('Entering:'||l_proc, 5);
2098: --
2099: --
2100: Open curs_susp_chk;
2101: Fetch curs_susp_chk into l_dummy;
2104: l_is_not_susp :=true;
2105: End if;
2106: Close curs_susp_chk;
2107: --
2108: hr_utility.set_location('Leaving:'||l_proc, 5);
2109: Return l_is_not_susp;
2110: Exception
2111: When others then
2112: raise;
2266: already_exists_flag boolean := false;
2267: --
2268: Begin
2269: --
2270: hr_utility.set_location('Entering:'||l_proc, 5);
2271: --
2272: hr_utility.set_location('Checking whether Budget is a Position Control and Ld profile turned on', 6);
2273:
2274: --
2268: Begin
2269: --
2270: hr_utility.set_location('Entering:'||l_proc, 5);
2271: --
2272: hr_utility.set_location('Checking whether Budget is a Position Control and Ld profile turned on', 6);
2273:
2274: --
2275: --Check whether we need to take LD encumbrnace in to consideration.
2276: --If budget is a Control Budget and LD profile is turned on consider LD Assignment Encumbrance
2281: ld_is_present :=true;
2282: End if;
2283: Close csr_posctrl_budget_chk;
2284: --
2285: hr_utility.set_location('CALCULATING FOR :'||to_char(p_actual_cmmtmnt_start_dt,'DD/MM/RRRR'),7);
2286: --
2287: --
2288: -- CALCULATE COMMITMENT FOR EACH ASSIGMENT IN A POSITION WHICH IS EFFECTIVE
2289: -- AS OF THE COMMIMENT CALCULATION DATE.
2293: If p_budgeted_entity_cd ='POSITION' then
2294: ---
2295: if (p_assignment_id is null) then
2296: --
2297: hr_utility.set_location('Opening the assignements based on entity id ' ||p_entity_id ||' '||p_actual_cmmtmnt_start_dt||' '||p_actual_cmmtmnt_end_dt ,10);
2298:
2299: Open csr_pos_assignments( p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
2300: p_commit_end_dt => p_actual_cmmtmnt_end_dt,
2301: p_position_id => p_entity_id) ;
2300: p_commit_end_dt => p_actual_cmmtmnt_end_dt,
2301: p_position_id => p_entity_id) ;
2302: --
2303: Else
2304: hr_utility.set_location('Opening the assignements based on assignment id ' ||p_assignment_id ,10);
2305: Open csr_pos_single_assignment(p_assignment_id => p_assignment_id) ;
2306: End if;
2307: --
2308: Elsif p_budgeted_entity_cd ='JOB' then
2306: End if;
2307: --
2308: Elsif p_budgeted_entity_cd ='JOB' then
2309: ---
2310: hr_utility.set_location('Opening the assignements based on Job id ' ,10);
2311: Open csr_job_assignments(p_job_id => p_entity_id,
2312: p_commit_end_dt => p_actual_cmmtmnt_end_dt,
2313: p_commit_calculation_dt => p_actual_cmmtmnt_start_dt) ;
2314: --
2313: p_commit_calculation_dt => p_actual_cmmtmnt_start_dt) ;
2314: --
2315: Elsif p_budgeted_entity_cd ='GRADE' then
2316: --
2317: hr_utility.set_location('Opening the assignements based on Grade id ' ,10);
2318: Open csr_grade_assignments(p_grade_id => p_entity_id,
2319: p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
2320: p_commit_end_dt => p_actual_cmmtmnt_end_dt) ;
2321: --
2320: p_commit_end_dt => p_actual_cmmtmnt_end_dt) ;
2321: --
2322: Elsif p_budgeted_entity_cd ='ORGANIZATION' then
2323: --
2324: hr_utility.set_location('Opening the assignements based on ORGANIZATION id ' ,10);
2325: Open csr_org_assignments(p_organization_id => p_entity_id,
2326: p_commit_calculation_dt => p_actual_cmmtmnt_start_dt,
2327: p_commit_end_dt => p_actual_cmmtmnt_end_dt) ;
2328: --
2331: End if;
2332: --Loop
2333: --
2334:
2335: hr_utility.set_location('--Next assignment--',10);
2336: savepoint assignment_level;
2337: --
2338: l_entity_rec_cnt := 0;
2339: If p_entity_id is NOT NULL then
2343: If (p_assignment_id is null) then
2344: --
2345: loop
2346: l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2347: hr_utility.set_location('Fetching the assignements based on Position ' ||l_entity_rec_cnt ,10);
2348: Fetch csr_pos_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2349: t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2350: t_entity_assignments(l_entity_rec_cnt).business_group_id,
2351: t_entity_assignments(l_entity_rec_cnt).payroll_id,
2363: Else
2364: --
2365: loop
2366: l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2367: hr_utility.set_location('Fetching the assignements based on assignment id ' ||p_assignment_id ,10);
2368: Fetch csr_pos_single_assignment into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2369: t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2370: t_entity_assignments(l_entity_rec_cnt).business_group_id,
2371: t_entity_assignments(l_entity_rec_cnt).payroll_id,
2385: Elsif p_budgeted_entity_cd ='JOB' then
2386: ---
2387: loop
2388: l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2389: hr_utility.set_location('Fetching the assignements based on job ' ,10);
2390: Fetch csr_job_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2391: t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2392: t_entity_assignments(l_entity_rec_cnt).business_group_id,
2393: t_entity_assignments(l_entity_rec_cnt).payroll_id,
2405: Elsif p_budgeted_entity_cd ='GRADE' then
2406: ---
2407: loop
2408: l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2409: hr_utility.set_location('Fetching the assignements based on Grade ' ,10);
2410: Fetch csr_grade_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2411: t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2412: t_entity_assignments(l_entity_rec_cnt).business_group_id,
2413: t_entity_assignments(l_entity_rec_cnt).payroll_id,
2425: Elsif p_budgeted_entity_cd ='ORGANIZATION' then
2426: ---
2427: loop
2428: l_entity_rec_cnt := l_entity_rec_cnt +1 ;
2429: hr_utility.set_location('Fetching the assignements based on Org ' ,10);
2430: Fetch csr_org_assignments into t_entity_assignments(l_entity_rec_cnt).assignment_id,
2431: t_entity_assignments(l_entity_rec_cnt).pay_basis_id,
2432: t_entity_assignments(l_entity_rec_cnt).business_group_id,
2433: t_entity_assignments(l_entity_rec_cnt).payroll_id,
2448: --Check if LD encumbered for this Assignment during given period.
2449: --If yes then we need not calculate any commitments for this Assignment and Period;we will
2450: --delete any existing records from pqh_element_commitmetns for that Assignment and Period
2451:
2452: hr_utility.set_location('Fetched all the records - Record Count '||t_entity_assignments.COUNT ,20);
2453: --
2454:
2455: for assign_cnt in NVL(t_entity_assignments.FIRST,0)..NVL(t_entity_assignments.LAST,-1)
2456: loop
2453: --
2454:
2455: for assign_cnt in NVL(t_entity_assignments.FIRST,0)..NVL(t_entity_assignments.LAST,-1)
2456: loop
2457: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_start_date '||t_entity_assignments(assign_cnt).effective_start_date ,20);
2458: hr_utility.set_location('p_actual_cmmtmnt_start_dt '||p_actual_cmmtmnt_start_dt ,20);
2459: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_end_date '||t_entity_assignments(assign_cnt).effective_end_date ,20);
2460: hr_utility.set_location('p_actual_cmmtmnt_end_dt '||p_actual_cmmtmnt_end_dt ,20);
2461:
2454:
2455: for assign_cnt in NVL(t_entity_assignments.FIRST,0)..NVL(t_entity_assignments.LAST,-1)
2456: loop
2457: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_start_date '||t_entity_assignments(assign_cnt).effective_start_date ,20);
2458: hr_utility.set_location('p_actual_cmmtmnt_start_dt '||p_actual_cmmtmnt_start_dt ,20);
2459: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_end_date '||t_entity_assignments(assign_cnt).effective_end_date ,20);
2460: hr_utility.set_location('p_actual_cmmtmnt_end_dt '||p_actual_cmmtmnt_end_dt ,20);
2461:
2462: --Change the commitment_start_date and commitment_end_date as per the
2455: for assign_cnt in NVL(t_entity_assignments.FIRST,0)..NVL(t_entity_assignments.LAST,-1)
2456: loop
2457: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_start_date '||t_entity_assignments(assign_cnt).effective_start_date ,20);
2458: hr_utility.set_location('p_actual_cmmtmnt_start_dt '||p_actual_cmmtmnt_start_dt ,20);
2459: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_end_date '||t_entity_assignments(assign_cnt).effective_end_date ,20);
2460: hr_utility.set_location('p_actual_cmmtmnt_end_dt '||p_actual_cmmtmnt_end_dt ,20);
2461:
2462: --Change the commitment_start_date and commitment_end_date as per the
2463: --assigment details. So that the commitments for the date tracked assigment is calculated correctly.
2456: loop
2457: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_start_date '||t_entity_assignments(assign_cnt).effective_start_date ,20);
2458: hr_utility.set_location('p_actual_cmmtmnt_start_dt '||p_actual_cmmtmnt_start_dt ,20);
2459: hr_utility.set_location('t_entity_assignments(assign_cnt).effective_end_date '||t_entity_assignments(assign_cnt).effective_end_date ,20);
2460: hr_utility.set_location('p_actual_cmmtmnt_end_dt '||p_actual_cmmtmnt_end_dt ,20);
2461:
2462: --Change the commitment_start_date and commitment_end_date as per the
2463: --assigment details. So that the commitments for the date tracked assigment is calculated correctly.
2464: if t_entity_assignments(assign_cnt).effective_start_date < p_actual_cmmtmnt_start_dt then
2461:
2462: --Change the commitment_start_date and commitment_end_date as per the
2463: --assigment details. So that the commitments for the date tracked assigment is calculated correctly.
2464: if t_entity_assignments(assign_cnt).effective_start_date < p_actual_cmmtmnt_start_dt then
2465: hr_utility.set_location('Adjusting effective start date'||p_actual_cmmtmnt_start_dt ,20);
2466: t_entity_assignments(assign_cnt).effective_start_date := p_actual_cmmtmnt_start_dt ;
2467: end if;
2468:
2469: if nvl(t_entity_assignments(assign_cnt).effective_end_date,to_date('31-12-4712','DD-MM-RRRR')) > p_actual_cmmtmnt_end_dt then
2466: t_entity_assignments(assign_cnt).effective_start_date := p_actual_cmmtmnt_start_dt ;
2467: end if;
2468:
2469: if nvl(t_entity_assignments(assign_cnt).effective_end_date,to_date('31-12-4712','DD-MM-RRRR')) > p_actual_cmmtmnt_end_dt then
2470: hr_utility.set_location('Adjusting effective end date' ||p_actual_cmmtmnt_end_dt ,20);
2471: t_entity_assignments(assign_cnt).effective_end_date := p_actual_cmmtmnt_end_dt ;
2472: end if;
2473:
2474: hr_utility.set_location('Assignment_id :'||to_char(t_entity_assignments(assign_cnt).assignment_id),12);
2470: hr_utility.set_location('Adjusting effective end date' ||p_actual_cmmtmnt_end_dt ,20);
2471: t_entity_assignments(assign_cnt).effective_end_date := p_actual_cmmtmnt_end_dt ;
2472: end if;
2473:
2474: hr_utility.set_location('Assignment_id :'||to_char(t_entity_assignments(assign_cnt).assignment_id),12);
2475: l_psp_asg_encumbered :=false;
2476: IF (ld_is_present) THEN
2477: hr_utility.set_location('Caling GET_ASG_ENCUMBRANCES with following Params :',13);
2478: hr_utility.set_location('Encumbrace Start Date :'||to_char(p_actual_cmmtmnt_start_dt),14);
2473:
2474: hr_utility.set_location('Assignment_id :'||to_char(t_entity_assignments(assign_cnt).assignment_id),12);
2475: l_psp_asg_encumbered :=false;
2476: IF (ld_is_present) THEN
2477: hr_utility.set_location('Caling GET_ASG_ENCUMBRANCES with following Params :',13);
2478: hr_utility.set_location('Encumbrace Start Date :'||to_char(p_actual_cmmtmnt_start_dt),14);
2479: hr_utility.set_location('Encumbrance End Date :'||to_char(p_actual_cmmtmnt_end_dt),15);
2480: PSP_PQH_INTEGRATION.GET_ASG_ENCUMBRANCES( P_ASSIGNMENT_ID =>t_entity_assignments(assign_cnt).assignment_id,
2481: P_ENCUMBRANCE_START_DATE => t_entity_assignments(assign_cnt).effective_start_date,
2474: hr_utility.set_location('Assignment_id :'||to_char(t_entity_assignments(assign_cnt).assignment_id),12);
2475: l_psp_asg_encumbered :=false;
2476: IF (ld_is_present) THEN
2477: hr_utility.set_location('Caling GET_ASG_ENCUMBRANCES with following Params :',13);
2478: hr_utility.set_location('Encumbrace Start Date :'||to_char(p_actual_cmmtmnt_start_dt),14);
2479: hr_utility.set_location('Encumbrance End Date :'||to_char(p_actual_cmmtmnt_end_dt),15);
2480: PSP_PQH_INTEGRATION.GET_ASG_ENCUMBRANCES( P_ASSIGNMENT_ID =>t_entity_assignments(assign_cnt).assignment_id,
2481: P_ENCUMBRANCE_START_DATE => t_entity_assignments(assign_cnt).effective_start_date,
2482: P_ENCUMBRANCE_END_DATE => t_entity_assignments(assign_cnt).effective_end_date,
2475: l_psp_asg_encumbered :=false;
2476: IF (ld_is_present) THEN
2477: hr_utility.set_location('Caling GET_ASG_ENCUMBRANCES with following Params :',13);
2478: hr_utility.set_location('Encumbrace Start Date :'||to_char(p_actual_cmmtmnt_start_dt),14);
2479: hr_utility.set_location('Encumbrance End Date :'||to_char(p_actual_cmmtmnt_end_dt),15);
2480: PSP_PQH_INTEGRATION.GET_ASG_ENCUMBRANCES( P_ASSIGNMENT_ID =>t_entity_assignments(assign_cnt).assignment_id,
2481: P_ENCUMBRANCE_START_DATE => t_entity_assignments(assign_cnt).effective_start_date,
2482: P_ENCUMBRANCE_END_DATE => t_entity_assignments(assign_cnt).effective_end_date,
2483: P_ENCUMBRANCE_TABLE =>l_asg_detail_tab,
2487: IF(ld_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
2488: RAISE ld_call_error;
2489: END IF;
2490: IF(l_psp_asg_encumbered) THEN
2491: hr_utility.set_location('LD Encumbered this Assignment:'||to_char(t_entity_assignments(assign_cnt).assignment_id),16);
2492: END IF;
2493: END IF;
2494: l_is_assign_susp := check_non_susp_assignment(t_entity_assignments(assign_cnt).effective_start_date,
2495: t_entity_assignments(assign_cnt).assignment_id);
2497: --Process this Assignment only if LD has not encumbered it
2498: --
2499: if (l_is_assign_susp = true) THEN
2500: if( l_psp_asg_encumbered= false) THEN
2501: hr_utility.set_location('Assignment not Encumbered by LD:'||to_char(t_entity_assignments(assign_cnt).assignment_id),18);
2502: --
2503: -- FOR EACH ASSIGNMENT CALCULATE ELEMENT LEVEL COMMITMENT
2504: --
2505: For cnt in NVL(g_bdgt_cmmtmnt_elmnts.FIRST,0)..NVL(g_bdgt_cmmtmnt_elmnts.LAST,-1)
2505: For cnt in NVL(g_bdgt_cmmtmnt_elmnts.FIRST,0)..NVL(g_bdgt_cmmtmnt_elmnts.LAST,-1)
2506: Loop
2507:
2508: --
2509: hr_utility.set_location('ELMNT TYPE:'||to_char(g_bdgt_cmmtmnt_elmnts(cnt).element_type_id),19);
2510: --
2511: --
2512: l_entry_commitment := 0;
2513: --
2510: --
2511: --
2512: l_entry_commitment := 0;
2513: --
2514: hr_utility.set_location('p_actual_cmmtmnt_start_dt'||p_actual_cmmtmnt_start_dt,20);
2515: hr_utility.set_location('p_actual_cmmtmnt_end_dt'||p_actual_cmmtmnt_end_dt,20);
2516: --
2517: If g_bdgt_cmmtmnt_elmnts(cnt).formula_id is not null then
2518: --
2511: --
2512: l_entry_commitment := 0;
2513: --
2514: hr_utility.set_location('p_actual_cmmtmnt_start_dt'||p_actual_cmmtmnt_start_dt,20);
2515: hr_utility.set_location('p_actual_cmmtmnt_end_dt'||p_actual_cmmtmnt_end_dt,20);
2516: --
2517: If g_bdgt_cmmtmnt_elmnts(cnt).formula_id is not null then
2518: --
2519: hr_utility.set_location('Calculating for Formula',20);
2515: hr_utility.set_location('p_actual_cmmtmnt_end_dt'||p_actual_cmmtmnt_end_dt,20);
2516: --
2517: If g_bdgt_cmmtmnt_elmnts(cnt).formula_id is not null then
2518: --
2519: hr_utility.set_location('Calculating for Formula',20);
2520: --
2521: l_entry_commitment := get_commitment_from_formula (p_formula_id => g_bdgt_cmmtmnt_elmnts(cnt).formula_id,
2522: p_business_group_id => t_entity_assignments(assign_cnt).business_group_id,
2523: p_assignment_id => t_entity_assignments(assign_cnt).assignment_id,
2529: p_commitment_end_date => t_entity_assignments(assign_cnt).effective_end_date);
2530: --
2531: ElsIf g_bdgt_cmmtmnt_elmnts(cnt).salary_basis_flag = 'Y' then
2532: --
2533: hr_utility.set_location('Calculating for Salary Basis',21);
2534: --
2535: -- The commitment for this element type is to be calculated
2536: -- from salary basis;
2537: --
2542: --
2543: If t_entity_assignments(assign_cnt).pay_basis_id IS NOT NULL then
2544: --
2545: --
2546: hr_utility.set_location('Valid Pay Basis id',25);
2547: --
2548: l_entry_commitment := get_commitment_from_sal_basis( p_commit_calculation_dt => p_commit_calculation_dt,
2549: p_actual_cmmtmnt_start_dt => t_entity_assignments(assign_cnt).effective_start_date,
2550: p_commit_calculation_end_dt => p_commit_end_dt,
2561: p_asst_frequency => t_entity_assignments(assign_cnt).frequency);
2562: --
2563: Else
2564: --
2565: hr_utility.set_location('Pay basis id is NULL',30);
2566: --
2567: If g_bdgt_cmmtmnt_elmnts(cnt).element_input_value_id IS NOT NULL then
2568: --
2569: hr_utility.set_location('Default to element type calc',35);
2565: hr_utility.set_location('Pay basis id is NULL',30);
2566: --
2567: If g_bdgt_cmmtmnt_elmnts(cnt).element_input_value_id IS NOT NULL then
2568: --
2569: hr_utility.set_location('Default to element type calc',35);
2570: --
2571: l_entry_commitment := get_commitment_from_elmnt_type( p_commit_calculation_dt => p_commit_calculation_dt,
2572: p_actual_cmmtmnt_start_dt => t_entity_assignments(assign_cnt).effective_start_date,
2573: p_commit_calculation_end_dt => p_commit_end_dt,
2602: End if;
2603: --
2604: Else
2605: --
2606: hr_utility.set_location('Calc from element type',40);
2607: --
2608: l_entry_commitment := get_commitment_from_elmnt_type( p_commit_calculation_dt =>p_commit_calculation_dt,
2609: p_actual_cmmtmnt_start_dt => t_entity_assignments(assign_cnt).effective_start_date,
2610: p_commit_calculation_end_dt => p_commit_end_dt,
2623: --
2624: --
2625: End if;
2626: --
2627: hr_utility.set_location('Entry commitment :'||to_char(nvl(l_entry_commitment,0)),45);
2628: --
2629: --
2630: l_status := Calculate_overhead (p_element_commitment => l_entry_commitment,
2631: p_overhead_percentage => g_bdgt_cmmtmnt_elmnts(cnt).Overhead_percentage,
2630: l_status := Calculate_overhead (p_element_commitment => l_entry_commitment,
2631: p_overhead_percentage => g_bdgt_cmmtmnt_elmnts(cnt).Overhead_percentage,
2632: p_element_overhead => l_element_overhead);
2633: --
2634: hr_utility.set_location('Calculate_overhead finished:'||l_status,45);
2635: If l_status = -1 then
2636: rollback to assignment_level;
2637: Exit;
2638: End if;
2635: If l_status = -1 then
2636: rollback to assignment_level;
2637: Exit;
2638: End if;
2639: hr_utility.set_location('Calculate_overhead is success',45);
2640: --
2641: --
2642: -- If commitment is being re-generated for the same position and same
2643: -- dates we will delete the old record with the commitment values .
2645: --
2646: --Adding into the plsql table if the commitments are not already added otherwise updating and then performing insert to avoid duplicate datas.
2647:
2648: if t_element_commitment.FIRST is null then
2649: hr_utility.set_location('First element is created in t_element_commitment',40);
2650: t_element_commitment(1).assignment_id := t_entity_assignments(assign_cnt).assignment_id ;
2651: t_element_commitment(1).element_type_id := g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ;
2652: t_element_commitment(1).commitment := (l_entry_commitment+l_element_overhead) ;
2653: else
2650: t_element_commitment(1).assignment_id := t_entity_assignments(assign_cnt).assignment_id ;
2651: t_element_commitment(1).element_type_id := g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ;
2652: t_element_commitment(1).commitment := (l_entry_commitment+l_element_overhead) ;
2653: else
2654: hr_utility.set_location('Its not the first element',40);
2655: already_exists_flag := false;
2656: for element_cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) loop
2657: hr_utility.set_location('inside for loop',40);
2658: hr_utility.set_location('assignement' || t_element_commitment(element_cnt).assignment_id ||'x'||t_entity_assignments(assign_cnt).assignment_id,40);
2653: else
2654: hr_utility.set_location('Its not the first element',40);
2655: already_exists_flag := false;
2656: for element_cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) loop
2657: hr_utility.set_location('inside for loop',40);
2658: hr_utility.set_location('assignement' || t_element_commitment(element_cnt).assignment_id ||'x'||t_entity_assignments(assign_cnt).assignment_id,40);
2659: hr_utility.set_location('element type'||t_element_commitment(element_cnt).ELEMENT_TYPE_ID ||'x'||g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ,40);
2660: if (t_element_commitment(element_cnt).assignment_id = t_entity_assignments(assign_cnt).assignment_id and
2661: t_element_commitment(element_cnt).ELEMENT_TYPE_ID = g_bdgt_cmmtmnt_elmnts(cnt).element_type_id) then
2654: hr_utility.set_location('Its not the first element',40);
2655: already_exists_flag := false;
2656: for element_cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) loop
2657: hr_utility.set_location('inside for loop',40);
2658: hr_utility.set_location('assignement' || t_element_commitment(element_cnt).assignment_id ||'x'||t_entity_assignments(assign_cnt).assignment_id,40);
2659: hr_utility.set_location('element type'||t_element_commitment(element_cnt).ELEMENT_TYPE_ID ||'x'||g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ,40);
2660: if (t_element_commitment(element_cnt).assignment_id = t_entity_assignments(assign_cnt).assignment_id and
2661: t_element_commitment(element_cnt).ELEMENT_TYPE_ID = g_bdgt_cmmtmnt_elmnts(cnt).element_type_id) then
2662: hr_utility.set_location('Already existing value is updated cnt is:'||element_cnt,40);
2655: already_exists_flag := false;
2656: for element_cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) loop
2657: hr_utility.set_location('inside for loop',40);
2658: hr_utility.set_location('assignement' || t_element_commitment(element_cnt).assignment_id ||'x'||t_entity_assignments(assign_cnt).assignment_id,40);
2659: hr_utility.set_location('element type'||t_element_commitment(element_cnt).ELEMENT_TYPE_ID ||'x'||g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ,40);
2660: if (t_element_commitment(element_cnt).assignment_id = t_entity_assignments(assign_cnt).assignment_id and
2661: t_element_commitment(element_cnt).ELEMENT_TYPE_ID = g_bdgt_cmmtmnt_elmnts(cnt).element_type_id) then
2662: hr_utility.set_location('Already existing value is updated cnt is:'||element_cnt,40);
2663: t_element_commitment(element_cnt).commitment := t_element_commitment(element_cnt).commitment + l_entry_commitment+l_element_overhead ;
2658: hr_utility.set_location('assignement' || t_element_commitment(element_cnt).assignment_id ||'x'||t_entity_assignments(assign_cnt).assignment_id,40);
2659: hr_utility.set_location('element type'||t_element_commitment(element_cnt).ELEMENT_TYPE_ID ||'x'||g_bdgt_cmmtmnt_elmnts(cnt).element_type_id ,40);
2660: if (t_element_commitment(element_cnt).assignment_id = t_entity_assignments(assign_cnt).assignment_id and
2661: t_element_commitment(element_cnt).ELEMENT_TYPE_ID = g_bdgt_cmmtmnt_elmnts(cnt).element_type_id) then
2662: hr_utility.set_location('Already existing value is updated cnt is:'||element_cnt,40);
2663: t_element_commitment(element_cnt).commitment := t_element_commitment(element_cnt).commitment + l_entry_commitment+l_element_overhead ;
2664: already_exists_flag := true;
2665: EXit;
2666: end if;
2669: declare
2670: element_cnt number;
2671: begin
2672: element_cnt := t_element_commitment.COUNT+1 ;
2673: hr_utility.set_location('new element is created @:'||(t_element_commitment.COUNT+1),40);
2674: t_element_commitment(element_cnt).assignment_id := t_entity_assignments(assign_cnt).assignment_id ;
2675: t_element_commitment(element_cnt).ELEMENT_TYPE_ID := g_bdgt_cmmtmnt_elmnts(cnt).element_type_id;
2676: t_element_commitment(element_cnt).commitment := l_entry_commitment+l_element_overhead ;
2677: end;
2686: /** l_psp_asg_encumbered= true LD has encumbered **/
2687: -- LD Encumbered for this assignment , so there will be no PQH commitmens for this assignment
2688: --. Delete Assignment and Period records from pqh_commitment_elements
2689: --
2690: hr_utility.set_location('Delete LD encumbered commitments'||to_char(t_entity_assignments(assign_cnt).assignment_id),55);
2691: --
2692: DELETE
2693: FROM pqh_element_commitments
2694: WHERE budget_version_id = p_budget_version_id
2699: /** End of check for LD encumbrance **/
2700: End if;
2701: /** End of check for suspended assignment **/
2702: End loop;
2703: hr_utility.set_location('Consolidated the values now proceeding to insert',48);
2704: -- Printing the content of the t_element_commitment
2705: FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2706: hr_utility.set_location(t_element_commitment(cnt).element_type_id||'x'||t_element_commitment(cnt).assignment_id||'x'||t_element_commitment(cnt).commitment,48);
2707: end loop;
2702: End loop;
2703: hr_utility.set_location('Consolidated the values now proceeding to insert',48);
2704: -- Printing the content of the t_element_commitment
2705: FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2706: hr_utility.set_location(t_element_commitment(cnt).element_type_id||'x'||t_element_commitment(cnt).assignment_id||'x'||t_element_commitment(cnt).commitment,48);
2707: end loop;
2708: --end printing
2709: FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2710: hr_utility.set_location('Insert commitment'||cnt,48);
2706: hr_utility.set_location(t_element_commitment(cnt).element_type_id||'x'||t_element_commitment(cnt).assignment_id||'x'||t_element_commitment(cnt).commitment,48);
2707: end loop;
2708: --end printing
2709: FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2710: hr_utility.set_location('Insert commitment'||cnt,48);
2711: hr_utility.set_location('ELEMENT_TYPE_ID: '|| t_element_commitment(cnt).element_type_id||'ASSIGNMENT_ID: '||t_element_commitment(cnt).assignment_id,48);
2712: hr_utility.set_location('BUDGET_VERSION_ID: '||p_BUDGET_VERSION_ID,48);
2713: --
2714: INSERT
2707: end loop;
2708: --end printing
2709: FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2710: hr_utility.set_location('Insert commitment'||cnt,48);
2711: hr_utility.set_location('ELEMENT_TYPE_ID: '|| t_element_commitment(cnt).element_type_id||'ASSIGNMENT_ID: '||t_element_commitment(cnt).assignment_id,48);
2712: hr_utility.set_location('BUDGET_VERSION_ID: '||p_BUDGET_VERSION_ID,48);
2713: --
2714: INSERT
2715: INTO pqh_element_commitments
2708: --end printing
2709: FOR cnt in NVL(t_element_commitment.FIRST,0)..NVL(t_element_commitment.LAST,-1) LOOP
2710: hr_utility.set_location('Insert commitment'||cnt,48);
2711: hr_utility.set_location('ELEMENT_TYPE_ID: '|| t_element_commitment(cnt).element_type_id||'ASSIGNMENT_ID: '||t_element_commitment(cnt).assignment_id,48);
2712: hr_utility.set_location('BUDGET_VERSION_ID: '||p_BUDGET_VERSION_ID,48);
2713: --
2714: INSERT
2715: INTO pqh_element_commitments
2716: (
2738: sysdate,
2739: -1
2740: )
2741: ;
2742: hr_utility.set_location('Insert commitment2',48);
2743: end loop;
2744:
2745: /** assignment commitment calculation **/
2746: --
2743: end loop;
2744:
2745: /** assignment commitment calculation **/
2746: --
2747: hr_utility.set_location('Leaving:'||l_proc, 60);
2748: --
2749: RETURN 0;
2750: --
2751: Exception
2751: Exception
2752: When ld_call_error then
2753: -- Log error
2754:
2755: hr_utility.set_location('Exception raised when inserting record into elements table',1);
2756: pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => SQLERRM );
2757: RETURN -1;
2758: When others then
2759: -- Log error
2757: RETURN -1;
2758: When others then
2759: -- Log error
2760:
2761: hr_utility.set_location('Exception raised when inserting record into elements table',2);
2762: pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => SQLERRM );
2763: RETURN -1;
2764: End POPULATE_COMMITMENT_TABLE;
2765:
2802: l_cmmtmnt_end_dt date;*/
2803: --
2804: Begin
2805: --
2806: hr_utility.set_location('Entering:'||l_proc, 5);
2807: --
2808: /* --Kmullapu : Removed these params as we are no longer using commitment date
2809: Commitment dates are defaulted to Budget Dates.
2810: l_cmmtmnt_start_dt := fnd_date.canonical_to_date(p_cmmtmnt_start_dt);
2826: p_period_frequency => p_period_frequency);
2827: --
2828: COMMIT;
2829: --
2830: hr_utility.set_location('Leaving:'||l_proc, 10);
2831: --
2832: End;
2833: --
2834: -----------------------calculate_money_cmmtmnts--------------------------------
2875: --
2876: Begin
2877: --
2878:
2879: hr_utility.set_location('Entering:'||l_proc, 5);
2880: --
2881: -- CHECK IF THIS IS A VALID BUDGET VERSION.GET THE BUDGETS CALENDAR FREQ.
2882: --
2883: --
2961: l_context_level := l_context_level + 1;
2962: --
2963: g_budget_version_status := 'CALCULATION_SUCCESS';
2964: --
2965: hr_utility.set_location('Begin Processing positions', 6);
2966: --
2967: For pos_cnt in NVL(g_budget_entities.FIRST,0)..NVL(g_budget_entities.LAST,-1) Loop
2968: --
2969: If p_budgeted_entity_cd = 'POSITION' then
2985: p_level => l_context_level,
2986: p_log_context => l_log_context
2987: );
2988: --
2989: hr_utility.set_location('Set Savepoint', 9);
2990: --
2991: -- Set Save point for each position processed . We will rollback even if
2992: -- one record for the position failed.
2993: --
2997: --
2998: FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
2999: --
3000: Loop
3001: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3002: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3003: --
3004: -- The foll function gets all element types for a budget for which
3005: -- commitment has to be calculated, Calculates the respective commitment
2998: FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
2999: --
3000: Loop
3001: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3002: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3003: --
3004: -- The foll function gets all element types for a budget for which
3005: -- commitment has to be calculated, Calculates the respective commitment
3006: -- and Stores it into the commitment table.
3004: -- The foll function gets all element types for a budget for which
3005: -- commitment has to be calculated, Calculates the respective commitment
3006: -- and Stores it into the commitment table.
3007: --
3008: hr_utility.set_location('Position id :'||to_char(g_budget_entities(pos_cnt).entity_id),15);
3009: --
3010: l_entity_id := g_budget_entities(pos_cnt).entity_id;
3011: --
3012: l_status := populate_commitment_table
3041: End loop; /** Commitment Calculation dates **/
3042: --
3043: -- Save if commitment calculation status for this position.
3044: --
3045: hr_utility.set_location('PositionId '||g_budget_entities(pos_cnt).entity_id,13);
3046:
3047: If p_budgeted_entity_cd = 'POSITION' then
3048:
3049: Update pqh_budget_details
3086: --
3087: pqh_process_batch_log.end_log;
3088: --
3089: --
3090: hr_utility.set_location('Leaving:'||l_proc, 20);
3091: --
3092: EXCEPTION
3093: WHEN OTHERS THEN
3094: raise;
3128: BEGIN -- work_schedule_total_hours
3129:
3130: -- Set range to a single week if no dates are entered:
3131:
3132: hr_utility.set_location('work_schedule_total_hours setting dates', 3);
3133: v_range_start := NVL(p_range_start, sysdate);
3134: v_range_end := NVL(p_range_end, sysdate + 6);
3135:
3136: -- Check for valid range
3134: v_range_end := NVL(p_range_end, sysdate + 6);
3135:
3136: -- Check for valid range
3137: --
3138: hr_utility.set_location('work_schedule_total_hours', 5);
3139: --
3140: IF v_range_start > v_range_end THEN
3141: hr_utility.set_location('work_schedule_total_hours', 7);
3142: RETURN v_total_hours;
3137: --
3138: hr_utility.set_location('work_schedule_total_hours', 5);
3139: --
3140: IF v_range_start > v_range_end THEN
3141: hr_utility.set_location('work_schedule_total_hours', 7);
3142: RETURN v_total_hours;
3143: END IF;
3144: --
3145: -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
3154: insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'),trunc(sysdate));
3155: END IF;
3156: --
3157: -- Track range dates:
3158: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
3159: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
3160: --
3161: -- Check if the work schedule is an id or a name. If the work
3162: -- schedule does not exist, then return 0.
3155: END IF;
3156: --
3157: -- Track range dates:
3158: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
3159: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
3160: --
3161: -- Check if the work schedule is an id or a name. If the work
3162: -- schedule does not exist, then return 0.
3163: --
3189: end if;
3190: --
3191: v_curr_date := v_range_start;
3192: --
3193: hr_utility.set_location('work_schedule_total_hours curr_date = '||to_char(v_curr_date), 20);
3194: --
3195: LOOP
3196: v_curr_day := TO_CHAR(v_curr_date, 'DY');
3197: --
3194: --
3195: LOOP
3196: v_curr_day := TO_CHAR(v_curr_date, 'DY');
3197: --
3198: hr_utility.set_location('curr_day = '||v_curr_day, 20);
3199: --
3200: hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
3201: v_total_hours := v_total_hours
3202: + FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value
3196: v_curr_day := TO_CHAR(v_curr_date, 'DY');
3197: --
3198: hr_utility.set_location('curr_day = '||v_curr_day, 20);
3199: --
3200: hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
3201: v_total_hours := v_total_hours
3202: + FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value
3203: (p_bg_id,
3204: c_ws_tab_name,
3205: v_ws_name,
3206: v_curr_day));
3207: v_curr_date := v_curr_date + 1;
3208: --
3209: hr_utility.set_location('curr_date = '||to_char(v_curr_date), 20);
3210: --
3211: EXIT WHEN v_curr_date > v_range_end;
3212: --
3213: END LOOP;
3243: --
3244: -- Check for valid range. If range_end is NULL or range_end < range_start
3245: -- 0 hours will be returned.
3246: --
3247: hr_utility.set_location('standard_hours_worked', 5);
3248: --
3249: IF p_range_start > p_range_end THEN
3250: hr_utility.set_location('standard_hours_worked', 7);
3251: RETURN v_total_hours;
3246: --
3247: hr_utility.set_location('standard_hours_worked', 5);
3248: --
3249: IF p_range_start > p_range_end THEN
3250: hr_utility.set_location('standard_hours_worked', 7);
3251: RETURN v_total_hours;
3252: END IF;
3253: --
3254: -- This portion calculates how may hours are worked in a day.
3271: v_wrkday_hours := p_std_hrs/l_wkdays;
3272: --
3273: v_curr_date := p_range_start;
3274: --
3275: hr_utility.set_location('standard_hours_worked', 10);
3276:
3277: LOOP
3278: --
3279: -- Loop through all the working days in the range supplied and find
3280: -- how may hours were worked , during that period.
3281: --
3282: v_curr_day := TO_CHAR(v_curr_date, 'DY');
3283: --
3284: hr_utility.set_location('standard_hours_worked', 15);
3285: --
3286: IF UPPER(v_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
3287: v_total_hours := v_total_hours + v_wrkday_hours;
3288: hr_utility.set_location('standard_hours_worked v_total_hours = ', v_total_hours);
3284: hr_utility.set_location('standard_hours_worked', 15);
3285: --
3286: IF UPPER(v_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
3287: v_total_hours := v_total_hours + v_wrkday_hours;
3288: hr_utility.set_location('standard_hours_worked v_total_hours = ', v_total_hours);
3289: END IF;
3290: --
3291: v_curr_date := v_curr_date + 1;
3292: --
3366: -- Selecting "count" because we want to continue processing even if
3367: -- the from_freq is not a pay basis.
3368: --
3369: --
3370: hr_utility.set_location('Get_Annualizing_Factor', 5);
3371: --
3372: begin -- Is passes frequency , pay basis?
3373:
3374: --
3373:
3374: --
3375: -- Decode pay basis and set v_annualizing_factor accordingly.
3376: --
3377: hr_utility.set_location('Is it salary basis'||nvl(p_freq,'null'),10);
3378:
3379: --
3380: --
3381: SELECT lookup_code
3388: -- If the passed frequency , uses lookup PAY_BASIS , then the foll
3389: -- portion will be executed .Otherwise , exception NO DATA FOUND will
3390: -- be raised .
3391: --
3392: hr_utility.set_location('Get_Annualizing_Factor', 15);
3393: --
3394: v_use_pay_basis := 1;
3395: --
3396: -- The lookup PAY_BASIS uses 4 lookup_code's - MONTHLY , ANNUAL ,
3398: -- frequency , if it is one of the above 4 lookup codes.
3399: --
3400: IF v_pay_basis = 'MONTHLY' THEN
3401: --
3402: hr_utility.set_location('Monthly salary basis ',20);
3403: --
3404: v_annualizing_factor := 12;
3405: --
3406: ELSIF v_pay_basis = 'HOURLY' THEN
3404: v_annualizing_factor := 12;
3405: --
3406: ELSIF v_pay_basis = 'HOURLY' THEN
3407: --
3408: hr_utility.set_location('Hourly salary basis',25);
3409: --
3410: IF p_period_start_date IS NOT NULL THEN
3411: v_range_start := p_period_start_date;
3412: v_range_end := p_period_end_date;
3448: END IF;
3449: --
3450: ELSIF v_pay_basis = 'PERIOD' THEN
3451: --
3452: hr_utility.set_location('Period salary basis',40);
3453: --
3454: -- If the salary basis is PERIOD , then fetch the period type of the
3455: -- associated payroll . But as seen in the assignment form , it is
3456: -- possible to create an assignment with pay basis = PERIOD but the
3458: -- circumstances , We will fall back to budget element frequency.
3459: --
3460: If p_payroll IS NOT NULL then
3461: --
3462: hr_utility.set_location('Payroll is Exists',45);
3463: --
3464: l_payroll_period_type := get_payroll_period_type
3465: (p_payroll_id => p_payroll,
3466: p_effective_dt => p_period_start_date);
3476: v_annualizing_factor := get_number_per_fiscal_year(p_dflt_elmnt_frequency);
3477: --
3478: End if;
3479:
3480: hr_utility.set_location('Get_Annualizing_Factor', 50);
3481:
3482: ELSIF v_pay_basis = 'ANNUAL' THEN
3483: --
3484: hr_utility.set_location('Annual salary basis',55);
3480: hr_utility.set_location('Get_Annualizing_Factor', 50);
3481:
3482: ELSIF v_pay_basis = 'ANNUAL' THEN
3483: --
3484: hr_utility.set_location('Annual salary basis',55);
3485: --
3486: v_annualizing_factor := 1;
3487: --
3488: ELSE
3488: ELSE
3489: --
3490: -- Did not recognize "pay basis"
3491: --
3492: hr_utility.set_location('Get_Annualizing_Factor', 60);
3493:
3494: v_annualizing_factor := 0;
3495: RETURN v_annualizing_factor;
3496:
3498:
3499: EXCEPTION
3500: WHEN NO_DATA_FOUND THEN
3501: --
3502: hr_utility.set_location('Exception raised !',65);
3503: --
3504: -- The only reason why this exception should be raised , is when an invalid
3505: -- salary basis has been entered .
3506: --
3524: (UPPER(p_freq) = 'NOT ENTERED') THEN
3525: --
3526: -- Get "annualizing factor" from period type of the payroll.
3527: --
3528: hr_utility.set_location('Get_Annualizing_Factor', 70);
3529: --
3530: If p_payroll IS NOT NULL then
3531: --
3532: l_payroll_period_type := get_payroll_period_type
3551: --
3552: --
3553: BEGIN
3554: --
3555: hr_utility.set_location('Get_Annualizing_Factor',75);
3556:
3557: SELECT PT.number_per_fiscal_year
3558: INTO v_annualizing_factor
3559: FROM per_time_period_types PT
3558: INTO v_annualizing_factor
3559: FROM per_time_period_types PT
3560: WHERE UPPER(PT.period_type) = UPPER(p_freq);
3561:
3562: hr_utility.set_location('Get_Annualizing_Factor',80);
3563: --
3564: exception when NO_DATA_FOUND then
3565: --
3566: -- Added as part of SALLY CLEANUP.
3567: -- Could have been passed in an ASG_FREQ dbi which might
3568: -- have the values of 'Day' or 'Month' which do not map to a
3569: -- time period type. So we'll do these by hand.
3570:
3571: hr_utility.set_location('Get_Annualizing_Factor',85);
3572: IF UPPER(p_freq) = 'DAY' THEN
3573: v_annualizing_factor := c_days_per_year;
3574: ELSIF UPPER(p_freq) = 'MONTH' THEN
3575: v_annualizing_factor := c_months_per_year;
3580: END;
3581:
3582: ELSE -- Hourly employee...
3583: --
3584: hr_utility.set_location('Get_Annualizing_Factor', 90);
3585:
3586: IF p_period_start_date IS NOT NULL THEN
3587: v_range_start := p_period_start_date;
3588: v_range_end := p_period_end_date;
3591: v_range_start := sysdate;
3592: v_range_end := sysdate + 6;
3593: v_period_hours := FALSE;
3594: END IF;
3595: hr_utility.set_location('getting std hours', 100);
3596: v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
3597: v_range_start,
3598: v_range_end,
3599: p_asg_std_freq);
3610: --
3611: ----The Convert_Period_Type function starts here --------
3612: --
3613: BEGIN
3614: hr_utility.set_location('Convert_Period_Type', 10);
3615: hr_utility.set_location('p_from_freq'||p_from_freq, 10);
3616: hr_utility.set_location('p_to_freq'||p_to_freq, 10);
3617: --
3618: -- If From_Freq and To_Freq are the same, then we're done.
3611: ----The Convert_Period_Type function starts here --------
3612: --
3613: BEGIN
3614: hr_utility.set_location('Convert_Period_Type', 10);
3615: hr_utility.set_location('p_from_freq'||p_from_freq, 10);
3616: hr_utility.set_location('p_to_freq'||p_to_freq, 10);
3617: --
3618: -- If From_Freq and To_Freq are the same, then we're done.
3619: --
3612: --
3613: BEGIN
3614: hr_utility.set_location('Convert_Period_Type', 10);
3615: hr_utility.set_location('p_from_freq'||p_from_freq, 10);
3616: hr_utility.set_location('p_to_freq'||p_to_freq, 10);
3617: --
3618: -- If From_Freq and To_Freq are the same, then we're done.
3619: --
3620: hr_utility.set_location('Starting Conversion p_from_freq'||p_from_freq||' p_to_freq'||p_to_freq,15);
3616: hr_utility.set_location('p_to_freq'||p_to_freq, 10);
3617: --
3618: -- If From_Freq and To_Freq are the same, then we're done.
3619: --
3620: hr_utility.set_location('Starting Conversion p_from_freq'||p_from_freq||' p_to_freq'||p_to_freq,15);
3621:
3622: IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED') THEN
3623:
3624: RETURN p_figure;
3641: --
3642: -- Annualize "Figure" and convert to To_Freq.
3643: --
3644:
3645: hr_utility.set_location('v_from_annualizing_factor '|| v_from_annualizing_factor, 20);
3646: hr_utility.set_location('v_to_annualizing_factor' || v_to_annualizing_factor, 20);
3647: hr_utility.set_location('p_figure' || p_figure, 20);
3648:
3649: hr_utility.set_location('Convert_Period_Type', 20);
3642: -- Annualize "Figure" and convert to To_Freq.
3643: --
3644:
3645: hr_utility.set_location('v_from_annualizing_factor '|| v_from_annualizing_factor, 20);
3646: hr_utility.set_location('v_to_annualizing_factor' || v_to_annualizing_factor, 20);
3647: hr_utility.set_location('p_figure' || p_figure, 20);
3648:
3649: hr_utility.set_location('Convert_Period_Type', 20);
3650:
3643: --
3644:
3645: hr_utility.set_location('v_from_annualizing_factor '|| v_from_annualizing_factor, 20);
3646: hr_utility.set_location('v_to_annualizing_factor' || v_to_annualizing_factor, 20);
3647: hr_utility.set_location('p_figure' || p_figure, 20);
3648:
3649: hr_utility.set_location('Convert_Period_Type', 20);
3650:
3651: IF v_to_annualizing_factor = 0 OR
3645: hr_utility.set_location('v_from_annualizing_factor '|| v_from_annualizing_factor, 20);
3646: hr_utility.set_location('v_to_annualizing_factor' || v_to_annualizing_factor, 20);
3647: hr_utility.set_location('p_figure' || p_figure, 20);
3648:
3649: hr_utility.set_location('Convert_Period_Type', 20);
3650:
3651: IF v_to_annualizing_factor = 0 OR
3652: v_to_annualizing_factor = -999 OR
3653: v_from_annualizing_factor = -999 THEN
3651: IF v_to_annualizing_factor = 0 OR
3652: v_to_annualizing_factor = -999 OR
3653: v_from_annualizing_factor = -999 THEN
3654:
3655: hr_utility.set_location('Convert_Period_Type', 25);
3656:
3657: v_converted_figure := 0;
3658: RETURN v_converted_figure;
3659:
3658: RETURN v_converted_figure;
3659:
3660: ELSE
3661:
3662: hr_utility.set_location('Convert_Period_Type', 30);
3663:
3664: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
3665:
3666: END IF;
3664: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
3665:
3666: END IF;
3667:
3668: hr_utility.set_location('Leaving : Convert_Period_Type'||NVL(v_converted_figure,0), 35);
3669:
3670: RETURN v_converted_figure;
3671:
3672: END Convert_Period_Type;
3784:
3785: l_proc varchar2(72) := g_package || 'refresh_asg_ele_commitments';
3786: Begin
3787:
3788: hr_utility.set_location('Entering:'||l_proc, 5);
3789:
3790: -- Control Budget Id (position - money) as of the effective date for the current BG
3791: Open csr_get_ctrl_bdgt;
3792: Fetch csr_get_ctrl_bdgt into l_budget_id,l_budget_name,
3862: FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
3863: --
3864: Loop
3865:
3866: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3867: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3868: --
3869: -- The foll function gets all element types for a budget for which
3870: -- commitment has to be calculated, Calculates the respective commitment
3863: --
3864: Loop
3865:
3866: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3867: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3868: --
3869: -- The foll function gets all element types for a budget for which
3870: -- commitment has to be calculated, Calculates the respective commitment
3871: -- and Stores it into the commitment table.
3979: FOR cnt IN NVL(g_cmmtmnt_calc_dates.FIRST,0)..NVL(g_cmmtmnt_calc_dates.LAST,-1)
3980: --
3981: Loop
3982: --
3983: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3984: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3985:
3986: savepoint salary_proposal_level;
3987: hr_utility.set_location('Default to element type calc',35);
3980: --
3981: Loop
3982: --
3983: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3984: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3985:
3986: savepoint salary_proposal_level;
3987: hr_utility.set_location('Default to element type calc',35);
3988: --
3983: hr_utility.set_location('->'||to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt,'DD/MM/RRRR'),13);
3984: hr_utility.set_location('#->'||to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,'DD/MM/RRRR')||' to '|| to_char(g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,'DD/MM/RRRR'),14);
3985:
3986: savepoint salary_proposal_level;
3987: hr_utility.set_location('Default to element type calc',35);
3988: --
3989: -- If (p_effective_date between g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt and g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt) then
3990: If (p_effective_date between g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt and g_cmmtmnt_calc_dates(cnt).cmmtmnt_end_dt) OR (p_effective_date <= g_cmmtmnt_calc_dates(cnt).cmmtmnt_start_dt) Then
3991: --
4005: p_element_input_value_id => l_input_value_id,
4006: p_normal_hours => l_normal_hours,
4007: p_asst_frequency => l_frequency);
4008: --
4009: hr_utility.set_location('l_entry_commitment'||l_entry_commitment,44);
4010: l_status := Calculate_overhead
4011: (p_element_commitment => l_entry_commitment,
4012: p_overhead_percentage => l_overhead_percentage,
4013: p_element_overhead => l_element_overhead);
4010: l_status := Calculate_overhead
4011: (p_element_commitment => l_entry_commitment,
4012: p_overhead_percentage => l_overhead_percentage,
4013: p_element_overhead => l_element_overhead);
4014: hr_utility.set_location('l_entry_commitment: '||l_entry_commitment,44);
4015: hr_utility.set_location('l_element_overhead: '||nvl(l_element_overhead,0),44);
4016: hr_utility.set_location('l_status: '||l_status,45);
4017: If l_status = -1 then
4018: rollback to salary_proposal_level;
4011: (p_element_commitment => l_entry_commitment,
4012: p_overhead_percentage => l_overhead_percentage,
4013: p_element_overhead => l_element_overhead);
4014: hr_utility.set_location('l_entry_commitment: '||l_entry_commitment,44);
4015: hr_utility.set_location('l_element_overhead: '||nvl(l_element_overhead,0),44);
4016: hr_utility.set_location('l_status: '||l_status,45);
4017: If l_status = -1 then
4018: rollback to salary_proposal_level;
4019: End if;
4012: p_overhead_percentage => l_overhead_percentage,
4013: p_element_overhead => l_element_overhead);
4014: hr_utility.set_location('l_entry_commitment: '||l_entry_commitment,44);
4015: hr_utility.set_location('l_element_overhead: '||nvl(l_element_overhead,0),44);
4016: hr_utility.set_location('l_status: '||l_status,45);
4017: If l_status = -1 then
4018: rollback to salary_proposal_level;
4019: End if;
4020:
4017: If l_status = -1 then
4018: rollback to salary_proposal_level;
4019: End if;
4020:
4021: hr_utility.set_location('Delete commitment',46);
4022: --
4023: Delete from pqh_element_commitments
4024: Where budget_version_id = l_budget_version_id
4025: AND ASSIGNMENT_ID = p_assignment_id
4029: g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt between COMMITMENT_START_DATE
4030: and COMMITMENT_END_DATE);
4031: --
4032: --
4033: hr_utility.set_location('Insert commitment',48);
4034: hr_utility.set_location('l_entry_commitment:'||l_entry_commitment,48);
4035: --
4036: Insert into pqh_element_commitments(
4037: ELEMENT_COMMITMENT_ID ,
4030: and COMMITMENT_END_DATE);
4031: --
4032: --
4033: hr_utility.set_location('Insert commitment',48);
4034: hr_utility.set_location('l_entry_commitment:'||l_entry_commitment,48);
4035: --
4036: Insert into pqh_element_commitments(
4037: ELEMENT_COMMITMENT_ID ,
4038: BUDGET_VERSION_ID,
4055: l_entry_commitment+nvl(l_element_overhead,0),
4056: sysdate,
4057: -1);
4058:
4059: hr_utility.set_location('Insert commitment2'||(l_entry_commitment+l_element_overhead),48);
4060: --
4061: End if;
4062: --
4063: End Loop;
4065: End if;
4066: Exception
4067: When others then
4068: -- Log error
4069: hr_utility.set_location(sqlerrm,1000);
4070: pqh_process_batch_log.insert_log
4071: (
4072: p_message_type_cd => 'ERROR',
4073: p_message_text => SQLERRM