1 PACKAGE BODY PA_CC_GL_TRANS_CCDS
2 -- $Header: PACCGLTB.pls 120.5 2006/01/20 02:08:57 tsomani noship $
3 AS
4
5 -- Declaration of Global variables
6
7 G_Debug_Mode BOOLEAN ;
8 G_User_Id NUMBER ;
9 G_Conc_Request_ID NUMBER ;
10 G_Conc_Prog_Appl_Id NUMBER ;
11 G_Conc_Program_Id NUMBER ;
12 G_Conc_Login_Id NUMBER ;
13 G_EI_Set_Size NUMBER ;
14 G_Current_SOB_Id NUMBER ;
15 G_Calling_Module VARCHAR2(2);
16
17 PROCEDURE TRANSFER_CCDS_TO_GL (
18 P_Gl_Category IN VARCHAR2,
19 P_Expenditure_Batch IN VARCHAR2,
20 P_From_Project_Number IN VARCHAR2,
21 P_To_Project_Number IN VARCHAR2,
22 P_End_Gl_Date IN DATE,
23 P_Debug_Mode IN VARCHAR2,
24 RET_CODE IN OUT NOCOPY VARCHAR2,
25 ERRBUF IN OUT NOCOPY VARCHAR2)
26 IS
27
28 Cursor Exp_Items is
29 SELECT expenditure_item_id from pa_expenditure_items_all where expenditure_item_id in
30 (SELECT ei.expenditure_item_id
31 FROM pa_cc_dist_lines ccd,
32 Pa_expenditure_items ei,
33 Pa_expenditures exp
34 WHERE ccd.transfer_status_code in ('P','R')
35 AND (p_gl_category is null OR ccd.line_type = p_gl_category)
36 AND (ccd.line_type = 'BL' OR
37 (Ccd.line_type = 'PC' AND EXISTS (SELECT 'X'
38 FROM pa_draft_invoices_all di,
39 Pa_draft_invoice_details_all did
40 WHERE di.draft_invoice_num = did.draft_invoice_num
41 AND ccd.reference_1 = did.draft_invoice_detail_id
42 AND di.transfer_status_code = 'A')))
43 AND TRUNC (CCD.GL_Date) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
44 AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID
45 AND ei.expenditure_item_id = ccd.expenditure_item_id
46 AND exp.expenditure_id = ei.expenditure_id
47 AND exp.expenditure_group = p_EXPENDITURE_BATCH)
48 AND p_EXPENDITURE_BATCH is NOT NULL
49 UNION ALL
50 SELECT expenditure_item_id from pa_expenditure_items_all where expenditure_item_id in
51 (SELECT ei.expenditure_item_id
52 FROM pa_cc_dist_lines ccd,
53 pa_expenditure_items ei,
54 pa_projects_all proj
55 WHERE ccd.transfer_status_code in ('P','R')
56 AND (p_gl_category is null OR ccd.line_type = p_gl_category)
57 AND (ccd.line_type = 'BL' OR
58 (Ccd.line_type = 'PC' AND EXISTS (SELECT 'X'
59 FROM pa_draft_invoices_all di,
60 Pa_draft_invoice_details_all did
61 WHERE di.draft_invoice_num = did.draft_invoice_num
62 AND ccd.reference_1 = did.draft_invoice_detail_id
63 AND di.transfer_status_code = 'A')))
64 AND TRUNC (CCD.GL_DATE) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
65 AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID
66 AND ei.expenditure_item_id = ccd.expenditure_item_id
67 AND ei.project_id = proj.project_id
68 AND (((p_FROM_PROJECT_NUMBER is not null and proj. SEGMENT1 >= p_FROM_PROJECT_NUMBER)
69 AND (p_TO_PROJECT_NUMBER is not null and proj. SEGMENT1 <= p_TO_PROJECT_NUMBER))
70 OR
71 (p_FROM_PROJECT_NUMBER is not null and p_TO_PROJECT_NUMBER is null
72 and proj.SEGMENT1 >= p_FROM_PROJECT_NUMBER)
73 OR
74 (p_TO_PROJECT_NUMBER is not null and p_FROM_PROJECT_NUMBER is null
75 and proj.SEGMENT1 <= p_TO_PROJECT_NUMBER)))
76 AND (p_FROM_PROJECT_NUMBER is NOT NULL or p_TO_PROJECT_NUMBER is NOT NULL)
77 AND p_EXPENDITURE_BATCH is NULL
78 UNION ALL
79 SELECT expenditure_item_id from pa_expenditure_items_all where expenditure_item_id in
80 (SELECT ccd.expenditure_item_id
81 FROM pa_cc_dist_lines ccd
82 WHERE ccd.transfer_status_code in ('P','R')
83 AND (p_gl_category is null OR ccd.line_type = p_gl_category)
84 AND (ccd.line_type = 'BL' OR
85 (ccd.line_type = 'PC' AND EXISTS (SELECT 'X'
86 FROM pa_draft_invoices_all di,
87 pa_draft_invoice_details_all did
88 WHERE di.draft_invoice_num = did.draft_invoice_num
89 AND ccd.reference_1 = did.draft_invoice_detail_id
90 AND di.transfer_status_code = 'A')))
91 AND TRUNC (CCD.GL_DATE) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
92 AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID)
93 AND p_FROM_PROJECT_NUMBER is NULL
94 AND p_TO_PROJECT_NUMBER is NULL
95 AND p_EXPENDITURE_BATCH is NULL;
96
97 EiIdTab PA_PLSQL_DATATYPES.IdTabTyp;
98 l_result_code VARCHAR2(30);
99
100 BEGIN
101 IF p_debug_mode = 'Y' THEN
102 g_debug_mode := TRUE;
103 ELSE
104 g_debug_mode := FALSE;
105 END IF;
106
107 set_curr_function('transfer_ccds_to_gl');
108
109 log_message('10 : Calling the initialization procedure ');
110
111 PA_CC_GL_TRANS_CCDS.TRANSFER_CCDS_INITIALIZE ;
112
113 log_message('20 : After the call to initialization procedure ');
114
115 If P_Gl_Category is NOT NULL Then
116 pa_cc_gl_trans_ccds.G_Calling_Module := P_Gl_Category;
117 Else
118 pa_cc_gl_trans_ccds.G_Calling_Module := 'CC';
119 End If;
120
121 log_message('30 : Calling module is '||G_Calling_Module);
122
123 --
124 -- Check if the profile option PA_EXP_ITEMS_PER_SET is set from the value
125 -- set in G_EI_Set_Size variable by fnd_profile.value() function.
126 -- If the value of G_EI_Set_Size is NULL, then set a default value of 500.
127 --
128
129 IF G_EI_Set_Size IS NULL THEN
130 G_EI_Set_Size := 500;
131 END IF;
132
133 Open Exp_Items;
134
135 LOOP
136
137 Fetch Exp_Items bulk collect into EiidTab LIMIT G_EI_Set_Size;
138
139 IF EiidTab.count > 0 Then
140
141 FORALL i IN 1..EiidTab.count
142 UPDATE pa_expenditure_items
143 SET last_updated_by = last_updated_by
144 WHERE expenditure_item_id = EiidTab (i);
145
146 log_message('40 : EI locked with '||sql%rowcount ||' records');
147
148 FORALL i IN 1..EiidTab.count
149 UPDATE pa_cc_dist_lines ccd
150 SET ccd.transfer_status_code = 'X',
151 ccd.transfer_rejection_code = NULL,
152 ccd.request_id = G_conc_request_id
153 WHERE ccd.expenditure_item_id = EiidTab (i)
154 AND CCD.transfer_status_code IN ('P', 'R')
155 AND (p_gl_category IS NULL OR CCD.line_type = p_gl_category)
156 AND (CCD.line_type = 'BL'
157 OR (CCD.line_type = 'PC' AND EXISTS (SELECT 'X'
158 FROM pa_draft_invoices_all di,
159 Pa_draft_invoice_details_all did
160 WHERE di.draft_invoice_num = did.draft_invoice_num
161 AND ccd.reference_1 = did.draft_invoice_detail_id
162 AND di.transfer_status_code = 'A')))
163 AND TRUNC (CCD.GL_DATE) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
164 AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID ;
165
166 log_message('50 : CCDL updated with '||sql%rowcount ||' records');
167
168 PA_XLA_INTERFACE_PKG.Create_Events
169 (P_calling_module => G_Calling_Module,
170 P_data_set_id => G_conc_request_id,
171 x_result_code => l_result_code);
172
173 commit ;
174
175 End If;
176
177 EXIT WHEN nvl (EiidTab.last,0) < G_EI_Set_Size;
178
179 END LOOP ; /** Exp_Items **/
180
181 log_message('60 : End of the iteration ');
182
183 Close Exp_Items;
184
185 log_message('61 : End of the procedure ');
186
187 reset_curr_function;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 reset_curr_function;
192 RAISE ;
193
194 END TRANSFER_CCDS_TO_GL ;
195
196 PROCEDURE TRANSFER_CCDS_INITIALIZE
197 IS
198
199 BEGIN
200
201 set_curr_function('transfer_ccds_initialize');
202 -- Initialize the User_id (G_User_Id)
203 log_message('10.10 : Initialize the User_id ');
204 pa_cc_gl_trans_ccds.G_User_Id := fnd_profile.value('USER_ID');
205
206 -- Initialize concurrent request id
207 log_message('10.20 : Initialize concurrent request id ');
208 pa_cc_gl_trans_ccds.G_Conc_Request_ID := fnd_global.conc_request_id ;
209
210 -- Initialize concurrent program application id
211 log_message('10.30 : Initialize concurrent program application id ');
212 pa_cc_gl_trans_ccds.G_Conc_Prog_Appl_Id := fnd_global.prog_appl_id ;
213
214 -- Initialize concurrent program application id
215 log_message('10.40 : Initialize concurrent program application id ');
216 pa_cc_gl_trans_ccds.G_Conc_Program_Id := fnd_global.conc_program_id ;
217
218 -- Initialize login id
219 log_message('10.50 : Initialize login id ');
220 pa_cc_gl_trans_ccds.G_Conc_Login_Id := fnd_profile.value('LOGIN_ID');
221
222 -- initialize set size
223 log_message('10.60 : Initialize set size ');
224 pa_cc_gl_trans_ccds.G_EI_Set_Size := fnd_profile.value('PA_NUM_EXP_ITEMS_PER_SET');
225
226 select set_of_books_id
227 into G_Current_SOB_Id
228 from pa_implementations;
229
230 reset_curr_function;
231
232 EXCEPTION
233
234 WHEN OTHERS THEN
235 reset_curr_function;
236 RAISE ;
237
238 END TRANSFER_CCDS_INITIALIZE ;
239
240 PROCEDURE log_message( p_message IN VARCHAR2) IS
241 BEGIN
242
243 pa_cc_utils.log_message(p_message);
244
245 END log_message;
246
247 PROCEDURE set_curr_function(p_function IN VARCHAR2) IS
248 BEGIN
249
250 pa_cc_utils.set_curr_function(p_function);
251
252 END;
253
254 PROCEDURE reset_curr_function IS
255 BEGIN
256
257 pa_cc_utils.reset_curr_function;
258
259 END;
260
261
262 END PA_CC_GL_TRANS_CCDS;
[Home] [Help]