1 PACKAGE BODY JAI_JRG_23AC1_TRIGGER_PKG AS
2 /* $Header: jai_jrg_23ac1_t.plb 120.4 2012/05/23 07:49:08 zxin ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME BRI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_JRG_23AC1_BRIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_JRG_23AC1_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 This cursor is to fetch the Information regarding vendor change from JAI_RCV_CENVAT_CLAIMS.
17 */
18
19 CURSOR c_fetch_vendor_info(cp_transaction_type rcv_transactions.transaction_type%type)
20 IS
21 SELECT
22 vendor_changed_flag ,
23 vendor_id ,
24 vendor_site_id
25 FROM
26 JAI_RCV_CENVAT_CLAIMS
27 WHERE
28 transaction_id in
29 (
30 SELECT
31 transaction_id
32 FROM
33 rcv_transactions
34 WHERE
35 transaction_type = cp_transaction_type /* 'RECEIVE' --Ramananda for removal of SQL LITERALs */
36 START WITH
37 transaction_id = pr_new.receipt_ref
38 CONNECT BY PRIOR
39 parent_transaction_id = transaction_id
40 );
41
42 --Variable definitions
43 v_vendor_change JAI_RCV_CENVAT_CLAIMS.vendor_changed_flag%type ; /* To hold the Flag value whether vendor is changed or not. */
44 v_vendor_id JAI_RCV_CENVAT_CLAIMS.vendor_id%type ; /* To hold the vendor id of ja_in_receipt_cenvat. */
45 v_vendor_site_id JAI_RCV_CENVAT_CLAIMS.vendor_site_id%type ; /* To hold the vendor site id of ja_in_receipt_cenvat. */
46 v_range_no JAI_CMN_VENDOR_SITES.excise_duty_range%type ;
47 v_division_no JAI_CMN_VENDOR_SITES.excise_duty_division%type ;
48
49 /*Bug 9122545*/
50 CURSOR c_org_addl_rg_flag(cp_organization_id jai_cmn_inventory_orgs.organization_id%TYPE,
51 cp_location_id jai_cmn_inventory_orgs.location_id%TYPE)
52 IS
53 SELECT nvl(allow_negative_rg_flag,'N')
54 FROM jai_cmn_inventory_orgs
55 WHERE organization_id = cp_organization_id
56 AND location_id = cp_location_id;
57
58 lv_allow_negative_rg_flag jai_cmn_inventory_orgs.allow_negative_rg_flag%TYPE;
59 /*end bug 9122545*/
60
61 BEGIN
62 pv_return_code := jai_constants.successful ;
63 /*********************************************************************************************************************************************************************
64
65 Created By : Aiyer
66
67 Creation Date : 21-Jul-2003
68
69 Enhancement Bug : 3025626
70
71 Purpose : Modify the vendor_id, vendor_site_id, Range and Division information in JAI_CMN_RG_23AC_I_TRXS table from the
72 JAI_RCV_CENVAT_CLAIMS table when a third party supplier information has been registered through the
73 Claim Modvat On Receipt form.
74
75 Dependency : - The following dependency has been created in this bug
76 1. Technical dependency due to datamodel change :-
77 3 new fields VENDOR_CHANGED_FLAG,VENDOR_ID and VENDOR_SITE_ID have
78 been added to the table JAI_RCV_CENVAT_CLAIMS and the view JAI_RCV_CLAIM_MODVAT_V
79 (base view of the form JAINMVAT.fmb) has been modified to add vendor_changed_flag
80 and vendor_site_id. Also the way the vendor_id is fetched in the view has also been changed.
81
82 2. Functional Dependency:-
83 The form JAINMVAT, with this enhancement, has the capability to capture the third party vendor
84 information.
85 For this 3 new fields vendor_changed_flag , dsp_vendor_site_code and vendor_site_id have been added to the form.
86 Another 2 new triggers ja_in_rg23_part_ii_bi_trg and ja_in_pla_bi_trg.sql have been created as a part of this enhancement bug.
87
88 Change History :
89
90
91 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
92 DB Entity as required for CASE COMPLAINCE. Version 116.1
93
94 2. 13-Jun-2005 File Version: 116.2
95 Ramananda for bug#4428980. Removal of SQL LITERALs is done
96
97 3. 27-Nov-2009 Bug 9122545 File version 120.0.12000000.2 / 120.0.12010000.2 / 120.1
98 Checked the setup option to allow negative balance in quantity register, and throw an error
99 only when the option is N and the register balance is negative.
100 4. 04-JUN-2010 vkaranam for bug#9755875
101 Issue:
102 TST1213.XB2.QA:CENVAT CREDIT NOT AVBL FOR RECEIPT CREATED FROM RMA IF RG BAL NEG
103 Fix:
104 allow -ve balance in qty register shall be checked only for issue type of transactions.
105 hence added the transaction type check.
106
107 Future Dependencies For the release Of this Object:-
108 (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/
109 A datamodel change )
110
111 ----------------------------------------------------------------------------------------------------------------------------------------------------
112 Current Version Current Bug Dependent Files Version Author Date Remarks
113 Of File On Bug/Patchset Dependent On
114 ja_in_rg23_part_i_bi_trg.sql
115 ----------------------------------------------------------------------------------------------------------------------------------------------------
116 616.1 3025626 IN60104D1+3025626 1. ja_3025626_alter.sql 616.1 Aiyer 21/07/2003 Enhancement, Introduced data
117 \ 2. JAI_RCV_CLAIM_MODVAT_V.sql 616.1 model changes in table JAI_RCV_CENVAT_CLAIMS,
118 model changes in table JAI_RCV_CENVAT_CLAIMS
119 3. ja_in_pla_bi_trg.sql 616.1 alterations in view JAI_RCV_CLAIM_MODVAT_V,
120 4. ja_in_rg23_part_ii_bi_trg.sql 616.1 added 3 new fields in form JAINMVAT.fmb and
121 5. JAINMVAT.fmb 616.4 2 new triggers - ja_in_pla_bi_trg.sql and
122 6. JAF23A_1.rdf 616.1 ja_in_rg23_part_ii_bi_trg.sql. All present in patch 3025626.
123
124 **********************************************************************************************************************************************************************/
125 --added the below if condition for bug#9755875
126 if pr_new.transaction_type IN ('I', 'IA', 'IOI', 'PI','RTV')
127 then
128 /*bug 9122545*/
129 OPEN c_org_addl_rg_flag(pr_new.organization_id, pr_new.location_id);
130 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag;
131 CLOSE c_org_addl_rg_flag;
132
133 IF lv_allow_negative_rg_flag = 'N'
134 THEN
135 IF pr_new.closing_balance_qty < 0
136 THEN
137 --Commented call by nprashar for bug # 13576122
138 /*raise_application_error(-20115,'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||
139 pr_new.register_type||','||pr_new.organization_id||','||pr_new.location_id);*/
140 --Added code for bug # 13576122
141 pv_return_code := 'X';
142 pv_return_message := 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'|| pr_new.register_type||','||pr_new.organization_id||','||pr_new.location_id;
143 goto end_of_program;
144 END IF;
145 END IF;
146 /*end bug 9122545*/
147 END IF;--bug#9755875
148
149 OPEN c_fetch_vendor_info('RECEIVE');
150 FETCH c_fetch_vendor_info INTO v_vendor_change ,
151 v_vendor_id ,
152 v_vendor_site_id;
153
154 IF c_fetch_vendor_info%FOUND THEN
155 IF nvl(v_vendor_change,'N') = 'N' THEN
156 CLOSE c_fetch_vendor_info;
157 return; /* The Trigger should not do anything when vendor,vendor site is not changed. */
158
159 ELSE /* Incase the count is present, then this table should have the changed vendor id and vendor site id.*/
160 /* Assigning the changed vendor values. */
161 pr_new.vendor_id := v_vendor_id ;
162 pr_new.vendor_site_id := v_vendor_site_id;
163
164 /* To bring the Division and Range of the changed vendor/vendorsite id. */
165 jai_rcv_utils_pkg.get_div_range
166 (
167 v_vendor_id ,
168 v_vendor_site_id ,
169 v_range_no ,
170 v_division_no
171 );
172 pr_new.range_no := v_range_no;
173 pr_new.division_no := v_division_no;
174 END IF;
175 END IF;
176 CLOSE c_fetch_vendor_info;
177 <<end_of_program>>
178 fnd_file.put_line(FND_FILE.LOG, 'End of program BRI_T1');
179 exception
180 when others THEN
181 /* raise_application_error(-20010,'Exception is raised in ja_in_rg23_part_i_bi_trg' || sqlerrm);
182 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Exception is raised in ja_in_rg23_part_i_bi_trg' || sqlerrm ; return ;
183 END BRI_T1 ;
184 END JAI_JRG_23AC1_TRIGGER_PKG ;
[Home] [Help]