1 PACKAGE BODY JAI_OPM_GMD_TRIGGER_PKG AS
2 /* $Header: jai_opm_gmd_t.plb 120.1.12010000.3 2009/01/02 07:03:55 sguduru ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_OPM_GMD_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_OPM_GMD_ARU_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARU_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
16 Cursor C_chk_hdr IS
17 SELECT po_id
18 FROM JAI_OPM_OSP_HDRS
19 WHERE osp_header_id = pr_new.batch_id ;
20
21 Cursor C_batch_no is
22 SELECT batch_no, batch_status --, wip_whse_code
23 , ja_osp_batch
24 from gme_batch_header
25 WHERE batch_id =pr_new.batch_id;
26
27 Cursor C_Rec_Exist IS SELECT rowid, unit_price FROM JAI_OPM_OSP_DTLS
28 WHERE osp_header_id = pr_new.batch_id
29 AND organization_id = pr_new.organization_id
30 AND inventory_item_id = pr_new.inventory_item_id;
31
32 cursor c_whse_orgn(l_whse_code varchar2) is
33 select organization_id
34 from mtl_secondary_inventories
35 where secondary_inventory_name = l_whse_code;
36
37 Cursor C_Excise_Rate(P_Form_Name varchar2) IS
38 SELECT rate
39 FROM JAI_OPM_OSP_EXC_RATES
40 WHERE form_name = P_Form_Name;
41
42 Cursor C_Form_name IS
43 SELECT form_name FROM JAI_OPM_OSP_HDRS
44 WHERE osp_header_id = pr_new.batch_id;
45
46 Cursor C_Inv_Ind(cp_organization_id number, cp_inventory_item_id number) IS
47 select decode(inventory_item_flag, 'Y', 0, 1) noninv_ind
48 from mtl_system_items
49 where organization_id = cp_organization_id
50 and inventory_item_id = cp_inventory_item_id;
51 --SELECT noninv_ind FROM ic_item_mst
52 --WHERE item_id = p_item_id;
53
54 CURSOR c_get_location_id (cpn_po_id number)
55 IS
56 SELECT ship_to_location_id location_id
57 FROM po_headers_all
58 WHERE po_header_id = cpn_po_id;
59
60
61
62 l_rowid VARCHAR2(40);
63 l_updated_by NUMBER;
64 l_osp_dtl_id NUMBER(15);
65 l_iss_rcpt_flag VARCHAR2(1);
66 l_btch_stat NUMBER(1);
67 l_rcpt_qty NUMBER;
68 l_orgn_code Varchar2(4);
69 l_location_id NUMBER;
70 -- l_whse_code Varchar2(4);
71 l_form_name Varchar2(16);
72 l_unit_price NUMBER;
73 l_excise_rate NUMBER;
74 l_excise_payable NUMBER;
75 l_noninv_ind Number(1);
76 l_whse_orgn varchar2(4);
77 l_batch_no varchar2(32);
78 l_ja_osp_batch gme_batch_header.ja_osp_batch%type;
79
80 v_file_name varchar2(30);
81 ln_po_id number;
82
83 BEGIN
84 pv_return_code := jai_constants.successful ;
85 /*------------------------------------------------------------------------------------------------------------
86 Change History :
87 ------------------------------------------------------------------------------------------------------------
88 1 26/04/2003 Sriram - bug # 2808732
89 With OPM J , major changes have been made in the way base apps behaves with respect to the production batch cycle.
90 The values of batch status which was 3 earlier is now 2 . Hence the code which was to get executed when the status was
91 3 should now get executed when the status is 2 . This change has been done in the trigger and commented apropriately.
92
93 2 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
94 as required for CASE COMPLAINCE. Version 116.1
95
96 3. 13-Jun-2005 File Version: 116.2
97 Ramananda for bug#4428980. Removal of SQL LITERALs is done
98 4 14/07/2005 4485801 Brathod, File Version 117.1
99 Issue: Inventory Convergence Uptake for R12 Initiative
100
101 5. 04-Jan-2006 rallamse bug#4924272 File Version 120.1
102 Issue : IF C_chk_hdr%NOTFOUND check after the statement CLOSE C_chk_hdr;
103 Fix: Moved the CLOSE C_chk_hdr after the check mentioned above.
104
105 6 . 3-Dec-2008 Changes by nprashar for bug 7540543, During insert into table JAI_OPM_OSP_DETAILS,
106 for columns UOM_CODE,PLAN_QUANTITY dtl_num, wip_plan_quantity are now being used.
107 ------------------------------------------------------------------------------------------------------------- */
108
109 OPEN C_chk_hdr;
110 FETCH C_chk_hdr INTO ln_po_id;
111 /* rallamse bug#4924272 Moved the if condition before the CLOSE C_chk_hdr */
112 IF C_chk_hdr%NOTFOUND THEN
113 CLOSE C_chk_hdr;
114 RETURN;
115 END IF;
116 CLOSE C_chk_hdr;
117
118
119 v_file_name := 'ompt117_osp.log';
120
121 OPEN C_batch_no;
122 FETCH C_batch_no INTO l_batch_no,l_btch_stat, l_ja_osp_batch;
123 CLOSE C_batch_no;
124
125 IF ( nvl(l_ja_osp_batch, -999) <> 1 ) THEN
126 RETURN;
127 END IF;
128
129 OPEN C_whse_orgn(pr_new.subinventory);
130 FETCH C_whse_orgn INTO l_orgn_code;
131 CLOSE C_whse_orgn;
132
133 IF NVL(pr_old.actual_qty,0) < pr_new.actual_qty THEN
134 OPEN C_Rec_Exist;
135 FETCH C_Rec_Exist INTO l_rowid, l_unit_price;
136 CLOSE C_Rec_Exist;
137
138 l_updated_by := pr_new.last_updated_by;
139
140 OPEN C_Inv_Ind(pr_new.organization_id, pr_new.inventory_item_id);
141 FETCH C_Inv_Ind INTO l_noninv_ind;
142 CLOSE C_Inv_Ind;
143
144 IF l_btch_stat = 2 AND pr_new.line_type = -1 THEN
145 IF l_rowid IS NOT NULL THEN
146
147 IF NVL(l_unit_price,0) > 0 THEN
148
149 OPEN C_Form_name;
150 FETCH C_Form_name INTO l_form_name;
151 CLOSE C_Form_name ;
152
153 OPEN C_Excise_Rate(l_form_name) ;
154 FETCH C_Excise_Rate INTO l_Excise_Rate ;
155 CLOSE C_Excise_Rate ;
156
157 IF l_excise_rate IS NULL THEN
158 l_excise_rate := 10;
159 END IF;
160
161 END IF;
162 l_excise_payable := ((NVL(l_unit_price,0)*pr_new.actual_qty*nvl(l_excise_rate,0))/100);
163
164 UPDATE JAI_OPM_OSP_DTLS
165 SET act_quantity = pr_new.actual_qty,
166 excise_payable = l_excise_payable,
167 last_updated_by = l_updated_by,
168 last_update_date = sysdate
169 WHERE rowid = l_rowid;
170 END IF;
171 ELSIF l_btch_stat IN (3,4) AND pr_new.line_type = -1 AND l_noninv_ind = 0 THEN
172 /* RAISE_APPLICATION_ERROR('-20009','Actual Quantity can not be changed'); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Actual Quantity can not be changed' ; return ;
173 ELSIF l_btch_stat = 2 AND pr_new.line_type = 1 THEN -- made the comparison against the l_btch_stat to 3 from 2 for OPM J completion - sriram - bug # 2808732
174 l_rcpt_qty := pr_new.actual_qty - pr_old.actual_qty;
175
176 OPEN c_get_location_id (ln_po_id);
177 FETCH c_get_location_id INTO l_location_id;
178 CLOSE c_get_location_id;
179
180 INSERT INTO JAI_OPM_OSP_DTLS (OSP_DETAIL_ID,
181 OSP_HEADER_ID,
182 TRANS_DATE,
183 UOM_CODE,
184 PLAN_QUANTITY ,
185 ACT_QUANTITY,
186 UNIT_PRICE,
187 MODVAT_CLAIMED,
188 EXCISE_PAYABLE,
189 ISSUE_RECPT_FLAG,
190 CREATION_DATE,
191 CREATED_BY,
192 LAST_UPDATED_BY ,
193 LAST_UPDATE_DATE,
194 LAST_UPDATE_LOGIN,
195 PROCESSING_CHARGE,
196 MAIN_RCPT_FLAG,
197 inventory_item_id,
198 organization_id)
199 VALUES ( JAI_OPM_OSP_DTLS_S1.NEXTVAL,
200 pr_new.batch_id,
201 sysdate,
202 /* Commented by nprashar for bug 7540543
203 pr_new.item_um,
204 pr_new.plan_qty,
205 */
206 pr_new.dtl_um, /*Added by nprashar for bug 7540543*/
207 pr_new.wip_plan_qty, /*Added by nprashar for bug 7540543*/
208 l_rcpt_qty ,
209 null,
210 null,
211 null,
212 'R',
213 SYSDATE,
214 l_updated_by,
215 l_updated_by,
216 SYSDATE,
217 null,
218 null,
219 'N',
220 pr_new.inventory_item_id,
221 pr_new.organization_id
222 ) returning OSP_HEADER_ID into l_osp_dtl_id;
223
224 jai_cmn_rg_opm_pkg.create_rg_i_entry(
225 l_location_id,
226 pr_new.batch_id,
227 sysdate,
228 l_rcpt_qty ,
229 pr_new.item_um,
230 l_updated_by,
231 pr_new.organization_id,
232 pr_new.inventory_item_id
233 );
234
235 ELSIF l_btch_stat = 4 AND pr_new.line_type = 1 THEN
236 /* RAISE_APPLICATION_ERROR('-20011','Actual Output Quantity can not be changed');
237 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Actual Output Quantity can not be changed' ; return ;
238 END IF;
239 ELSIF pr_old.actual_qty > pr_new.actual_qty THEN
240 /* RAISE_APPLICATION_ERROR('-20012','You can not reduce the Actual Quantity');
241 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'You can not reduce the Actual Quantity' ; return ;
242 END IF ;
243 END ARU_T1 ;
244
245 END JAI_OPM_GMD_TRIGGER_PKG ;
[Home] [Help]