[Home] [Help]
PACKAGE BODY: APPS.PSA_FA_MASS_ADDITIONS
Source
1 PACKAGE BODY PSA_FA_MASS_ADDITIONS AS
2 /* $Header: PSAFAUCB.pls 120.4.12020000.3 2013/02/12 07:11:06 yanasing ship $ */
3
4 --===========================FND_LOG.START=====================================
5 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
8 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
10 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAFAUCB.PSA_FA_MASS_ADDITIONS.';
12 --===========================FND_LOG.END=======================================
13
14 PROCEDURE update_asset_type
15 (err_buf OUT NOCOPY VARCHAR2,
16 ret_code OUT NOCOPY VARCHAR2,
17 p_ledger_id IN NUMBER,
18 p_chart_of_accounts IN NUMBER,
19 p_asset_book IN VARCHAR2,
20 p_capital_acct_from IN VARCHAR2,
21 p_capital_acct_to IN VARCHAR2,
22 p_cip_acct_from IN VARCHAR2,
23 p_cip_acct_to IN VARCHAR2) IS
24
25 p_where_clause VARCHAR2(3000);
26 p_mass_add_query VARCHAR2(4000);
27 p_mass_add_capital_stmt VARCHAR2(4000);
28 p_mass_add_cip_stmt VARCHAR2(4000);
29 l_mass_addition_id NUMBER;
30
31 TYPE var_cur IS REF CURSOR;
32 mass_add_cur VAR_CUR;
33 -- ========================= FND LOG ===========================
34 l_full_path VARCHAR2(100) := g_path || 'update_asset_type';
35 -- ========================= FND LOG ===========================
36
37 BEGIN
38 PRINT_HEADER_INFO (p_asset_book, p_capital_acct_from, p_capital_acct_to, p_cip_acct_from, p_cip_acct_to);
39
40 p_where_clause := fa_rx_flex_pkg.flex_sql
41 (101, 'GL#', p_chart_of_accounts, 'CC', 'WHERE', 'ALL','BETWEEN', p_capital_acct_from, p_capital_acct_to);
42
43 p_mass_add_query := 'SELECT DISTINCT mass_addition_id
44 FROM fa_mass_additions fma, gl_code_combinations cc
45 WHERE fma.payables_code_combination_id = cc.code_combination_id
46 AND fma.asset_type = '''||'EXPENSED'||''''||
47 ' AND fma.posting_status IN ('''||'NEW'||''','''||'MERGED'||''''||')'||
48 ' AND book_type_code = :p_asset_book';
49
50 p_mass_add_capital_stmt := p_mass_add_query || ' AND ' || p_where_clause;
51
52 -- ========================= FND LOG ===========================
53 psa_utils.debug_other_string(g_state_level,
54 l_full_path,
55 'Select statement used for fetching mass additions (Capitalized)');
56 psa_utils.debug_other_string(g_state_level,l_full_path,p_mass_add_capital_stmt);
57 -- ========================= FND LOG ===========================
58
59 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Updating asset type for mass additions (Capitalized)');
60 PRINT_REPORT_HEADER;
61
62 OPEN mass_add_cur FOR p_mass_add_capital_stmt USING p_asset_book;
63 LOOP
64 FETCH mass_add_cur INTO l_mass_addition_id;
65 EXIT WHEN mass_add_cur%NOTFOUND;
66
67 UPDATE fa_mass_additions
68 SET asset_type = 'CAPITALIZED',
69 depreciate_flag = 'YES'
70 WHERE mass_addition_id = l_mass_addition_id;
71
72 PRINT_MASS_ADDITION_DETAILS (l_mass_addition_id);
73
74 END LOOP;
75
76 CLOSE mass_add_cur;
77
78 p_where_clause := fa_rx_flex_pkg.flex_sql
79 (101, 'GL#', p_chart_of_accounts,
80 'CC', 'WHERE', 'ALL','BETWEEN', p_cip_acct_from, p_cip_acct_to);
81
82 p_mass_add_cip_stmt := p_mass_add_query || ' AND ' || p_where_clause;
83
84 FND_FILE.NEW_LINE (FND_FILE.LOG, 1);
85 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
86 -- ========================= FND LOG ===========================
87 psa_utils.debug_other_string(g_state_level,
88 l_full_path,
89 'Select statement used for fetching mass additions (CIP)');
90 psa_utils.debug_other_string(g_state_level,l_full_path, p_mass_add_cip_stmt);
91 -- ========================= FND LOG ===========================
92
93 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
94 'Updating asset type for mass additions (CIP)');
95 PRINT_REPORT_HEADER;
96
97 OPEN mass_add_cur FOR p_mass_add_cip_stmt USING p_asset_book;
98 LOOP
99 FETCH mass_add_cur INTO l_mass_addition_id;
100 EXIT WHEN mass_add_cur%NOTFOUND;
101
102 UPDATE fa_mass_additions
103 SET asset_type = 'CIP'
104 WHERE mass_addition_id = l_mass_addition_id;
105
106 PRINT_MASS_ADDITION_DETAILS (l_mass_addition_id);
107
108 END LOOP;
109
110 CLOSE mass_add_cur;
111
112 COMMIT;
113
114 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Complete ...');
115
116 EXCEPTION
117 WHEN FND_FILE.UTL_FILE_ERROR THEN
118 -- Need not error out of the conc pgm just because of file i/o error.
119 -- File creation errors are recorded in the log file.
120 NULL;
121
122 END update_asset_type;
123
124 PROCEDURE print_header_info (p_asset_book IN VARCHAR2,
125 p_capital_acct_from IN VARCHAR2,
126 p_capital_acct_to IN VARCHAR2,
127 p_cip_acct_from IN VARCHAR2,
128 p_cip_acct_to IN VARCHAR2) IS
129
130 BEGIN
131 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'PROGRAM - UPDATE ASSET TYPE');
132
133 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
134 'Asset Book : '|| p_asset_book );
135
136 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
137 'Capitalize Account From : '|| p_capital_acct_from );
138
139 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
140 'Capitalize Account To : '|| p_capital_acct_to );
141
142 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
143 'CIP Account From : '|| p_cip_acct_from );
144
145 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
146 'CIP Account To : '|| p_cip_acct_to );
147 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
148
149 EXCEPTION
150 WHEN FND_FILE.UTL_FILE_ERROR THEN
151 -- Need not error out of the conc pgm just because of file i/o error.
152 -- File creation errors are recorded in the log file.
153 NULL;
154
155 END print_header_info;
156
157 PROCEDURE print_report_header IS
158
159 BEGIN
160
161 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
162 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD ('Mass Addition ID', 17) ||
163 RPAD ('Invoice Number', 27) ||
164 RPAD ('Amount', 22) || 'Description' );
165
166 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD ('----------------', 17) ||
167 RPAD ('--------------', 27) ||
168 RPAD ('------', 22) || '-----------' );
169 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
170
171 END print_report_header;
172
173 PROCEDURE print_mass_addition_details (p_mass_addition_id IN NUMBER) IS
174
175 CURSOR c_invoice_details (c_mass_addition_id NUMBER) IS
176 SELECT mass_addition_id, invoice_number, payables_cost, description
177 FROM fa_mass_additions
178 WHERE mass_addition_id = c_mass_addition_id;
179
180 l_mass_addition_details c_invoice_details%rowtype;
181 BEGIN
182 OPEN c_invoice_details (p_mass_addition_id);
183 FETCH c_invoice_details
184 INTO l_mass_addition_details;
185 CLOSE c_invoice_details;
186
187 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
188 RPAD (l_mass_addition_details.mass_addition_id, 17) ||
189 RPAD (l_mass_addition_details.invoice_number, 27) ||
190 RPAD (l_mass_addition_details.payables_cost, 22) ||
191 l_mass_addition_details.description );
192
193
194 END print_mass_addition_details;
195
196
197 END PSA_FA_MASS_ADDITIONS;