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