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