[Home] [Help]
PACKAGE BODY: APPS.PSA_FA_INVOICE_DISTRIBUTIONS
Source
1 PACKAGE BODY PSA_FA_INVOICE_DISTRIBUTIONS AS
2 /* $Header: PSAFATAB.pls 120.5.12010000.2 2009/04/17 05:33:44 gnrajago 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.PSAFATAB.PSA_FA_INVOICE_DISTRIBUTIONS.';
12 --===========================FND_LOG.END=======================================
13
14 PROCEDURE update_assets_tracking_flag
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_from_gl_date IN VARCHAR2,
20 p_to_gl_date IN VARCHAR2,
21 p_from_account IN VARCHAR2,
22 p_to_account IN VARCHAR2) IS
23
24 p_where_clause VARCHAR2(3000);
25 p_inv_dist VARCHAR2(4000);
26 p_prev_inv NUMBER := 0;
27
28 l_from_gl_date DATE;
29 l_to_gl_date DATE;
30 l_invoice_id ap_invoice_distributions_all.invoice_id%type;
31 l_inv_dist_id ap_invoice_distributions_all.invoice_distribution_id%type;
32 l_dist_line_num ap_invoice_distributions_all.distribution_line_number%type;
33
34 TYPE var_cur IS REF CURSOR;
35 inv_dist_cur VAR_CUR;
36 -- ========================= FND LOG ===========================
37 l_full_path VARCHAR2(100) := g_path || 'update_assets_tracking_flag';
38 -- ========================= FND LOG ===========================
39
40 BEGIN
41 l_from_gl_date := to_date(substr(p_from_gl_date,1,10),'YYYY/MM/DD');
42 l_to_gl_date := to_date(substr(p_to_gl_date,1,10) ,'YYYY/MM/DD');
43
44 p_where_clause := fa_rx_flex_pkg.flex_sql
45 (101, 'GL#', p_chart_of_accounts, 'CC', 'WHERE', 'ALL','BETWEEN', p_from_account, p_to_account);
46
47 p_inv_dist := 'SELECT invoice_id, distribution_line_number, invoice_distribution_id
48 FROM ap_invoice_distributions ap_inv_dist, gl_code_combinations cc
49 WHERE ap_inv_dist.dist_code_combination_id = cc.code_combination_id
50 AND posted_flag = '''||'Y'||''''||
51 'AND assets_addition_flag = '''||'U'||''''||
52 'AND assets_tracking_flag != '''||'Y'||''''||
53 'AND accounting_date BETWEEN :from_gl_date and :to_gl_date';
54
55 IF l_from_gl_date IS NULL THEN
56
57 p_inv_dist := 'SELECT invoice_id, distribution_line_number, invoice_distribution_id
58 FROM ap_invoice_distributions ap_inv_dist, gl_code_combinations cc
59 WHERE ap_inv_dist.dist_code_combination_id = cc.code_combination_id
60 AND posted_flag = '''||'Y'||''''||
61 'AND assets_addition_flag = '''||'U'||''''||
62 'AND assets_tracking_flag != '''||'Y'||''''||
63 'AND accounting_date <= :to_gl_date';
64
65 END IF;
66
67 p_inv_dist := p_inv_dist||' AND '||p_where_clause||' ORDER BY invoice_id';
68
69 -- ========================= FND LOG ===========================
70 psa_utils.debug_other_string(g_state_level,
71 l_full_path,
72 'Select statement used for fetching invoice distributions');
73 psa_utils.debug_other_string(g_state_level,l_full_path,p_inv_dist);
74 -- ========================= FND LOG ===========================
75
76 PRINT_HEADER_INFO (l_from_gl_date, l_to_gl_date, p_from_account, p_to_account);
77
78 IF p_from_gl_date IS NOT NULL THEN
79 OPEN inv_dist_cur FOR p_inv_dist USING l_from_gl_date, l_to_gl_date;
80 ELSE
81 OPEN inv_dist_cur FOR p_inv_dist USING l_to_gl_date;
82 END IF;
83
84 LOOP
85 FETCH inv_dist_cur INTO l_invoice_id, l_dist_line_num, l_inv_dist_id;
86 EXIT WHEN inv_dist_cur%NOTFOUND;
87
88 IF p_prev_inv = 0 THEN
89 p_prev_inv := l_invoice_id;
90 END IF;
91
92 IF p_prev_inv <> l_invoice_id THEN
93 PRINT_INVOICE_DETAILS(p_prev_inv);
94 p_prev_inv := l_invoice_id;
95 END IF;
96
97 UPDATE ap_invoice_distributions
98 SET assets_tracking_flag = 'Y'
99 WHERE invoice_distribution_id = l_inv_dist_id;
100
101 END LOOP;
102
103 PRINT_INVOICE_DETAILS(l_invoice_id);
104
105 CLOSE inv_dist_cur;
106
107 COMMIT;
108
109 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
110 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'update_assets_tracking_flag: Processing Complete ...');
111
112 EXCEPTION
113 WHEN FND_FILE.UTL_FILE_ERROR THEN
114 -- Need not error out of the conc pgm just because of file i/o error.
115 -- File creation errors are recorded in the log file.
116 NULL;
117
118 END update_assets_tracking_flag;
119
120 PROCEDURE print_header_info
121 (p_from_gl_date IN DATE, p_to_gl_date IN DATE, p_from_account IN VARCHAR2, p_to_account IN VARCHAR2) IS
122
123 BEGIN
124 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'print_header_info: PROGRAM - UPDATE ASSETS TRACKING FLAG');
125
126 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'GL Date From : '|| p_from_gl_date );
127
128 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'GL Date To : '|| p_to_gl_date );
129
130 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Account From : '|| p_from_account );
131
132 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Account To : '|| p_to_account );
133 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
134
135 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Updating assets tracking flag for invoice distributions ...');
136 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
137 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD ('Supplier', 32) ||
138 RPAD ('Invoice ID', 17) ||
139 RPAD ('Invoice Number', 27) ||
140 RPAD ('Line', 6) ||
141 RPAD ('Amount', 22) || 'Description' );
142
143 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD ('--------', 32) ||
144 RPAD ('----------', 17) ||
145 RPAD ('--------------', 27) ||
146 RPAD ('----', 6) ||
147 RPAD ('------', 22) || '-----------' );
148
149 FND_FILE.NEW_LINE (FND_FILE.OUTPUT, 1);
150
151 EXCEPTION
152 WHEN FND_FILE.UTL_FILE_ERROR THEN
153 -- Need not error out NOCOPY of the conc pgm just because of file i/o error.
154 -- File creation errors are recorded in the log file.
155 NULL;
156
157 END print_header_info;
158
159 PROCEDURE print_invoice_details (p_invoice_id IN NUMBER) IS
160
161 CURSOR c_invoice_details (c_invoice_id NUMBER) IS
162 SELECT po_ven.vendor_name supplier,
163 ap_inv.invoice_id invoice_id,
164 ap_inv.invoice_num invoice_number,
165 ap_inv_line.line_number line_number,
166 ap_inv_line.description line_description,
167 ap_inv_line.amount line_amount
168 FROM ap_invoices_all ap_inv,
169 ap_invoice_lines_all ap_inv_line,
170 po_vendors po_ven
171 WHERE ap_inv.invoice_id = c_invoice_id
172 AND ap_inv.invoice_id = ap_inv_line.invoice_id
173 AND ap_inv.vendor_id = po_ven.vendor_id;
174
175 l_invoice_details c_invoice_details%rowtype;
176 BEGIN
177 OPEN c_invoice_details (p_invoice_id);
178 LOOP
179 FETCH c_invoice_details INTO l_invoice_details;
180 EXIT WHEN c_invoice_details%NOTFOUND;
181
182 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'print_invoice_details: ' || RPAD (l_invoice_details.supplier, 32) ||
183 RPAD (l_invoice_details.invoice_id, 17) ||
184 RPAD (l_invoice_details.invoice_number, 27) ||
185 RPAD (l_invoice_details.line_number, 6) ||
186 RPAD (l_invoice_details.line_amount, 22) || l_invoice_details.line_description);
187
188 END LOOP;
189 CLOSE c_invoice_details;
190
191 END print_invoice_details;
192
193 END PSA_FA_INVOICE_DISTRIBUTIONS;