18: l_total_records NUMBER :=0;
19: l_cut_off_date DATE;
20: l_commit_size PLS_INTEGER;
21: l_fnd_msg VARCHAR2 (2000);
22: l_debug varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
23:
24: CURSOR discarded_records(p_cut_off_date IN DATE) IS
25: select cdl.rowid
26: from pa_expenditure_items_all ei
32: BEGIN
33: /*
34: * To Initialize the error stack
35: */
36: PA_DEBUG.Set_Curr_Function(
37: p_function => 'Set_Flag_Cut_Off_Records',
38: p_process => 'PLSQL',
39: p_write_file => 'LOG',
40: p_debug_mode => p_debug_mode);
38: p_process => 'PLSQL',
39: p_write_file => 'LOG',
40: p_debug_mode => p_debug_mode);
41:
42: PA_DEBUG.g_err_stage := 'Process : Set Flag of Cut Off Records';
43: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage,
44: p_write_file => 'OUT');
45: /*
46: * Assume Success
39: p_write_file => 'LOG',
40: p_debug_mode => p_debug_mode);
41:
42: PA_DEBUG.g_err_stage := 'Process : Set Flag of Cut Off Records';
43: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage,
44: p_write_file => 'OUT');
45: /*
46: * Assume Success
47: */
52: * the profile date
53: */
54:
55: IF l_debug ='Y'THEN -- bug 2674619
56: PA_DEBUG.g_err_stage := 'Arguments';
57: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58: PA_DEBUG.g_err_stage := '---------';
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
53: */
54:
55: IF l_debug ='Y'THEN -- bug 2674619
56: PA_DEBUG.g_err_stage := 'Arguments';
57: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58: PA_DEBUG.g_err_stage := '---------';
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
54:
55: IF l_debug ='Y'THEN -- bug 2674619
56: PA_DEBUG.g_err_stage := 'Arguments';
57: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58: PA_DEBUG.g_err_stage := '---------';
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62:
55: IF l_debug ='Y'THEN -- bug 2674619
56: PA_DEBUG.g_err_stage := 'Arguments';
57: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58: PA_DEBUG.g_err_stage := '---------';
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62:
63: PA_DEBUG.g_err_stage := '50 : before geting the value of global commit size';
56: PA_DEBUG.g_err_stage := 'Arguments';
57: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58: PA_DEBUG.g_err_stage := '---------';
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62:
63: PA_DEBUG.g_err_stage := '50 : before geting the value of global commit size';
64: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
57: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
58: PA_DEBUG.g_err_stage := '---------';
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62:
63: PA_DEBUG.g_err_stage := '50 : before geting the value of global commit size';
64: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
65: END IF;
59: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62:
63: PA_DEBUG.g_err_stage := '50 : before geting the value of global commit size';
64: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
65: END IF;
66:
67: l_commit_size := pa_rep_util_glob.G_util_fetch_size;
60: PA_DEBUG.g_err_stage := 'p_debug_mode : '||p_debug_mode;
61: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
62:
63: PA_DEBUG.g_err_stage := '50 : before geting the value of global commit size';
64: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
65: END IF;
66:
67: l_commit_size := pa_rep_util_glob.G_util_fetch_size;
68:
66:
67: l_commit_size := pa_rep_util_glob.G_util_fetch_size;
68:
69: IF l_debug ='Y'THEN -- bug 2674619
70: PA_DEBUG.g_err_stage := 'l_commit_size : '||to_char(nvl(l_commit_size,0));
71: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
72:
73: PA_DEBUG.g_err_stage := '100 : after geting the value of global commit size';
74: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
67: l_commit_size := pa_rep_util_glob.G_util_fetch_size;
68:
69: IF l_debug ='Y'THEN -- bug 2674619
70: PA_DEBUG.g_err_stage := 'l_commit_size : '||to_char(nvl(l_commit_size,0));
71: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
72:
73: PA_DEBUG.g_err_stage := '100 : after geting the value of global commit size';
74: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
75:
69: IF l_debug ='Y'THEN -- bug 2674619
70: PA_DEBUG.g_err_stage := 'l_commit_size : '||to_char(nvl(l_commit_size,0));
71: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
72:
73: PA_DEBUG.g_err_stage := '100 : after geting the value of global commit size';
74: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
75:
76: PA_DEBUG.g_err_stage := '150 : before geting the value of profile PA_UTL_START_DATE';
77: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
70: PA_DEBUG.g_err_stage := 'l_commit_size : '||to_char(nvl(l_commit_size,0));
71: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
72:
73: PA_DEBUG.g_err_stage := '100 : after geting the value of global commit size';
74: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
75:
76: PA_DEBUG.g_err_stage := '150 : before geting the value of profile PA_UTL_START_DATE';
77: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
78: END IF;
72:
73: PA_DEBUG.g_err_stage := '100 : after geting the value of global commit size';
74: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
75:
76: PA_DEBUG.g_err_stage := '150 : before geting the value of profile PA_UTL_START_DATE';
77: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
78: END IF;
79:
80: l_cut_off_date :=FND_PROFILE.VALUE('PA_UTL_START_DATE');
73: PA_DEBUG.g_err_stage := '100 : after geting the value of global commit size';
74: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
75:
76: PA_DEBUG.g_err_stage := '150 : before geting the value of profile PA_UTL_START_DATE';
77: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
78: END IF;
79:
80: l_cut_off_date :=FND_PROFILE.VALUE('PA_UTL_START_DATE');
81:
82: IF l_cut_off_date IS NULL THEN
83: retcode := 2;
84: errbuf := 'The profile PA_UTL_START_DATE is not defined and its value is null';
85: IF l_debug ='Y'THEN -- bug 2674619
86: PA_DEBUG.g_err_stage := '200 : The PA_UTL_START_DATE is not defined and its value is null';
87: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
88: END IF;
89: FND_MESSAGE.Set_Name('PA','PA_UTL_START_DATE');
90: l_fnd_msg := FND_MESSAGE.Get;
83: retcode := 2;
84: errbuf := 'The profile PA_UTL_START_DATE is not defined and its value is null';
85: IF l_debug ='Y'THEN -- bug 2674619
86: PA_DEBUG.g_err_stage := '200 : The PA_UTL_START_DATE is not defined and its value is null';
87: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
88: END IF;
89: FND_MESSAGE.Set_Name('PA','PA_UTL_START_DATE');
90: l_fnd_msg := FND_MESSAGE.Get;
91: PA_DEBUG.Log_Message(p_message => l_fnd_msg,
87: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
88: END IF;
89: FND_MESSAGE.Set_Name('PA','PA_UTL_START_DATE');
90: l_fnd_msg := FND_MESSAGE.Get;
91: PA_DEBUG.Log_Message(p_message => l_fnd_msg,
92: p_write_file => 'OUT');
93: PA_Debug.Reset_Curr_Function;
94: RETURN;
95: END IF;
89: FND_MESSAGE.Set_Name('PA','PA_UTL_START_DATE');
90: l_fnd_msg := FND_MESSAGE.Get;
91: PA_DEBUG.Log_Message(p_message => l_fnd_msg,
92: p_write_file => 'OUT');
93: PA_Debug.Reset_Curr_Function;
94: RETURN;
95: END IF;
96:
97: IF l_debug ='Y'THEN -- bug 2674619
94: RETURN;
95: END IF;
96:
97: IF l_debug ='Y'THEN -- bug 2674619
98: PA_DEBUG.g_err_stage := 'PA_UTL_START_DATE Profile Value:'||TO_CHAR(l_cut_off_date,'DD-MON-YYYY');
99: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
100:
101: PA_DEBUG.g_err_stage := 'Set util_summarized_flag to X of all records having date before cutoff date';
102: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
95: END IF;
96:
97: IF l_debug ='Y'THEN -- bug 2674619
98: PA_DEBUG.g_err_stage := 'PA_UTL_START_DATE Profile Value:'||TO_CHAR(l_cut_off_date,'DD-MON-YYYY');
99: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
100:
101: PA_DEBUG.g_err_stage := 'Set util_summarized_flag to X of all records having date before cutoff date';
102: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
103:
97: IF l_debug ='Y'THEN -- bug 2674619
98: PA_DEBUG.g_err_stage := 'PA_UTL_START_DATE Profile Value:'||TO_CHAR(l_cut_off_date,'DD-MON-YYYY');
99: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
100:
101: PA_DEBUG.g_err_stage := 'Set util_summarized_flag to X of all records having date before cutoff date';
102: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
103:
104: PA_DEBUG.g_err_stage := '250 : Before Update CDL flag to X';
105: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
98: PA_DEBUG.g_err_stage := 'PA_UTL_START_DATE Profile Value:'||TO_CHAR(l_cut_off_date,'DD-MON-YYYY');
99: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
100:
101: PA_DEBUG.g_err_stage := 'Set util_summarized_flag to X of all records having date before cutoff date';
102: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
103:
104: PA_DEBUG.g_err_stage := '250 : Before Update CDL flag to X';
105: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
106: END IF;
100:
101: PA_DEBUG.g_err_stage := 'Set util_summarized_flag to X of all records having date before cutoff date';
102: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
103:
104: PA_DEBUG.g_err_stage := '250 : Before Update CDL flag to X';
105: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
106: END IF;
107:
108: for rec in discarded_records(l_cut_off_date)
101: PA_DEBUG.g_err_stage := 'Set util_summarized_flag to X of all records having date before cutoff date';
102: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
103:
104: PA_DEBUG.g_err_stage := '250 : Before Update CDL flag to X';
105: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
106: END IF;
107:
108: for rec in discarded_records(l_cut_off_date)
109: LOOP
118: l_records_update := 0;
119: END IF;
120: END LOOP;
121: IF l_debug ='Y'THEN -- bug 2674619
122: PA_DEBUG.g_err_stage := '300 : After update the CDL Flag';
123: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
124: END IF;
125:
126: IF l_debug ='Y'THEN -- bug 2674619
119: END IF;
120: END LOOP;
121: IF l_debug ='Y'THEN -- bug 2674619
122: PA_DEBUG.g_err_stage := '300 : After update the CDL Flag';
123: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
124: END IF;
125:
126: IF l_debug ='Y'THEN -- bug 2674619
127: PA_DEBUG.g_err_stage := 'Process completed successfully, '||to_char(l_total_records)||' Records discarded';
123: PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
124: END IF;
125:
126: IF l_debug ='Y'THEN -- bug 2674619
127: PA_DEBUG.g_err_stage := 'Process completed successfully, '||to_char(l_total_records)||' Records discarded';
128: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
129: END IF;
130: PA_DEBUG.Reset_curr_function;
131:
124: END IF;
125:
126: IF l_debug ='Y'THEN -- bug 2674619
127: PA_DEBUG.g_err_stage := 'Process completed successfully, '||to_char(l_total_records)||' Records discarded';
128: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
129: END IF;
130: PA_DEBUG.Reset_curr_function;
131:
132: EXCEPTION
126: IF l_debug ='Y'THEN -- bug 2674619
127: PA_DEBUG.g_err_stage := 'Process completed successfully, '||to_char(l_total_records)||' Records discarded';
128: PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
129: END IF;
130: PA_DEBUG.Reset_curr_function;
131:
132: EXCEPTION
133: WHEN OTHERS THEN
134: retcode := 2;
133: WHEN OTHERS THEN
134: retcode := 2;
135: errbuf := SUBSTR(SQLERRM,1,240);
136: IF l_debug ='Y'THEN -- bug 2674619
137: PA_DEBUG.Log_Message( SQLERRM);
138: PA_DEBUG.Log_Message(PA_DEBUG.g_err_stack);
139: END IF;
140: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_REP_UTIL_SETUP.set_flag_cut_off_records'
141: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
134: retcode := 2;
135: errbuf := SUBSTR(SQLERRM,1,240);
136: IF l_debug ='Y'THEN -- bug 2674619
137: PA_DEBUG.Log_Message( SQLERRM);
138: PA_DEBUG.Log_Message(PA_DEBUG.g_err_stack);
139: END IF;
140: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_REP_UTIL_SETUP.set_flag_cut_off_records'
141: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
142: RAISE;
137: PA_DEBUG.Log_Message( SQLERRM);
138: PA_DEBUG.Log_Message(PA_DEBUG.g_err_stack);
139: END IF;
140: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_REP_UTIL_SETUP.set_flag_cut_off_records'
141: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
142: RAISE;
143: PA_DEBUG.Reset_curr_function;
144: END set_flag_cut_off_records;
145: END PA_REP_UTIL_SETUP;
139: END IF;
140: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_REP_UTIL_SETUP.set_flag_cut_off_records'
141: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
142: RAISE;
143: PA_DEBUG.Reset_curr_function;
144: END set_flag_cut_off_records;
145: END PA_REP_UTIL_SETUP;