1 PACKAGE BODY PA_REP_UTIL_SETUP as
2 /* $Header: PARRSETB.pls 120.1 2005/08/19 17:00:27 mwasowic noship $ */
3
4 /*
5 * This procedure reads profile and set the flag of CDL data to X of all
6 * records before the cut off date define in the profile
7 */
8
9
10 PROCEDURE set_flag_cut_off_records(
11 errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
12 ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
13 ,p_debug_mode IN VARCHAR2
14 )
15 IS
16
17 l_records_update NUMBER :=0;
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
27 ,pa_cost_distribution_lines_all cdl
28 where ei.expenditure_item_date < p_cut_off_date
29 and ei.expenditure_item_id = cdl.expenditure_item_id;
30
31
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);
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 */
48
49 retcode := 0;
50 /*
51 * Set Util_Summarized_Flag to X of all Records having date before
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;
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
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);
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;
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
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;
107
108 for rec in discarded_records(l_cut_off_date)
109 LOOP
110 UPDATE pa_cost_distribution_lines_all cdl
111 SET cdl.util_summarized_flag = 'X'
112 WHERE rowid = rec.rowid;
113
114 l_records_update := l_records_update + 1;
115 l_total_records := l_total_records + 1;
116 IF l_records_update = l_commit_size THEN
117 commit;
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
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;
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;
143 PA_DEBUG.Reset_curr_function;
144 END set_flag_cut_off_records;
145 END PA_REP_UTIL_SETUP;
[Home] [Help]