1 PACKAGE BODY JAI_JRG_23AC1_TRIGGER_PKG AS
2 /* $Header: jai_jrg_23ac1_t.plb 120.0 2005/09/01 12:36:07 rallamse noship $ */
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 BEGIN
49 pv_return_code := jai_constants.successful ;
50 /*********************************************************************************************************************************************************************
51
52 Created By : Aiyer
53
54 Creation Date : 21-Jul-2003
55
56 Enhancement Bug : 3025626
57
58 Purpose : Modify the vendor_id, vendor_site_id, Range and Division information in JAI_CMN_RG_23AC_I_TRXS table from the
59 JAI_RCV_CENVAT_CLAIMS table when a third party supplier information has been registered through the
60 Claim Modvat On Receipt form.
61
62 Dependency : - The following dependency has been created in this bug
63 1. Technical dependency due to datamodel change :-
64 3 new fields VENDOR_CHANGED_FLAG,VENDOR_ID and VENDOR_SITE_ID have
65 been added to the table JAI_RCV_CENVAT_CLAIMS and the view JAI_RCV_CLAIM_MODVAT_V
66 (base view of the form JAINMVAT.fmb) has been modified to add vendor_changed_flag
67 and vendor_site_id. Also the way the vendor_id is fetched in the view has also been changed.
68
69 2. Functional Dependency:-
70 The form JAINMVAT, with this enhancement, has the capability to capture the third party vendor
71 information.
72 For this 3 new fields vendor_changed_flag , dsp_vendor_site_code and vendor_site_id have been added to the form.
73 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.
74
75 Change History :
76
77
78 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
79 DB Entity as required for CASE COMPLAINCE. Version 116.1
80
81 2. 13-Jun-2005 File Version: 116.2
82 Ramananda for bug#4428980. Removal of SQL LITERALs is done
83
84 Future Dependencies For the release Of this Object:-
85 (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/
86 A datamodel change )
87
88 ----------------------------------------------------------------------------------------------------------------------------------------------------
89 Current Version Current Bug Dependent Files Version Author Date Remarks
90 Of File On Bug/Patchset Dependent On
91 ja_in_rg23_part_i_bi_trg.sql
92 ----------------------------------------------------------------------------------------------------------------------------------------------------
93 616.1 3025626 IN60104D1+3025626 1. ja_3025626_alter.sql 616.1 Aiyer 21/07/2003 Enhancement, Introduced data
94 \ 2. JAI_RCV_CLAIM_MODVAT_V.sql 616.1 model changes in table JAI_RCV_CENVAT_CLAIMS,
95 model changes in table JAI_RCV_CENVAT_CLAIMS
96 3. ja_in_pla_bi_trg.sql 616.1 alterations in view JAI_RCV_CLAIM_MODVAT_V,
97 4. ja_in_rg23_part_ii_bi_trg.sql 616.1 added 3 new fields in form JAINMVAT.fmb and
98 5. JAINMVAT.fmb 616.4 2 new triggers - ja_in_pla_bi_trg.sql and
99 6. JAF23A_1.rdf 616.1 ja_in_rg23_part_ii_bi_trg.sql. All present in patch 3025626.
100
101 **********************************************************************************************************************************************************************/
102
103 OPEN c_fetch_vendor_info('RECEIVE');
104 FETCH c_fetch_vendor_info INTO v_vendor_change ,
105 v_vendor_id ,
106 v_vendor_site_id;
107
108 IF c_fetch_vendor_info%FOUND THEN
109 IF nvl(v_vendor_change,'N') = 'N' THEN
110 CLOSE c_fetch_vendor_info;
111 return; /* The Trigger should not do anything when vendor,vendor site is not changed. */
112
113 ELSE /* Incase the count is present, then this table should have the changed vendor id and vendor site id.*/
114 /* Assigning the changed vendor values. */
115 pr_new.vendor_id := v_vendor_id ;
116 pr_new.vendor_site_id := v_vendor_site_id;
117
118 /* To bring the Division and Range of the changed vendor/vendorsite id. */
119 jai_rcv_utils_pkg.get_div_range
120 (
121 v_vendor_id ,
122 v_vendor_site_id ,
123 v_range_no ,
124 v_division_no
125 );
126 pr_new.range_no := v_range_no;
127 pr_new.division_no := v_division_no;
128 END IF;
129 END IF;
130 CLOSE c_fetch_vendor_info;
131 exception
132 when others THEN
133 /* raise_application_error(-20010,'Exception is raised in ja_in_rg23_part_i_bi_trg' || sqlerrm);
134 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Exception is raised in ja_in_rg23_part_i_bi_trg' || sqlerrm ; return ;
135 END BRI_T1 ;
136 END JAI_JRG_23AC1_TRIGGER_PKG ;
[Home] [Help]