1 PACKAGE BODY JAI_JMCR_TRIGGER_PKG AS
2 /* $Header: jai_jcmr_t.plb 120.4 2012/05/23 08:02:21 zxin ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARIU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_JCMR_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_JCMR_ARIU_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARIU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15
16 v_quantity number; -- := pr_new.quantity_applied; --rpokkula for File.Sql.35
17 v_qty number; -- := pr_new.quantity_applied;--rpokkula for File.Sql.35
18 v_ref_line number; -- := pr_new.ref_line_id ; --rpokkula for File.Sql.35
19 v_receipt number; -- := pr_new.receipt_id; --rpokkula for File.Sql.35
20 v_line number;
21 p_picking_header_id number;
22 p_order_header_id number;
23 p_org_id number;
24 p_warehouse_id number;
25 v_order_header_id number;
26 v_converted_rate number;
27 v_set_of_books_id number;
28 v_currency_code varchar2(10);
29 v_conv_date date;
30 v_conv_type_code varchar2(10);
31 v_conv_rate number;
32 v_picking_header_id number;
33 v_org_id number;
34 v_warehouse_id number;
35 v_customer number;
36 v_subinventory varchar2(30); -- := pr_new.subinventory; --rpokkula for File.Sql.35
37 v_order varchar2(1); --:=pr_new.ORDER_INVOICE; --rpokkula for File.Sql.35
38 v_link_to_cust_trx_line_id number;
39
40
41 CURSOR so_picking_hdr_cur IS
42 SELECT source_header_id, delivery_detail_id
43 FROM wsh_delivery_details
44 WHERE delivery_detail_id = pr_new.ref_line_id;
45
46 CURSOR Org_warehouse_cur(p_picking_header_id NUMBER) IS
47 SELECT NVL(org_id,0), ORGANIZATION_ID
48 FROM wsh_delivery_details
49 WHERE delivery_detail_id = p_picking_header_id;
50
51 -- Altered by Arun for 11i
52
53 CURSOR get_conv_detail_cur(p_order_header_id Number) IS
54 SELECT TRANSACTIONAL_CURR_CODE, conversion_type_code, conversion_rate,
55 NVL(conversion_rate_date,ordered_date) conversion_date
56 FROM oe_order_headers_all
57 WHERE header_id = p_order_header_id;
58 /* Bug 5243532. Added by Lakshmi Gopalsami
59 * Removed the cursor set_of_books_cur as this
60 * is not used anywhere.
61 */
62
63 CURSOR set_of_books_cur(p_org_id NUMBER, p_warehouse_id NUMBER) IS
64 SELECT set_of_books_id
65 FROM org_organization_definitions
66 WHERE nvl(operating_unit,0) = p_org_id
67 AND organization_id = p_warehouse_id;
68
69 cursor line_id is
70 select source_line_id
71 from wsh_delivery_details
72 where delivery_detail_id =pr_new.ref_line_id;
73
74 cursor customer is
75 select bill_to_customer_id
76 from ra_customer_trx_all
77 where customer_trx_id in (select customer_trx_id from ra_customer_trx_lines_all
78 where customer_trx_line_id =pr_new.ref_line_id);
79
80 CURSOR Tax_Amount_Cur IS
81 SELECT sum(a.tax_amount) tax_amount
82 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
83 WHERE a.tax_id = b.tax_id
84 AND a.link_to_cust_trx_line_id = v_ref_line
85 AND b.tax_type NOT IN (
86 jai_constants.tax_type_tds ,
87 jai_constants.tax_type_modvat_recovery);
88 --AND b.tax_type NOT IN ('TDS','Modvat Recovery');
89 BEGIN
90 pv_return_code := jai_constants.successful ;
91 /*-------------------------------------------------------------------------------------------
92 Change History for FileName: ja_in_receipts_match_ar_trg.sql
93 S.No. dd/mm/yyyy Author and Details
94 ---------------------------------------------------------------------------------------------
95 1 17/06/2003 Vijay Shankar for Bug# 3007159, FileVersion# 616.1
96 the trigger is getting fired for all the Sales and Invoice transactions. but actually this should get
97 fired only for AR Invoice transactions. inorder to make this trigger fire only for sales order transactions,
98 the when condition of the trigger is modified
99
100 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
101 DB Entity as required for CASE COMPLAINCE. Version 116.1
102
103 3. 13-Jun-2005 File Version: 116.2
104 Ramananda for bug#4428980. Removal of SQL LITERALs is done
105 -------------------------------------------------------------------------------------------*/
106
107 v_quantity := pr_new.quantity_applied; --rpokkula for File.Sql.35
108 v_qty := pr_new.quantity_applied; --rpokkula for File.Sql.35
109 v_ref_line := pr_new.ref_line_id ; --rpokkula for File.Sql.35
110 v_receipt := pr_new.receipt_id; --rpokkula for File.Sql.35
111 v_subinventory := pr_new.subinventory; --rpokkula for File.Sql.35
112 v_order :=pr_new.ORDER_INVOICE; --rpokkula for File.Sql.35
113
114 /*
115 open so_picking_hdr_cur;
116 fetch so_picking_hdr_cur into v_order_header_id,v_picking_header_id;
117 close so_picking_hdr_cur;
118
119 open Org_warehouse_cur(v_picking_header_id);
120 fetch Org_warehouse_cur into v_org_id,v_warehouse_id;
121 close Org_warehouse_cur;
122
123 open line_id;
124 fetch line_id into v_link_to_cust_trx_line_id;
125 close line_id;
126
127 open get_conv_detail_cur(v_order_header_id);
128 fetch get_conv_detail_cur into v_currency_code,v_conv_type_code,v_conv_rate,v_conv_date;
129 close get_conv_detail_cur;
130
131 open set_of_books_cur(v_org_id,v_warehouse_id);
132 fetch set_of_books_cur into v_set_of_books_id;
133 close set_of_books_cur;
134
135 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,v_currency_code ,
136 v_conv_date ,v_conv_type_code,v_conv_rate);
137 */
138
139 open customer;
140 fetch customer into v_customer;
141 close customer;
142
143 /*
144
145
146 IF nvl(v_quantity,0) = 0 THEN
147 v_quantity := -nvl(pr_old.quantity_applied,0);
148 END IF;
149
150 UPDATE JAI_AR_TRX_LINES
151 SET matched_quantity = nvl(matched_quantity,0) + v_quantity
152 WHERE Customer_Trx_Line_Id = v_ref_line;
153 */
154
155 -- if v_order='I' then
156
157 jai_cmn_rcv_matching_pkg.ar_default_taxes(v_ref_line, v_customer, v_ref_line, 1, v_receipt, v_qty);
158
159 --end if;
160
161 FOR rec IN tax_amount_cur LOOP
162 UPDATE JAI_AR_TRX_LINES
163 SET tax_amount = rec.tax_amount,
164 total_amount = line_amount + rec.tax_amount
165 WHERE Customer_Trx_Line_Id = v_ref_line;
166
167 END LOOP;
168
169 /* Added an exception block by Ramananda for bug#4570303 */
170 EXCEPTION
171 WHEN OTHERS THEN
172 Pv_return_code := jai_constants.unexpected_error;
173 Pv_return_message := 'Encountered an error in JAI_JMCR_TRIGGER_PKG.ARIU_T1 ' || substr(sqlerrm,1,1900);
174
175 END ARIU_T1 ;
176
177 /*
178 REM +======================================================================+
179 REM NAME ARIU_T2
180 REM
181 REM DESCRIPTION Called from trigger JAI_JCMR_ARIUD_T1
182 REM
183 REM NOTES Refers to old trigger JAI_JCMR_ARIU_T3
184 REM
185 REM +======================================================================+
186 */
187 PROCEDURE ARIU_T2 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
188 v_quantity number; -- := pr_new.quantity_applied; --Ramananda for File.Sql.35
189 v_ref_line number; -- := pr_new.ref_line_id ; --Ramananda for File.Sql.35
190 v_receipt number; -- := pr_new.receipt_id; --Ramananda for File.Sql.35
191 v_line number;
192 p_picking_header_id number;
193 p_order_header_id number;
194 p_org_id number;
195 p_warehouse_id number;
196 v_order_header_id number;
197 v_converted_rate number;
198 v_set_of_books_id number;
199 v_currency_code varchar2(10);
200 v_conv_date date;
201 v_conv_type_code varchar2(10);
202 v_conv_rate number;
203 v_picking_header_id number;
204 v_org_id number;
205 v_warehouse_id number;
206 v_customer number;
207 v_order varchar2(1); --:=pr_new.ORDER_INVOICE; --Ramananda for File.Sql.35
208
209
210 CURSOR so_picking_hdr_cur IS
211 SELECT source_header_id, delivery_detail_id
212 FROM wsh_delivery_details
213 WHERE delivery_detail_id = pr_new.ref_line_id;
214
215 -- Altered by Arun for 11i.
216 CURSOR Org_warehouse_cur(p_picking_header_id NUMBER) IS
217 SELECT NVL(org_id,0), ORGANIZATION_ID
218 FROM wsh_delivery_details
219 WHERE delivery_detail_id = p_picking_header_id;
220
221 CURSOR get_conv_detail_cur(p_order_header_id Number) IS
222 SELECT TRANSACTIONAL_CURR_CODE, conversion_type_code, conversion_rate,
223 NVL(conversion_rate_date,ordered_date) conversion_date
224 FROM oe_order_headers_all
225 WHERE header_id = p_order_header_id;
226
227 /* Bug 5243532. Added by Lakshmi Gopalsami
228 * Removed the cursor set_of_books_cur as this
229 * is not used anywhere.
230 */
231
232 -- Altered by Arun for 11i
233 cursor line is select source_line_id
234 from wsh_delivery_details
235 where delivery_detail_id =pr_new.ref_line_id;
236
237 -- Altered by Arun for 11i
238 cursor customer is
239 select SOLD_TO_ORG_ID
240 from oe_order_headers_all
241 where header_id in (select source_header_id
242 from wsh_delivery_details
243 where delivery_detail_id =pr_new.ref_line_id );
244
245 CURSOR set_of_books_cur(p_org_id NUMBER, p_warehouse_id NUMBER) IS
246 SELECT set_of_books_id
247 FROM org_organization_definitions
248 WHERE nvl(operating_unit,0) = p_org_id
249 AND organization_id = p_warehouse_id;
250
251
252 BEGIN
253 pv_return_code := jai_constants.successful ;
254
255 /*-------------------------------------------------------------------------------------------
256 Change History for FileName: ja_in_receipts_match_trigger.sql
257 S.No. dd/mm/yyyy Author and Details
258 ---------------------------------------------------------------------------------------------
259 1 17/06/2003 Vijay Shankar for Bug# 3007159, FileVersion# 616.1
260 the trigger is getting fired for all the Sales and Invoice transactions. but actually this should get
261 fired only for Sales Order transactions. inorder to make this trigger fire only for sales order transactions,
262 the when condition of the trigger is modified
263
264 2 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
265 DB Entity as required for CASE COMPLAINCE. Version 116.1
266
267 3. 13-Jun-2005 File Version: 116.2
268 Ramananda for bug#4428980. Removal of SQL LITERALs is done
269 vkaranam for bug#9839132
270 Pass the conversion rate as on thE ordered date for the order else conversion rate is 1.
271 -------------------------------------------------------------------------------------------*/
272
273 v_quantity := pr_new.quantity_applied; --Ramananda for File.Sql.35
274 v_ref_line := pr_new.ref_line_id ; --Ramananda for File.Sql.35
275 v_receipt := pr_new.receipt_id; --Ramananda for File.Sql.35
276 v_order :=pr_new.ORDER_INVOICE; --Ramananda for File.Sql.35
277
278
279
280 --start additions for bug#9839132
281 open so_picking_hdr_cur;
282 fetch so_picking_hdr_cur into v_order_header_id, v_picking_header_id;
283 close so_picking_hdr_cur;
284
285 open Org_warehouse_cur(v_picking_header_id);
286 fetch Org_warehouse_cur into v_org_id, v_warehouse_id;
287 close Org_warehouse_cur;
288
289 open get_conv_detail_cur(v_order_header_id);
290 fetch get_conv_detail_cur into v_currency_code, v_conv_type_code, v_conv_rate, v_conv_date;
291 close get_conv_detail_cur;
292
293 open set_of_books_cur(v_org_id,v_warehouse_id);
294 fetch set_of_books_cur into v_set_of_books_id;
295 close set_of_books_cur;
296
297 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,v_currency_code ,
298 v_conv_date ,v_conv_type_code,v_conv_rate);
299
300 if v_order='O' then
301 v_converted_rate := nvl(v_converted_rate,1);
302 else
303 v_converted_rate :=1;
304 end if;
305 --end additions for bug#9839132
306 open line;
307 fetch line into v_line;
308 close line;
309
310 open customer;
311 fetch customer into v_customer;
312 close customer;
313
314 -- if v_order='O' then
315 /*added teh below condition by vkaranma for bug#6030615(interorg)*/
316 IF v_order = 'X' THEN
317 v_line:= v_ref_line;
318 END IF;
319
320
321 jai_cmn_rcv_matching_pkg.om_default_taxes(pr_new.subinventory, v_customer, v_ref_line,
322 v_receipt, v_line, v_quantity, nvl(v_converted_rate,1), v_order);--passed v_converted_rate value for bug#9839132
323
324 -- RAISE_APPLICATION_ERROR(-20120, 'Bonded Register Has AFTER -> ') ;
325
326 -- end if;
327 /* Added an exception block by Ramananda for bug#4570303 */
328 EXCEPTION
329 WHEN OTHERS THEN
330 Pv_return_code := jai_constants.unexpected_error;
331 Pv_return_message := 'Encountered an error in JAI_JMCR_TRIGGER_PKG.ARIU_T2 ' || substr(sqlerrm,1,1900);
332
333 END ARIU_T2 ;
334
335 END JAI_JMCR_TRIGGER_PKG ;
[Home] [Help]