eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.RA_LL_RCV_GROUPS_PKG

Source


1 PACKAGE BODY RA_LL_RCV_GROUPS_PKG AS
2 /*$Header: ARRWGLTB.pls 120.5.12010000.2 2008/08/25 19:05:03 mpsingh ship $ */
3 
4 PROCEDURE Delete_Row (
5     X_GROUP_ID  				 IN				 NUMBER,
6     X_CUSTOMER_TRX_ID  				 IN				 NUMBER,
7     X_CASH_RECEIPT_ID				 IN				 NUMBER
8 ) IS
9 BEGIN
10 
11     DELETE AR_ACTIVITY_DETAILS
12     WHERE  1 = 1
13     AND CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
14     AND GROUP_ID = X_GROUP_ID
15     AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- BUG 7241111
16     AND CUSTOMER_TRX_LINE_ID = (select customer_trx_line_id
17                                 from ra_customer_trx
18                                 where customer_trx_id = X_CUSTOMER_TRX_ID);
19 
20 
21     IF ( SQL%NOTFOUND ) THEN
22     -- 18 Oct 2005, don't need to raise error, when there are no rows
23     /*RAISE NO_DATA_FOUND;
24     */ null;
25     END IF;
26 END Delete_Row;
27 
28 PROCEDURE Insert_lintax_Rows (
29     X_GROUP_ID          IN NUMBER,
30     X_CASH_RECEIPT_ID   IN NUMBER,
31     X_CUSTOMER_TRX_ID   IN NUMBER,
32     X_lin               in number,
33     x_tax               in number                ,
34     X_lin_dsc           in number,
35     x_tax_dsc           in number                ,
36     x_CREATED_BY_MODULE in varchar2
37     -- Oct 04 added two param below
38     ,x_inv_to_rct_rate  in number default 1
39     ,x_rct_curr_code    in varchar2 default arpcurr.FunctionalCurrency
40 ) IS
41 
42 cursor c_lintax
43 is
44     select to_char(line.line_number) apply_to,
45     line.customer_trx_line_id LINE_ID,
46     -- No nvl needed in the foll amounts since arp_process_det_pkg.initialization
47     -- would have updated the values to not-nulls
48     line.source_data_key4 GROUP_ID ,
49     line.amount_due_remaining line_rem,
50     line.amount_due_original line_orig,
51     tax.amount_due_remaining tax_rem,
52     tax.amount_due_original tax_orig
53   from ra_customer_trx_lines line,
54   (select link_to_cust_trx_line_id,
55           line_type,
56           sum(nvl(amount_due_original,0)) amount_due_original,
57           sum(nvl(amount_due_remaining,0)) amount_due_remaining
58    from ra_customer_trx_lines
59    where   nvl(line_type,'TAX') =  'TAX'
60    group by link_to_cust_trx_line_id,
61           line_type
62   ) tax
63   where line.line_type = 'LINE'
64     and line.source_data_key4 = x_group_id
65     and   line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
66     and line.customer_trx_id = x_customer_trx_id;
67 
68   lintax_row c_lintax%rowtype;
69 
70   line_count    number;
71   --iterator     number := 0;
72   iterator     number := 1;
73 
74   all_linrem_tot number;
75   all_linorig_tot number;
76   line_run_tot number := 0;
77   line_2b_applied  number;
78 
79   all_taxrem_tot number;
80   all_taxorig_tot number;
81   tax_run_tot number := 0;
82   tax_2b_applied  number;
83 
84   -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
85   lindsc_run_tot number := 0;
86   lindsc_2b_applied  number;
87 
88   taxdsc_run_tot number := 0;
89   taxdsc_2b_applied  number;
90   -- End of additions for bug 4775656
91 
92   --no need for the total for all lines, for amt_app_from
93   --since we are not pro-rating amt_app_from. To get amt_app_from
94   --we are just multiplying the inv_to_rct_rate into prorated amt
95   /*all_amt_app_from number;
96   amt_app_from_run_tot number := 0;*/
97   cross_currency_2b_applied number;
98 
99   l_line_id   NUMBER;
100 
101 BEGIN
102   begin
103     select count(*) ,
104     sum(nvl(line.amount_due_remaining,0)),
105     sum(nvl(tax.amount_due_remaining,0)),
106     sum(nvl(line.amount_due_original,0)),
107     sum(nvl(tax.amount_due_original,0))
108     into line_count,
109          all_linrem_tot, all_taxrem_tot,
110          all_linorig_tot, all_taxorig_tot
111     from ra_customer_trx_lines line,
112     (select link_to_cust_trx_line_id,
113           line_type,
114           sum(nvl(amount_due_original,0)) amount_due_original,
115           sum(nvl(amount_due_remaining,0)) amount_due_remaining
116      from ra_customer_trx_lines
117      where nvl(line_type,'TAX') =  'TAX'
118      group by link_to_cust_trx_line_id,
119           line_type
120     ) tax
121     where line.customer_trx_id = x_customer_trx_id
122     and line.line_type = 'LINE'
123     and line.source_data_key4 = x_group_id
124     and   line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
125     ;
126   exception
127     when others then
128       arp_standard.debug ('Error in calcuating the total of all rows', 'plsql',
129                           'RA_LL_RCV_GROUPS_PKG.INSERT_ROW', 1);
130       raise ;
131   end;
132   for lintax_row in c_lintax loop
133 
134     -- Prorate the Line Amount
135     if iterator = line_count then
136       arp_standard.debug ('i='||to_char(iterator)||'.'|| 'THIS IS THE LAST. line_run_tot=' || line_run_tot);
137       line_2b_applied := nvl(x_lin,0) - line_run_tot;
138       tax_2b_applied := nvl(x_tax,0) - tax_run_tot;
139 
140       -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
141       lindsc_2b_applied := nvl(x_lin_dsc,0) - lindsc_run_tot;
142       taxdsc_2b_applied := nvl(x_tax_dsc,0) - taxdsc_run_tot;
143       -- End of additions for bug 4775656
144     else -- If the adr on the invoice is zero, then
145       if all_linrem_tot > 0 then
146         arp_standard.debug ('i='||to_char(iterator)||'.'||
147                             'NOT LAST, all_linrem_tot<>0. line_run_tot=' || line_run_tot
148                             || '. all_linorig_tot=' || all_linorig_tot);
149         line_2b_applied := arpcurr.currRound(lintax_row.line_rem * nvl(x_lin,0) / all_linrem_tot);
150 
151       else -- Overappl (all_linrem_tot < 0) should be done at the UI level,
152            -- so this means all_linrem_tot = 0
153         arp_standard.debug ('i='||to_char(iterator)||'.'||
154                             'NOT LAST, all_linrem_tot=0. line_run_tot=' || line_run_tot
155                             || '. all_linorig_tot=' || all_linorig_tot);
156         if all_linorig_tot <> 0 then
157           line_2b_applied := arpcurr.currRound(lintax_row.line_orig * nvl(x_lin,0) / all_linorig_tot);
158         else
159           line_2b_applied := 0;
160         end if;
161       end if;
162 
163      --Prorate the Tax Amount
164 
165      if all_taxrem_tot > 0 then
166        tax_2b_applied := arpcurr.currRound(lintax_row.tax_rem * nvl(x_tax,0) / all_taxrem_tot);
167      else-- Overappl (all_taxrem_tot < 0) should be done at the UI level,
168            -- so this means all_taxrem_tot = 0
169       if all_taxorig_tot <> 0 then
170          tax_2b_applied := arpcurr.currRound(lintax_row.tax_orig * nvl(x_tax,0) / all_taxorig_tot);
171        else
172          tax_2b_applied := 0;
173        end if;
174      end if ;
175 
176       -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
177       -- Proate in the same ratio as that of the lin2bapplied / all_lin2bapplied_tot
178         if nvl(x_lin,0) <> 0 then
179           lindsc_2b_applied := arpcurr.currRound(x_lin_dsc * nvl(line_2b_applied,0) /  nvl(x_lin,0) );
180         else
181           lindsc_2b_applied := 0;
182         end if;
183       -- Proate in the same ratio as that of the tax2bapplied / all_tax2bapplied_tot
184         if nvl(x_tax,0) <> 0 then
185           taxdsc_2b_applied := arpcurr.currRound(x_tax_dsc * nvl(tax_2b_applied,0) / nvl(x_tax,0) );
186         else
187           taxdsc_2b_applied := 0;
188         end if;
189       -- End of additions for bug 4775656
190     end if;
191 
192     -- Calculate the Allocated Receipt Amount for the line
193     cross_currency_2b_applied := arp_util.currRound((line_2b_applied+tax_2b_applied) * nvl(x_inv_to_rct_rate,1), x_rct_curr_code);
194     arp_standard.debug ('i='||to_char(iterator)||'.'||
195                               'line_amount='||to_char(line_2b_applied)||'.'||
196                                'tax_amount='||to_char(tax_2b_applied)||'.'||
197                                'alloc_rct_amt='||to_char(cross_currency_2b_applied)||'.'
198                              , 'plsql',
199                           'RA_LL_RCV_GROUPS_PKG.INSERT_ROW', 1);
200 
201     Select ar_Activity_details_s.nextval
202      INTO l_line_id
203      FROM DUAL;
204 
205     INSERT INTO AR_ACTIVITY_DETAILS (
206         LINE_ID,
207         APPLY_TO,
208         customer_trx_line_id,
209         CASH_RECEIPT_ID,
210         GROUP_ID,
211         AMOUNT,
212         allocated_receipt_amount,
213         TAX,
214         CREATED_BY,
215         CREATION_DATE,
216         LAST_UPDATE_LOGIN,
217         LAST_UPDATE_DATE,
218         LAST_UPDATED_BY,
219         OBJECT_VERSION_NUMBER,
220         CREATED_BY_MODULE,
221         SOURCE_TABLE,
222         line_discount,
223         tax_discount,
224 	CURRENT_ACTIVITY_FLAG
225     )
226 
227     VALUES (
228         l_line_id,
229         lintax_row.apply_to,
230         lintax_row.line_id,
231         DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
232         DECODE(X_GROUP_ID, FND_API.G_MISS_NUM, NULL , X_GROUP_ID),
233         arpcurr.currRound(nvl(line_2b_applied ,0)),
234         arp_util.currRound(nvl(cross_currency_2b_applied,0), x_rct_curr_code ),
235         arpcurr.currRound(nvl(tax_2b_applied ,0)),
236         NVL(FND_GLOBAL.user_id,-1),
237         SYSDATE,
238         decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,
239                FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
240         SYSDATE,
241         NVL(FND_GLOBAL.user_id,-1),
242         0, -- Object Version Number is zero when the insert is at the group level
243         x_created_by_module,
244         'RA',
245         lindsc_2b_applied,
246         taxdsc_2b_applied,
247 	'Y'
248         );
249 
250     line_run_tot := line_run_tot + line_2b_applied;
251     tax_run_tot := tax_run_tot + tax_2b_applied;
252     -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
253     lindsc_run_tot := lindsc_run_tot + lindsc_2b_applied;
254     taxdsc_run_tot := taxdsc_run_tot + taxdsc_2b_applied;
255     -- End of additions for bug 4775656
256     iterator := iterator + 1;
257   end loop;
258 END Insert_lintax_Rows;
259 
260 
261 PROCEDURE Lock_Row (
262     X_CUSTOMER_TRX_ID				 IN				 NUMBER,
263     X_CASH_RECEIPT_ID				 IN				 NUMBER,
264     x_object_Version_number in number
265 ) IS
266 BEGIN
267   null;
268 END Lock_Row;
269 
270 
271 PROCEDURE Insert_Row (
272     X_ROWID			 IN OUT NOCOPY ROWID,
273     X_CASH_RECEIPT_ID		 IN NUMBER,
274     X_GROUP_ID     		 IN NUMBER,
275     X_CUSTOMER_TRX_ID            IN NUMBER,
276     X_line_only                  IN NUMBER,
277     x_tax_only                   IN NUMBER,
278     X_lin_dsc                in        number,
279     x_tax_dsc             in number                ,
280     x_CREATED_BY_MODULE          IN VARCHAR2
281     -- Oct 04 added two param below
282     ,x_inv_to_rct_rate in number default 1
283     ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
284 ) IS
285 begin
286         --insert_lintax_rows ( x_cash_receipt_id, x_customer_Trx_id, x_line_only, x_tax_only,
287         --                x_lin_dsc, x_tax_dsc, x_created_by_module
288         --                ,x_inv_to_rct_rate,x_rct_curr_code);
289 
290          Insert_lintax_Rows (
291             X_CASH_RECEIPT_ID => X_CASH_RECEIPT_ID,
292             X_GROUP_ID     	=> X_GROUP_ID,
293             X_CUSTOMER_TRX_ID  =>    X_CUSTOMER_TRX_ID,
294             x_lin               => x_line_only,
295             x_tax                => x_tax_only,
296             x_lin_dsc           => x_lin_dsc,
297             x_tax_dsc           => x_tax_dsc,
298             x_Created_By_Module => 'AR'
299             -- Oct 04, 2005 Two params added below
300             ,x_inv_to_rct_rate => x_inv_to_rct_rate
301             ,x_rct_curr_code       => x_rct_curr_code);
302 end;
303 
304 
305 
306 PROCEDURE Update_Row (
307     X_ROWID	         IN OUT NOCOPY  ROWID,
308     X_CASH_RECEIPT_ID   IN NUMBER,
309     X_GROUP_ID          IN NUMBER,
310     X_CUSTOMER_TRX_ID   IN NUMBER,
311     X_line_only         in number,
312     x_tax_only          in number                ,
313     X_lin_dsc                in        number,
314     x_tax_dsc             in number                ,
315     x_CREATED_BY_MODULE in varchar2
316     -- Oct 04 added two param below
317     ,x_inv_to_rct_rate  in number default 1
318     ,x_rct_curr_code    in varchar2 := arpcurr.FunctionalCurrency
319 ) IS
320   p_rowid rowid;
321 BEGIN
322   delete_Row (x_group_id => x_group_id,
323             x_customer_trx_id => x_customer_trx_id,
324             x_cash_receipt_id => x_cash_receipt_id);
325   insert_Row (
326     x_rowid => p_ROWID				   				 ,
327     X_CASH_RECEIPT_ID => X_CASH_RECEIPT_ID			 				 ,
328     X_GROUP_ID => X_GROUP_ID     				 				 ,
329     X_CUSTOMER_TRX_ID => X_CUSTOMER_TRX_ID,
330     X_line_only => X_line_only,
331     x_tax_only => x_tax_only,
332     X_lin_dsc => X_lin_dsc,
333     x_tax_dsc => x_tax_dsc,
334     x_created_by_module => x_created_by_module
335             -- Oct 04, 2005 Two params added below
336             ,x_inv_to_rct_rate =>x_inv_to_rct_rate
337             ,x_rct_curr_code       =>x_rct_curr_code
338 );
339 
340 END Update_Row;
341 
342 
343 
344 PROCEDURE Select_Row (
345     X_APPLY_TO     				 IN OUT NOCOPY				 VARCHAR2,
346     X_TAX_BALANCE  				 IN OUT NOCOPY				 NUMBER,
347     X_CUSTOMER_TRX_LINE_ID				 IN OUT NOCOPY				 NUMBER,
348     X_COMMENTS     				 IN OUT NOCOPY				 VARCHAR2,
349     X_TAX          				 IN OUT NOCOPY				 NUMBER,
350     X_CASH_RECEIPT_ID				 IN OUT NOCOPY				 NUMBER,
351     X_ATTRIBUTE_CATEGORY				 IN OUT NOCOPY				 VARCHAR2,
352     X_ALLOCATED_RECEIPT_AMOUNT				 IN OUT NOCOPY				 NUMBER,
353     X_GROUP_ID     				 IN OUT NOCOPY				 NUMBER,
354     X_TAX_DISCOUNT 				 IN OUT NOCOPY				 NUMBER,
355     X_AMOUNT       				 IN OUT NOCOPY				 NUMBER,
356     X_LINE_DISCOUNT				 IN OUT NOCOPY				 NUMBER,
357     X_ATTRIBUTE9   				 IN OUT NOCOPY				 VARCHAR2,
358     X_ATTRIBUTE8   				 IN OUT NOCOPY				 VARCHAR2,
359     X_ATTRIBUTE7   				 IN OUT NOCOPY				 VARCHAR2,
360     X_ATTRIBUTE6   				 IN OUT NOCOPY				 VARCHAR2,
361     X_ATTRIBUTE5   				 IN OUT NOCOPY				 VARCHAR2,
362     X_ATTRIBUTE4   				 IN OUT NOCOPY				 VARCHAR2,
363     X_ATTRIBUTE3   				 IN OUT NOCOPY				 VARCHAR2,
364     X_ATTRIBUTE2   				 IN OUT NOCOPY				 VARCHAR2,
365     X_ATTRIBUTE1   				 IN OUT NOCOPY				 VARCHAR2,
366     X_LINE_BALANCE 				 IN OUT NOCOPY				 NUMBER,
367     X_ATTRIBUTE15  				 IN OUT NOCOPY				 VARCHAR2,
368     X_ATTRIBUTE14  				 IN OUT NOCOPY				 VARCHAR2,
369     X_ATTRIBUTE13  				 IN OUT NOCOPY				 VARCHAR2,
370     X_ATTRIBUTE12  				 IN OUT NOCOPY				 VARCHAR2,
371     X_ATTRIBUTE11  				 IN OUT NOCOPY				 VARCHAR2,
372     X_ATTRIBUTE10  				 IN OUT NOCOPY				 VARCHAR2
373 ) IS
374 
375 
376 BEGIN
377 
378     SELECT
379         NVL( APPLY_TO,FND_API.G_MISS_CHAR ),
380         NVL( TAX_BALANCE,FND_API.G_MISS_NUM ),
381         NVL( CUSTOMER_TRX_LINE_ID,FND_API.G_MISS_NUM ),
382         NVL( COMMENTS,FND_API.G_MISS_CHAR ),
383         NVL( TAX,FND_API.G_MISS_NUM ),
384         NVL( CASH_RECEIPT_ID,FND_API.G_MISS_NUM ),
385         NVL( ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR ),
386         NVL( ALLOCATED_RECEIPT_AMOUNT,FND_API.G_MISS_NUM ),
387         NVL( GROUP_ID,FND_API.G_MISS_NUM ),
388         NVL( TAX_DISCOUNT,FND_API.G_MISS_NUM ),
389         NVL( AMOUNT,FND_API.G_MISS_NUM ),
390         NVL( LINE_DISCOUNT,FND_API.G_MISS_NUM ),
391         NVL( ATTRIBUTE9,FND_API.G_MISS_CHAR ),
392         NVL( ATTRIBUTE8,FND_API.G_MISS_CHAR ),
393         NVL( ATTRIBUTE7,FND_API.G_MISS_CHAR ),
394         NVL( ATTRIBUTE6,FND_API.G_MISS_CHAR ),
395         NVL( ATTRIBUTE5,FND_API.G_MISS_CHAR ),
396         NVL( ATTRIBUTE4,FND_API.G_MISS_CHAR ),
397         NVL( ATTRIBUTE3,FND_API.G_MISS_CHAR ),
398         NVL( ATTRIBUTE2,FND_API.G_MISS_CHAR ),
399         NVL( ATTRIBUTE1,FND_API.G_MISS_CHAR ),
400         NVL( LINE_BALANCE,FND_API.G_MISS_NUM ),
401         NVL( ATTRIBUTE15,FND_API.G_MISS_CHAR ),
402         NVL( ATTRIBUTE14,FND_API.G_MISS_CHAR ),
403         NVL( ATTRIBUTE13,FND_API.G_MISS_CHAR ),
404         NVL( ATTRIBUTE12,FND_API.G_MISS_CHAR ),
405         NVL( ATTRIBUTE11,FND_API.G_MISS_CHAR ),
406         NVL( ATTRIBUTE10,FND_API.G_MISS_CHAR )
407         INTO
408         X_APPLY_TO,
409         X_TAX_BALANCE,
410         X_CUSTOMER_TRX_LINE_ID,
411         X_COMMENTS,
412         X_TAX,
413         X_CASH_RECEIPT_ID,
414         X_ATTRIBUTE_CATEGORY,
415         X_ALLOCATED_RECEIPT_AMOUNT,
416         X_GROUP_ID,
417         X_TAX_DISCOUNT,
418         X_AMOUNT,
419         X_LINE_DISCOUNT,
420         X_ATTRIBUTE9,
421         X_ATTRIBUTE8,
422         X_ATTRIBUTE7,
423         X_ATTRIBUTE6,
424         X_ATTRIBUTE5,
425         X_ATTRIBUTE4,
426         X_ATTRIBUTE3,
427         X_ATTRIBUTE2,
428         X_ATTRIBUTE1,
429         X_LINE_BALANCE,
430         X_ATTRIBUTE15,
431         X_ATTRIBUTE14,
432         X_ATTRIBUTE13,
433         X_ATTRIBUTE12,
434         X_ATTRIBUTE11,
435         X_ATTRIBUTE10
436         FROM AR_ACTIVITY_DETAILS
437     WHERE  1 = 1  AND CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
438          AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- BUG 7241111
439  AND CUSTOMER_TRX_LINE_ID = X_CUSTOMER_TRX_LINE_ID
440 ;
441 
442 
443 EXCEPTION
444     WHEN NO_DATA_FOUND THEN
445         FND_MESSAGE.SET_NAME( 'FUN', 'FUN_API_NO_RECORD' );
446         FND_MESSAGE.SET_TOKEN( 'RECORD', 'p_AR_ACTIVITY_DETAILS_rec');
447         FND_MESSAGE.SET_TOKEN( 'VALUE', '' );
448         FND_MSG_PUB.ADD;
449         RAISE FND_API.G_EXC_ERROR;
450 END Select_Row;
451 
452 
453 
454 
455 
456 END;