[Home] [Help]
PACKAGE BODY: APPS.PA_MC_RETN_INV_DETAIL_PKG
Source
1 PACKAGE BODY PA_MC_RETN_INV_DETAIL_PKG as
2 /* $Header: PAMCRIDB.pls 120.2 2005/08/26 11:28:39 skannoji noship $*/
3 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 PROCEDURE Process_RetnInvDetails(p_project_id IN NUMBER,
6 p_draft_invoice_num IN NUMBER,
7 p_action IN VARCHAR2,
8 p_request_id IN NUMBER) IS
9
10
11 /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
12 new table gl_alc_ledger_rships_v and corresponding columns */
13
14 /* CURSOR c_reporting_sob ( p_set_of_books_id IN NUMBER, in_org_id IN NUMBER) IS
15 SELECT reporting_set_of_books_id,
16 reporting_currency_code,
17 conversion_type
18 FROM gl_mc_reporting_options
19 WHERE primary_set_of_books_id = p_set_of_books_id
20 AND application_id = 275
21 AND org_id = NVL(in_org_id,-99)
22 AND enabled_flag = 'Y'; */
23
24
25 CURSOR c_reporting_sob ( p_set_of_books_id IN NUMBER, in_org_id IN NUMBER) IS
26 SELECT ledger_id reporting_set_of_books_id,
27 currency_code reporting_currency_code,
28 alc_default_conv_rate_type conversion_type
29 FROM gl_alc_ledger_rships_v
30 WHERE source_ledger_id = p_set_of_books_id
31 AND application_id = 275
32 AND (org_id = -99 OR org_id = in_org_id)
33 AND relationship_enabled_flag = 'Y';
34
35
36 currency VARCHAR2(30);
37 sob NUMBER;
38 l_org_id NUMBER;
39 l_program_id NUMBER:= fnd_global.conc_program_id;
40 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
41 l_program_update_date DATE := sysdate;
42 l_last_update_date DATE := sysdate;
43 l_last_updated_by NUMBER:= fnd_global.user_id;
44 l_created_by NUMBER:= fnd_global.user_id;
45 l_last_update_login NUMBER:= fnd_global.login_id;
46 l_invoice_date DATE;
47
48
49 BEGIN
50 IF g1_debug_mode = 'Y' THEN
51 pa_retention_util.write_log('Entering pa_mc_retn_inv_detail_pkg.Process_RetnInvDetails');
52 END IF;
53
54 IF NOT gl_mc_currency_pkg.G_PA_UPGRADE_MODE THEN
55
56 IF p_action ='INSERT' THEN /* Inserting new record */
57
58 -- pa_retention_util.write_log('Leaving pa_mc_retn_inv_detail_pkg.Process_RetnInvDetails');
59
60 SELECT p.org_id, p.projfunc_currency_code ,
61 imp.set_of_books_id sob,
62 di.invoice_date invoice_date
63 INTO l_org_id, currency, sob, l_invoice_date
64 FROM pa_projects_all p, pa_implementations imp,
65 pa_draft_invoices_all di
66 WHERE di.project_id = p.project_id
67 AND di.draft_invoice_num = p_draft_invoice_num
68 AND p.project_id = p_project_id
69 /* Shared services changes: removed NVL from the org_id join.*/
70 AND imp.org_id = p.org_id;
71
72 FOR v_rsob IN c_reporting_sob( sob,l_org_id) LOOP
73
74 DECLARE
75
76 l_temp_val NUMBER := 0;
77 l_err_code NUMBER := 0;
78 x_err_stack VARCHAR2(2000);
79 x_err_code NUMBER := 0;
80 x_err_stage VARCHAR2(2000);
81 l_result_code VARCHAR2(15);
82 l_exchange_rate NUMBER :=0;
83 l_x_exchange_rate NUMBER :=0;
84 l_denominator_rate NUMBER;
85 l_numerator_rate NUMBER;
86 l_exchange_rate_date DATE;
87 l_exchange_rate_type VARCHAR2(30);
88 l_report_amount NUMBER := 0;
89
90
91 BEGIN
92
93 l_exchange_rate_date := l_invoice_date;
94 l_exchange_rate_type := v_rsob.conversion_type;
95
96 IF g1_debug_mode = 'Y' THEN
97 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Primary SOB : ' || sob );
98 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Reporting SOB : ' || v_rsob.reporting_set_of_books_id );
99 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Completion date : ' || to_char(l_invoice_date) );
100 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Org Id : ' || l_org_id);
101 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Projfunc Currency: ' || currency );
102 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Exchange rate Type : ' ||l_exchange_rate_type );
103 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Exchange rate : ' ||l_exchange_rate );
104 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Exchange date : ' ||to_char(l_exchange_rate_date));
105 END IF;
106
107
108
109 gl_mc_currency_pkg.get_rate(p_primary_set_of_books_id => sob,
110 p_reporting_set_of_books_id => v_rsob.reporting_set_of_books_id,
111 p_trans_date => l_invoice_date,
112 p_trans_currency_code => currency,
113 p_trans_conversion_type => l_exchange_rate_type,
114 p_trans_conversion_date => l_exchange_rate_date,
115 p_trans_conversion_rate => l_exchange_rate,
116 p_application_id => 275,
117 p_org_id => l_org_id,
118 p_fa_book_type_code => NULL,
119 p_je_source_name => NULL,
120 p_je_category_name => NULL,
121 p_result_code => l_result_code,
122 p_denominator_rate => l_denominator_rate,
123 p_numerator_rate => l_numerator_rate);
124
125 IF g1_debug_mode = 'Y' THEN
126 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'After the Rate API ');
127 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Exchange rate Type : ' ||l_exchange_rate_type );
128 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Exchange rate : ' ||l_exchange_rate );
129 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Exchange date : ' ||to_char(l_exchange_rate_date));
130 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'p_draft_invoice_num : '|| p_draft_invoice_num);
131 END IF;
132
133 FOR InvDetRec IN (SELECT
134 retn_invoice_detail_id,
135 project_id,
136 draft_invoice_num,
137 line_num,
138 projfunc_currency_code,
139 projfunc_total_retained
140 FROM pa_retn_invoice_details
141 WHERE project_id = p_project_id
142 AND draft_invoice_num = p_draft_invoice_num)
143 LOOP
144
145 l_report_amount := 0;
146
147 l_report_amount := pa_mc_currency_pkg.CurrRound((
148 (InvDetRec.projfunc_total_retained/ l_denominator_rate)*
149 l_numerator_rate),v_rsob.reporting_currency_code);
150
151 IF g1_debug_mode = 'Y' THEN
152 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Before inserting into pa_mc_retn_inv_details');
153 pa_retention_util.write_log('Process_RetnInvDetails: ' || '------------------------------');
154 END IF;
155
156 /* Bug 2976939: Added set_of_books_id check in the select below.Since this check was not there
157 only for one RSOB record will be inserted. */
158
159 BEGIN
160 NULL;
161 END;
162 IF g1_debug_mode = 'Y' THEN
163 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'After inserting into pa_mc_retn_inv_details');
164 END IF;
165
166 END LOOP; -- End of invoice loop
167
168 END;
169
170 END LOOP; -- End of Reporting set of books
171
172 ELSIF p_action='DELETE' THEN
173
174 IF g1_debug_mode = 'Y' THEN
175 pa_retention_util.write_log('Process_RetnInvDetails: ' || 'Delete Invoice Details');
176 END IF;
177 END IF;
178
179 END IF; /* Not MRC Upgrade */
180 IF g1_debug_mode = 'Y' THEN
181 pa_retention_util.write_log('Leaving pa_mc_retn_inv_detail_pkg.Process_RetnInvDetails');
182 END IF;
183
184 END Process_RetnInvDetails;
185
186 END PA_MC_RETN_INV_DETAIL_PKG;