[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Table_Insert(Organization_ID Number, Element_Type_ID Number DEFAULT NULL, Person_ID Number,
Assignment_ID Number, Begin_Date Date, End_Date Date, Original_Sch_Code Varchar2,
Schedule_Line_ID Number);
Function Insert_Records_To_Table(p_template_id number, p_start_date DATE,p_end_date DATE,p_set_of_books_id number,p_business_group_id number) return NUMBER IS
l_cur_handle1 INTEGER;
select count(1)
from psp_report_template_details
Where template_id = p_template_id
and CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
and CRITERIA_LOOKUP_CODE = 'ORG';
g_element_type_id_str := '( Select DISTINCT a.ELEMENT_TYPE_ID
from PSP_ELEMENT_TYPES a, PAY_ELEMENT_TYPES_F b
where a.ELEMENT_TYPE_ID = b.ELEMENT_TYPE_ID
AND a.business_group_id = ' || p_business_group_id || '
AND a.set_of_books_id = ' || p_set_of_books_id || ')' ;
First, need to check if the assignments from the selected organizations exist in the PSP_SCHEDULES table.
If they do not, then, we need to add these assignments to the report to be displayed.
This is pretty straight-forward. If they do, then, we need to proceed to the next step.
*******************************************************************************************************/
-- First, obtain the Start and End Dates for assignments not existing in
-- PSP_SCHEDULE_HIERARCHY=> Time Period Begin and End Dates
/* For bug
Select Start_Date, End_Date
into l_Time_Period_Start_Date, l_Time_Period_End_Date
from PER_TIME_PERIODS
whee Time_Period_ID = v_Time_Period_ID; */
g_organization_str := ' a.Organization_id in (select to_number(criteria_value1)
from psp_report_template_details
where template_id = '|| p_template_id || '
and CRITERIA_LOOKUP_TYPE = ''PSP_SELECTION_CRITERIA''
and CRITERIA_LOOKUP_CODE = ''ORG'' ) ' ;
dbms_sql.parse(l_cur_handle1, 'Select DISTINCT a.Assignment_ID ASSIGNMENT_ID,
a.Organization_ID ORGANIZATION_ID,
a.Person_ID PERSON_ID
from PER_ASSIGNMENTS_F a
where ' || g_organization_str || '
and a.business_group_id = '|| p_business_group_id ||'
and a.payroll_id in ( select payroll_id from
pay_payrolls_f where gl_set_of_books_id = '|| p_set_of_books_id || ')
and ( :p_end_date between a.EFFECTIVE_START_DATE
and a.EFFECTIVE_END_DATE)
and Not(Exists(Select ''X''
from PSP_SCHEDULE_HIERARCHY SCHI
,PSP_SCHEDULE_LINES SCHL
where SCHI.Assignment_ID = a.ASSIGNMENT_ID
and SCHI.SCHEDULE_HIERARCHY_ID = SCHL.SCHEDULE_HIERARCHY_ID
and ((SCHL.SCHEDULE_BEGIN_DATE between :p_start_date and :p_end_date )
or (SCHL.SCHEDULE_END_DATE between :p_start_date and :p_end_date )
or (SCHL.SCHEDULE_BEGIN_DATE <= :p_start_date and SCHL.SCHEDULE_END_DATE
>= :p_end_date ))))', dbms_sql.V7);
--dbms_output.put_line('After calling the Table_Insert Procedure');
--dbms_output.put_line('Have to insert Assignments : ' || v_Assgn_List_To_Add || ' to Report');
and end dates that fully encapsulate the selected time period. If they do, then, we NEED NOT
display these assignments.
If they do not, then we should obtain the time periods that are not covered and proceed to the
next step (viz Check if the periods are covered by Element Group schedules and Assignment Level
Schedules).
*******************************************************************************************************/
Process_All_ET_Schedules(p_template_id , p_start_date ,p_end_date);
and end dates that fully encapsulate the selected time period. If they do, then, we NEED NOT
display these assignments.
If they do not, then we should obtain the time periods that are not covered and proceed to the
next step (viz Check if the periods are covered by Assignment Level Schedules).
*******************************************************************************************************/
Process_All_EG_Schedules(p_template_id , p_start_date ,p_end_date);
begin and end dates that fully encapsulate the selected time period. If they do, then, we NEED
NOT display these assignments.
If they do not, then we should display these records.
*******************************************************************************************************/
Process_All_ASSGN_Schedules(p_template_id , p_start_date ,p_end_date);
End Insert_Records_To_Table;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'GLOBAL ELEMENT LEVEL SCHEDULES'
l_GE_Period1_Start Date;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'ASSIGNMENT LEVEL SCHEDULES'
l_AS_Period1_Start Date;
dbms_sql.parse(l_cur_handle, 'SELECT DISTINCT a.ASSIGNMENT_ID ASSIGNMENT_ID,
a.ORGANIZATION_ID ORGANIZATION_ID,
b.PERSON_ID PERSON_ID,
c.ELEMENT_TYPE_ID ELEMENT_TYPE_ID,
d.SCHEDULE_LINE_ID SCHEDULE_LINE_ID,
d.SCHEDULE_BEGIN_DATE SCHEDULE_BEGIN_DATE,
d.SCHEDULE_END_DATE SCHEDULE_END_DATE,
fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_start_date) ||''') TIME_PERIOD_START_DATE,
fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_end_date) ||''') TIME_PERIOD_END_DATE
from PER_ASSIGNMENTS_F a,
PSP_SCHEDULES b,
PSP_SCHEDULE_HIERARCHY c,
PSP_SCHEDULE_LINES d,
PSP_DEFAULT_LABOR_SCHEDULES f
where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
and b.ASSIGNMENT_ID = c.ASSIGNMENT_ID
and c.SCHEDULE_HIERARCHY_ID = d.SCHEDULE_HIERARCHY_ID
and NOT(d.SCHEDULE_BEGIN_DATE <= :p_start_date AND
d.SCHEDULE_END_DATE >= :p_end_date )
and NOT(d.SCHEDULE_BEGIN_DATE > :p_end_date OR
d.SCHEDULE_END_DATE < :p_start_date )
and (:p_end_date BETWEEN a.EFFECTIVE_START_DATE and
a.EFFECTIVE_END_DATE)
and c.SCHEDULING_TYPES_CODE = ''A''
and a.ORGANIZATION_ID = f.ORGANIZATION_ID
and (:p_start_date >= f.SCHEDULE_BEGIN_DATE
or :p_end_date <= f.SCHEDULE_END_DATE
or ( :p_start_date <= f.SCHEDULE_BEGIN_DATE and
:p_END_DATE >= f.SCHEDULE_END_DATE))
and ' || g_organization_str ,
dbms_sql.V7);
-- dbms_output.put_line('After calling the Table_Insert Procedure');
-- dbms_output.put_line('After calling the Table_Insert Procedure');
--dbms_output.put_line('After calling the Table_Insert Procedure');
--dbms_output.put_line('After calling the Table_Insert Procedure');
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'GLOBAL ELEMENT LEVEL SCHEDULES'
l_GE_Period1_Start Date;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'ELEMENT_GROUP LEVEL SCHEDULES'
l_EG_Period1_Start Date;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'ASSIGNMENT LEVEL SCHEDULES'
l_AS_Period1_Start Date;
dbms_sql.parse(l_cur_handle, 'SELECT DISTINCT a.ASSIGNMENT_ID ASSIGNMENT_ID,
a.ORGANIZATION_ID ORGANIZATION_ID,
b.PERSON_ID PERSON_ID,
f.ELEMENT_TYPE_ID ELEMENT_TYPE_ID,
d.SCHEDULE_LINE_ID SCHEDULE_LINE_ID,
d.SCHEDULE_BEGIN_DATE SCHEDULE_BEGIN_DATE,
d.SCHEDULE_END_DATE SCHEDULE_END_DATE,
fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_start_date) ||''') TIME_PERIOD_START_DATE,
fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_end_date) ||''') TIME_PERIOD_END_DATE
from PER_ASSIGNMENTS_F a,
PSP_SCHEDULES b,
PSP_SCHEDULE_HIERARCHY c,
PSP_SCHEDULE_LINES d,
PSP_GROUP_ELEMENT_LIST f,
PSP_DEFAULT_LABOR_SCHEDULES g
where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
and b.ASSIGNMENT_ID = c.ASSIGNMENT_ID
and c.SCHEDULE_HIERARCHY_ID = d.SCHEDULE_HIERARCHY_ID
and f.ELEMENT_GROUP_ID = c.ELEMENT_GROUP_ID
and NOT(d.SCHEDULE_BEGIN_DATE <= :p_start_date AND
d.SCHEDULE_END_DATE >= :p_end_date )
and NOT(d.SCHEDULE_BEGIN_DATE > :p_end_date OR
d.SCHEDULE_END_DATE < :p_start_date )
and (:p_end_date BETWEEN a.EFFECTIVE_START_DATE and
a.EFFECTIVE_END_DATE)
and c.SCHEDULING_TYPES_CODE = ''EG''
and f.ELEMENT_GROUP_ID IN (Select Distinct
PGEL.ELEMENT_GROUP_ID
from PSP_GROUP_ELEMENT_LIST PGEL,
PSP_ELEMENT_GROUPS PEG
where PGEL.Element_Type_ID IN ' ||
g_element_type_id_str || '
and PGEL.Element_Group_ID =
PEG.Element_Group_ID)
and a.ORGANIZATION_ID = g.ORGANIZATION_ID
and f.ELEMENT_TYPE_ID IN ' || g_element_type_id_str || '
and (:p_start_date >= g.SCHEDULE_BEGIN_DATE
or :p_end_date <= g.SCHEDULE_END_DATE
or (:p_start_date <= g.SCHEDULE_BEGIN_DATE and
:p_end_date >= g.SCHEDULE_END_DATE))
and ' || g_organization_str,
dbms_sql.V7);
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'GLOBAL ELEMENT LEVEL SCHEDULES'
l_GE_Period1_Start Date;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'ELEMENT_TYPE LEVEL SCHEDULES'
l_Period1_Start Date;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'ELEMENT_GROUP LEVEL SCHEDULES'
l_EG_Period1_Start Date;
-- (Period1 => Before the Selected Time Period Start Date,
-- Period2 => After the Selected Time Period End Date) for 'ASSIGNMENT LEVEL SCHEDULES'
l_AS_Period1_Start Date;
dbms_sql.parse(l_cur_handle, 'SELECT DISTINCT a.ASSIGNMENT_ID ASSIGNMENT_ID,
a.ORGANIZATION_ID ORGANIZATION_ID,
b.PERSON_ID PERSON_ID,
c.ELEMENT_TYPE_ID ELEMENT_TYPE_ID,
d.SCHEDULE_LINE_ID SCHEDULE_LINE_ID,
d.SCHEDULE_BEGIN_DATE SCHEDULE_BEGIN_DATE,
d.SCHEDULE_END_DATE SCHEDULE_END_DATE,
fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_start_date) ||''') TIME_PERIOD_START_DATE,
fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_end_date) ||''') TIME_PERIOD_END_DATE
from PER_ASSIGNMENTS_F a,
PSP_SCHEDULES b,
PSP_SCHEDULE_HIERARCHY c,
PSP_SCHEDULE_LINES d,
PSP_DEFAULT_LABOR_SCHEDULES f
where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
and b.ASSIGNMENT_ID = c.ASSIGNMENT_ID
and c.SCHEDULE_HIERARCHY_ID = d.SCHEDULE_HIERARCHY_ID
and NOT(d.SCHEDULE_BEGIN_DATE <= :p_start_date AND
d.SCHEDULE_END_DATE >= :p_end_date )
and NOT(d.SCHEDULE_BEGIN_DATE > :p_end_date OR
d.SCHEDULE_END_DATE < :p_start_date )
and ( :p_end_date BETWEEN a.EFFECTIVE_START_DATE and
a.EFFECTIVE_END_DATE)
and c.SCHEDULING_TYPES_CODE = ''ET''
and ' || g_organization_str || '
and a.ORGANIZATION_ID = f.ORGANIZATION_ID
and ( :p_start_date >= f.SCHEDULE_BEGIN_DATE
or :p_end_date <= f.SCHEDULE_END_DATE
or ( :p_start_date <= f.SCHEDULE_BEGIN_DATE and
:p_end_date >= f.SCHEDULE_END_DATE))
and c.ELEMENT_TYPE_ID IN ' || g_element_type_id_str,
dbms_sql.V7);
--dbms_output.put_line('After calling the Table_Insert Procedure');
This is a private procedure that is called to actually insert data to the intermediate
PSP_REP_ORG_DLS_PKG table that is used by the Organization Level DLS Report. *******************************************************************************************************/
Procedure Table_Insert(Organization_ID Number, Element_Type_ID Number DEFAULT NULL, Person_ID Number,
Assignment_ID Number, Begin_Date Date, End_Date Date, Original_Sch_Code Varchar2,
Schedule_Line_ID Number) IS
l_RowID varchar2(30);
-- dbms_output.put_line('About to Insert record to table');
/**dbms_output.put_line('Values to be inserted are : Org ID - ' || to_char(Organization_ID) ||
', Elem. Type ID - ' || to_char(Element_Type_ID) || ', Person ID - ' ||
to_char(Person_ID) || ', Assignment ID - ' || to_char(Assignment_ID)
|| ', Begin Date - ' || to_char(Begin_Date) || ', End Date - ' || to_char(End_Date));**/
Select PSP_REP_ORG_DLS_S.NextVal
into p_Batch_Sequence_Number
from DUAL;
Select PSP_REP_ORG_DLS2_S.NextVal
into l_Line_ID
from DUAL;
PSP_REP_ORG_DLS_PKG.INSERT_ROW (
X_ROWID => l_RowID,
X_ORG_DLS_BATCH_ID => p_Batch_Sequence_Number,
X_ORG_DLS_LINE_ID => l_Line_ID,
X_ORGANIZATION_ID => Organization_ID,
X_ASSIGNMENT_ID => Assignment_ID,
X_SCHEDULE_LINE_ID => Schedule_Line_ID,
X_ORIGINAL_SCH_CODE => Original_Sch_Code,
X_SCHEDULE_BEGIN_DATE => Begin_Date,
X_SCHEDULE_END_DATE => End_Date,
X_PERSON_ID => Person_ID,
X_ELEMENT_TYPE_ID => Element_Type_ID,
X_MODE => 'R'
);
End Table_Insert;
select DISTINCT c.ASSIGNMENT_ID,
d.SCHEDULE_BEGIN_DATE,
d.SCHEDULE_END_DATE
from PER_ASSIGNMENTS_F a,
PSP_SCHEDULES b,
PSP_SCHEDULE_HIERARCHY c,
PSP_SCHEDULE_LINES d,
PSP_GROUP_ELEMENT_LIST e
where a.assignment_id = b.assignment_id
and b.assignment_id = c.assignment_id
and c.schedule_hierarchy_id = d.schedule_hierarchy_id
and a.assignment_id = l_Assignment_ID
and not (d.schedule_begin_date <= l_Time_Period_Start
or d.schedule_end_date >= l_Time_Period_End)
and l_Time_Period_End Between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
and c.SCHEDULING_TYPES_CODE = 'A';*/
select DISTINCT c.ASSIGNMENT_ID,
d.SCHEDULE_BEGIN_DATE,
d.SCHEDULE_END_DATE
from PER_ASSIGNMENTS_F a,
PSP_SCHEDULES b,
PSP_SCHEDULE_HIERARCHY c,
PSP_SCHEDULE_LINES d
where a.assignment_id = b.assignment_id
and b.assignment_id = c.assignment_id
and c.schedule_hierarchy_id = d.schedule_hierarchy_id
and a.assignment_id = l_Assignment_ID
and not (d.schedule_begin_date <= l_Time_Period_Start
or d.schedule_end_date >= l_Time_Period_End)
and l_Time_Period_End Between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
and c.SCHEDULING_TYPES_CODE = 'A';
Select DISTINCT e.Start_Date_Active, e.End_Date_Active
from PER_ASSIGNMENTS_F a,
PAY_ELEMENT_ENTRIES_F b,
PAY_ELEMENT_LINKS_F c,
PAY_ELEMENT_TYPES_F d,
PSP_ELEMENT_TYPE_ACCOUNTS e
where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
and b.ELEMENT_LINK_ID = c.ELEMENT_LINK_ID
and c.ELEMENT_TYPE_ID = d.ELEMENT_TYPE_ID
and d.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID
and e.ELEMENT_TYPE_ID = l_Element_Type_ID
and (l_Time_Period_End Between a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE)
and (l_Time_Period_End Between b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE)
and (l_Time_Period_End Between c.EFFECTIVE_START_DATE AND c.EFFECTIVE_END_DATE)
and (l_Time_Period_End Between d.EFFECTIVE_START_DATE AND d.EFFECTIVE_END_DATE)
and a.ASSIGNMENT_ID = l_Assignment_ID;
-- A Record exists in the Global Elements table for user selected Element Type ID.
-- Need to calculate the portion of the Time Period that is not covered by the Element
Calculate_Split_Periods(l_Assignment_Id, C1_Row.Start_Date_Active, C1_Row.End_Date_Active,
l_Time_Period_Start, l_Time_Period_End, l_GE_Period1_Start, l_GE_Period1_End,
l_GE_Period2_Start, l_GE_Period2_End);
select DISTINCT c.ASSIGNMENT_ID,
c.ELEMENT_GROUP_ID,
d.SCHEDULE_BEGIN_DATE,
d.SCHEDULE_END_DATE
from PER_ASSIGNMENTS_F a,
PSP_SCHEDULES b,
PSP_SCHEDULE_HIERARCHY c,
PSP_SCHEDULE_LINES d,
PSP_GROUP_ELEMENT_LIST e
where a.assignment_id = b.assignment_id
and b.assignment_id = c.assignment_id
and c.schedule_hierarchy_id = d.schedule_hierarchy_id
and a.assignment_id = l_Assignment_ID
and l_Time_Period_End Between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
and not (d.schedule_begin_date <= l_Time_Period_Start
or d.schedule_end_date >= l_Time_Period_End)
and c.SCHEDULING_TYPES_CODE = 'EG'
and e.element_group_id = c.ELEMENT_GROUP_ID
and e.ELEMENT_GROUP_ID = (Select Distinct PGEL.ELEMENT_GROUP_ID
from PSP_GROUP_ELEMENT_LIST PGEL,
PSP_ELEMENT_GROUPS PEG
where PGEL.Element_Type_ID = l_Element_Type_ID
and PGEL.Element_Group_ID = PEG.Element_Group_ID
and (PEG.Start_Date_Active Between
l_Time_Period_Start and
l_Time_Period_End)
and (PEG.End_Date_Active Between l_Time_Period_Start and
l_Time_Period_End));
Select MAX(SCHEDULE_END_DATE) into l_max_schedule_end_date
from PSP_REP_SCH_EMP_V
where assignment_id = l_assignment_id
and schedule_end_date < l_schedule_begin_date
and schedule_end_date >= l_time_period_start
and not exists (select assignment_id from PSP_REP_SCH_EMP_V
where assignment_id = l_assignment_id and
l_schedule_begin_date between schedule_begin_date and schedule_end_date
and schedule_begin_date < l_schedule_begin_date);
select min(schedule_begin_date) into l_temp_schedule_begin_date
from PSP_REP_SCH_EMP_V
where assignment_id = l_assignment_id and
l_schedule_begin_date between schedule_begin_date and schedule_end_date
and schedule_begin_date < l_schedule_begin_date;
Select MIN(SCHEDULE_BEGIN_DATE) into l_min_schedule_begin_date
from PSP_REP_SCH_EMP_V
where assignment_id = l_assignment_id
and schedule_begin_date > l_schedule_end_date
and schedule_begin_date <= l_time_period_end
and not exists (select assignment_id from PSP_REP_SCH_EMP_V
where assignment_id = l_assignment_id and
l_schedule_end_date between schedule_begin_date and schedule_end_date
and schedule_end_date > l_schedule_end_date);
select max(schedule_end_date) into l_temp_schedule_end_date
from PSP_REP_SCH_EMP_V
where assignment_id = l_assignment_id and
l_schedule_end_date between schedule_begin_date and schedule_end_date
and schedule_end_date > l_schedule_end_date;
Select Org_Schedule_ID, Schedule_Begin_Date, Schedule_End_Date
from PSP_DEFAULT_LABOR_SCHEDULES
where Organization_ID = v_Organization_ID;
Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
=> v_Organization_ID, Person_ID => v_Person_ID,
Element_Type_ID => v_Element_Type_ID, Begin_Date =>
DLS_Begin_Date, End_Date => DLS_End_Date,
Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
C1_Row.Org_Schedule_ID);
Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
=> v_Organization_ID, Person_ID => v_Person_ID,
Element_Type_ID => v_Element_Type_ID, Begin_Date =>
DLS_Begin_Date, End_Date => DLS_End_Date,
Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
C1_Row.Org_Schedule_ID);
Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
=> v_Organization_ID, Person_ID => v_Person_ID,
Element_Type_ID => v_Element_Type_ID, Begin_Date =>
DLS_Begin_Date, End_Date => DLS_End_Date,
Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
C1_Row.Org_Schedule_ID);
Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
=> v_Organization_ID, Person_ID => v_Person_ID,
Element_Type_ID => v_Element_Type_ID, Begin_Date =>
DLS_Begin_Date, End_Date => DLS_End_Date,
Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
C1_Row.Org_Schedule_ID);