1 PACKAGE BODY JAI_RCV_RT_TRIGGER_PKG AS
2 /* $Header: jai_rcv_rt_t.plb 120.8 2007/10/10 15:39:18 rchandan ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_RCV_RT_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_RCV_RT_ARI_T2
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARI_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 v_tax_count number := 0;
16 v_orgn_setup_count number := 0;
17 v_currency_code gl_sets_of_books.currency_code%type;
18 v_gl_set_of_bks_id number;
19
20
21 cursor c_get_tax_count(p_shipment_header_id number, p_shipment_line_id number) is
22 select count(1)
23 from JAI_RCV_LINE_TAXES
24 where shipment_header_id = p_shipment_header_id
25 and shipment_line_id = p_shipment_line_id;
26
27
28 cursor chk_org_setup_is_present (
29 p_organization_id number ,
30 p_location_id number
31 )
32 is
33 select
34 count(1)
35 from
36 JAI_CMN_INVENTORY_ORGS
37 where
38 organization_id = p_organization_id AND
39 location_id = p_location_id;
40
41 /* Bug 5243532. Added by Lakshmi Gopalsami
42 * Removed cursors c_fetch_sob_id and c_fetch_currency_code
43 * and implemented caching logic to get the values.
44 */
45 cursor c_fetch_shipment_line_info(p_shipment_line_id number) is
46 select destination_type_code from
47 rcv_shipment_lines
48 where shipment_line_id = p_shipment_line_id;
49
50 v_destination_type_code rcv_shipment_lines.destination_type_code%type; --3655330
51
52 /* Bug 5243532. Added by Lakshmi Gopalsami
53 * Defined variable for implementing caching logic.
54 */
55 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
56
57 /* following added by CSahoo BUG#5592114 */
58 lv_scenario varchar2(50);
59 ln_location_id number;
60 cursor c_get_location_id(p_organization_id number, cp_subinventory varchar2) is
61 select location_id
62 from JAI_INV_SUBINV_DTLS
63 where organization_id = p_organization_id
64 and sub_inventory_name = cp_subinventory;
65
66
67 BEGIN
68 pv_return_code := jai_constants.successful ;
69 /*------------------------------------------------------------------------------------------
70 FILENAME: ja_in_localization_setup_checks_trg.sql
71
72 CHANGE HISTORY:
73 S.No Date Author and Details
74
75 1. 02/01/2004 Nagaraj.s for Bug#3496327. Version: 619.1
76 This Trigger is created to validate whether Localization setups exist for
77 the Organization/Location combination.
78
79 2. 07/06/2004 Nagaraj.s for Bug#3655330. Version : 619.2
80 In case of change in destination type for standard routing, the location
81 would still be null as a result of which the setups are checked and the error
82 is thrown up. This scenario of change in destination type is not supported
83 by Localization yet and hence a message in such a scenario is :
84 Localization does not support change in destination type
85
86 3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
87 DB Entity Names, as required for CASE COMPLAINCE. Version 116.1
88
89 4. 13-Jun-2005 File Version: 116.2
90 Ramananda for bug#4428980. Removal of SQL LITERALs is done
91
92 5. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.3
93 GL Sources and GL Categories got changed. Refer bug for the details
94
95 6. 20-Feb-2007 CSahoo for Bug#5592114. File Version 120.3
96 Forward Porting of 11i BUG#5592023
97 Modified the code not to error during RECEIVE trx of Standard Routing changed to Direct delivery
98 + enhanced this trigger to execute for direct delivery case also
99
100 14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved
101
102 8. 10-oct-2007 rchandan for bug#6488175, File version 120.8
103 Issue : R12RUP04.I/ORG.QA.ST1: RTP GOES INTO ERROR IF LOCATIONIS NOT GIVENFOR INTRANSIT
104 Fix : the check for Il setup should be made only for DELIVER transaction. This was happening only for
105 PO source_document_code. made a change to allow this to happen for INVENTORY source_document_code
106 also.
107 --------------------------------------------------------------------------------------------
108
109 -------------------------------------------------------------------------------------------
110
111 --Dependency section post IN60105d2
112
113
114
115 Dependencies For Future Bugs
116 -------------------------------------
117
118
119 -------------------------------------------------------------------------------------------------
120 */
121
122 /* Bug 5243532. Added by Lakshmi Gopalsami
123 * Removed cursors c_fetch_sob_id and c_fetch_currency_code and
124 * implemented caching logic for getting SOB.
125 */
126 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
127 (p_org_id => pr_new.organization_id);
128 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
129 v_currency_code := l_func_curr_det.currency_code;
130
131 open c_get_tax_count
132 (
133 p_shipment_header_id => pr_new.shipment_header_id,
134 p_shipment_line_id => pr_new.shipment_line_id
135 );
136 fetch c_get_tax_count into v_tax_count;
137 close c_get_tax_count;
138
139 if nvl(v_tax_count,0) = 0 then
140 return;
141 end if;
142
143 /* Start, added by CSahoo BUG#5592114 */
144 OPEN c_fetch_shipment_line_info(pr_new.shipment_line_id);
145 FETCH c_fetch_shipment_line_info INTO v_destination_type_code;
146 CLOSE c_fetch_shipment_line_info;
147
148 IF v_destination_type_code='INVENTORY'
149 AND pr_new.source_document_code IN ('PO','INVENTORY')/*rchandan for bug#6488175*/
150 THEN
151 IF pr_new.transaction_type = 'RECEIVE' THEN
152 RETURN;
153 ELSIF pr_new.transaction_type = 'DELIVER' THEN
154 lv_scenario := 'STANDARD_TO_DIRECT'; /* go ahead to validate the IL Setup */
155 END IF;
156
157 ELSIF pr_new.transaction_type = 'DELIVER' THEN
158 RETURN; -- no need of this check for deliver case except for above
159 END IF;
160 /* End, added by CSahoo BUG#5592114 */
161
162 open chk_org_setup_is_present
163 (
164 p_organization_id => pr_new.organization_id ,
165 p_location_id => pr_new.location_id
166 );
167 fetch chk_org_setup_is_present into v_orgn_setup_count;
168 close chk_org_setup_is_present;
169
170 if nvl(v_orgn_setup_count,0) = 0 then
171 /* Start, added by CSahoo Bug#5592114 */
172 if lv_scenario = 'STANDARD_TO_DIRECT' then
173 open c_get_location_id(pr_new.organization_id, pr_new.subinventory);
174 fetch c_get_location_id into ln_location_id;
175 close c_get_location_id;
176
177 if ln_location_id is not null then
178
179 open chk_org_setup_is_present
180 (
181 p_organization_id => pr_new.organization_id ,
182 p_location_id => ln_location_id
183 );
184 fetch chk_org_setup_is_present into v_orgn_setup_count;
185 close chk_org_setup_is_present;
186
187 if nvl(v_orgn_setup_count,0) > 0 then
188 return; /* no problem with setup. so, do not error */
189 end if;
190
191 end if;
192
193 end if;
194 /* End, added by CSahoo Bug#5592114 */
195
196 open c_fetch_shipment_line_info(pr_new.shipment_line_id);
197 fetch c_fetch_shipment_line_info into v_destination_type_code;
198 close c_fetch_shipment_line_info;
199
200 --3655330
201 if pr_new.source_document_code in ('PO') and v_destination_type_code='INVENTORY' then
202 fnd_file.put_line(fnd_file.log, 'Cannot process records.The change of destination type for standard/inspection routing is not supported by Localization');
203 app_exception.raise_exception( exception_type => 'APP' ,
204 exception_code => -20120 ,
205 exception_text => 'Localization does not support change in destination type'
206 );
207 end if;
208
209 fnd_file.put_line(fnd_file.log, 'Cannot process records.The organization Location combination does not have a valid localization setup');
210 app_exception.raise_exception( exception_type => 'APP' ,
211 exception_code => -20120 ,
212 exception_text => 'No India Localization setup for this Location '
213 );
214 end if;
215
216 END ARI_T1 ;
217
218 /*
219 REM +======================================================================+
220 REM NAME BRI_T1
221 REM
222 REM DESCRIPTION Called from trigger JAI_RCV_RT_BRIUD_T1
223 REM
224 REM NOTES Refers to old trigger JAI_RCV_RT_BRI_T1
225 REM
226 REM +======================================================================+
227 */
228 PROCEDURE BRI_T1 ( pr_old t_rec%type , pr_new in out t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
229 v_shipment_header_id rcv_shipment_headers.shipment_header_id % type;
230 /* Commented rallamse bug#4479131 PADDR Elimination
231 v_rowid JAI_CMN_LOCATORS_T.row_id%type;
232 */
233 -- CSahoo for Bug 5344225
234 lv_request_id NUMBER ;
235 lv_group_id NUMBER ;
236 lv_profile_val VARCHAR2(100);
237 --lv_debug VARCHAR2(1) := 'Y';
238 --ln_file_hdl UTL_FILE.FILE_TYPE;
239
240 CURSOR c_rcv_hdr IS
241 SELECT rowid, receipt_source_code, receipt_num, shipment_num, shipped_date, organization_id,
242 vendor_id, vendor_site_id, customer_id, customer_site_id
243 FROM rcv_shipment_headers
244 WHERE shipment_header_id = pr_new.shipment_header_id;
245
246 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
247 r_rcv_hdr c_rcv_hdr%ROWTYPE;
248 v_chk number(5);
249 CURSOR c_jai_rcv_hdr_chk(cp_shipment_header_id number) is
250 select 1
251 from jai_rcv_headers
252 where shipment_header_id = cp_shipment_header_id;
253
254 v_error_mesg VARCHAR2(200); -- added by Aparajita for bug#2514979 on 18/08/2002.
255 v_receipt_source_code rcv_shipment_headers.receipt_source_code%type; --ashish for bug # 2613817
256
257 -- Vijay Shankar for Enhancement Bug# 3496408
258 -- lv_opm_flag MTL_PARAMETERS_VIEW.process_enabled_flag%TYPE;
259 lv_process_mode VARCHAR2(1);
260 lv_request_desc VARCHAR2(200);
261 lv_req_id NUMBER;
262 lv_result BOOLEAN;
263
264 CURSOR c_receipt_line(cp_shipment_line_id IN NUMBER) IS
265 SELECT tax_modified_flag
266 FROM JAI_RCV_LINES
267 WHERE shipment_line_id = cp_shipment_line_id;
268 lv_tax_modified_flag JAI_RCV_LINES.tax_modified_flag%TYPE;
269
270 v_temp1 VARCHAR2(30);
271 v_chk_form VARCHAR2(30); -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
272 lv_called_from VARCHAR2(30);
273
274 lv_allow_tax_change_hook VARCHAR2(1);
275
276 /* Vijay Shankar for Bug#4250171 */
277 lv_comments VARCHAR2(30);
278 lv_submit_jainrvctp VARCHAR2(1); --File.Sql.35 Cbabu := 'N';
279
280 --Cursor Added by Sanjikum for Bug #4105721
281 CURSOR c_mtl_trx(cp_organization_id IN NUMBER) IS
282 SELECT NVL(process_enabled_flag, jai_constants.no) process_enabled_flag
283 FROM mtl_parameters_view
284 WHERE Organization_id = cp_organization_id;
285
286 r_mtl_trx c_mtl_trx%ROWTYPE;
287
288 /* start bgowrava for forward porting Bug#5636560 */
289 lv_parent_trx_type JAI_RCV_TRANSACTIONS.transaction_type%type;
290 CURSOR c_parent_trx_type is
291 SELECT transaction_type
292 from JAI_RCV_TRANSACTIONS
293 where transaction_id = pr_new.parent_transaction_id;
294 /* End bgowrava for Bug#5636560 */
295
296 --Function Added by Sanjikum for Bug #4105721
297 FUNCTION get_deliver_unit_price(p_shipment_line_id IN NUMBER)
298 RETURN NUMBER
299 IS
300 PRAGMA AUTONOMOUS_TRANSACTION;
301 CURSOR c_deliver_unit_price(cp_transaction_type rcv_transactions.transaction_type%type) IS
302 SELECT po_unit_price
303 FROM rcv_transactions
304 WHERE shipment_line_id = p_shipment_line_id
305 AND transaction_type = cp_transaction_type ; /* 'DELIVER'; Ramananda for removal of SQL LITERALs */
306
307 r_deliver_unit_price c_deliver_unit_price%ROWTYPE;
308 BEGIN
309 pv_return_code := jai_constants.successful ;
310 OPEN c_deliver_unit_price('DELIVER');
311 FETCH c_deliver_unit_price INTO r_deliver_unit_price;
312 CLOSE c_deliver_unit_price;
313
314 RETURN r_deliver_unit_price.po_unit_price;
315 END get_deliver_unit_price;
316 BEGIN
317 pv_return_code := jai_constants.successful ;
318 /*------------------------------------------------------------------------------------------
319 Change history for ja_in_receipt_tax_insert_trg.sql
320 S.No Date Author and Details
321 -------------------------------------------------------------------------------------------------
322 1. 31-aug-01 Subbu Modified code for DFF issue.
323
324 2. 07-07-02 Nagaraj.s for Bug2449826.
325 Incorporated an IF condition
326 wherein if the comments is OPM Receipt
327 then the trigger should not be processed
328 further.
329
330 3. 18/08/2002 Aparajita, revamp of process at receipt. bug #2514979.
331 Added the call to procedure Ja_In_Set_Rcv_Process_Flags
332 to set the process flag for various processes.
333
334 4. 23/08/2002 Nagaraj.s for Bug2525910
335 Incorporated an parameter in the call to ja_in_receipts_p.sql
336 pr_new.ROUTING_HEADER_ID
337 5. 24/10/2002 ashish for bug # 2613817
338 changes done for express receipt functionality.
339 this functionality enables a user to perform the express receipt.
340 this functionality was lost and is reintroduced.
341
342
343 6. 04/03/2003 Nagaraj.s for Bug#2692052 Version:615.4
344 High Dependency with this Patch
345 Added 3 arguments for the call to JA_NI_SET_RCV_PROCESS_FLAGS.
346 The Arguments are :
347 pr_new.ATTRIBUTE3
348 NVL(pr_new.ROUTING_HEADER_ID,0)
349 'TRIGGER'
350
351 7. 2003/04/01 Sriram - Bug # 2881674
352 Attribute5 was not getting copied for 'India RMA Receipt' attribute category. This has
353 been fixed in this bug.
354
355 8. 08/07/2003 Nagaraj.s for Bug#3036825. Version : 616.1
356 A new parameter attribute_category is passed to
357 ja_in_set_rcv_process_flags through this procedure.
358
359 10. 15/10/2003 Nagaraj.s for Bug#3162928. Version : 616.2
360 One more Condition is added in the Trigger to allow
361 "To handle Deliver RTR RTV" to fire in case of an
362 RMA Receipt/standard Delivery.
363
364 11. 08/01/2004 Nagaraj.s for Bug#3354415. Version : 618.1
365 The call to ja_in_set_rcv_process_flags is now having one more parameter
366 p_attribute5. Hence this would result into an Dependency. This is being
367 passed to the procedure
368
369 12. 13/03/2004 Nagaraj.s for Bug#3456636. Version: 619.1
370 The call to ja_in_set_rcv_process_flags is made only in case of Transaction
371 Type=RECEIVE so that the program flow may not enter ja_in_set_rcv_process_flags
372 in case of other transactions.
373 This Patch has an alter statement and is hence a high dependency.
374
375 13. 16/06/2004 SSUMAITH - bug# 3683666 File version 115.1
376
377 if the attribute_category is a null value and attribute2 is a not null value
378 it is being set to NULL and passed on to the jai_rcv_tax_pkg.default_taxes_onto_line procedure.
379
380 If the value is not one of the India Localization standard ones, then we entering the
381 values in a JAI_CMN_ERRORS_T and are returing the control.
382
383 The 'INR' check which was commented is now un-commented so that code returns in cases where
384 in Non-INR set of books , no processing occurs..
385
386 Dependency due to this bug - None
387
388 14. 16/07/2004 Vijay Shankar for Enhancement Bug# 3496408, Version: 115.2
389 trigger enabled to Support CORRECT transactions for Localization Processing
390 Also DELIVER and RTR transactions are delinked from Old Code and linked to New code with this enhancement.
391 New Concurrent program JAINRVCTP is called incase of DELIVER, RTR and CORRECT transactions
392 HIGH DEPENDENCY
393
394 15. 20/10/2004 Vijay Shankar for Bug#3927371 (3836490), Version: 115.3
395 Concurrent request for JAINRVCTP should not be fired for Direct Delivery case, as it is handled in
396 ja_in_rel_close_loc Also the issue of RTV passing Localization Accounting for UNORDERED Receipt
397 even if it is not matched is resolved by returning back the execution if PO_HEADER_ID
398 link is not found for transaction
399
400 16. 03/11/2004 Vijay Shankar for Bug#3959765, Version: 115.4
401 Modified the code added for Bug#3683666, so that the trigger fires even if NEW.attribute_category has customer DFF values.
402 commented the code that is checking for localization DFFs and doesnt allow customer DFFs for localization processing.
403
404 17. 30/11/2005 Aparajita for bug#4036241. Version#115.5
405
406 Introduced the call to centralized packaged procedure,
407 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
408
409 18 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.6
410 Following are the changes done as part of RECEIPTS DEPLUG
411 - Submits a request for "India - Receiving Transaction Processor" for DELIVER, RTR, RTV, Any CORRECTs, RECEIVE
412 Transations that are not Created without Navigating from Localization Receipts Screen
413 - Makes a call to jai_rcv_tax_pkg.default_taxes_onto_line incase of 'RECEIVE', 'UNORDERED', 'MATCH', 'RETURN TO VENDOR' transactions only
414 - a new parameter v_chk_form is added in call to jai_rcv_tax_pkg.default_taxes_onto_line based on which request for JAINRVCTP is submitted
415 incase of RECEIVE transaction
416 - Commented the call to ja_in_set_rvc_process_flags as it is redundant with RECEIPTS DEPLUG from Old Code
417 - Updates flags of JAI_RCV_LINES with X value. updates transaction_id to MATCH transaction_id in case of
418 UNORDERED transaction
419
420 19 03/02/2005 Vijay Shankar for Bug# 4159557, Version:115.7
421 Modified the code, so that users will be able to modify taxes of Receipt by Querying it in Localization Screen with the
422 help of localization Receipts Hook for Open Interface/WMS Receipts.
423 v_chk_form chk is modified to look only for ASBN Receipts and submit request for "India - Receiving Transaction Processor"
424
425 * This is a dependancy for Future Versions of the trigger *
426
427 20 22/02/2005 Vijay Shankar for Bug# 4199929, Version:115.8
428 Revoked the call to jai_cmn_hook_pkg as it is replaced with Orgn. Addl. info setup usage in jai_rcv_tax_pkg.default_taxes_onto_line call
429
430 * This is a dependancy for Future Versions of the trigger *
431
432 21 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.9
433 modified an if condition to assign a proper value to lv_called_from variable. if a wrong value is assigned, then
434 India RTP may not process the transaction
435
436 22 25/03/2005 Vijay Shankar for Bug#4250171. Version:115.11
437 Code is modified to populate JAI_RCV_TRANSACTIONS even if the transaction is created through an
438 OPM Receipt/Return. This modification is done, so that VAT Processing of OPM Receipt happens through Discrete code
439
440 23 07/04/2005 Harshita for Bug #4285064 Version : 115.12
441
442 When a user creates a new receipt against a purchase order, he needs to enter the following information
443 through a DFF : invoice no, invoice_date, Claim Cenvat On Receipt etc.
444 This DFF is provided at two places, header and line.
445 Information from the header DFF is captured into the rcv_shipment_headers table.
446 Information from the lines DFF is captured into the rcv_transactions table.
447 This information is retrieved into our base tables JAI_RCV_TRANSACTIONS and JAI_RCV_LINES.
448 At this time, a facility has been provided for the user to default the information
449 given at the header level DFF to all the lines only if these columns are null at the
450 line level. Else the information in the line level DFF is sustained.
451 For this NVL conditions have been added where this information gets defaulted.
452
453 24 15/04/2005 Harshita for Bug #4285064 Version : 115.13
454 Debug messages that have been added for testing were not removed in the previous chech in.
455 Removed the debug messages.
456
457 25 15/04/2005 Sanjikum for Bug #4105721, File Version 116.0(115.14)
458
459 Problem
460 -------
461 In case of RTR and RTV, PO_UNIT_PRICE is not updated with the proper costing effect.
462 Previously base was updating the PO_UNIT_PRICE, same as the PO_UNIT_PRICE of the
463 Deliver Transaction. Now base has changed the logic, and in 11.5.10, it is not
464 Populated correctly
465
466 Fix
467 ---
468 In case of RTR and RTV, PO_UNIT_PRICE is updated same as the PO_UNIT_PRICE of the Deliver Transaction.
469 Following changes are done for the same -
470
471 1) Created a new inline function get_deliver_unit_price
472 2) Added a new IF Condition, before the <<end_of_trigger>> LABEL
473 IF pr_new.transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR') THEN
474 pr_new.po_unit_price := get_deliver_unit_price(pr_new.shipment_line_id);
475 END IF;
476
477 26 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
478 Code is modified due to the Impact of Receiving Transactions DFF Elimination
479
480 * High Dependancy for future Versions of this object *
481
482
483 27 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
484 as required for CASE COMPLAINCE. Version 116.1
485
486 28. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
487 Removal of SQL LITERALs is done
488
489 29 06-Jul-2005 rallamse for bug# PADDR Elimination
490 Commented reference to JAI_CMN_LOCATORS_T table
491
492 30. 01-Aug-2005 Ramananda for bug#4565478 (4519697), File Version 120.3
493 Changed the If condition for setting the value of lv_called_from
494
495 Dependency due to this Bug
496 --------------------------
497 jai_rcv_trx_prc.plb (120.3)
498 jai_rcv_tax.plb (120.3)
499
500 31. 13-Feb-2007 bgowrava for forward porting Bug#5636560 (11i bug#5405889). File Version 120.2
501 Added an if condition to return from the trigger if parent transaction type of CORRECT is
502 NOT IN (RECEIVE, MATCH, DELIVER, RETURN TO RECEIVING , RETURN TO VENDOR).
503 The reson is, we support once these transaction types
504
505 added a cursor c_parent_trx_type to fetch transaction_type of parent_transaction_id from ja_in_rcv_transactions
506
507 Dependancy due to this bug: None
508
509 32. 20-Feb-2007 CSahoo, BUG#5344225, File Version 120.4
510 Forward Porting of 11i Bug 5343848
511 Issue : India - Receiving Transaction Processor Concurrent Program was called
512 for each transaction on a shipment line.
513 Fix:
514 Following approach was taken in case RTP was fired in the IMMEDIATE MODE.
515 -------------------------------------------------------------------------
516 Added code to check if variable gv_shipment_header_id is null.
517 if yes,
518 a) Get the Request Id of the base RVCTP
519 b) Call the India - RTP concurrent passing the Shipment Header Id and the Request Id of the base RVCTP.
520 c) Set the variable gv_shipment_header_id to the Shipment header Id called.
521 else
522 null ;
523
524 Following approach was taken in case RTP was fired in the ONLINE MODE.
525 -------------------------------------------------------------------------
526 Added code to check if variable gv_group_id is null.
527 if yes,
528 a) Get the group_id of the base table rcv_transactions
529 b) Call the India - RTP concurrent passing the Shipment Header Id and the group id of rcv_transactions.
530 c) Set the variable gv_group_id to the group_id passed.
531 else
532 null ;
533
534 Dependency Due to this Bug : Yes.
535
536 33. 25-may-2207 CSahoo, bug#6071528, file version 120.6
537 added the following line fnd_profile.get('RCV_TP_MODE',lv_profile_val);
538
539 34. 03-JUN-2007 SACSETHI BUG 6078460 File version
540
541 Problem- IN Purchasing to Return to vendor cycle , vat and cenvat was not reversing in accounting .
542
543 Solution - Argument was not required when we going to call concurrent program JAINRVCTP
544
545 Reasong - For bug 5344225 - we made the approach to execute only concurrent program JAINRVCTP for only one time
546 instead of calling again and again .....
547
548 -------------------------------------------------------------------------------------------------
549 Dependencies For Future Bugs
550 -------------------------------------------------------------------------------------------------
551 IN60104d + 3036825
552 IN60105d2 + 3354415 + 3456636 + 3496408 + 3927371 + 3959765
553 IN60105d2 + 3354415 + 3456636 + 3496408 + 3927371 + 3959765 + 4033992 + 4036241
554
555 IN60106 + 3940588 + 4199929 + 4346453
556
557 Sl No. Bug Dependent on
558 Bug/Patch set Details
559 -------------------------------------------------------------------------------------------------
560 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
561 4033992.
562 ja_in_util_pkg_s.sql 115.0
563 ja_in_util_pkg_b.sql 115.0
564 ------------------------------------------------------------------------------------------------- */
565
566 --File.Sql.35 Cbabu
567 lv_submit_jainrvctp := 'N';
568
569 --if jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_RECEIPT_TAX_INSERT_TRG',
570 -- p_inventory_orgn_id => pr_new.organization_id)
571
572 -- = FALSE
573 --then
574 /* India Localization funtionality is not required */
575 -- return;
576 --end if;
577
578
579 /*start bgowrava for forward porting Bug#5636560 */
580 if pr_new.transaction_type = 'CORRECT' then
581
582 open c_parent_trx_type;
583 fetch c_parent_trx_type into lv_parent_trx_type;
584 close c_parent_trx_type;
585
586 /* IL support only the corrections of the following transaction types. Hence if the parent of the
587 correction is not within these trx types, we should return back from this trigger */
588 if nvl(lv_parent_trx_type, 'XX') not in
589 ('RECEIVE', 'MATCH', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR')
590 then
591 return;
592 end if;
593
594 end if;
595 /*end bgowrava for Bug#5636560 */
596
597
598 IF pr_new.comments in ('OPM RECEIPT','OPM Receipt Correction') THEN
599 lv_comments := pr_new.comments;
600 ELSE
601 lv_comments := NULL;
602 END IF;
603
604 /* Vijay Shankar for Bug#4250171 */
605 /* following insert is moved from bottom to here to take care of OPM Functionality also */
606 IF pr_new.transaction_type in ( 'RECEIVE', 'DELIVER', 'RETURN TO RECEIVING',
607 'RETURN TO VENDOR', 'CORRECT', 'MATCH')
608 THEN
609 jai_rcv_transactions_pkg.insert_row(
610 P_SHIPMENT_HEADER_ID => pr_new.shipment_header_id,
611 P_SHIPMENT_LINE_ID => pr_new.shipment_line_id,
612 P_TRANSACTION_ID => pr_new.transaction_id,
613 P_TRANSACTION_DATE => pr_new.transaction_date,
614 P_TRANSACTION_TYPE => pr_new.transaction_type,
615 P_QUANTITY => pr_new.quantity,
616 P_UOM_CODE => nvl(pr_new.uom_code, jai_general_pkg.get_uom_code(pr_new.unit_of_measure)),
617 P_PARENT_TRANSACTION_ID => pr_new.parent_transaction_id,
618 P_PARENT_TRANSACTION_TYPE => NULL,
619 P_destination_type_code => pr_new.destination_type_code,
620 P_RECEIPT_NUM => NULL,
621 P_ORGANIZATION_ID => pr_new.organization_id,
622 P_LOCATION_ID => NULL,
623 P_INVENTORY_ITEM_ID => NULL,
624 p_excise_invoice_no => null,
625 p_excise_invoice_date => null,
626 p_tax_amount => null,
627 P_assessable_value => NULL,
628 P_currency_conversion_rate => pr_new.currency_conversion_rate,
629 P_ITEM_CLASS => NULL,
630 P_ITEM_cenvatABLE => NULL,
631 P_ITEM_EXCISABLE => NULL,
632 P_ITEM_TRADING_FLAG => NULL,
633 P_INV_ITEM_FLAG => NULL,
634 P_INV_ASSET_FLAG => NULL,
635 P_LOC_SUBINV_TYPE => NULL,
636 P_BASE_SUBINV_ASSET_FLAG => NULL,
637 P_ORGANIZATION_TYPE => NULL,
638 P_EXCISE_IN_TRADING => NULL,
639 P_COSTING_METHOD => NULL,
640 P_BOE_APPLIED_FLAG => NULL,
641 P_THIRD_PARTY_FLAG => NULL,
642 P_ATTRIBUTE_CATEGORY => lv_comments,
643 P_ATTRIBUTE1 => NULL,
644 P_ATTRIBUTE2 => NULL,
645 P_ATTRIBUTE3 => NULL,
646 P_ATTRIBUTE4 => NULL,
647 P_ATTRIBUTE5 => NULL,
648 P_ATTRIBUTE6 => NULL,
649 P_ATTRIBUTE7 => NULL,
650 P_ATTRIBUTE8 => NULL,
651 P_ATTRIBUTE9 => NULL,
652 P_ATTRIBUTE10 => NULL,
653 p_tax_transaction_id => NULL
654 );
655
656 OPEN c_rcv_hdr;
657 FETCH c_rcv_hdr into r_rcv_hdr;
658 CLOSE c_rcv_hdr;
659 /*v_rowid := r_rcv_hdr.rowid;*/
660
661 open c_jai_rcv_hdr_chk(pr_new.shipment_header_id);
662 fetch c_jai_rcv_hdr_chk into v_chk;
663 close c_jai_rcv_hdr_chk;
664
665 if v_chk is null then
666
667 INSERT INTO jai_rcv_headers(
668 SHIPMENT_HEADER_ID
669 ,RECEIPT_SOURCE_CODE
670 ,RECEIPT_NUM
671 ,SHIPMENT_NUM
672 ,SHIPPED_DATE
673 ,ORGANIZATION_ID
674 ,VENDOR_ID
675 ,VENDOR_SITE_ID
676 ,CUSTOMER_ID
677 ,CUSTOMER_SITE_ID,
678 creation_date,
679 created_by,
680 last_update_date,
681 last_updated_by,
682 last_update_login
683 ) VALUES (
684 pr_new.shipment_header_id,
685 r_rcv_hdr.receipt_source_code,
686 r_rcv_hdr.receipt_num,
687 r_rcv_hdr.shipment_num,
688 r_rcv_hdr.shipped_date,
689 r_rcv_hdr.organization_id,
690 r_rcv_hdr.vendor_id,
691 r_rcv_hdr.vendor_site_id,
692 r_rcv_hdr.customer_id,
693 r_rcv_hdr.customer_site_id,
694 sysdate,
695 fnd_global.user_id,
696 sysdate,
697 fnd_global.user_id,
698 fnd_global.login_id
699 );
700 end if;
701 END IF;
702
703 /* Vijay Shankar for Bug#4250171 */
704 IF pr_new.comments in ('OPM RECEIPT', 'OPM Receipt Correction') THEN
705 IF pr_new.comments = 'OPM Receipt Correction' THEN
706 lv_request_desc := 'India - Receiving Transaction Processor for OPM '|| initcap(pr_new.transaction_type);
707 lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
708 lv_submit_jainrvctp := 'Y';
709 GOTO end_of_trigger;
710 ELSE
711 RETURN;
712 END IF;
713 END IF;
714
715 v_shipment_header_id := pr_new.shipment_header_id;
716
717 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
718 -- IFpr_new.attribute_category IS NULL AND :
719 -- commented, Harshita for bug #4285064
720 -- 'RETURN TO RECEIVING' added by ssumaith - bug#3633666
721 IF pr_new.transaction_type IN ('RECEIVE', 'UNORDERED','DELIVER','RETURN TO RECEIVING') -- DELIVER added by sriram - Bug # 2881674 (RMA Accounting Entries )
722 THEN
723
724 For head_rec IN (SELECT attribute1,
725 attribute2,
726 attribute3,
727 attribute4,
728 attribute5, --ashish for bug # 2613817
729 receipt_source_code,
730 attribute_category
731 FROM rcv_shipment_headers
732 WHERE shipment_header_id = v_shipment_header_id)
733 LOOP
734
735 IF head_rec.attribute_category = 'India Receipt' THEN
736 pr_new.attribute_category := nvl(pr_new.attribute_category, head_rec.attribute_category);
737 pr_new.attribute1 := nvl(pr_new.attribute1, head_rec.attribute1) ; -- nvl conditions added by Harshita for bug #4285064
738 pr_new.attribute2 := nvl(pr_new.attribute2, head_rec.attribute2) ;
739 pr_new.attribute3 := nvl(pr_new.attribute3, head_rec.attribute3) ;
740 pr_new.attribute4 := nvl(pr_new.attribute4, head_rec.attribute4) ;
741 pr_new.attribute5 := nvl(pr_new.attribute5, head_rec.attribute5) ; --ashish for bug # 2613817
742 v_receipt_source_code := head_rec.receipt_source_code;--ashish for bug # 2613817
743 ELSIF head_rec.attribute_category = 'India RMA Receipt' THEN
744 pr_new.attribute_category := nvl(pr_new.attribute_category, head_rec.attribute_category) ;
745 pr_new.attribute1 := nvl(pr_new.attribute1, head_rec.attribute1) ; -- -- nvl conditions added by Harshita for bug #4285064
746 pr_new.attribute2 := nvl(pr_new.attribute2, head_rec.attribute2) ;
747 pr_new.attribute3 := nvl(pr_new.attribute3, head_rec.attribute3) ;
748 pr_new.attribute4 := nvl(pr_new.attribute4, head_rec.attribute4) ;
749 pr_new.attribute5 := nvl(pr_new.attribute5, head_rec.attribute5) ; -- sriram for 'India RMA Receipt' attribute5 was not getting copied -- Bug # 2881674 (RMA Accounting Entries ).
750 END IF;
751
752 END LOOP;
753 END IF;
754
755 if pr_new.attribute_category = 'India Return to Vendor' or pr_new.transaction_type = 'RETURN TO VENDOR' then
756 pr_new.attribute4 :=pr_new.attribute4;
757 pr_new.attribute_category:= 'India Return to Vendor';
758 end if;
759
760 -- code added in Bug#3683666 is a problem if Customer has their own DFFs. So,
761 -- following condition added by Vijay Shankar for Bug#3959765 by modifying the code added in Bug#3683666
762 IF pr_new.source_document_code = 'RMA' AND
763 pr_new.attribute_category IS NULL AND
764 pr_new.attribute2 is NOT NULL
765 THEN
766 pr_new.attribute2 := null;
767 END IF;
768
769 --ashish for bug # 2613817
770 IF pr_new.attribute_category <> 'India RMA Receipt'
771 AND v_receipt_source_code in ('VENDOR','INTERNAL ORDER')
772 AND pr_new.attribute15 is null
773 AND pr_new.interface_source_code is null
774 THEN
775 pr_new.attribute15 := pr_new.attribute5;
776 pr_new.attribute5 := null;
777 END IF;
778 -- End ashish for bug # 2613817
779 */
780
781 -- if pr_new.transaction_type NOT IN ('CORRECT', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR') then -- Vijay Shankar for Bug#3940588
782 -- following IF condition added by Vijay Shankar for Bug#3940588
783 if pr_new.transaction_type IN ('RECEIVE', 'UNORDERED', 'MATCH',
784 'RETURN TO VENDOR') -- RTV is added in the list just for validation checking in the calling procedure
785 then
786
787 jai_rcv_tax_pkg.default_taxes_onto_line(
788 pr_new.transaction_id,
789 pr_new.parent_transaction_id,
790 pr_new.shipment_header_id,
791 pr_new.shipment_line_id,
792 pr_new.organization_id,
793 pr_new.requisition_line_id,
794 pr_new.quantity,
795 pr_new.primary_quantity,
796 pr_new.po_line_location_id,
797 pr_new.transaction_type,
798 pr_new.source_document_code,
799 pr_new.destination_type_code,
800 pr_new.subinventory,
801 pr_new.vendor_id,
802 pr_new.vendor_site_id,
803 pr_new.po_header_id,
804 pr_new.po_line_id,
805 pr_new.location_id,
806 pr_new.transaction_date,
807 pr_new.uom_code,
808 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. pr_new.attribute1,
809 --to_date(pr_new.attribute2,'YYYY/MM/DD HH24:MI:SS'),
810 --pr_new.attribute3,
811 --pr_new.attribute4,
812 pr_new.attribute15,
813 pr_new.currency_code,
814 pr_new.currency_conversion_type,
815 pr_new.currency_conversion_date,
816 pr_new.currency_conversion_rate,
817 pr_new.creation_date,
818 pr_new.created_by,
819 pr_new.last_update_date,
820 pr_new.last_updated_by,
821 pr_new.last_update_login,
822 pr_new.unit_of_measure,
823 pr_new.po_distribution_id,
824 pr_new.oe_order_header_id,
825 pr_new.oe_order_line_id,
826 pr_new.routing_header_id
827 -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
828 -- , v_chk_form -- commented by ssumaith - R12-PADDR
829 -- Vijay Shankar for Bug#4159557
830 , pr_new.interface_source_code
831 , pr_new.interface_transaction_id
832 , lv_allow_tax_change_hook
833 );
834 end if;
835
836 pr_new.attribute15 := null;
837
838 -- added by Vijay Shankar for Bug#3940588
839 -- following is to take care that the old code will not be executed because
840 UPDATE JAI_RCV_LINES
841 SET
842 process_receiving = 'X', --DECODE(process_receiving, 'Y', 'Y', v_process_receiving),
843 process_delivery = 'X', --DECODE(process_delivery, 'Y', 'Y', v_process_delivery),
844 process_third_party_inv = 'X', --DECODE(process_third_party_inv, 'Y', 'Y', v_process_third_party_inv),
845 process_modvat = 'X', --DECODE(process_modvat, 'Y', 'Y', v_process_modvat),
846 process_rg = 'X', --DECODE(process_rg, 'Y', 'Y', v_process_rg),
847 process_populate_cenvat = 'X', --DECODE(process_populate_cenvat,'Y','Y',v_process_populate_cenvat) --Changed by Nagaraj.s for Bug3036825
848 process_rtr = 'X',
849 process_rtv = 'X'
850 -- this update is to take care that the RECEIPT line is of MATCH and not of UNORDERED
851 , transaction_id = decode(pr_new.transaction_type,'MATCH', pr_new.transaction_id, transaction_id)
852 ,last_update_date = sysdate
853 ,last_updated_by = fnd_global.user_id
854 WHERE shipment_line_id = pr_new.shipment_line_id;
855
856 /* following added as part of VAT Impl. Changes */
857 IF pr_new.transaction_type = 'MATCH' THEN
858 UPDATE JAI_RCV_LINE_TAXES
859 SET transaction_id = pr_new.transaction_id
860 , last_update_date = sysdate
861 , last_updated_by = fnd_global.user_id
862 WHERE shipment_line_id = pr_new.shipment_line_id
863 and (transaction_id is null or transaction_id <> pr_new.transaction_id);
864 END IF;
865
866 /* Start, Vijay Shankar for Bug# 3496408
867 "MATCH" is added in the following if condition by Vijay Shankar for Bug#3940588
868 */
869 IF pr_new.transaction_type in ( 'RECEIVE', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'CORRECT', 'MATCH') THEN
870 BEGIN
871 pv_return_code := jai_constants.successful ;
872
873 lv_process_mode := FND_PROFILE.value('JA_IN_RCP_TP_MODE');
874
875 OPEN c_receipt_line(pr_new.shipment_line_id);
876 FETCH c_receipt_line INTO lv_tax_modified_flag;
877 CLOSE c_receipt_line;
878
879 -- Code modified by Vijay Shankar for Bug#3940588. Refer to Previous version for changes
880 -- Incase of transactions Other than CORRECT, the request has to fired always
881 if ( lv_process_mode = '1' and pr_new.transaction_type = 'CORRECT') -- '1' Indicates Online Mode
882 OR ( pr_new.transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR'
883 , 'DELIVER')
884 -- OR (pr_new.transaction_type = 'DELIVER' AND (lv_tax_modified_flag='N' OR pr_new.routing_header_id <> 3)) -- Vijay Shankar for Bug#3940588
885 )
886 -- following condition added by Vijay Shankar for Bug#3940588. this piece of condition is copied from jai_rcv_tax_pkg.default_taxes_onto_line procedure
887 -- OR ( v_chk_form IS NULL AND pr_new.transaction_type IN ('RECEIVE', 'ASBN') )
888 -- following added by Vijay Shankar for Bug#4159557 by commenting the above chk
889 OR ( v_chk_form IS NULL AND pr_new.transaction_type IN ('ASBN') )
890 --OR ( pr_new.transaction_type = 'RECEIVE' AND nvl(lv_tax_modified_flag, 'N') <> 'Y' )
891 --commented the above and added the below by Ramananda for Bug#4565478
892 OR ( pr_new.transaction_type = 'RECEIVE' AND nvl(lv_tax_modified_flag, 'N') = 'N' )
893 then
894
895 lv_request_desc := 'India - Receiving Transaction Processor for '|| initcap(pr_new.transaction_type);
896
897 /* IF v_chk_form IS NULL AND pr_new.transaction_type IN ('RECEIVE', 'ASBN') THEN */
898 /* above condition modified as below by Vijay Shankar for Bug#4250236(4245089) as part of VAT Impl. */
899 IF ( v_chk_form IS NULL AND pr_new.transaction_type IN ('ASBN') )
900 --OR ( pr_new.transaction_type = 'RECEIVE' AND nvl(lv_tax_modified_flag, 'N') <> 'Y' )
901 --commented the above and added the below by Ramananda for Bug#4565478
902 OR ( pr_new.transaction_type = 'RECEIVE' AND nvl(lv_tax_modified_flag, 'N') = 'N' )
903 THEN
904 lv_called_from := 'JAINPORE';
905 ELSE
906 lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
907 END IF;
908
909 /* Vijay Shankar for Bug#4250171 */
910 lv_submit_jainrvctp := 'Y';
911
912 end if;
913
914 EXCEPTION
915 WHEN OTHERS THEN
916 /* RAISE_APPLICATION_ERROR( -20100,'Localization Correction errored -> ' || SQLERRM);
917 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Localization Correction errored -> ' || SQLERRM ; return ;
918 END;
919 -- added by Vijay Shankar for Bug#3940588
920 if pr_new.transaction_type IN ('CORRECT', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'DELIVER') then
921 -- previous code which is at the bottom of trigger is brought here
922
923 /* Commented by rallamse bug#4479131 PADDR Elimination
924 if pr_new.transaction_type = 'RETURN TO VENDOR' THEN
925 UPDATE JAI_CMN_LOCATORS_T
926 SET row_id = v_rowid
927 WHERE FORM_NAME = 'JAINRTVN';
928 end if;
929 */
930 --Added by Sanjikum for Bug #4105721
931
932 IF pr_new.transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR') THEN
933
934 OPEN c_mtl_trx(pr_new.organization_id);
935 FETCH c_mtl_trx INTO r_mtl_trx;
936 CLOSE c_mtl_trx;
937
938 IF r_mtl_trx.process_enabled_flag = 'Y' THEN
939 pr_new.po_unit_price := get_deliver_unit_price(pr_new.shipment_line_id);
940 END IF;
941 END IF;
942
943 /* commented by Vijay Shankar for Bug#4250171
944 return;
945 */
946 end if;
947
948 end if;
949 -- End, Vijay Shankar for Bug# 3496408
950
951 <<end_of_trigger>>
952
953 IF lv_submit_jainrvctp = 'Y' THEN
954
955 fnd_profile.get('RCV_TP_MODE',lv_profile_val); --added by csahoo for bug#6071528
956 /*Added by CSahoo, BUG 5344225*/
957 IF lv_profile_val <> 'BATCH' THEN
958
959 IF
960 ( jai_rcv_trx_processing_pkg.gv_shipment_header_id is null
961 OR
962 (jai_rcv_trx_processing_pkg.gv_shipment_header_id is not null
963 AND
964 NVL(jai_rcv_trx_processing_pkg.gv_shipment_header_id,0) <> pr_new.shipment_header_id)
965 )
966 or
967 ( jai_rcv_trx_processing_pkg.gv_group_id is null
968 OR
969 (jai_rcv_trx_processing_pkg.gv_group_id is not null
970 AND
971 NVL(jai_rcv_trx_processing_pkg.gv_group_id,0) <> pr_new.group_id)
972 )
973 THEN
974
975 IF lv_profile_val = 'IMMEDIATE' THEN
976 lv_request_id := fnd_global.conc_request_id ;
977 ELSIF lv_profile_val = 'ONLINE' THEN
978 lv_group_id := pr_new.group_id ;
979 END IF ;
980
981
982 /* END BUG 5344225*/
983
984 lv_result := FND_REQUEST.set_mode(true);
985
986
987 -- Date 03/06/2007 by sacsethi for bug 6078460
988 -- Following parameter is commented
989 -- p_transaction_type , p_shipment_line_id , p_transaction_id
990
991
992 lv_req_id := FND_REQUEST.submit_request(
993 'JA', 'JAINRVCTP', lv_request_desc, '', FALSE,
994 pr_new.organization_id, -- p_organization_id (number)
995 '', -- p_transaction_from (date)
996 '', -- p_transaction_to (date)
997 '', -- p_transaction_type
998 '', -- p_parent_type
999 pr_new.shipment_header_id, -- p_shipment_header_id
1000 '', -- p_receipt_num
1001 '', -- p_shipment_line_id
1002 '', -- p_transaction_id
1003 'Y', -- p_commit_switch -> indicates whether to commit the data or not
1004 lv_called_from, -- p_called_from
1005 'N', -- p_simulate_flag
1006 'N', -- p_trace_switch
1007 CHR(0), '', '', '', '', '', '', '', '',
1008 '', '', '', '', '', '', '', '', '', '',
1009 '', '', '', '', '', '', '', '', '', '',
1010 '', '', '', '', '', '', '', '', '', '',
1011 '', '', '', '', '', '', '', '', '', '',
1012 '', '', '', '', '', '', '', '', '', '',
1013 '', '', '', '', '', '', '', '', '', '',
1014 '', '', '', '', '', '', '', '', '', '',
1015 '', '', '', '', '', '', '', ''
1016 );
1017 jai_rcv_trx_processing_pkg.gv_shipment_header_id := pr_new.shipment_header_id ;
1018 jai_rcv_trx_processing_pkg.gv_group_id := pr_new.group_id ;
1019 END IF;
1020 END IF;
1021
1022
1023 END IF;
1024
1025 END BRI_T1 ;
1026
1027 END JAI_RCV_RT_TRIGGER_PKG ;
[Home] [Help]