1 PACKAGE jai_rcv_excise_processing_pkg AUTHID CURRENT_USER AS
2 /* $Header: jai_rcv_exc_prc.pls 120.8 2011/06/23 03:47:16 wenzhou ship $ */
3
4 /*
5 OPEN ISSUES:
6 1) Partial Delivery of CGIN items to NonBonded Delivery then what should we do the second claim of RECEIVE
7 */
8
9 /* --------------------------------------------------------------------------------------
10 Filename:
11
12 Change History:
13
14 Date Bug Remarks
15 --------- ---------- -------------------------------------------------------------
16 08-Jun-2005 Version 116.1 jai_rcv_exc_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
17 as required for CASE COMPLAINCE.
18
19 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.2
20 GL Sources and GL Categories got changed. Refer bug for the details
21
22
23 30/10/2006 SACSETHI for bug 5228046, File version 120.2
24 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
25 This bug has datamodel and spec changes.
26
27 16/04/2007 Vkarnaam for bug #5989740 File version 120.3
28 Forward Porting the changes in 115 bug 5907436(Enh:handling Secondary and Higher Education Cess).
29
30 18/11/2008 vumaasha
31 Forward porting the changes in the 115bug 4545776
32 Changed the cursor c_source_orgn_loc to fetch shipment_num (
33 rcv_shipment_headers )
34 or delivery_id ( wsh_new_deliveries ) based on whether delivery_id is
35 number or character value respectively.
36 14-aug-2009 vkaranam for bug#4750798
37 fwdported the changes done in 115 bug 4619176 /7229349
38 I havent considered shcess changes in 7229349 fix as all shcess changes are not fped.
39 15. 07-jul-2010 vkaranam for bug#9854329
40 ISSUE: ADDITIONAL CVD IS NOT HITTING ANY DUTY BOOK AT THE TIME OF MAKING RTV.
41 FIX: added a new column CENVAT_CR_REVRSL_FLAG in the table ja_in_hr_organization_units.
42 Depending on the value of this column, it would be decided to reverse the CVD, additional CVD, CVD cess and CVD SH cess
43 type of taxes when we do a RTV. If the flag is 'Y', then the CVD, additional CVD, CVD cess and CVD SH cess type of taxes
44 would get reversed. But if it is 'N', then these taxes neednot be reversed.
45 Added the code for the above changes.
46 --------------------------------------------------------------------------------------
47 */
48
49 lb_rg_debug CONSTANT BOOLEAN := true;
50 gn_cenvat_rnd CONSTANT NUMBER := 0;
51
52 gv_source_name CONSTANT VARCHAR2(25) := 'Purchasing India';
53 gv_category_name CONSTANT VARCHAR2(25) := 'Receiving India';
54
55
56 CENVAT_CREDIT CONSTANT VARCHAR2(2) := 'Cr';
57 CENVAT_DEBIT CONSTANT VARCHAR2(2) := 'Dr';
58 SECOND_50PTG_CLAIM CONSTANT VARCHAR2(15) := '2nd 50% Claim';
59
60 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
61 CGIN_FIRST_CLAIM CONSTANT VARCHAR2(10) := '1st Claim';
62 CGIN_SECOND_CLAIM CONSTANT VARCHAR2(10) := '2nd Claim';
63
64 TYPE tax_breakup IS RECORD(
65 basic_excise NUMBER := 0,
66 addl_excise NUMBER := 0,
67 other_excise NUMBER := 0,
68 cvd NUMBER := 0,
69 non_cenvat NUMBER := 0,
70 excise_edu_cess NUMBER := 0,
71 cvd_edu_cess NUMBER := 0,
72 addl_cvd NUMBER := 0, -- Date 30/10/2006 Bug 5228046 added by SACSETHI
73 /*added the following by vkaranam for budget 07 impact - bug#5907436*/
74 sh_exc_edu_cess NUMBER := 0,
75 sh_cvd_edu_cess NUMBER := 0,
76 --Added by Wenqiong for the bug12645490 on 22/06/2011 begin.
77 any_other_boe_1 NUMBER := 0,
78 any_other_boe_2 NUMBER := 0,
79 any_other_boe_3 NUMBER := 0,
80 any_other_boe_4 NUMBER := 0,
81 any_other_boe_5 NUMBER := 0
82 --Added by Wenqiong for the bug12645490 on 22/06/2011 end.
83 );
84
85 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
86 SELECT *
87 FROM JAI_RCV_TRANSACTIONS
88 WHERE transaction_id = cp_transaction_id;
89
90 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
91 SELECT shipment_header_id, requisition_line_id, primary_quantity, uom_code, unit_of_measure,
92 po_header_id, po_line_id, po_line_location_id, transaction_id, subinventory,
93 vendor_id, vendor_site_id, customer_id, customer_site_id, oe_order_line_id,
94 transaction_type, destination_type_code, source_document_code, quantity
95 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ,attribute_category attr_cat, attribute1, attribute2, attribute3, attribute4, attribute5 rma_type
96 FROM rcv_transactions
97 WHERE transaction_id = cp_transaction_id;
98
99 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
100 CURSOR c_jai_receipt_line(cp_shipment_line_id IN NUMBER) IS
101 select rma_type
102 from JAI_RCV_LINES
103 where shipment_line_id = cp_shipment_line_id;
104
105 CURSOR c_orgn_info(cp_organization_id IN NUMBER, cp_location_id IN NUMBER) IS
106 SELECT modvat_rm_account_id, modvat_cg_account_id, modvat_pla_account_id, cenvat_rcvble_account,
107 excise_in_rg23d, excise_23d_account, excise_rcvble_account,
108 ssi_unit_flag, pref_rg23a, pref_rg23c, pref_pla, nvl(ssi_unit_flag, 'N') allow_negative_pla,
109 excise_edu_cess_rm_account, excise_edu_cess_cg_account, excise_edu_cess_rcvble_accnt -- Vijay Shankar for Bug#3940588
110 , cess_paid_payable_account_id -- Vijay Shankar for Bug#4211045
111 , rtv_account_flag, rtv_expense_account_id ,-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.. SSI Func.
112 /*added the following columns by vkaranam for budget 07 impact - bug#5989740*/
113 sh_cess_cg_account_id,sh_cess_paid_payable_acct_id,sh_cess_rcvble_acct_id,sh_cess_rm_account,sh_cess_rnd_account_id,
114 CENVAT_CR_REVRSL_FLAG --added for bug#9854329
115 FROM JAI_CMN_INVENTORY_ORGS
116 WHERE organization_id = cp_organization_id
117 AND location_id = cp_location_id;
118
119 CURSOR c_rcv_params(cp_organization_id IN NUMBER) IS
120 SELECT receiving_account_id
121 FROM rcv_parameters
122 WHERE organization_id = cp_organization_id;
123
124
125 /*
126 || vumaasha bug#5749963. Added decode statement for shipment_num.
127 */
128
129
130 CURSOR c_source_orgn_loc(cp_shipment_hdr_id IN NUMBER, cp_req_line_id IN NUMBER) IS
131 SELECT organization_id, location_id
132 FROM JAI_OM_WSH_LINES_ALL
133 WHERE delivery_id = ( SELECT
134 decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,rsh.shipment_num,
135 (select delivery_id
136 from wsh_new_deliveries
137 where name=rsh.shipment_num
138 )
139
140 )
141 from rcv_shipment_headers rsh
142 where shipment_header_id = cp_shipment_hdr_id
143 )
144 AND order_line_id IN ( select line_id
145 from oe_order_lines_all
146 where source_document_type_id = 10 -- 10 corresponds to 'Internal' document type
147 and source_document_line_id = cp_req_line_id
148 );
149
150 --start additions for bug 4750798
151 PROCEDURE get_excise_tax_rounding_factor(
152 p_transaction_id IN NUMBER,
153 p_Excise_rf OUT NOCOPY NUMBER,
154 p_Excise_edu_cess_rf OUT NOCOPY NUMBER,
155 p_Excise_she_cess_rf OUT NOCOPY NUMBER
156 );
157
158 --end bug 4750798
159
160
161 PROCEDURE do_cenvat_rounding(
162 p_transaction_id IN NUMBER,
163 pr_tax IN OUT NOCOPY TAX_BREAKUP,
164 p_codepath IN OUT NOCOPY VARCHAR2
165 );
166
167 PROCEDURE process_transaction(
168 p_transaction_id IN NUMBER,
169 p_cenvat_claimed_ptg IN OUT NOCOPY VARCHAR2,
170 p_process_status OUT NOCOPY VARCHAR2,
171 p_process_message OUT NOCOPY VARCHAR2,
172 p_simulate_flag IN VARCHAR2,
173 p_codepath IN OUT NOCOPY VARCHAR2,
174 -- following parameters introduced for second claim of receive transaction
175 p_process_special_reason IN VARCHAR2 DEFAULT NULL,
176 p_process_special_qty IN NUMBER DEFAULT NULL
177 );
178
179 PROCEDURE rg_i_entry(
180 p_transaction_id IN NUMBER,
181 pr_tax IN TAX_BREAKUP,
182 p_register_entry_type IN VARCHAR2,
183 p_register_id OUT NOCOPY NUMBER,
184 p_process_status OUT NOCOPY VARCHAR2,
185 p_process_message OUT NOCOPY VARCHAR2,
186 p_simulate_flag IN VARCHAR2,
187 p_codepath IN OUT NOCOPY VARCHAR2
188 );
189
190 PROCEDURE rg23_part_i_entry(
191 p_transaction_id IN NUMBER,
192 pr_tax IN TAX_BREAKUP,
193 p_register_entry_type IN VARCHAR2,
194 p_register_id OUT NOCOPY NUMBER,
195 p_process_status OUT NOCOPY VARCHAR2,
196 p_process_message OUT NOCOPY VARCHAR2,
197 p_simulate_flag IN VARCHAR2,
198 p_codepath IN OUT NOCOPY VARCHAR2
199 );
200
201 PROCEDURE rg23_d_entry(
202 p_transaction_id IN NUMBER,
203 pr_tax IN TAX_BREAKUP,
204 p_register_entry_type IN VARCHAR2,
205 p_register_id OUT NOCOPY NUMBER,
206 p_process_status OUT NOCOPY VARCHAR2,
207 p_process_message OUT NOCOPY VARCHAR2,
208 p_simulate_flag IN VARCHAR2,
209 p_codepath IN OUT NOCOPY VARCHAR2
210 );
211
212 PROCEDURE rg23_part_ii_entry(
213 p_transaction_id IN NUMBER,
214 pr_tax IN TAX_BREAKUP,
215 p_part_i_register_id IN NUMBER,
216 p_register_entry_type IN VARCHAR2,
217 p_reference_num IN VARCHAR2,
218 p_register_id OUT NOCOPY NUMBER,
219 p_process_status OUT NOCOPY VARCHAR2,
220 p_process_message OUT NOCOPY VARCHAR2,
221 p_simulate_flag IN VARCHAR2,
222 p_codepath IN OUT NOCOPY VARCHAR2
223 );
224
225 PROCEDURE pla_entry(
226 p_transaction_id IN NUMBER,
227 pr_tax IN TAX_BREAKUP,
228 p_register_entry_type IN VARCHAR2,
229 p_register_id OUT NOCOPY NUMBER,
230 p_process_status OUT NOCOPY VARCHAR2,
231 p_process_message OUT NOCOPY VARCHAR2,
232 p_simulate_flag IN VARCHAR2,
233 p_codepath IN OUT NOCOPY VARCHAR2
234 );
235
236 PROCEDURE accounting_entries(
237 p_transaction_id IN NUMBER,
238 pr_tax IN TAX_BREAKUP,
239 p_cgin_code IN VARCHAR2,
240 p_cenvat_accounting_type IN VARCHAR2,
241 p_amount_register IN VARCHAR2,
242 p_cenvat_account_id OUT NOCOPY NUMBER,
243 p_process_status OUT NOCOPY VARCHAR2,
244 p_process_message OUT NOCOPY VARCHAR2,
245 p_simulate_flag IN VARCHAR2,
246 p_codepath IN OUT NOCOPY VARCHAR2
247 , pv_retro_reference IN VARCHAR2 DEFAULT NULL --Added by Eric on Jan 18,2008 for retro
248 );
249
250 PROCEDURE derive_cgin_scenario(
251 p_transaction_id IN NUMBER,
252 p_cgin_code OUT NOCOPY VARCHAR2,
253 p_process_status OUT NOCOPY VARCHAR2,
254 p_process_message OUT NOCOPY VARCHAR2,
255 p_codepath IN OUT NOCOPY VARCHAR2
256 );
257
258 PROCEDURE update_registers(
259 p_quantity_register_id IN NUMBER,
260 p_quantity_register IN VARCHAR2,
261 p_payment_register_id IN NUMBER,
262 p_payment_register IN VARCHAR2,
263 p_charge_account_id IN NUMBER,
264 p_process_status OUT NOCOPY VARCHAR2,
265 p_process_message OUT NOCOPY VARCHAR2,
266 p_simulate_flag IN VARCHAR2,
267 p_codepath IN OUT NOCOPY VARCHAR2
268 );
269
270 PROCEDURE validate_transaction(
271 p_transaction_id IN NUMBER,
272 p_validation_type IN VARCHAR2,
273 p_process_status OUT NOCOPY VARCHAR2,
274 p_process_message OUT NOCOPY VARCHAR2,
275 p_simulate_flag IN VARCHAR2,
276 p_codepath IN OUT NOCOPY VARCHAR2
277 );
278
279 PROCEDURE generate_excise_invoice(
280 p_transaction_id IN NUMBER,
281 p_organization_id IN NUMBER,
282 p_location_id IN NUMBER,
283 p_excise_invoice_no OUT NOCOPY VARCHAR2,
284 p_excise_invoice_date OUT NOCOPY DATE,
285 p_simulate_flag IN VARCHAR2,
286 p_errbuf OUT NOCOPY VARCHAR2,
290 FUNCTION get_receive_claimed_ptg(
287 p_codepath IN OUT NOCOPY VARCHAR2
288 );
289
291 p_transaction_id IN NUMBER,
292 p_shipment_line_id IN NUMBER,
293 p_codepath IN OUT NOCOPY VARCHAR2
294 ) RETURN NUMBER;
295
296 PROCEDURE get_tax_amount_breakup(
297 p_shipment_line_id IN NUMBER,
298 p_transaction_id IN NUMBER,
299 p_curr_conv_rate IN NUMBER,
300 pr_tax OUT NOCOPY TAX_BREAKUP,
301 p_breakup_type IN VARCHAR2,
302 p_codepath IN OUT NOCOPY VARCHAR2
303 );
304
305 PROCEDURE other_cenvat_rg_recording(
306 p_source_register IN VARCHAR2,
307 p_source_register_id IN NUMBER,
308 p_tax_type IN VARCHAR2,
309 p_credit IN NUMBER,
310 p_debit IN NUMBER,
311 p_process_status OUT NOCOPY VARCHAR2,
312 p_process_message OUT NOCOPY VARCHAR2
313 );
314
315 PROCEDURE check_cenvat_balances(
316 p_organization_id IN NUMBER,
317 p_location_id IN NUMBER,
318 p_transaction_amount IN NUMBER,
319 p_register_type IN VARCHAR2,
320 p_process_flag OUT NOCOPY VARCHAR2,
321 p_process_message OUT NOCOPY VARCHAR2
322 );
323
324 PROCEDURE derive_duty_registers(
325 p_organization_id IN NUMBER,
326 p_location_id IN NUMBER,
327 p_item_class IN VARCHAR2,
328 pr_tax IN TAX_BREAKUP,
329 p_cenvat_register_type OUT NOCOPY VARCHAR2,
330 -- p_edu_cess_register_type OUT VARCHAR2,
331 p_process_flag OUT NOCOPY VARCHAR2,
332 p_process_message OUT NOCOPY VARCHAR2,
333 p_codepath IN OUT NOCOPY VARCHAR2,
334 p_transaction_type IN VARCHAR2 --added for bug#9854329
335 );
336
337 procedure rtv_processing_for_ssi(
338 pn_transaction_id NUMBER,
339 pv_codepath in out nocopy varchar2,
340 pv_process_status out nocopy varchar2,
341 pv_process_message out nocopy varchar2
342 );
343
344 END jai_rcv_excise_processing_pkg;
[Home] [Help]