[Home] [Help]
PACKAGE BODY: APPS.JL_CO_FA_TA_VALIDATE_PKG
Source
1 PACKAGE BODY jl_co_fa_ta_validate_pkg AS
2 /* $Header: jlcoftvb.pls 120.3 2006/09/20 17:54:29 abuissa ship $ */
3 ----------------------------------------------------------------------------
4 -- PROCEDURE --
5 -- validate_status --
6 -- --
7 -- DESCRIPTION --
8 -- Use this procedure to validate technical appraisals --
9 -- --
10 -- PURPOSE: --
11 -- Oracle Applications Rel 11.0 --
12 -- --
13 -- PARAMETERS: --
14 -- p_appraisal_id - Appraisal identification number --
15 -- --
16 -- HISTORY: --
17 -- 07/15/98 Sujit Dalai Created --
18 -- 09/27/98 Sujit Dalai Changed the output printed to the log to --
19 -- provide a users with a more helpful text.--
20 -- 10/21/98 Sujit Dalai Changed the messages --
21 ----------------------------------------------------------------------------
22
23 PROCEDURE validate_status ( ERRBUF OUT NOCOPY VARCHAR2,
24 RETCODE OUT NOCOPY VARCHAR2,
25 p_appraisal_id IN NUMBER
26 ) IS
27
28 CURSOR c_appraisals IS
29 SELECT apr.appraisal_id,
30 apr.fiscal_year,
31 cur.currency_code
32 FROM jl_co_fa_appraisals apr,
33 fnd_currencies_active_v cur
34 WHERE apr.appraisal_id = nvl (p_appraisal_id, apr.appraisal_id)
35 AND apr.currency_code = cur.currency_code(+)
36 AND appraisal_status NOT IN ('V','P')
37 FOR UPDATE OF appraisal_status;
38
39 CURSOR c_assets(p_appr_id NUMBER) IS
40 SELECT ad.asset_number,
41 a.asset_number appr_asset_number,
42 a.appraisal_value,
43 a.status
44 FROM jl_co_fa_asset_apprs a,
45 fa_additions ad
46 WHERE a.asset_number = ad.asset_number(+)
47 AND a.appraisal_id = p_appr_id
48 AND NVL(a.status, 'E') <> 'V'
49 FOR UPDATE OF status;
50
51 x_count1 NUMBER;
52 x_status VARCHAR2(1) := 'V';
53 x_appraisal_status VARCHAR2(1) := 'V';
54 err_num NUMBER;
55 err_msg VARCHAR2(200);
56
57 BEGIN
58
59 fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
60 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
61 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
62 fnd_message.set_name('JL', 'JL_CO_FA_APPR_NUMBER');
63 fnd_message.set_token('APPRAISAL_NUMBER', p_appraisal_id);
64 fnd_file.put_line( 1, fnd_message.get);
65 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
66
67 FOR rec_appraisal IN c_appraisals LOOP
68
69 fnd_message.set_name('JL', 'JL_CO_FA_APPR_MESG');
70 fnd_message.set_token('APPRAISAL_NUMBER', rec_appraisal.appraisal_id);
71 fnd_file.put_line( 1, fnd_message.get);
72 /*Checking for the Fiscal Year */
73
74 IF rec_appraisal.fiscal_year < 1990 THEN
75 x_appraisal_status := 'F';
76 fnd_message.set_name('JL', 'JL_CO_FA_LESS_THAN_1990');
77 fnd_message.set_token('APPRAISAL_NUMBER', rec_appraisal.appraisal_id);
78 fnd_file.put_line( fnd_file.LOG, fnd_message.get);
79 END IF;
80
81 /* Checking for valid currency Code */
82
83 IF rec_appraisal.currency_code IS NULL THEN
84
85 IF x_appraisal_status = 'V' THEN
86 x_appraisal_status := 'C';
87 END IF;
88
89 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_CURRENCY_CODE');
90 fnd_message.set_token('APPRAISAL_NUMBER', rec_appraisal.appraisal_id);
91 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
92 END IF;
93
94 FOR rec_asset IN c_assets(rec_appraisal.appraisal_id) LOOP
95
96 /* Checking for valid asset number */
97
98 IF rec_asset.asset_number IS NULL THEN
99 x_status := 'A';
100
101 IF x_appraisal_status = 'V' THEN
102 x_appraisal_status := 'R';
103 END IF;
104
105 fnd_message.set_name('JL', 'JL_CO_FA_ASSET_NOT_FOUND');
106 fnd_message.set_token('ASSET_NUMBER', rec_asset.appr_asset_number);
107 fnd_message.set_token('APPRAISAL_NUMBER', rec_appraisal.appraisal_id);
108 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
109 END IF;
110
111 /* Checking for appraisal value */
112
113 IF (rec_asset.appraisal_value < 0) THEN
114 IF x_status = 'V' THEN
115 x_status := 'N';
116 END IF;
117
118 IF x_appraisal_status = 'V' THEN
119 x_appraisal_status := 'R';
120 END IF;
121 fnd_message.set_name('JL', 'JL_CO_FA_NEGATIVE_VALUE');
122 fnd_message.set_token('APPRAISAL_NUMBER', rec_appraisal.appraisal_id);
123 fnd_message.set_token('ASSET_NUMBER', rec_asset.appr_asset_number);
124 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
125 END IF;
126
127
128 UPDATE jl_co_fa_asset_apprs
129 SET status = x_status
130 WHERE current of c_assets;
131
132 x_status := 'V';
133
134 END LOOP;
135
136
137 UPDATE jl_co_fa_appraisals
138 SET appraisal_status = x_appraisal_status
139 WHERE current of c_appraisals;
140
141 x_appraisal_status := 'V';
142 fnd_file.put_line(FND_FILE.LOG, ' ');
143 END LOOP;
144
145 COMMIT WORK;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
150 fnd_file.put_line( 1, fnd_message.get);
151 err_num := SQLCODE;
152 err_msg := substr(SQLERRM, 1, 200);
153 RAISE_APPLICATION_ERROR( err_num, err_msg);
154
155 END validate_status;
156 END jl_co_fa_ta_validate_pkg;