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