[Home] [Help]
PACKAGE BODY: APPS.GML_PO_GLDIST
Source
1 PACKAGE BODY GML_PO_GLDIST AS
2 /* $Header: GMLDISTB.pls 120.1.12020000.2 2012/09/14 07:19:50 guobxu ship $ */
3
4
5 v_lang VARCHAR2(10) := 'ENG';
6
7
8 /*##########################################################################
9 # PROC
10 # poglded2_calc_dist_amount_aqui
11 #
12 # GLOBAL VARIABLES
13 #
14 # RETURNS
15 # 1 = success
16 # -1 = failure
17 #
18 # DESCRIPTION
19 # calculate amount_trans using indicators in gl_event_plc and po_cost_dtl
20 # before inserting into po_dist_dtl table
21 # HISTORY
22 # 2/17/99 T.Ricci increment var GML_PO_GLDIST.P_tot_amount_aap_aqui
23 # when calculating an AAP account (was only doing it for AAC)
24 # Bug820997
25 ##########################################################################*/
26
27 FUNCTION calc_dist_amount_aqui RETURN NUMBER AS
28
29 /* Cursor for getting orgn. for a particular whse_code.*/
30 CURSOR Cur_orgn_for_whse IS
31 SELECT orgn_code
32 FROM ic_whse_mst
33 WHERE whse_code = GML_PO_GLDIST.P_to_whse;
34
35 /* Cursor for getting std_act_ind, exp_booked_ind and aqui_cost_ind*/
36 /* for a particular event code and source_code.*/
37 CURSOR Cur_get_ind_set IS
38 SELECT std_actual_ind , exp_booked_ind ,
39 acquis_cost_ind
40 FROM gl_evnt_plc e, gl_srce_mst s, gl_evnt_mst m
41 WHERE e.co_code = GML_PO_GLDIST.P_co_code and
42 e.trans_source_type = s.trans_source_type and e.event_type = m.event_type
43 and e.trans_source_type = m.trans_source_type
44 and s.trans_source_code = 'PO' and m.event_code = 'RCPT'
45 and e.delete_mark = 0;
46
47 /* Cursor for getting the cmpnt_cls_id and analysis_code for a specific aqui_cost_id.*/
48 CURSOR Cur_po_cost_mst IS
49 SELECT cmpntcls_id , analysis_code
50 FROM po_cost_mst
51 WHERE aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id;
52
53 /* Cursor for getting the cost_amount and incl_ind for a particular po_id, line_id and doc_type.*/
54 CURSOR Cur_po_cost_dtl IS
55 SELECT incl_ind,cost_amount
56 FROM po_cost_dtl
57 WHERE doc_type = GML_PO_GLDIST.P_doc_type and
58 pos_id = GML_PO_GLDIST.P_pos_id and
59 line_id = GML_PO_GLDIST.P_line_id and
60 aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id;
61
62 X_std_actual_ind NUMBER;
63 X_exp_booked_ind NUMBER;
64 X_aqui_cost_ind NUMBER;
65 X_cost_cmpntcls_id NUMBER;
66 X_cost_analysis_code CM_ALYS_MST.COST_ANALYSIS_CODE%TYPE; -- bug14480540
67 X_incl_ind NUMBER;
68 X_total_cost NUMBER;
69 workfloat1 NUMBER;
70 X_orgn_code VARCHAR2(5);
71 X_retvar NUMBER DEFAULT 0;
72 X_row_count NUMBER;
73 X_cost_mthd VARCHAR2(10);
74 X_cost_amount NUMBER;
75 x_cmpntcls_id NUMBER;
76 x_analysis_code VARCHAR2(70);
77 x_cost NUMBER;
78 x_status NUMBER;
79
80 BEGIN
81 OPEN Cur_orgn_for_whse;
82 FETCH Cur_orgn_for_whse INTO X_orgn_code;
83 IF Cur_orgn_for_whse%NOTFOUND THEN
84 X_orgn_code := FND_PROFILE.VALUE ('GEMMS_DEFAULT_ORGN');
85 END IF;
86
87 /* cmpntcls_id and analysis code comes for each row from the poglded2_process_trans. Hence, commented.*/
88 OPEN Cur_po_cost_mst ;
89 FETCH Cur_po_cost_mst INTO GML_PO_GLDIST.P_cost_cmpntcls_id, GML_PO_GLDIST.P_cost_analysis_code ;
90 CLOSE Cur_po_cost_mst;
91
92 OPEN Cur_po_cost_dtl;
93 FETCH Cur_po_cost_dtl INTO X_incl_ind, X_cost_amount;
94 CLOSE Cur_po_cost_dtl;
95
96 IF X_incl_ind = 1 THEN
97 X_retvar := gmf_cmcommon.cmcommon_get_cost ( GML_PO_GLDIST.P_gl_item_id, GML_PO_GLDIST.P_to_whse,
98 X_orgn_code , GML_PO_GLDIST.P_po_date,
99 X_cost_mthd , GML_PO_GLDIST.P_cost_cmpntcls_id,
100 GML_PO_GLDIST.P_cost_analysis_code, 3,
101 X_total_cost, X_row_count );
102 IF (x_row_count IS NULL) THEN
103 x_row_count := 0;
104 END IF;
105
106 IF X_retvar < 1 THEN
107 GML_PO_GLDIST.P_po_cost := 0;
108 END IF;
109 FOR i IN 1..x_row_count LOOP
110 /* This routine below returns the total cost in a loop,as cmcommon_get_cost routine */
111 /* returns only the row count.*/
112 gmf_cmcommon.get_multiple_cmpts_cost(i,x_cmpntcls_id,x_analysis_code,x_total_cost,3,x_status);
113 x_cost := nvl(x_cost,0) + nvl (x_total_cost,0) ;
114 END LOOP;
115 IF X_retvar < 1 THEN
116 GML_PO_GLDIST.P_po_cost := 0 ;
117 ELSE
118 GML_PO_GLDIST.P_po_cost := X_cost; /* Returned cost for the particular pair.*/
119 END IF;
120 ELSE
121 GML_PO_GLDIST.P_po_cost := X_cost_amount ;
122 END IF;
123
124 GML_PO_GLDIST.P_tmp_po_cost := GML_PO_GLDIST.P_po_cost / GML_PO_GLDIST.P_exchange_rate ;
125
126 OPEN Cur_get_ind_set;
127 FETCH Cur_get_ind_set INTO X_std_actual_ind, X_exp_booked_ind, X_aqui_cost_ind;
128 CLOSE Cur_get_ind_set;
129
130 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_INV THEN
131 IF X_std_actual_ind = 1 THEN
132 GML_PO_GLDIST.P_amount_trans_aqui := X_cost_amount * GML_PO_GLDIST.P_order_qty1;
133 ELSE
134 GML_PO_GLDIST.P_amount_trans_aqui := GML_PO_GLDIST.P_order_qty1 * GML_PO_GLDIST.P_tmp_po_cost;
135 END IF;
136 GML_PO_GLDIST.P_tot_amount_inv_aqui := nvl(GML_PO_GLDIST.P_tot_amount_inv_aqui, 0) + nvl (GML_PO_GLDIST.P_amount_trans_aqui,0);
137 END IF;
138
139 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_AAP AND X_aqui_cost_ind = 0 THEN
140 GML_PO_GLDIST.P_amount_trans_aqui := X_cost_amount * GML_PO_GLDIST.P_order_qty1;
141 GML_PO_GLDIST.P_amount_base_aqui := GML_PO_GLDIST.P_amount_trans_aqui * GML_PO_GLDIST.P_exchange_rate;
142 GML_PO_GLDIST.P_amount_base_aqui := ROUND (GML_PO_GLDIST.P_amount_base_aqui, GML_PO_GLDIST.P_precision);
143 GML_PO_GLDIST.P_amount_trans_aqui := ROUND (GML_PO_GLDIST.P_amount_trans_aqui, GML_PO_GLDIST.P_precision);
144 GML_PO_GLDIST.P_tot_amount_aap_aqui := GML_PO_GLDIST.P_amount_trans_aqui;
145 RETURN 0;
146 END IF;
147
148 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_AAP THEN
149 GML_PO_GLDIST.P_amount_trans_aqui := -(X_cost_amount * (GML_PO_GLDIST.P_order_qty1)) ;
150 GML_PO_GLDIST.P_tot_amount_aap_aqui := GML_PO_GLDIST.P_amount_trans_aqui;
151
152 END IF;
153
154 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_PPV THEN
155 IF X_std_actual_ind = 1 THEN
156 GML_PO_GLDIST.P_amount_trans_aqui := 0;
157 ELSE
158 GML_PO_GLDIST.P_extended_price := ROUND (GML_PO_GLDIST.P_extended_price, GML_PO_GLDIST.P_precision);
159 GML_PO_GLDIST.P_tmp_amt := (GML_PO_GLDIST.P_order_qty1 * GML_PO_GLDIST.P_tmp_po_cost);
160 GML_PO_GLDIST.P_tmp_amt := ROUND (GML_PO_GLDIST.P_tmp_amt, GML_PO_GLDIST.P_precision);
161 GML_PO_GLDIST.P_amount_trans_aqui := -(GML_PO_GLDIST.P_tot_amount_inv_aqui + GML_PO_GLDIST.P_tot_amount_aap_aqui );
162 END IF;
163 /* B908529 clear for PPV calc with multiple lines */
164 P_tot_amount_inv_aqui := 0;
165 END IF;
166
167 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_EXP THEN
168 IF X_incl_ind = 0 THEN
169 /* po_amount_trans already is amount of aquisition on PO*/
170 GML_PO_GLDIST.P_amount_trans_aqui := X_cost_amount * GML_PO_GLDIST.P_order_qty1;
171 ELSE
172 GML_PO_GLDIST.P_amount_trans_aqui := 0;
173 END IF;
174 END IF;
175
176 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_AAC THEN
177 IF X_aqui_cost_ind = 1 THEN
178 GML_PO_GLDIST.P_amount_trans_aqui := (X_cost_amount * GML_PO_GLDIST.P_order_qty1) * (-1);
179 ELSE
180 GML_PO_GLDIST.P_amount_trans_aqui := 0;
181 END IF;
182 GML_PO_GLDIST.P_tot_amount_aap_aqui := GML_PO_GLDIST.P_amount_trans_aqui;
183 END IF;
184
185 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_ACV THEN
186 IF X_incl_ind = 1 THEN
187 GML_PO_GLDIST.P_tmp_amt := X_cost_amount * GML_PO_GLDIST.P_order_qty1;
188 GML_PO_GLDIST.P_tmp_amt := ROUND (GML_PO_GLDIST.P_tmp_amt, GML_PO_GLDIST.P_precision);
189 GML_PO_GLDIST.P_tmp_amt2 := (GML_PO_GLDIST.P_order_qty1 * GML_PO_GLDIST.P_tmp_po_cost);
190 GML_PO_GLDIST.P_tmp_amt2 := ROUND (GML_PO_GLDIST.P_tmp_amt2, GML_PO_GLDIST.P_precision);
191 /*Sandeep. Bug Fixed for wrong totalling of ACV acct title. */
192 /*GML_PO_GLDIST.P_amount_trans_aqui := GML_PO_GLDIST.P_tmp_amt - GML_PO_GLDIST.P_tmp_amt2;*/
193 GML_PO_GLDIST.P_amount_trans_aqui := -(GML_PO_GLDIST.P_tot_amount_inv_aqui + GML_PO_GLDIST.P_tot_amount_aap_aqui);
194 ELSE
195 GML_PO_GLDIST.P_amount_trans_aqui := 0;
196 END IF;
197 /* B908529 clear for PPV calc with multiple lines */
198 P_tot_amount_inv_aqui := 0;
199 END IF;
200
201 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_ERV THEN
202 GML_PO_GLDIST.P_amount_trans_aqui := 0;
203 END IF;
204
205 /*Sandeep. Bug Fixed.The following Amount base for Aquisition is modified.*/
206 /* It is multiplied with Extended cost, instead of X_cost_amount.*/
207 GML_PO_GLDIST.P_amount_base_aqui := X_cost_amount * GML_PO_GLDIST.P_exchange_rate;
208 GML_PO_GLDIST.P_amount_base_aqui := GML_PO_GLDIST.P_amount_trans_aqui * GML_PO_GLDIST.P_exchange_rate;
209
210 GML_PO_GLDIST.P_amount_base_aqui := ROUND (GML_PO_GLDIST.P_amount_base_aqui, GML_PO_GLDIST.P_precision);
211
212 GML_PO_GLDIST.P_amount_trans_aqui := ROUND (GML_PO_GLDIST.P_amount_trans_aqui, GML_PO_GLDIST.P_precision);
213
214 RETURN 0;
215
216 END calc_dist_amount_aqui;
217
218 /*##########################################################################
219 # PROC
220 # calc_dist_amount
221 #
222 # GLOBAL VARIABLES
223 #
224 # RETURNS
225 # 1 = success
226 # -1 = failure
227 #
228 # DESCRIPTION
229 # calculate amount_trans using indicators in gl_event_plc and po_cost_dtl
230 # before inserting into po_dist_dtl table
231 #
232 ##########################################################################*/
233
234 PROCEDURE calc_dist_amount AS
235
236 CURSOR Cur_orgn_for_whse IS
237 SELECT orgn_code
238 FROM ic_whse_mst
239 WHERE whse_code = GML_PO_GLDIST.P_to_whse;
240
241 CURSOR Cur_get_ind_set IS
242 SELECT std_actual_ind , exp_booked_ind ,
243 acquis_cost_ind
244 FROM gl_evnt_plc e, gl_srce_mst s, gl_evnt_mst m
245 WHERE e.co_code = GML_PO_GLDIST.P_co_code and
246 e.trans_source_type = s.trans_source_type and e.event_type = m.event_type
247 and e.trans_source_type = m.trans_source_type
248 and s.trans_source_code = 'PO' and m.event_code = 'RCPT'
249 and e.delete_mark = 0;
250
251 X_std_actual_ind NUMBER;
252 X_exp_booked_ind NUMBER;
253 X_aqui_cost_ind NUMBER;
254 rvar NUMBER;
255 X_workfloat1 NUMBER;
256 X_orgn_code VARCHAR2(10);
257 X_retvar NUMBER;
258 X_tmp_amt NUMBER;
259 X_cost_mthd VARCHAR2(10) DEFAULT NULL;
260 X_total_cost NUMBER;
261 X_row_count NUMBER;
262 x_cmpntcls_id NUMBER;
263 x_analysis_code CM_ALYS_MST.COST_ANALYSIS_CODE%TYPE; -- bug14480540
264 x_status NUMBER;
265 x_cost NUMBER;
266 X_retr_ind NUMBER;
267
268 BEGIN
269 OPEN Cur_orgn_for_whse;
270 FETCH Cur_orgn_for_whse INTO X_orgn_code;
271 IF Cur_orgn_for_whse%NOTFOUND THEN
272 X_orgn_code := FND_PROFILE.VALUE ('GEMMS_DEFAULT_ORGN');
273 END IF;
274 CLOSE Cur_orgn_for_whse;
275
276 IF GML_PO_GLDIST.P_cost_cmpntcls_id IS NOT NULL AND GML_PO_GLDIST.P_cost_analysis_code IS NOT NULL THEN
277 X_retr_ind := 3;
278 X_retvar := gmf_cmcommon.cmcommon_get_cost( GML_PO_GLDIST.P_gl_item_id, GML_PO_GLDIST.P_to_whse,
279 X_orgn_code , GML_PO_GLDIST.P_po_date,
280 X_cost_mthd , GML_PO_GLDIST.P_cost_cmpntcls_id,
281 GML_PO_GLDIST.P_cost_analysis_code, X_retr_ind,
282 X_total_cost, X_row_count );
283 IF (x_row_count IS NULL) THEN
284 x_row_count := 0;
285 END IF;
286
287 IF X_retvar < 1 THEN
288 GML_PO_GLDIST.P_po_cost := 0 ;
289 END IF;
290 ELSE
291 X_retr_ind := 5;
292 X_retvar := gmf_cmcommon.cmcommon_get_cost ( GML_PO_GLDIST.P_gl_item_id, GML_PO_GLDIST.P_to_whse,
293 X_orgn_code , GML_PO_GLDIST.P_po_date,
294 X_cost_mthd , GML_PO_GLDIST.P_cost_cmpntcls_id,
295 GML_PO_GLDIST.P_cost_analysis_code, X_retr_ind,
296 X_total_cost, X_row_count );
297 IF x_row_count IS NULL THEN
298 x_row_count := 0;
299 END IF;
300 END IF;
301
302 FOR i IN 1..x_row_count LOOP
303 /* This routine below returns the total cost in a loop,as cmcommon_get_cost routine */
304 /* returns only the row count.*/
305 gmf_cmcommon.get_multiple_cmpts_cost(i,x_cmpntcls_id,x_analysis_code,x_total_cost,X_retr_ind,x_status);
306 x_cost := nvl(x_cost,0) + nvl (x_total_cost,0) ;
307 END LOOP;
308 IF X_retvar < 1 THEN
309 GML_PO_GLDIST.P_po_cost := 0 ;
310 ELSE
311 GML_PO_GLDIST.P_po_cost := X_cost; /* Returned cost for the particular pair.*/
312 END IF;
313 GML_PO_GLDIST.P_tmp_po_cost := GML_PO_GLDIST.P_po_cost / GML_PO_GLDIST.P_exchange_rate;
314 OPEN Cur_get_ind_set;
315 FETCH Cur_get_ind_set INTO X_std_actual_ind, X_exp_booked_ind, X_aqui_cost_ind;
316 CLOSE Cur_get_ind_set;
317
318 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_INV THEN
319 IF X_std_actual_ind = 1 THEN
320 GML_PO_GLDIST.P_amount_trans := GML_PO_GLDIST.P_extended_price ;
321 ELSE
322 GML_PO_GLDIST.P_amount_trans := GML_PO_GLDIST.P_order_qty1 * GML_PO_GLDIST.P_tmp_po_cost ;
323 END IF;
324 GML_PO_GLDIST.P_tot_amount_inv := nvl(GML_PO_GLDIST.P_tot_amount_inv,0) + nvl(GML_PO_GLDIST.P_amount_trans, 0);
325 END IF;
326
327 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_EXP THEN
328 GML_PO_GLDIST.P_amount_trans := GML_PO_GLDIST.P_extended_price ;
329 END IF;
330
331 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_AAP THEN
332 GML_PO_GLDIST.P_amount_trans := GML_PO_GLDIST.P_extended_price * (-1);
333 GML_PO_GLDIST.P_tot_amount_aap := GML_PO_GLDIST.P_amount_trans;
334 END IF;
335
336 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_PPV THEN
337 IF ( X_std_actual_ind = 1 OR GML_PO_GLDIST.P_non_inv_ind = 1 ) THEN
338 GML_PO_GLDIST.P_amount_trans := 0;
339 ELSE
340 X_tmp_amt := (GML_PO_GLDIST.P_order_qty1 * GML_PO_GLDIST.P_tmp_po_cost) ;
341 X_tmp_amt := ROUND (X_tmp_amt, GML_PO_GLDIST.P_precision);
342 /*P_amount_trans := P_tot_amount_inv - P_amount_trans - X_tmp_amt;*/
343 GML_PO_GLDIST.P_amount_trans := -(GML_PO_GLDIST.P_tot_amount_inv + GML_PO_GLDIST.P_tot_amount_aap );
344 END IF;
345 /* B908529 clear for PPV calc with multiple lines */
346 P_tot_amount_inv := 0;
347 END IF;
348
349 IF GML_PO_GLDIST.P_acct_ttl_num = GML_PO_GLDIST.GL$AT_ERV THEN
350 GML_PO_GLDIST.P_amount_trans := 0;
351 END IF;
352
353 /* # 10/13/95 Convert amount_trans to amount_base*/
354 GML_PO_GLDIST.P_amount_base := GML_PO_GLDIST.P_amount_trans * GML_PO_GLDIST.P_exchange_rate;
355 GML_PO_GLDIST.P_amount_base := ROUND (GML_PO_GLDIST.P_amount_base, GML_PO_GLDIST.P_precision);
356 GML_PO_GLDIST.P_amount_trans := ROUND (GML_PO_GLDIST.P_amount_trans, GML_PO_GLDIST.P_precision); /* workfloat1 comes from curr_rounding procedure*/
357 END calc_dist_amount;
358
359
360 /*##########################################################################
361 # PROC
362 # receive_data
363 #
364 # DESCRIPTION
365 # recieve arguments from calling_form
366 #
367 # PARAMETERS to be passed ..
368 # doc_type
369 # pos_id
370 # line_id
371 # orgn_code
372 # po_date
373 # shipvend_id
374 # base_currency
375 # billing_currency
376 # to_whse
377 # line_no
378 # item_no
379 # extended_price
380 # project
381 # order_qty1
382 # order_um1
383 # item_id
384 # mul_div_sign
385 # exchange_rate
386 # price
387 # action
388 # V_Single_aqui
389 # retcode
390 # HISTORY
391 # created by Sandeep 12.Oct.1998
392 # converted from PLL to Stored Proc by Tony Ricci 10/30/98
393 # major changes included using PL/SQL tables instead of RECORD GROUPS
394 #
395 # 21-JUN-1999 Tony Ricci change order by in select from gl_accu_map
396 # B931936
397 # B1377089 RVK 31-Aug-2000 Some of the PO's were not getting
398 # updated with proper AAP and PPV accts as P_acqui_cost_id was not getting
399 # initialized. Also subledger update was failing due to uninitialized id
400 #
401 ############################################################################*/
402
403 PROCEDURE receive_data (V_doc_type VARCHAR2, V_pos_id NUMBER,
404 V_line_id NUMBER, V_orgn_code VARCHAR2,
405 V_po_date DATE, V_shipvend_id NUMBER,
406 V_base_currency VARCHAR2,
407 V_billing_currency VARCHAR2,
408 V_to_whse VARCHAR2, V_line_no NUMBER,
409 V_item_no VARCHAR2, V_extended_price NUMBER,
410 V_project VARCHAR2, V_order_qty1 NUMBER,
411 V_order_um1 VARCHAR2, V_gl_item_id NUMBER,
412 V_mul_div_sign NUMBER, V_exchange_rate NUMBER,
413 V_price NUMBER,V_action NUMBER,
414 V_Single_aqui BOOLEAN,
415 retcode IN OUT NOCOPY NUMBER,
416 V_transaction_type IN VARCHAR2) AS
417
418 X_co_code VARCHAR2(5);
419
420 CURSOR Cur_base_curr IS
421 SELECT plcy.base_currency_code
422 FROM sy_orgn_mst orgn, gl_plcy_mst plcy
423 WHERE orgn.orgn_code = V_orgn_code and orgn.co_code = plcy.co_code;
424
425 CURSOR Cur_orgn_mst IS
426 SELECT co_code
427 FROM sy_orgn_mst
428 WHERE orgn_code = V_orgn_code;
429
430 /* RVK B1394532 */
431 CURSOR Cur_whse_co_code IS
432 SELECT mst.co_code, mst.orgn_code
433 FROM sy_orgn_mst mst, ic_whse_mst ic
434 WHERE ic.whse_code = v_to_whse and
435 mst.orgn_code = ic.orgn_code;
436
437 CURSOR Cur_acctg_unit_id IS
438 SELECT acctg_unit_id
439 FROM gl_accu_map
440 WHERE co_code = X_co_code and
441 (orgn_code = V_orgn_code or orgn_code IS NULL) and
442 (whse_code = V_to_whse or whse_code IS NULL) and
443 delete_mark = 0
444 order by nvl(orgn_code, ' ') desc, nvl(whse_code, ' ') desc;
445
446 CURSOR Cur_item_mst IS
447 SELECT noninv_ind,gl_class
448 FROM ic_item_mst
449 WHERE item_id = V_gl_item_id;
450
451 CURSOR Cur_po_vend_mst IS
452 SELECT vendgl_class gl_vendorgl_class
453 FROM po_vend_mst
454 WHERE vendor_id = V_shipvend_id;
455
456 CURSOR Cur_po_cost_mst IS
457 SELECT cmpntcls_id , analysis_code
458 FROM po_cost_mst
459 WHERE aqui_cost_id = P_aqui_cost_id;
460
461 /*Sandeep. Modified the Cursor for doc_type 'PORD' and 'RECV". */
462 /*Initially, it was hard-coded to 'PORD'.*/
463 CURSOR Cur_get_aqui_costs IS
464 SELECT aqui_cost_id , cost_amount , incl_ind
465 FROM po_cost_dtl
466 WHERE doc_type = P_doc_type and pos_id = P_pos_id and
467 line_id = P_line_id;
468 CURSOR Cur_fiscal_year IS
469 SELECT fiscal_year,period
470 FROM gl_cldr_dtl
471 WHERE co_code = P_co_code and
472 period_end_date >= P_po_date
473 and delete_mark = 0;
474
475 CURSOR Cur_ledg_code IS
476 SELECT ledger_code
477 FROM gl_ledg_map
478 WHERE co_code = P_co_code and
479 (orgn_code = P_orgn_code or orgn_code IS NULL) and
480 delete_mark = 0;
481
482 CURSOR Cur_dec_precision IS
483 SELECT decimal_precision
484 FROM gl_curr_mst
485 WHERE currency_code = V_billing_currency;
486
487 X_retvar NUMBER DEFAULT 0;
488 X_aqui_row_num NUMBER DEFAULT 0;
489 X_row_num NUMBER DEFAULT 0;
490 X_status NUMBER DEFAULT 0;
491 X_retval NUMBER DEFAULT 0;
492
493 /* PL/SQL table types are defined in gmlgldists.pls */
494 X_gltitles1 t_gltitlestable;
495 X_cmpntcls1 t_cmpntclstable;
496 X_analysiscode1 t_analysiscodetable;
497
498 BEGIN
499 P_doc_type := V_doc_type;
500 P_pos_id := V_pos_id;
501 P_line_id := V_line_id;
502 P_orgn_code := V_orgn_code;
503 P_po_date := V_po_date;
504 P_shipvend_id := V_shipvend_id;
505 P_base_currency := V_base_currency;
506 P_billing_currency := V_billing_currency;
507 P_to_whse := V_to_whse;
508 P_line_no := V_line_no;
509 P_item_no := V_item_no;
510 P_project := V_project;
511 P_order_qty1 := V_order_qty1;
512 P_order_um1 := V_order_um1;
513 P_gl_item_id := V_gl_item_id;
514 P_mul_div_sign := V_mul_div_sign;
515 P_exchange_rate := V_exchange_rate;
516 P_extended_price := ( V_order_qty1 * v_price );
517 P_action := V_action;
518 retcode := 0;
519 P_transaction_type := V_transaction_type;
520
521 /* B1377089 RVK */
522 P_aqui_cost_id := NULL;
523
524 /* Each time delete the distributions and recreate them. */
525 /* B1409258*/
526 IF V_action = 4
527 THEN
528 DELETE po_dist_dtl
529 WHERE doc_type = P_doc_type
530 AND DOC_ID = P_pos_id
531 AND line_id = P_line_id;
532 END IF;
533
534
535 OPEN Cur_dec_precision;
536 FETCH Cur_dec_precision INTO P_precision;
537 CLOSE Cur_dec_precision;
538
539 IF P_base_currency IS NULL THEN
540 OPEN Cur_base_curr;
541 FETCH Cur_base_curr INTO P_base_currency ;
542 CLOSE Cur_base_curr;
543 END IF;
544
545 IF P_exchange_rate IS NULL OR P_exchange_rate = 0 THEN
546 IF P_base_currency IS NULL THEN
547 P_default_currency := SY$DEFAULT_CURR;
548 END IF;
549
550
551 IF P_default_currency = P_base_currency THEN
552 P_exchange_rate := 1;
553 P_mul_div_sign := 0;
554 ELSE
555 /* PLL call to GLCOMMON*/
556 X_retvar := GML_PO_GLDIST.get_exchg_rate( 1, P_po_date, P_default_currency ,P_Billing_currency);
557 IF X_retvar < 1 THEN /*- Query Fails*/
558 P_exchange_rate := 1;
559 P_mul_div_sign := 0;
560 END IF;
561 END IF;
562 END IF;
563
564 IF P_mul_div_sign = 1 THEN
565 P_exchange_rate := 1.0/P_exchange_rate;
566 ELSE
567 P_exchange_rate := P_exchange_rate;
568 END IF;
569
570 OPEN Cur_orgn_mst;
571 FETCH Cur_orgn_mst INTO P_co_code;
572 CLOSE Cur_orgn_mst;
573
574 /* RVK B1394532 */
575 OPEN Cur_whse_co_code;
576 FETCH Cur_whse_co_code INTO P_whse_co_code,P_whse_orgn_code;
577 IF Cur_whse_co_code%NOTFOUND THEN
578 P_whse_co_code := P_co_code;
579 P_whse_orgn_code := P_orgn_code;
580 END IF;
581 CLOSE Cur_whse_co_code;
582
583
584 /*Sandeep. Code added to check, if 'GL$FINANCIAL_PACKAGE' is set to ORAFIN,*/
585 /* Then Fiscal Yr and Period values are fetched*/
586 /* from FINANCIAL Tables, else, fetched from GEMMS Tables.*/
587 /* B1297909 */
588 /* IF FND_PROFILE.VALUE ('GL$FINANCIAL_PACKAGE' ) = 'ORAFIN' THEN */
589 X_retval := GML_PO_GLDIST.get_orafin_sob (P_co_code, 0);
590 IF X_retval >= 0 THEN
591 /* GML_PO_GLDIST.P_period_date := P_po_date;*/
592 X_retval := GML_PO_GLDIST.get_ofperiod_info (P_co_code, 0,
593 GML_PO_GLDIST.P_sobname,GML_PO_GLDIST.P_calendar_name,
594 GML_PO_GLDIST.P_period_type, NULL, NULL,P_po_date);
595 /* 11.Nov.98 GLCOMMON.pll is modified, and accordingly , the changes */
596 /* are reflected here.*/
597 IF X_retval >= 0 THEN
598 GML_PO_GLDIST.P_fiscal_year := GML_PO_GLDIST.P_periodyear;
599 GML_PO_GLDIST.P_period := GML_PO_GLDIST.P_periodnumber;
600 END IF;
601 /* 11.Nov.98. Change ends here.*/
602 END IF;
603 /* ELSE
604 OPEN Cur_fiscal_year;
605 FETCH Cur_fiscal_year INTO P_fiscal_year, P_period;
606 CLOSE Cur_fiscal_year;
607 END IF;
608 */
609 OPEN Cur_ledg_code;
610 FETCH Cur_ledg_code INTO P_ledger_code;
611 CLOSE Cur_ledg_code;
612
613 /* Select proper acctg_unit_id for each warehouse.*/
614 OPEN Cur_acctg_unit_id;
615 FETCH Cur_acctg_unit_id INTO P_acctg_unit_id;
616 CLOSE Cur_acctg_unit_id;
617
618 /*Added select of gl_class to be passed to mapping PCR 9475*/
619 OPEN Cur_item_mst;
620 FETCH Cur_item_mst INTO P_non_inv_ind, P_itemglclass;
621 CLOSE Cur_item_mst;
622
623 /* Added select of vendor gl_class to be passed to mapping*/
624 OPEN Cur_po_vend_mst;
625 FETCH Cur_po_vend_mst INTO P_vend_gl_class;
626 CLOSE Cur_po_vend_mst;
627
628 IF V_Single_aqui = TRUE THEN
629 GML_PO_GLDIST.poglded2_check_new_aqui(retcode) ;
630 ELSE
631 GML_PO_GLDIST.load_acct_titles('ITEM',
632 P_gl_item_id,
633 P_co_code,
634 P_non_inv_ind,
635 P_to_whse,
636 P_po_date,
637 0, /* incl_ind for aqui cost.*/
638 /* '0' passed for an item*/
639 X_row_num,
640 X_status,
641 X_gltitles1,
642 X_cmpntcls1,
643 X_analysiscode1);
644 FOR i IN 1 .. X_row_num LOOP
645 P_amount_trans := 0;
646 P_amount_base := 0;
647 P_amount_trans_aqui := 0;
648 P_amount_base_aqui := 0;
649
650 P_acct_ttl_num := X_gltitles1(i);
651 P_cost_cmpntcls_id := X_cmpntcls1(i);
652 P_cost_analysis_code := X_analysiscode1(i);
653 IF P_cost_cmpntcls_id = 0 THEN
654 P_cost_cmpntcls_id := NULL;
655 END IF;
656 GML_PO_GLDIST.process_trans ('ITEM', retcode);
657 END LOOP;
658
659 /*Initialise X_aqui_row_num*/
660 X_aqui_row_num := 0;
661 FOR Rec IN Cur_get_aqui_costs LOOP
662 P_amount_trans := 0;
663 P_amount_base := 0;
664 P_amount_trans_aqui := 0;
665 P_amount_base_aqui := 0;
666
667 P_aqui_cost_id := Rec.aqui_cost_id;
668 P_cost_amount := Rec.cost_amount;
669 P_incl_ind := Rec.incl_ind;
670
671 P_aqui_cmpntcls_id := 0;
672 P_aqui_analysis_code := NULL;
673 OPEN Cur_po_cost_mst;
674 FETCH Cur_po_cost_mst INTO P_aqui_cmpntcls_id,P_aqui_analysis_code;
675 IF Cur_po_cost_mst%NOTFOUND THEN
676 CLOSE Cur_po_cost_mst;
677 ELSE
678 CLOSE Cur_po_cost_mst;
679 /*X_no_acqui_titles := poglded2_load_acct_title_array ('AQUI',X_aqui_row_num );*/
680 GML_PO_GLDIST.load_acct_titles('AQUI',
681 P_gl_item_id,
682 P_co_code,
683 P_non_inv_ind,
684 P_to_whse,
685 P_po_date,
686 P_incl_ind,
687 X_row_num,
688 X_status,
689 X_gltitles1,
690 X_cmpntcls1,
691 X_analysiscode1);
692 FOR i IN 1..X_row_num LOOP
693 P_acct_ttl_num := X_gltitles1(i);
694 P_cost_cmpntcls_id := X_cmpntcls1(i);
695 P_cost_analysis_code := X_analysiscode1(i);
696 IF P_cost_cmpntcls_id = 0 THEN
697 P_cost_cmpntcls_id := NULL;
698 END IF;
699 process_trans ('AQUI',retcode);
700 END LOOP;
701 END IF;
702 END LOOP;
703 END IF;
704
705 END receive_data;
706
707 /*************************************************************************
708 # PROC
709 # poglded2_process_trans
710 #
711 # INPUT PARAMETERS
712 # V_type (10) 'TEMM' or 'AQUI'
713 #
714 #
715 # DESCRIPTION
716 # pass data parmeters to poglded2_process_trans to post into the database.
717 #
718 # HISTORY
719 # created by 12.Oct.1998
720 #
721 #**************************************************************************/
722
723 PROCEDURE process_trans (V_type VARCHAR2, retcode IN OUT NOCOPY NUMBER) AS
724 X_retvar NUMBER;
725 BEGIN
726 P_acct_id := GML_PO_GLDIST.default_mapping ;
727 P_acctg_unit_no := GML_PO_GLDIST.get_acctg_unit_no ;
728 GML_PO_GLDIST.get_acct_no (P_acct_no, P_acct_desc );
729 IF (V_type = 'ITEM') THEN
730 GML_PO_GLDIST.calc_dist_amount ;
731 ELSIF (V_type = 'AQUI') THEN
732 IF P_aqui_cost_id > 0 THEN
733 X_retvar := GML_PO_GLDIST.calc_dist_amount_aqui ;
734 END IF;
735 END IF;
736 GML_PO_GLDIST.set_data (retcode);
737 END process_trans;
738
739 /*##########################################################################
740 # PROC
741 # poglded2_default_mapping
742 #
743 # INPUT PARAMETERS
744 # Package Variables are passed to the fuction
745 # HISTORY
746 # created by Sandeep 12.Oct.1998
747 # RETURNS
748 # < 0 - Mapping failed
749 # > 0 - Mapping Successful.
750 #
751 #########################################################################*/
752
753 FUNCTION default_mapping RETURN NUMBER AS
754 X_i NUMBER;
755 BEGIN
756 /* RVK B1394532 */
757 gmf_get_mappings.get_account_mappings ( P_whse_co_code,
758 P_whse_orgn_code,
759 P_to_whse,
760 P_gl_item_id,
761 P_shipvend_id,
762 P_cust_id,
763 P_reason_code,
764 P_itemglclass,
765 P_vend_gl_class,
766 P_cust_gl_class,
767 P_base_currency,
768 P_routing_id,
769 P_charge_id,
770 P_taxauth_id,
771 P_aqui_cost_id,
772 P_resources,
773 P_cost_cmpntcls_id,
774 P_cost_analysis_code,
775 P_order_type,
776 P_sub_event_type );
777 P_acct_id := gmf_get_mappings.get_account_value (P_acct_ttl_num );
778 RETURN (P_acct_id );
779
780 END default_mapping;
781
782 /*##########################################################################
783 # PROC
784 # get_acctg_unit_no
785 #
786 # INPUT PARAMETERS
787 # Package Variables are passed to the Function
788 # RETURNS
789 # If success, returns acctg_unit_no ELSE null.
790 # HISTORY
791 # created by Sandeep 12.0ct.1998
792 #
793 # 21-JUN-1999 Tony Ricci change order by in select from gl_accu_map
794 # B931936
795 # 21-JUN-1999 Tony Ricci add login to select and check map_orgn_ind
796 # B931936
797 ############################################################################*/
798
799 FUNCTION get_acctg_unit_no RETURN VARCHAR2 AS
800
801 CURSOR Cur_map_orgn_ind IS
802 SELECT map_orgn_ind
803 FROM gl_sevt_ttl
804 WHERE sub_event_type = P_sub_event_type and
805 acct_ttl_type = P_acct_ttl_num;
806
807 CURSOR Cur_whse_orgn_code IS
808 SELECT orgn_code
809 FROM ic_whse_mst
810 WHERE whse_code = P_to_whse;
811
812 /* RVK B1394532 */
813 CURSOR Cur_acctg_unit_id (vc_orgn_code VARCHAR2) IS
814 SELECT acctg_unit_id , orgn_code , whse_code
815 FROM gl_accu_map
816 WHERE co_code = P_whse_co_code and
817 (orgn_code = vc_orgn_code or orgn_code IS NULL) and
818 (whse_code = P_to_whse or whse_code IS NULL) and
819 delete_mark = 0
820 order by nvl(orgn_code, ' ') desc, nvl(whse_code, ' ') desc;
821
822 CURSOR Cur_acctg_unit_no IS
823 SELECT acctg_unit_no
824 FROM gl_accu_mst
825 WHERE acctg_unit_id = P_acctg_unit_id;
826
827 X_acctg_orgn VARCHAR2(10);
828 X_acctg_whse VARCHAR2(10);
829 X_map_orgn_ind gl_sevt_ttl.map_orgn_ind%TYPE;
830 X_orgn_code sy_orgn_mst.orgn_code%TYPE;
831 X_co_code sy_orgn_mst.co_code%TYPE;
832
833 BEGIN
834
835 X_orgn_code := P_orgn_code;
836
837 OPEN Cur_map_orgn_ind;
838 FETCH Cur_map_orgn_ind INTO X_map_orgn_ind;
839 CLOSE Cur_map_orgn_ind;
840
841 IF X_map_orgn_ind = 0 THEN
842 OPEN Cur_whse_orgn_code;
843 FETCH Cur_whse_orgn_code INTO X_orgn_code;
844 CLOSE Cur_whse_orgn_code;
845 END IF;
846
847 OPEN Cur_acctg_unit_id (X_orgn_code);
848 FETCH Cur_acctg_unit_id INTO P_acctg_unit_id, X_acctg_orgn, X_acctg_whse;
849 CLOSE Cur_acctg_unit_id;
850
851 OPEN Cur_acctg_unit_no;
852 FETCH Cur_acctg_unit_no INTO P_acctg_unit_no;
853 CLOSE Cur_acctg_unit_no;
854 RETURN ( P_acctg_unit_no );
855
856 END get_acctg_unit_no;
857
858 /*##########################################################################
859 # PROC
860 # get_acct_no
861 #
862 # INPUT PARAMETERS
863 # Package variables are passed to the procedure
864 # DESCRIPTION
865 # This procedure returns the corresponding Account no. and Account desc
866 # based on the P_acct_id
867 ##########################################################################*/
868
869 PROCEDURE get_acct_no(V_acct_no OUT NOCOPY VARCHAR2, V_acct_desc OUT NOCOPY VARCHAR2) AS
870
871 CURSOR Cur_acct_no IS
872 SELECT acct_no, acct_desc
873 FROM gl_acct_mst
874 WHERE acct_id= P_acct_id;
875
876 BEGIN
877 OPEN Cur_acct_no;
878 FETCH Cur_acct_no INTO V_acct_no, V_acct_desc;
879 CLOSE Cur_acct_no;
880
881 END get_acct_no;
882
883
884
885
886
887 /*##############################################################################
888 #
889 # Procedure Name
890 # proc get_exchg_rate
891 # Input Parameters
892 # psource_type - the source type e.g. 1, 2 etc
893 # _date - the name of the variable (NOT the date itself)
894 # containing the trans date to use
895 # pto_currency - the to currency code to select by
896 # pfrom_currency - the from currency code to select by
897 # Description
898 # Retrieves the exchange rate and mul_div_sign based on the parameters
899 # send in, psource_type _date, pto_currency and pfrom_currency, from
900 # gl_xchg_rte table. the row selected should be the latest dated
901 # row.
902 #
903 ##############################################################################*/
904
905 FUNCTION get_exchg_rate(V_psource_type NUMBER, V_po_date DATE,
906 V_default_currency VARCHAR2 ,V_billing_currency VARCHAR2 )
907 RETURN NUMBER AS
908 CURSOR Cur_get_exch_rate IS
909 SELECT ex.exchange_rate , ex.mul_div_sign,
910 ex.exchange_rate_date
911 FROM gl_xchg_rte ex, gl_srce_mst src
912 WHERE ex.to_currency_code = V_default_currency and
913 ex.from_currency_code= V_billing_currency and
914 ex.exchange_rate_date <= V_po_date and
915 ex.rate_type_code = src.rate_type_code and
916 src.trans_source_type = V_psource_type and ex.delete_mark=0
917 order by 3 desc;
918
919 CURSOR Cur_get_exch_rate_inv IS
920 SELECT ex.exchange_rate , ex.mul_div_sign,
921 ex.exchange_rate_date
922 FROM gl_xchg_rte ex, gl_srce_mst src
923 WHERE ex.to_currency_code = V_billing_currency and
924 ex.from_currency_code= V_default_currency and
925 ex.exchange_rate_date <= V_po_date and
926 ex.rate_type_code = src.rate_type_code and
927 src.trans_source_type = V_psource_type and ex.delete_mark=0
928 order by 3 desc;
929 X_fetch NUMBER DEFAULT 0;
930 BEGIN
931 OPEN Cur_get_exch_rate;
932 FETCH Cur_get_exch_rate INTO GML_PO_GLDIST.P_exchange_rate, GML_PO_GLDIST.P_mul_div_sign, GML_PO_GLDIST.P_exch_date;
933 IF Cur_get_exch_rate%NOTFOUND THEN
934 X_fetch := 0;
935 OPEN Cur_get_exch_rate_inv;
936 X_fetch := 1;
937 FETCH Cur_get_exch_rate_inv INTO GML_PO_GLDIST.P_exchange_rate, GML_PO_GLDIST.P_mul_div_sign, GML_PO_GLDIST.P_exch_date;
938 IF Cur_get_exch_rate_inv%NOTFOUND THEN
939 X_fetch := 0;
940 GML_PO_GLDIST.P_exchange_rate := 1;
941 GML_PO_GLDIST.P_mul_div_sign := 0;
942 ELSE
943 IF GML_PO_GLDIST.P_mul_div_sign = 0 THEN
944 GML_PO_GLDIST.P_mul_div_sign := 1;
945 ELSE
946 GML_PO_GLDIST.P_mul_div_sign := 0;
947 END IF;
948 END IF;
949 CLOSE Cur_get_exch_rate_inv;
950 CLOSE Cur_get_exch_rate;
951 RETURN ( X_fetch );
952 END IF;
953 END get_exchg_rate;
954
955
956 /*############################################################################
957 #
958 # PROC
959 # set_data
960 #
961 #
962 # DESCRIPTION
963 # This procedure would set data into the Record group for final posting
964 # into the PO Dist table. ( PO_DIST_DTL ).
965 # Uday Phadtare 02/25/2002 B2237665 Added do_type in the where clause
966 # when updating po_dist_dtl.
967 ##############################################################################*/
968
969 PROCEDURE set_data(retcode IN OUT NOCOPY NUMBER) AS
970 /*x_row_num NUMBER DEFAULT 0;*/
971 X_amount_base NUMBER DEFAULT 0;
972 X_amount_trans NUMBER DEFAULT 0;
973 X_last_update_date DATE;
974 X_created_by NUMBER;
975 X_creation_date DATE;
976 X_last_updated_by NUMBER;
977 X_last_update_login NUMBER;
978 X_trans_cnt NUMBER;
979 X_text_code NUMBER;
980 X_delete_mark NUMBER;
981 X_retval NUMBER;
982 X_order_qty1 NUMBER;
983 err_msg VARCHAR2(100);
984
985 /*Bug# 1324319 Added code to pass the AAP and PPV accts generated at the OPM side
986 over to the APPS side.*/
987 X_combination_id NUMBER;
988
989 CURSOR Cur_count_rows IS
990 SELECT count (*) from po_dist_dtl
991 WHERE doc_id = GML_PO_GLDIST.P_pos_id and
992 line_id = GML_PO_GLDIST.P_line_id and
993 doc_type = GML_PO_GLDIST.P_doc_type;
994
995 BEGIN
996 GML_PO_GLDIST.P_doc_type := GML_PO_GLDIST.P_doc_type;
997 GML_PO_GLDIST.P_recv_seq_no := GML_PO_GLDIST.GL$SE_NEW_RECV ;
998
999 OPEN Cur_count_rows;
1000 FETCH Cur_count_rows INTO GML_PO_GLDIST.P_row_num;
1001 CLOSE Cur_count_rows;
1002 GML_PO_GLDIST.P_row_num := nvl(GML_PO_GLDIST.P_row_num, 0) + 1;
1003
1004 IF GML_PO_GLDIST.P_type = 'ITEM' THEN
1005 X_amount_base := GML_PO_GLDIST.P_amount_base;
1006 X_amount_trans := GML_PO_GLDIST.P_amount_trans;
1007 ELSIF GML_PO_GLDIST.P_type = 'AQUI' THEN
1008 X_amount_base := GML_PO_GLDIST.P_amount_base_aqui;
1009 X_amount_trans := GML_PO_GLDIST.P_amount_trans_aqui;
1010 END IF;
1011
1012 X_last_update_date := SYSDATE;
1013 X_created_by := FND_PROFILE.VALUE ('USER_ID');
1014 X_creation_date := SYSDATE;
1015 X_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
1016 X_last_update_login := 0;
1017 X_trans_cnt := 0;
1018 X_text_code := NULL;
1019 X_delete_mark := 0;
1020
1021 /*Sandeep. Bug Fixed. Modified for setting the Qty to '0' , if it is an*/
1022 /* Aqui. row.*/
1023 IF GML_PO_GLDIST.p_aqui_cost_id = 0 OR
1024 GML_PO_GLDIST.p_aqui_cost_id IS NULL THEN
1025 GML_PO_GLDIST.P_aqui_cost_id := NULL;
1026 X_order_qty1 := GML_PO_GLDIST.P_order_qty1;
1027 ELSE
1028 X_order_qty1 := 0;
1029 END IF;
1030
1031 IF (GML_PO_GLDIST.P_acct_id IS NULL OR GML_PO_GLDIST.P_acct_id = -1) THEN
1032 retcode := 1;
1033 ELSIF GML_PO_GLDIST.P_acctg_unit_id IS NULL THEN
1034 retcode := 2;
1035 ELSIF GML_PO_GLDIST.P_fiscal_year IS NULL THEN
1036 retcode := 3;
1037 ELSIF GML_PO_GLDIST.P_ledger_code IS NULL THEN
1038 retcode := 4;
1039 END IF;
1040
1041 IF retcode >0 THEN
1042 RETURN;
1043 END IF;
1044
1045
1046 IF (GML_PO_GLDIST.P_action = 1 ) THEN
1047 INSERT INTO PO_DIST_DTL ( DOC_TYPE,
1048 DOC_ID,
1049 LINE_ID,
1050 RECV_SEQ_NO,
1051 SEQ_NO,
1052 AQUI_COST_ID,
1053 ITEM_ID,
1054 ACCTG_UNIT_ID,
1055 ACCT_ID,
1056 ACCT_DESC,
1057 ACCT_TTL_TYPE,
1058 AMOUNT_BASE,
1059 AMOUNT_TRANS,
1060 QUANTITY,
1061 QUANTITY_UM,
1062 PROJECT_NO,
1063 GL_POSTED_IND,
1064 EXPORTED_DATE,
1065 CURRENCY_TRANS,
1066 CURRENCY_BASE,
1067 CO_CODE,
1068 LEDGER_CODE,
1069 FISCAL_YEAR,
1070 PERIOD,
1071 LAST_UPDATE_DATE,
1072 CREATED_BY,
1073 CREATION_DATE,
1074 LAST_UPDATED_BY,
1075 LAST_UPDATE_LOGIN,
1076 TRANS_CNT,
1077 TEXT_CODE,
1078 DELETE_MARK)
1079 VALUES (GML_PO_GLDIST.P_doc_type,
1080 GML_PO_GLDIST.P_pos_id,
1081 GML_PO_GLDIST.P_line_id,
1082 GML_PO_GLDIST.P_recv_seq_no,
1083 GML_PO_GLDIST.p_row_num,
1084 GML_PO_GLDIST.P_aqui_cost_id,
1085 GML_PO_GLDIST.P_gl_item_id,
1086 GML_PO_GLDIST.P_acctg_unit_id,
1087 GML_PO_GLDIST.P_acct_id,
1088 GML_PO_GLDIST.P_acct_desc,
1089 GML_PO_GLDIST.P_acct_ttl_num,
1090 nvl(X_amount_base,0),
1091 nvl(X_amount_trans,0),
1092 nvl(X_order_qty1,0),
1093 GML_PO_GLDIST.P_order_um1,
1094 GML_PO_GLDIST.P_project,
1095 nvl(GML_PO_GLDIST.P_gl_posted_ind,0),
1096 GML_PO_GLDIST.P_po_date,
1097 GML_PO_GLDIST.P_billing_currency,
1098 GML_PO_GLDIST.P_base_currency,
1099 GML_PO_GLDIST.P_co_code,
1100 GML_PO_GLDIST.P_ledger_code,
1101 GML_PO_GLDIST.P_fiscal_year,
1102 GML_PO_GLDIST.P_period,
1103 X_last_update_date,
1104 X_created_by,
1105 X_creation_date,
1106 X_last_updated_by,
1107 X_last_update_login,
1108 X_trans_cnt,
1109 X_text_code,
1110 X_delete_mark );
1111
1112
1113 /*Bug# 1324319 Added code to pass the AAP and PPV accts generated at the OPM side
1114 over to the APPS side. */
1115 if GML_PO_GLDIST.P_acct_ttl_num in (3100,6100) and GML_PO_GLDIST.P_aqui_cost_id is NULL
1116 then
1117 /* RVK 1394532 */
1118 GML_PO_GLDIST.combination_id( GML_PO_GLDIST.P_whse_co_code,
1119 GML_PO_GLDIST.P_acct_id,
1120 GML_PO_GLDIST.P_acctg_unit_id,
1121 X_combination_id);
1122
1123 GML_PO_GLDIST.update_accounts_orcl( GML_PO_GLDIST.P_pos_id,
1124 GML_PO_GLDIST.P_line_id,
1125 GML_PO_GLDIST.P_orgn_code,
1126 GML_PO_GLDIST.P_acct_ttl_num,
1127 X_combination_id);
1128 end if;
1129
1130 ELSIF ( GML_PO_GLDIST.P_action = 4 ) THEN
1131 GML_PO_GLDIST.P_row_num_upd := NVL(GML_PO_GLDIST.P_row_num_upd,0) + 1;
1132 UPDATE PO_DIST_DTL SET aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id,
1133 item_id = GML_PO_GLDIST.P_gl_item_id,
1134 acctg_unit_id = GML_PO_GLDIST.P_acctg_unit_id,
1135 acct_id = GML_PO_GLDIST.P_acct_id,
1136 acct_desc = GML_PO_GLDIST.P_acct_desc,
1137 acct_ttl_type = GML_PO_GLDIST.P_acct_ttl_num,
1138 amount_base = nvl(X_amount_base,0),
1139 amount_trans = nvl(X_amount_trans,0),
1140 quantity = nvl(GML_PO_GLDIST.P_order_qty1,0),
1141 quantity_um = GML_PO_GLDIST.P_order_um1,
1142 project_no = GML_PO_GLDIST.P_project,
1143 gl_posted_ind = nvl(GML_PO_GLDIST.P_gl_posted_ind,0),
1144 last_update_date = X_last_update_date,
1145 last_updated_by = X_last_updated_by,
1146 last_update_login = X_last_update_login
1147
1148 WHERE doc_type = GML_PO_GLDIST.P_doc_type and /* B2237665 */
1149 doc_id = GML_PO_GLDIST.P_pos_id and
1150 line_id = GML_PO_GLDIST.P_line_id and
1151 recv_seq_no = GML_PO_GLDIST.P_recv_seq_no and
1152 acct_ttl_type = GML_PO_GLDIST.P_acct_ttl_num and
1153 seq_no = GML_PO_GLDIST.P_row_num_upd;
1154
1155 /* B1409258 PPB added the above insert statement incase if PO distributions are not created
1156 for PO due to some reason. If the PO is then updated the the correct distributions will be created...
1157 ie program goes to update the po_dist_dtl and finds no record there and then inserts a new if there is
1158 no record. */
1159 IF (SQL%ROWCOUNT = 0) THEN
1160
1161 INSERT INTO PO_DIST_DTL ( DOC_TYPE,
1162 DOC_ID,
1163 LINE_ID,
1164 RECV_SEQ_NO,
1165 SEQ_NO,
1166 AQUI_COST_ID,
1167 ITEM_ID,
1168 ACCTG_UNIT_ID,
1169 ACCT_ID,
1170 ACCT_DESC,
1171 ACCT_TTL_TYPE,
1172 AMOUNT_BASE,
1173 AMOUNT_TRANS,
1174 QUANTITY,
1175 QUANTITY_UM,
1176 PROJECT_NO,
1177 GL_POSTED_IND,
1178 EXPORTED_DATE,
1179 CURRENCY_TRANS,
1180 CURRENCY_BASE,
1181 CO_CODE,
1182 LEDGER_CODE,
1183 FISCAL_YEAR,
1184 PERIOD,
1185 LAST_UPDATE_DATE,
1186 CREATED_BY,
1187 CREATION_DATE,
1188 LAST_UPDATED_BY,
1189 LAST_UPDATE_LOGIN,
1190 TRANS_CNT,
1191 TEXT_CODE,
1192 DELETE_MARK)
1193 VALUES (GML_PO_GLDIST.P_doc_type,
1194 GML_PO_GLDIST.P_pos_id,
1195 GML_PO_GLDIST.P_line_id,
1196 GML_PO_GLDIST.P_recv_seq_no,
1197 GML_PO_GLDIST.p_row_num,
1198 GML_PO_GLDIST.P_aqui_cost_id,
1199 GML_PO_GLDIST.P_gl_item_id,
1200 GML_PO_GLDIST.P_acctg_unit_id,
1201 GML_PO_GLDIST.P_acct_id,
1202 GML_PO_GLDIST.P_acct_desc,
1203 GML_PO_GLDIST.P_acct_ttl_num,
1204 nvl(X_amount_base,0),
1205 nvl(X_amount_trans,0),
1206 nvl(X_order_qty1,0),
1207 GML_PO_GLDIST.P_order_um1,
1208 GML_PO_GLDIST.P_project,
1209 nvl(GML_PO_GLDIST.P_gl_posted_ind,0),
1210 GML_PO_GLDIST.P_po_date,
1211 GML_PO_GLDIST.P_billing_currency,
1212 GML_PO_GLDIST.P_base_currency,
1213 GML_PO_GLDIST.P_co_code,
1214 GML_PO_GLDIST.P_ledger_code,
1215 GML_PO_GLDIST.P_fiscal_year,
1216 GML_PO_GLDIST.P_period,
1217 X_last_update_date,
1218 X_created_by,
1219 X_creation_date,
1220 X_last_updated_by,
1221 X_last_update_login,
1222 X_trans_cnt,
1223 X_text_code,
1224 X_delete_mark );
1225 END IF;
1226
1227 /*Bug# 1324319 Added code to pass the AAP and PPV accts generated at the OPM side
1228 over to the APPS side.*/
1229 if GML_PO_GLDIST.P_acct_ttl_num in (3100,6100) and GML_PO_GLDIST.p_aqui_cost_id is NULL
1230 then
1231 /* RVK 1394532 */
1232 GML_PO_GLDIST.combination_id( GML_PO_GLDIST.P_whse_co_code,
1233 GML_PO_GLDIST.P_acct_id,
1234 GML_PO_GLDIST.P_acctg_unit_id,
1235 X_combination_id);
1236
1237 GML_PO_GLDIST.update_accounts_orcl( GML_PO_GLDIST.P_pos_id,
1238 GML_PO_GLDIST.P_line_id,
1239 GML_PO_GLDIST.P_orgn_code,
1240 GML_PO_GLDIST.P_acct_ttl_num,
1241 X_combination_id);
1242 end if;
1243 END IF;
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 err_msg := SUBSTRB(SQLERRM, 1, 100);
1247 RAISE_APPLICATION_ERROR(-20000,err_msg);
1248 retcode := 1;
1249
1250 END set_data;
1251
1252 /*############################################################################
1253 #
1254 # PROC
1255 # load_acct_title
1256 #
1257 # INPUT PARAMETERS
1258 # v_type
1259 # v_item_id
1260 # v_non_ind_ind
1261 # v_to_whse_code
1262 # v_item_id
1263 # v_trans_date
1264 # v_include_ind
1265 # OUTPUT PARAMETERS
1266 # v_row_num
1267 # v_status 1 = success
1268 # v_gltitles TYPE t_gltitlestable
1269 # v_cmpntcls TYPE t_cmpntclstable
1270 # v_analysiscode TYPE t_analysiscodetable
1271 #
1272 #
1273 # DESCRIPTION
1274 # load appropriate GL acct titles into array for mapping
1275 # Dynamically create as many INV rows as there
1276 # are PPV/Material Component Class costs for this item
1277 # arrays for later use by _process_trans
1278 #
1279 # Create single INV row if we do not calculate PPV and
1280 # are booking at PO price, i.e. std_actual_ind is 1.
1281 ############################################################################## */
1282
1283 PROCEDURE load_acct_titles(v_type VARCHAR2,
1284 v_item_id NUMBER,
1285 v_co_code VARCHAR2,
1286 v_non_inv_ind NUMBER,
1287 v_to_whse IN VARCHAR2,
1288 v_trans_date IN DATE,
1289 v_include_ind IN NUMBER,
1290 v_row_num OUT NOCOPY NUMBER,
1291 v_status OUT NOCOPY NUMBER,
1292 v_gltitles OUT NOCOPY t_gltitlestable,
1293 v_cmpntcls OUT NOCOPY t_cmpntclstable,
1294 v_analysiscode OUT NOCOPY t_analysiscodetable) AS
1295
1296 CURSOR cur_get_srcevtplc IS
1297 SELECT std_actual_ind,
1298 exp_booked_ind,
1299 acquis_cost_ind
1300 FROM gl_evnt_plc e, gl_srce_mst s, gl_evnt_mst m
1301 WHERE e.co_code = v_co_code
1302 AND e.trans_source_type = s.trans_source_type
1303 AND e.event_type = m.event_type
1304 AND e.trans_source_type = m.trans_source_type
1305 AND s.trans_source_code = 'PO'
1306 AND m.event_code = 'RCPT'
1307 AND e.delete_mark = 0;
1308
1309 CURSOR cur_get_orgn_code IS
1310 SELECT orgn_code
1311 FROM ic_whse_mst
1312 WHERE whse_code = v_to_whse
1313 AND delete_mark = 0;
1314
1315 x_po_whse_orgn VARCHAR2(4);
1316 x_std_act_ind NUMBER;
1317 x_acq_cst_ind NUMBER;
1318 x_exp_booked_ind NUMBER;
1319 x_row_num NUMBER DEFAULT 1;
1320 x_at_inv NUMBER DEFAULT 1500;
1321 x_at_aap NUMBER DEFAULT 3100;
1322 x_at_ppv NUMBER DEFAULT 6100;
1323 x_at_exp NUMBER DEFAULT 5100;
1324 x_at_aac NUMBER DEFAULT 3150;
1325 x_at_acv NUMBER DEFAULT 6150;
1326 x_at_erv NUMBER DEFAULT 5500;
1327 x_default_orgn VARCHAR2(4) := FND_PROFILE.VALUE('SY$DEFAULT_ORGANIZATION');
1328 x_cost_mthd VARCHAR2(10);
1329 x_cmpntcls_id NUMBER;
1330 x_analysis_code CM_ALYS_MST.COST_ANALYSIS_CODE%TYPE; -- bug14480540
1331 x_total_cost NUMBER;
1332 x_stautus NUMBER;
1333 x_status NUMBER;
1334 x_row_count NUMBER DEFAULT 0;
1335
1336 BEGIN
1337 GML_PO_GLDIST.P_type := V_type;
1338
1339 OPEN cur_get_srcevtplc;
1340 FETCH cur_get_srcevtplc INTO x_std_act_ind,x_exp_booked_ind,x_acq_cst_ind ;
1341 CLOSE cur_get_srcevtplc;
1342 IF v_type = 'ITEM' THEN
1343 IF v_non_inv_ind = 1 THEN
1344 v_gltitles(x_row_num) := x_at_exp;
1345 v_cmpntcls(x_row_num) := 0;
1346 v_analysiscode(x_row_num) := '';
1347 x_row_num := x_row_num + 1;
1348 ELSE
1349 /*prep to get all ppv/matl component costs for this item */
1350 OPEN cur_get_orgn_code;
1351 FETCH cur_get_orgn_code INTO x_po_whse_orgn;
1352 IF (cur_get_orgn_code%notfound) THEN
1353 x_po_whse_orgn := x_default_orgn;
1354 END IF;
1355 CLOSE cur_get_orgn_code;
1356 /* get all ppv/matl component costs for this item*/
1357 x_status := gmf_cmcommon.cmcommon_get_cost(v_item_id,v_to_whse,
1358 x_po_whse_orgn,v_trans_date,
1359 x_cost_mthd,x_cmpntcls_id,
1360 x_analysis_code,4,x_total_cost,
1361 x_row_count);
1362 IF (x_row_count IS NULL) THEN
1363 x_row_count := 0;
1364 END IF;
1365 /*force single INV row if std_actual_ind was set to 1*/
1366 /* Bug 1483360 */
1367 IF (x_status <> 1 OR x_row_count = 0 OR x_std_act_ind = 1 OR x_std_act_ind = 2) THEN
1368 /*PPV: force single INV acct title row*/
1369 v_gltitles(x_row_num) := x_at_inv;
1370 v_cmpntcls(x_row_num) := 0;
1371 v_analysiscode(x_row_num) := '';
1372 x_row_num := x_row_num + 1;
1373 ELSE
1374 FOR i IN 1..x_row_count LOOP
1375 /* This will loop for the no of rows returned from cmcommon_get_cost */
1376 /* routine into x_row_count.*/
1377 gmf_cmcommon.get_multiple_cmpts_cost(i,x_cmpntcls_id,x_analysis_code,
1378 x_total_cost,4,x_status);
1379 IF (x_status = 0) THEN
1380 v_gltitles(x_row_num) := x_at_inv;
1381 v_cmpntcls(x_row_num) := x_cmpntcls_id;
1382 v_analysiscode(x_row_num) := x_analysis_code;
1383 x_row_num := x_row_num + 1;
1384 END IF;
1385 END LOOP;
1386 END IF;
1387 END IF;
1388 v_gltitles(x_row_num) := x_at_aap;
1389 v_cmpntcls(x_row_num) := 0;
1390 v_analysiscode(x_row_num) := '';
1391 /* Bug 1483360 */
1392 IF (x_std_act_ind = 0 OR x_std_act_ind = 2) THEN
1393 x_row_num := x_row_num + 1;
1394 v_gltitles(x_row_num) := x_at_ppv;
1395 v_cmpntcls(x_row_num) := 0;
1396 v_analysiscode(x_row_num) := '';
1397 END IF;
1398 ELSIF(v_type = 'AQUI') THEN
1399 IF (v_include_ind = 0) THEN
1400 v_gltitles(x_row_num) := x_at_exp;
1401 ELSE
1402 v_gltitles(x_row_num) := x_at_inv;
1403 END IF;
1404 v_cmpntcls(x_row_num) := 0;
1405 v_analysiscode(x_row_num) := '';
1406 x_row_num := x_row_num + 1;
1407 IF (x_acq_cst_ind = 1) THEN
1408 v_gltitles(x_row_num) := x_at_aac;
1409 ELSE
1410 v_gltitles(x_row_num) := x_at_aap;
1411 END IF;
1412 v_cmpntcls(x_row_num) := 0;
1413 v_analysiscode(x_row_num) := '';
1414 /* Bug 1483360 */
1415 IF (x_std_act_ind = 0 OR x_std_act_ind = 2) THEN
1416 x_row_num := x_row_num + 1;
1417 v_gltitles(x_row_num) := x_at_acv;
1418 v_cmpntcls(x_row_num) := 0;
1419 v_analysiscode(x_row_num) := '';
1420 END IF;
1421 END IF;
1422 V_row_num := x_row_num;
1423
1424 END load_acct_titles;
1425
1426 /*##########################################################################
1427 # PROC
1428 # poglded2_check_new_aqui
1429 #
1430 # INPUT PARAMETERS
1431 # p_occur row to change
1432 #
1433 # GLOBAL VARIABLES
1434 #
1435 # RETURNS
1436 # 1 = success
1437 # -1 = failure
1438 #
1439 # DESCRIPTION
1440 # After po_dist_dtl database retreival if a new aquisition cost was entered
1441 # (In popaced2) map it and display.
1442 # If :system.record_status in INSERT mode, we call this procedure from
1443 # popaced2, for each of the record in the INSERT status.
1444 #
1445 ##########################################################################*/
1446
1447 PROCEDURE poglded2_check_new_aqui(retcode IN OUT NOCOPY NUMBER) AS
1448
1449 CURSOR Cur_po_cost_mst IS
1450 SELECT cmpntcls_id , analysis_code
1451 FROM po_cost_mst
1452 WHERE aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id;
1453
1454 CURSOR Cur_get_aqui_costs IS
1455 SELECT aqui_cost_id , cost_amount , incl_ind, delete_mark
1456 FROM po_cost_dtl
1457 WHERE doc_type = GML_PO_GLDIST.P_doc_type and
1458 pos_id = GML_PO_GLDIST.P_pos_id and
1459 line_id = GML_PO_GLDIST.P_line_id;
1460
1461 X_aqui_row_num NUMBER;
1462 X_no_acqui_titles NUMBER;
1463 X_row_num NUMBER;
1464 X_status NUMBER;
1465
1466 X_gltitles1 t_gltitlestable;
1467
1468 X_cmpntcls1 t_cmpntclstable;
1469
1470 X_analysiscode1 t_analysiscodetable;
1471
1472 BEGIN
1473 /*initialize X_aqui_row_num*/
1474 X_aqui_row_num := 0;
1475 /* Sandeep. 11.Nov.98. This procedure modified to delete the existing aqui*/
1476 /* rows and re-post the same in Update mode.*/
1477 GML_PO_GLDIST.delete_aqui_costs;
1478
1479 FOR Rec IN Cur_get_aqui_costs LOOP
1480 GML_PO_GLDIST.P_amount_trans := 0;
1481 GML_PO_GLDIST.P_amount_base := 0;
1482 GML_PO_GLDIST.P_amount_trans_aqui := 0;
1483 GML_PO_GLDIST.P_amount_base_aqui := 0;
1484
1485 GML_PO_GLDIST.P_aqui_cost_id := Rec.aqui_cost_id;
1486 GML_PO_GLDIST.P_cost_amount := Rec.cost_amount;
1487 GML_PO_GLDIST.P_incl_ind := Rec.incl_ind;
1488 /*Sandeep. 11.Nov.98. Added an extra column.*/
1489 GML_PO_GLDIST.P_delete_mark := Rec.delete_mark;
1490
1491 GML_PO_GLDIST.P_aqui_cmpntcls_id := 0;
1492
1493 OPEN Cur_po_cost_mst;
1494 FETCH Cur_po_cost_mst INTO GML_PO_GLDIST.P_aqui_cmpntcls_id,GML_PO_GLDIST.P_aqui_analysis_code;
1495 IF Cur_po_cost_mst%NOTFOUND THEN
1496 CLOSE Cur_po_cost_mst;
1497 ELSE
1498 CLOSE Cur_po_cost_mst;
1499 GML_PO_GLDIST.load_acct_titles('AQUI',
1500 GML_PO_GLDIST.P_gl_item_id,
1501 GML_PO_GLDIST.P_co_code,
1502 GML_PO_GLDIST.P_non_inv_ind,
1503 GML_PO_GLDIST.P_to_whse,
1504 GML_PO_GLDIST.P_po_date,
1505 GML_PO_GLDIST.P_incl_ind,
1506 X_row_num,
1507 X_status,
1508 X_gltitles1,
1509 X_cmpntcls1,
1510 X_analysiscode1);
1511 FOR i IN 1..X_row_num LOOP
1512 GML_PO_GLDIST.P_acct_ttl_num := X_gltitles1(i);
1513 GML_PO_GLDIST.P_cost_cmpntcls_id := X_cmpntcls1(i);
1514 GML_PO_GLDIST.P_cost_analysis_code := X_analysiscode1(i);
1515 IF GML_PO_GLDIST.P_cost_cmpntcls_id = 0 THEN
1516 GML_PO_GLDIST.P_cost_cmpntcls_id := NULL;
1517 END IF;
1518 GML_PO_GLDIST.process_trans ('AQUI',retcode) ;
1519 END LOOP;
1520 END IF;
1521 END LOOP;
1522 /* Sandeep. 11.Nov.98. Change ends here.*/
1523 X_aqui_row_num := 0;
1524 END poglded2_check_new_aqui;
1525
1526 /*##########################################################################
1527 # PROC
1528 # delete_aqui_costs
1529 #
1530 # DESCRIPTION
1531 # This Procedure deletes the existing Aquisition costs, when made a
1532 # modification in the Query mode. In short, it would delete the existing
1533 # Aquisition costs and re-post the data again.
1534 # HISTORY
1535 # created by Sandeep 12.Oct.1998
1536 ##########################################################################*/
1537
1538 PROCEDURE delete_aqui_costs IS
1539 BEGIN
1540 DELETE FROM PO_DIST_DTL
1541 WHERE nvl(aqui_cost_id,0) > 0 and
1542 doc_id = GML_PO_GLDIST.P_pos_id and
1543 line_id = GML_PO_GLDIST.P_line_id and
1544 doc_type = GML_PO_GLDIST.P_doc_type ;
1545 /* FORMS_DDL ('COMMIT');*/
1546 END delete_aqui_costs;
1547
1548 /*#############################################################
1549 # NAME
1550 # get_orafin_sob
1551 # SYNOPSIS
1552 # func glcommon_get_orafin_sob
1553 # V_co_code = company code for which set of books id is to be retrieved
1554 # V_err_ind = If 1 display error messages
1555 # RETURNS
1556 # 0 Success
1557 # -1 Fiscal Policy not setup
1558 # -2 set of books not defined for the co
1559 # -3 DB error
1560 # DESCRIPTION
1561 # This function will get set of books name for a co which is passed as
1562 # input parameter. When ever a call is made to this procedure
1563 # be sure that sob name(P_sobname), calendar_name, period type has to be
1564 # copied back to <block_name>.sob_name, <block_name>.calendar_name
1565 # <block_name>.period_type.
1566 #
1567 # HISTORY
1568 # 11/23/98 T.Ricci Ported from glcommon.pll
1569 ##################################################################*/
1570
1571 FUNCTION get_orafin_sob (V_co_code IN VARCHAR2, V_err_ind IN NUMBER)
1572 RETURN NUMBER IS
1573 X_syarg01 DATE DEFAULT NULL; /* ST_DATE*/
1574 X_syarg02 DATE DEFAULT NULL; /* EN_DATE*/
1575 X_syarg03 VARCHAR2(30) DEFAULT NULL; /* SOB_NAME*/
1576 X_syarg04 NUMBER(15); /* SOB_ID*/
1577 X_syarg05 NUMBER(15) DEFAULT 0; /* LAST_UDATED_BY*/
1578 X_syarg06 VARCHAR2(15) DEFAULT NULL; /* CURRENCY_CODE*/
1579 X_syarg07 NUMBER(15) DEFAULT 0; /* CHART_OF_ACCOUNTS_ID*/
1580 X_syarg08 VARCHAR2(15) DEFAULT NULL; /* PERIOD_SET_NAME*/
1581 X_syarg09 VARCHAR2(1) DEFAULT NULL; /* SUSPENSE_ALLOWED_FLAG*/
1582 X_syarg10 VARCHAR2(1) DEFAULT NULL; /* ALLOW_POSTING_WARNING_FLAG*/
1583 X_syarg11 VARCHAR2(15) DEFAULT NULL; /* ACCOUNTED_PERIOD_TYPE*/
1584 X_syarg12 VARCHAR2(20) DEFAULT NULL; /* SHORT_NAME*/
1585 X_syarg13 VARCHAR2(1) DEFAULT NULL; /* REQUIRE_BUDGET_JOURNALS_FLAG*/
1586 X_syarg14 VARCHAR2(1) DEFAULT NULL; /* ENABLE_BUDGETARY_CONTROL_FLAG*/
1587 X_syarg15 VARCHAR2(1) DEFAULT NULL;/* ALLOW_INTERCOMANY_POSTING_FLAG*/
1588 X_syarg16 DATE DEFAULT NULL; /* CREATION_DATE*/
1589 X_syarg17 NUMBER(15) DEFAULT 0; /* CREATION_BY*/
1590 X_syarg18 NUMBER(15) DEFAULT 0; /* LAST_UPDATE_LOGIN*/
1591 X_syarg19 NUMBER(15) DEFAULT 0; /* LATEST_ENCUMBERANCE_YEAR*/
1592 X_syarg20 VARCHAR2(15) DEFAULT NULL; /* EARLIEST_UNTRANS_PERIOD_NAME*/
1593 X_syarg21 NUMBER(15) DEFAULT 0; /* CUM_TRANS_CODE_COMBINATION_ID*/
1594 X_syarg22 NUMBER(15) DEFAULT 0; /* FUTURE_ENTERABLE_PERIODS_LIMIT*/
1595 X_syarg23 VARCHAR2(15) DEFAULT NULL; /* LATEST_OPENED_PERIOD_NAME*/
1596 X_syarg24 NUMBER(15) DEFAULT 0; /* RET_EARN_CODE_COMBINATION_ID*/
1597 X_syarg25 NUMBER(15) DEFAULT 0; /* RES_ENCUMB_CODE_COMBINATION_ID*/
1598 X_syarg26 NUMBER(15) DEFAULT 0; /* ROW_TO_FETCH*/
1599 X_syarg27 NUMBER(15) DEFAULT 0; /* ERROR_STATUS */
1600 X_rvar NUMBER(10) DEFAULT 0;
1601
1602
1603 CURSOR Cur_gl_plcy_mstc1 IS
1604 SELECT set_of_books_name
1605 FROM gl_plcy_mst
1606 WHERE co_code = V_co_code;
1607 BEGIN
1608 /* Select set of books name from fiscal policy.*/
1609 /*This function will be called from short_name procedure.*/
1610
1611 IF V_co_code IS NOT NULL THEN
1612 OPEN Cur_gl_plcy_mstc1;
1613 FETCH Cur_gl_plcy_mstc1 INTO P_sobname;
1614 IF Cur_gl_plcy_mstc1%NOTFOUND THEN
1615 CLOSE Cur_gl_plcy_mstc1;
1616 RETURN (-1);
1617 END IF;
1618 IF Cur_gl_plcy_mstc1%ISOPEN THEN
1619 CLOSE Cur_gl_plcy_mstc1;
1620 END IF;
1621 IF P_sobname IS NULL THEN
1622 RETURN (-2);
1623 END IF;
1624 END IF;
1625
1626 /* Get calendar name and period type for the set of books.*/
1627
1628 X_syarg03 := P_sobname;
1629 X_syarg04 := NULL;
1630 X_syarg26 := 1;
1631 X_syarg27 := 0;
1632 gmf_gl_get_sob_det.proc_gl_get_sob_det (X_syarg01,X_syarg02,X_syarg03,
1633 X_syarg04,X_syarg05,X_syarg06,
1634 X_syarg07,X_syarg08,X_syarg09,
1635 X_syarg10,X_syarg11,X_syarg12,
1636 X_syarg13,X_syarg14,X_syarg15,
1637 X_syarg16,X_syarg17,X_syarg18,
1638 X_syarg19,X_syarg20,X_syarg21,
1639 X_syarg22,X_syarg23,X_syarg24,
1640 X_syarg25,X_syarg26,X_syarg27);
1641
1642 X_rvar := X_syarg27;
1643 IF (X_syarg27 <> 0) THEN
1644 return -3;
1645 END IF;
1646
1647 P_calendar_name := X_syarg08;
1648 P_period_type := X_syarg11;
1649 RETURN(0);
1650 END get_orafin_sob;
1651
1652 /*#############################################################
1653 # FUNCTION
1654 # get_ofperiod_info
1655 # SYNOPSIS
1656 # func get_ofperiod_info
1657 # RETURNS
1658 # field number of failed field - failure
1659 # 0 - success
1660 # GLOBAL VARIABLES
1661 # workfield1 - periodname
1662 # workfield2 - periodstatus
1663 # workfield3 - periodnumber
1664 # workfield4 - quarternum
1665 # workfield5 - description
1666 # workfield6 - statuscode
1667 # workdated1 - period_start_date
1668 # workdated2 - period_end_date
1669 # DESCRIPTION
1670 # This procedure is to fetch Oracle financials GL data
1671 # related to fiscal year.
1672 # Before making call to this procedure be sure that sob name
1673 # has to populated by the call glcommon_get_orafin_sob.
1674 # when ever a call is made to this procedure be sure to copy the
1675 # following variables.
1676 # P_periodname to <block_name>.periodname
1677 # P_periodstatus to <block_name>.periodstatus
1678 # P_periodnumber to <block_name>.periodnumber
1679 # P_quarternum to <block_name>.quarternum
1680 # P_fiscal_year_desc to <block_name>.fiscal_year_desc
1681 # P_statuscode to <block_name>.statuscode
1682 # P_period_start_date to <block_name>.period_start_date
1683 # P_period_end_date to <block_name>.period_end_date
1684 #
1685 # HISTORY
1686 # 11/23/98 T.Ricci Ported from glcommon.pll
1687 ############################################################### */
1688
1689 FUNCTION get_ofperiod_info(V_co_code IN VARCHAR2, V_err_ind IN NUMBER,
1690 V_sobname VARCHAR2, V_calendar_name VARCHAR2,
1691 V_period_type VARCHAR2, V_gl_period NUMBER,
1692 V_fiscal_year NUMBER, V_gl_date DATE DEFAULT NULL)
1693 RETURN NUMBER IS
1694 X_retvar NUMBER DEFAULT 0;
1695
1696 /* Definition for the variables used in call to stored procedure*/
1697 /* named gmf_gl_get_period_info.gl_get_period_info*/
1698 X_syarg01 VARCHAR2(15); /* CALENDARNAME*/
1699 X_syarg02 VARCHAR2(15); /* PERIODTYPE*/
1700 X_syarg03 DATE; /* DATEINPERIOD*/
1701 X_syarg04 VARCHAR2(30); /* SOBNAME*/
1702 X_syarg05 VARCHAR2(2); /* APPABBR*/
1703 X_syarg06 VARCHAR2(30); /* PERIODNAME*/
1704 X_syarg07 VARCHAR2(1); /* PERIODSTATUS*/
1705 X_syarg08 NUMBER; /* PERIODYEAR*/
1706 X_syarg09 NUMBER; /* PERIODNUMBER*/
1707 X_syarg10 NUMBER; /* QUARTERNUM*/
1708 X_syarg11 VARCHAR2(240); /* DESCRIPTION*/
1709 X_syarg12 NUMBER DEFAULT 0; /* STATUSCODE*/
1710 X_syarg13 NUMBER; /* ROWTOFETCH*/
1711 X_syarg14 DATE; /* PERIOD_START_DATE*/
1712 X_syarg15 DATE; /* PERIOD_END_DATE*/
1713
1714 BEGIN
1715 X_syarg01 := V_calendar_name;
1716 X_syarg02 := V_period_type;
1717 X_syarg04 := V_sobname;
1718 X_syarg05 := 'gl';
1719 IF V_fiscal_year IS NULL THEN
1720 X_syarg08 := NULL;
1721 ELSE
1722 X_syarg08 := V_fiscal_year;
1723 END IF;
1724 IF V_gl_period IS NULL THEN
1725 X_syarg09 := NULL;
1726 ELSE
1727 X_syarg09 := V_gl_period;
1728 END IF;
1729 IF V_gl_date is NULL then
1730 X_syarg03 := NULL;
1731 ELSE
1732 X_syarg03 := V_gl_date;
1733 END IF;
1734 X_syarg13 := 1;
1735 X_syarg12 := 0;
1736 gmf_gl_get_period_info.gl_get_period_info(X_syarg01,X_syarg02,X_syarg03,X_syarg04,
1737 X_syarg05,X_syarg06,X_syarg07,X_syarg08,
1738 X_syarg09,X_syarg10,X_syarg11,X_syarg12,
1739 X_syarg13,X_syarg14,X_syarg15);
1740 IF X_syarg12 IS NULL THEN
1741 X_syarg12 := 0;
1742 END IF;
1743
1744 IF X_syarg12 < 0 THEN
1745 RETURN (-1);
1746 END IF;
1747
1748 P_periodname := X_syarg06;
1749 P_periodstatus := X_syarg07;
1750 P_periodyear := X_syarg08;
1751 P_periodnumber := X_syarg09;
1752 P_quarternum := X_syarg10;
1753 P_fiscal_year_desc := X_syarg11;
1754 P_statuscode := X_syarg12;
1755 P_period_start_date := X_syarg14;
1756 P_period_end_date := X_syarg15;
1757
1758 IF X_syarg12 = 100 THEN
1759 RETURN (-1);
1760 END IF;
1761 /* Since Data Type is set to Datetime, here statements are required
1762 to convert into DD-MON-YYYY HH24:MM:SS format. */
1763 /* Map the period status from OF to gemms*/
1764 IF ((P_periodstatus = 'F') OR
1765 (P_periodstatus = 'N')) THEN
1766 P_periodstatus := '1';
1767 ELSIF P_periodstatus = 'O' THEN
1768 P_periodstatus := '2';
1769 ELSIF P_periodstatus = 'C' THEN
1770 P_periodstatus := '3';
1771 ELSE
1772 P_periodstatus := '4';
1773 END IF;
1774
1775 RETURN (0);
1776 END get_ofperiod_info;
1777
1778
1779
1780 /*Bug# 1324319 Added code to pass the AAP and PPV accts generated at the OPM side
1781 over to the APPS side.*/
1782
1783
1784 /******************************************************************************
1785 * FUNCTION
1786 * get_combination_id
1787 * SYNOPSIS
1788 * proc get_combination_id
1789 * RETURNS
1790 * returns the combination id for an account and accounting unit passed.
1791 * GLOBAL VARIABLES
1792 *
1793 *
1794 * DESCRIPTION
1795 *
1796 *
1797 * HISTORY
1798 * 02/28/00 Preetam Bamb
1799 *******************************************************************************/
1800
1801 PROCEDURE combination_id( v_co_code IN VARCHAR2,
1802 v_acct_id IN NUMBER,
1803 v_acctg_unit_id IN NUMBER,
1804 v_combination_id IN OUT NOCOPY NUMBER) IS
1805
1806 v_acctg_unit_no gl_accu_mst.acctg_unit_no%TYPE := NULL;
1807 v_acct_no gl_acct_mst.acct_no%TYPE := NULL;
1808 v_application_short_name VARCHAR2(50);
1809 v_key_flex_code VARCHAR2(50);
1810 v_chart_of_account_id NUMBER;
1811 v_validation_date DATE;
1812 v_segment_count NUMBER;
1813 v_of_seg fnd_flex_ext.SegmentArray;
1814 x BOOLEAN;
1815 v_segment_delimiter gl_plcy_mst.segment_delimiter%TYPE;
1816
1817
1818 Cursor get_chart_id is
1819 select chart_of_accounts_id
1820 from gl_plcy_mst,gl_sets_of_books
1821 where co_code = P_CO_CODE
1822 and name like set_of_books_name
1823 and set_of_books_id = sob_id;
1824
1825
1826 BEGIN
1827
1828 SELECT acctg_unit_no INTO v_acctg_unit_no
1829 FROM gl_accu_mst WHERE acctg_unit_id = p_acctg_unit_id;
1830
1831 SELECT acct_no INTO v_acct_no
1832 FROM gl_acct_mst
1833 WHERE acct_id = p_acct_id;
1834
1835 /* SR dt 25-Jan-2001 B1530509 added select to get segment delimiter */
1836
1837 SELECT segment_delimiter INTO v_segment_delimiter
1838 FROM gl_plcy_mst
1839 WHERE co_code = p_co_code
1840 AND delete_mark = 0;
1841
1842 parse_account( p_co_code ,
1843 v_acctg_unit_no ||v_segment_delimiter|| v_acct_no,
1844 2,0, v_of_seg, v_segment_count ) ;
1845
1846 /* structure_no */
1847 Open get_chart_id;
1848 Fetch get_chart_id into v_chart_of_account_id;
1849 Close get_chart_id;
1850
1851
1852
1853
1854 v_application_short_name := 'SQLGL';
1855 v_key_flex_code := 'GL#';
1856 v_validation_date := SYSDATE;
1857
1858
1859 x := fnd_flex_ext.get_combination_id( v_application_short_name,
1860 v_key_flex_code,
1861 v_chart_of_account_id,
1862 v_validation_date,
1863 v_segment_count,
1864 v_of_seg,
1865 v_combination_id );
1866
1867
1868
1869
1870 END combination_id;
1871
1872
1873
1874 /******************************************************************************
1875 * FUNCTION
1876 * parse_account
1877 * DESCRIPTION
1878 * Parses the gemms account string and sorts the segment according
1879 * to the order defined in Oracle financials. This is done in order
1880 * to retrieve account balances from financial into gemms interface
1881 * table. This procedure does two jobs one parses gemms to financial
1882 * when v_gemms_acct is set to FALSE and parses financial segments to gemms
1883 * when v_gemms_acct is set to TRUE.
1884 *
1885 * INPUT PARAMETERS
1886 * v_account = Account string to be parsed
1887 * v_type = 0 Parses Account unit segments
1888 * = 1 Parses Account Segments
1889 * = 2 Parses both Account unit and Account segments
1890 * v_offset = Offset value.
1891 *
1892 * OUTPUT PARAMETERS
1893 * GLOBAL
1894 *
1895 * RETURNS
1896 *
1897 *
1898 * HISTORY
1899 * Dt 25-JAN-2001 Sukarna Reddy B1530509 Modified parse account To store segments
1900 * in an array and discard considering Length of each segment while parsing.
1901 * Piyush K. Mishra 17-May-2002 Bug#2376340
1902 * Changed the query for cursor cur_plcy_seg to pick the proper segment number, even
1903 * if the accounting flexfield segments are assigned to different segment columns in
1904 * OPM and GL. Select clause and order by clause has been changed.
1905 ******************************************************************************/
1906
1907 PROCEDURE parse_account( v_co_code IN VARCHAR2,
1908 v_account IN VARCHAR2,
1909 v_type IN NUMBER,
1910 v_offset IN NUMBER,
1911 v_segment IN OUT NOCOPY fnd_flex_ext.SegmentArray,
1912 V_no_of_seg IN OUT NOCOPY NUMBER )
1913 IS
1914
1915 /*Begin Bug#2376340 Piyush K. Mishra
1916 Changed the cursor query.*/
1917 CURSOR cur_plcy_seg IS
1918 SELECT p.type, p.length,
1919 --nvl(substrb(f.application_column_name,8),0) segment_ref, (Commented and added following for B#2376340)
1920 f.segment_num segment_ref,
1921 pm.segment_delimiter
1922 FROM gl_plcy_seg p,
1923 gl_plcy_mst pm,
1924 fnd_id_flex_segments f,
1925 gl_sets_of_books s
1926 WHERE p.co_code = v_co_code
1927 AND p.delete_mark = 0
1928 AND p.co_code = pm.co_code
1929 AND pm.sob_id = s.set_of_books_id
1930 AND s.chart_of_accounts_id = f.id_flex_num
1931 AND f.application_id = 101
1932 AND f.id_flex_code = 'GL#'
1933 AND LOWER(f.segment_name) = LOWER(p.short_name)
1934 AND f.enabled_flag = 'Y'
1935 ORDER BY p.segment_no;
1936 /*End Bug#2376340*/
1937
1938 x_segment_index NUMBER(10) DEFAULT 0;
1939 x_value NUMBER(10);
1940 x_index NUMBER(10);
1941 x_position NUMBER(10) DEFAULT 1;
1942 x_length NUMBER(10);
1943 x_result VARCHAR2(255);
1944 x_gemms_acct VARCHAR2(255);
1945 x_description VARCHAR2(1000) default '';
1946 source_accounts gmf_get_mappings.my_opm_seg_values;
1947 BEGIN
1948 /* B1530509 */
1949 source_accounts := gmf_get_mappings.get_opm_segment_values(v_account,v_co_code,2);
1950
1951 FOR cur_plcy_seg_tmp IN cur_plcy_seg LOOP
1952 x_segment_index := x_segment_index + 1;
1953 IF (cur_plcy_seg_tmp.type = v_type or v_type = 2) THEN
1954 IF (cur_plcy_seg_tmp.segment_ref = 0) THEN
1955 x_value := x_segment_index;
1956 ELSE
1957 x_value := cur_plcy_seg_tmp.segment_ref;
1958 END IF;
1959 x_index := x_value + v_offset;
1960 -- x_length := cur_plcy_seg_tmp.length; /*B1530509 Commented */
1961 v_segment(x_index) := source_accounts(x_position);
1962 --SUBSTR(v_account,x_position,x_length); /*B1530509 Commented*/
1963 x_position := x_position + 1;
1964 END IF;
1965 END LOOP;
1966
1967 v_no_of_seg := x_segment_index;
1968
1969 END parse_account;
1970
1971
1972
1973 /******************************************************************************
1974 * PROCEDURE
1975 * update_accounts_orcl
1976 * SYNOPSIS
1977 * proc update_accounts_orcl
1978 * RETURNS
1979 * This procedure will update the ORacle Fianancial table
1980 * po_distributions_all table with the correct accounts combination id
1981 * for Purchase Price Varaince (PPV ) and Accrued Accounts Payable(AAP)
1982 * GLOBAL VARIABLES
1983 *
1984 *
1985 * DESCRIPTION
1986 *
1987 *
1988 * HISTORY
1989 *02/28/00 Preetam Bamb
1990 *04/12/00 nchekuri Added Cursors inplace of direct select statements 'coz it was causing problems.
1991 *03/27/03 Bug 1994882 Get ccid if null value is passed.
1992 *******************************************************************************/
1993
1994 PROCEDURE update_accounts_orcl( v_po_id IN NUMBER,
1995 v_line_id IN NUMBER,
1996 v_orgn_code IN VARCHAR2,
1997 v_acct_ttl_num IN NUMBER,
1998 v_combination_id IN NUMBER)
1999 IS
2000
2001 v_po_header_id NUMBER;
2002 v_po_line_id NUMBER;
2003 v_po_line_location_id NUMBER;
2004 v_po_release_id NUMBER;
2005 x_combination_id NUMBER;
2006
2007 CURSOR Cur_get_std_poinf IS
2008 SELECT po_header_id,po_line_id,po_line_location_id
2009 FROM cpg_oragems_mapping
2010 WHERE po_id = v_po_id AND
2011 line_id = v_line_id;
2012
2013 CURSOR Cur_get_blk_poinf IS
2014 SELECT po_header_id, po_line_id, po_line_location_id, po_release_id
2015 FROM cpg_oragems_mapping
2016 WHERE po_id = v_po_id AND
2017 line_id = v_line_id;
2018
2019 CURSOR Cur_get_pln_poinf IS
2020 SELECT po_header_id, po_line_id, po_line_location_id, po_release_id
2021 FROM cpg_oragems_mapping
2022 WHERE po_id = v_po_id AND line_id = v_line_id;
2023
2024 /* B1994882 */
2025 CURSOR cur_get_ccid(v_act_ttl_typ NUMBER,v_po_id NUMBER,v_line_id NUMBER) IS
2026 SELECT cc.code_combination_id
2027 FROM gl_code_combinations_kfv cc,
2028 cpg_oragems_mapping map,
2029 gl_accu_mst acu,
2030 gl_acct_mst act,
2031 po_dist_dtl pdd,
2032 po_distributions_all pod,
2033 gl_plcy_mst gpm,
2034 gl_sets_of_books gsob
2035 WHERE map.po_id = v_po_id
2036 AND map.line_id = v_line_id
2037 AND pod.po_header_id = map.po_header_id
2038 AND pod.po_line_id = map.po_line_id
2039 AND pod.line_location_id = map.po_line_location_id
2040 AND NVL(pod.po_release_id,-1) = NVL(map.po_release_id,-1)
2041 AND map.po_id = pdd.doc_id
2042 AND map.line_id = pdd.line_id
2043 AND pdd.acct_ttl_type = v_act_ttl_typ
2044 AND pdd.acctg_unit_id = acu.acctg_unit_id
2045 AND pdd.acct_id = act.acct_id
2046 AND cc.concatenated_segments = acu.acctg_unit_no||gpm.SEGMENT_DELIMITER||act.acct_no
2047 AND cc.chart_of_accounts_id = gsob.CHART_OF_ACCOUNTS_ID
2048 AND gsob.name = gpm.set_of_books_name
2049 AND gsob.set_of_books_id = gpm.sob_id
2050 AND gpm.co_code = pdd.co_code;
2051
2052 BEGIN
2053
2054 /* B1994882 */
2055 x_combination_id := v_combination_id;
2056 IF x_combination_id is null THEN
2057 OPEN cur_get_ccid(p_acct_ttl_num,v_po_id,v_line_id);
2058 FETCH cur_get_ccid INTO x_combination_id;
2059 CLOSE cur_get_ccid;
2060 END IF;
2061
2062 if GML_PO_GLDIST.P_transaction_type in ('STANDARD')
2063 then
2064 OPEN Cur_get_std_poinf;
2065 FETCH Cur_get_std_poinf INTO v_po_header_id, v_po_line_id,
2066 v_po_line_location_id;
2067 IF Cur_get_std_poinf%FOUND THEN
2068
2069 if p_acct_ttl_num = 6100
2070 then
2071 update po_distributions_all
2072 set variance_account_id = x_combination_id
2073 where po_header_id = v_po_header_id
2074 and po_line_id = v_po_line_id
2075 and line_location_id = v_po_line_location_id;
2076 end if;
2077
2078 if p_acct_ttl_num = 3100
2079 then
2080 update po_distributions_all
2081 set accrual_account_id = x_combination_id
2082 where po_header_id = v_po_header_id
2083 and po_line_id = v_po_line_id
2084 and line_location_id = v_po_line_location_id;
2085 end if;
2086 end if;
2087 CLOSE Cur_get_std_poinf;
2088 END IF;
2089
2090
2091
2092 if GML_PO_GLDIST.P_transaction_type in ('BLANKET')
2093 then
2094 OPEN Cur_get_blk_poinf;
2095 FETCH Cur_get_blk_poinf INTO v_po_header_id, v_po_line_id,
2096 v_po_line_location_id, v_po_release_id;
2097
2098 IF Cur_get_blk_poinf%FOUND THEN
2099 if p_acct_ttl_num = 6100
2100 then
2101 update po_distributions_all
2102 set variance_account_id = x_combination_id
2103 where po_header_id = v_po_header_id
2104 and po_line_id = v_po_line_id
2105 and line_location_id = v_po_line_location_id
2106 and po_release_id = v_po_release_id;
2107 end if;
2108
2109 if p_acct_ttl_num = 3100
2110 then
2111 update po_distributions_all
2112 set accrual_account_id = x_combination_id
2113 where po_header_id = v_po_header_id
2114 and po_line_id = v_po_line_id
2115 and line_location_id = v_po_line_location_id
2116 and po_release_id = v_po_release_id;
2117 end if;
2118 end if;
2119 CLOSE Cur_get_blk_poinf;
2120 END IF;
2121
2122
2123 if GML_PO_GLDIST.P_transaction_type in ('PLANNED')
2124 then
2125
2126 /* In planned purchase order -- distributions are generated on the OPM side only when we create a
2127 release against it and in that case there use po_header_id,po_line_id,po_line_location_id and po_release_id
2128 from cpg_oragems_mapping for the corresponding po_id and line_id (and not bpo_id and bpo_line_id)
2129 */
2130 OPEN Cur_get_pln_poinf;
2131 FETCH Cur_get_pln_poinf INTO v_po_header_id, v_po_line_id,
2132 v_po_line_location_id, v_po_release_id;
2133
2134 IF Cur_get_pln_poinf%FOUND THEN
2135 if p_acct_ttl_num = 6100
2136 then
2137 update po_distributions_all
2138 set variance_account_id = x_combination_id
2139 where po_header_id = v_po_header_id
2140 and po_line_id = v_po_line_id
2141 and line_location_id = v_po_line_location_id
2142 and po_release_id = v_po_release_id;
2143 end if;
2144
2145 if p_acct_ttl_num = 3100
2146 then
2147 update po_distributions_all
2148 set accrual_account_id = x_combination_id
2149 where po_header_id = v_po_header_id
2150 and po_line_id = v_po_line_id
2151 and line_location_id = v_po_line_location_id
2152 and po_release_id = v_po_release_id;
2153 end if;
2154 end if;
2155 CLOSE Cur_get_pln_poinf;
2156 END IF;
2157 end update_accounts_orcl;
2158
2159 /*End Bug# 1324319 */
2160
2161
2162
2163 END GML_PO_GLDIST;