1 PACKAGE BODY JAI_PO_CST_TRIGGER_PKG AS
2 /* $Header: jai_po_cstg_t.plb 120.0.12010000.2 2008/11/21 07:16:42 srjayara ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME BRI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_PO_CSGT_BRIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_PO_CSGT_BRI_T1
11 REM
12 REM +======================================================================+
13 */
14 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
15
16 ln_transaction_id RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
17 ln_accrual_amount JAI_RCV_REP_ACCRUAL_T.ACCRUAL_AMOUNT%TYPE;
18 ln_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
19 lv_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE;
20
21 CURSOR cur_get_setOfBooksId
22 (cpn_operating_unit_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE,
23 cpv_information_context HR_ORGANIZATION_INFORMATION.ORG_INFORMATION_CONTEXT%TYPE
24 )
25 IS
26 SELECT to_number(o3.org_information3) set_of_books_id
27 FROM hr_organization_information o3
28 WHERE organization_id = cpn_operating_unit_id
29 AND o3.org_information_context = cpv_information_context;
30
31 CURSOR cur_get_accrual_amount
32 (cpn_transaction_id JAI_RCV_REP_ACCRUAL_T.TRANSACTION_ID%TYPE)
33 IS
34 SELECT accrual_amount
35 FROM jai_rcv_rep_accrual_t
36 WHERE transaction_id = cpn_transaction_id;
37
38 ln_transaction_qty RCV_TRANSACTIONS.QUANTITY%TYPE;
39
40 CURSOR cur_get_transaction_qty (cpn_transaction_id RCV_TRANSACTIONS.TRANSACTION_ID%TYPE)
41 IS
42 SELECT quantity
43 FROM rcv_transactions
44 WHERE transaction_id = cpn_transaction_id;
45
46 BEGIN
47 pv_return_code := jai_constants.successful ;
48 /*------------------------------------------------------------------------------------------
49 FILENAME: jai_po_csg_bri_t.sql
50 CHANGE HISTORY:
51
52 S.No Date Author and Details
53 1 21-NOV-2008 Bug 7565737 - India Localization Taxes are getting doubled for invoices.
54 Reason - Receipt matched invoices are also populated with rcv_transaction_id.
55 Fix - Added a check to process only the rows with invoice_distribution_id as
56 NULL.
57
58 Future Dependencies For the release Of this Object:-
59 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
60 A datamodel change )
61 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
62 Current Version Current Bug Dependent Files Version Author Date Remarks
63 Of File On Bug/Patchset Dependent On
64
65
66 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
67
68 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
69
70 OPEN cur_get_setOfBooksId (cpn_operating_unit_id => pr_new.operating_unit_id,
71 cpv_information_context => 'Operating Unit Information'
72 );
73 FETCH cur_get_setOfBooksId INTO ln_set_of_books_id;
74 CLOSE cur_get_setOfBooksId;
75
76 --IF jai_cmn_utils_pkg.check_jai_exists
77 -- ( p_calling_object => 'JAI_PO_CSGT_BRI_T1',
78 -- p_set_of_books_id => ln_set_of_books_id) = FALSE THEN
79 -- RETURN;
80 -- END IF;
81
82 ln_transaction_id := pr_new.rcv_transaction_id;
83
84 /* IF condition added for bug 7567537 - amount should be updated only for
85 * transactions from PO source*/
86 IF pr_new.invoice_distribution_id IS NULL AND pr_new.write_off_id IS NULL
87 THEN
88 OPEN cur_get_accrual_amount (cpn_transaction_id => ln_transaction_id);
89 FETCH cur_get_accrual_amount INTO ln_accrual_amount;
90 CLOSE cur_get_accrual_amount;
91
92 OPEN cur_get_transaction_qty (cpn_transaction_id => ln_transaction_id);
93 FETCH cur_get_transaction_qty into ln_transaction_qty;
94 CLOSE cur_get_transaction_qty;
95
96 IF NVL( ln_transaction_qty, 0) = 0 THEN
97 RETURN;
98 END IF;
99
100 IF ln_transaction_qty <> ABS(pr_new.quantity) THEN
101 ln_accrual_amount := ln_accrual_amount
102 * (ABS(pr_new.quantity) / ln_transaction_qty);
103 END IF;
104
105 IF NVL(pr_new.quantity,0) < 0 then
106 ln_accrual_amount := - ln_accrual_amount;
107 END IF;
108
109 IF NVL(ln_accrual_amount, 0) <> 0 THEN
110 IF NVL(pr_new.amount, 0) <> 0 THEN
111 pr_new.amount := pr_new.amount + ln_accrual_amount;
112 END IF;
113
114 IF NVL(pr_new.entered_amount, 0) <> 0
115 AND NVL(pr_new.currency_code,'X') <> jai_constants.func_curr THEN
116 pr_new.entered_amount := pr_new.entered_amount
117 + (ln_accrual_amount * pr_new.currency_conversion_rate);
118 END IF;
119 END IF;
120 END IF;
121 END BRI_T1 ;
122
123 END JAI_PO_CST_TRIGGER_PKG ;