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