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