1 PACKAGE BODY JAI_OPM_GBH_TRIGGER_PKG AS
2 /* $Header: jai_opm_gbh_t.plb 120.0.12010000.2 2009/12/09 06:44:00 nprashar ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_OPM_GBH_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_OPM_GBH_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_Reg_Sel IS SELECT register_selected, po_id
17 FROM JAI_OPM_OSP_HDRS
18 WHERE osp_header_id = pr_new.batch_id ;
19
20 Cursor C_Vend_Id(p_po_id number) IS SELECT a. Vendor_id, a.ship_to_location_id, b.location_code FROM po_headers_all a, hr_locations_all b
21 WHERE po_header_id = p_po_id
22 AND a.SHIP_TO_LOCATION_ID = b.LOCATION_ID; /*Added ship_to_location_id for bug # 9088563*/
23
24 Cursor C_Osp_Rcpt_Dtl IS
25 SELECT organization_id, INVENTORY_ITEM_ID,act_quantity,uom_code
26 FROM JAI_OPM_OSP_DTLS
27 WHERE osp_header_id = pr_new.batch_id
28 and issue_recpt_flag = 'R';
29
30 Cursor C_Tot_Excise IS SELECT SUM(excise_payable) tot_excise_payable
31 FROM JAI_OPM_OSP_DTLS
32 WHERE osp_header_id = pr_new.batch_id and
33 issue_recpt_flag = 'I';
34
35 Cursor C_Act_Out_Qty IS SELECT SUM(act_quantity) tot_qty
36 FROM JAI_OPM_OSP_DTLS
37 WHERE osp_header_id = pr_new.batch_id and
38 issue_recpt_flag = 'R';
39
40 Cursor C_Plan_Out_Qty IS SELECT plan_quantity plan_qty
41 FROM JAI_OPM_OSP_DTLS
42 WHERE osp_header_id = pr_new.batch_id and
43 issue_recpt_flag = 'R';
44 /*Commented by Brathod for Inv.Convergence, Cursor is not used anywhere in the code*/
45
46 Cursor C_Osp_Ret_days(cpn_organization_id jai_cmn_inventory_orgs.organization_id%type) is
47 SELECT MAX(NVL(OSP_RETURN_DAYS,0))
48 from JAI_CMN_INVENTORY_ORGS --JAI_OPM_ORGANIZATIONS
49 WHERE organization_id = cpn_organization_id;
50
51 l_updated_by NUMBER;
52 l_reg_sel VARCHAR2(20);
53 l_po_id number(10);
54 l_vend_id number(10);
55 l_plan_qty Number;
56 l_act_tot_qty Number;
57 l_tot_excise_payable Number;
58 l_recv_qty Number;
59 l_location_code Varchar2(4);
60 return_days number;
61 l_location_id po_headers_all.ship_to_location_id%TYPE; /* Bug#9088563 */
62
63 /* Added by Ramananda for removal of SQL LITERALs */
64 lv_register JAI_OPM_OSP_HDRS.REGISTER_SELECTED%type ;
65 lv_tran_name JAI_OPM_TXN_EXTN_HDRS.transaction_name%type ;
66
67 BEGIN
68 pv_return_code := jai_constants.successful ;
69 /*--------------------------------------------------------------------------------------------
70 Change History
71 --------------------------------------------------------------------------------------------
72 1. Ssumaith - Bug # 2695779 - File Version 712.1
73 Changed the cursor definition C_Vend_Id so that it picks up based on discrete tables .Currently
74 it is using OPM tables . Since 11.5.7 , this change has been done.
75
76 2. Ssumaith - Bug # 2808732
77 Since OPM 11.5.8 (OPM I ) , the table is changed to GME_BATCH_HEADER instead of the
78 table pm_btch_hdr .
79
80 3. ssumaith - bug # 2959256
81 Major dependency issues arise out of this bug.New columns have been added to the JAI_OPM_OSP_HDRS
82 table.The columns added are fin_year, form_number.All further patches using this object will need
83 to have this as a pre-requisite.
84
85 4. ssumaith - Bug # 3015825
86 Aligning with the base apps changes in OPM I and OPM J , when the status of the batch changes to wip
87 ie .. when the batch status = 2 , then transaction date , originla due date and ectended due dates
88 should also be inserted.
89 Presently , this was done at completion state , when the batch status = 3
90
91 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 13-Jun-2005 File Version: 116.2
95 Ramananda for bug#4428980. Removal of SQL LITERALs is done
96 08-Jul-2005 Brathod
97 Issue: Inventory Convergence Uptake
98 Solution:
99 - Code related to batch_status = 2 (Release) is commented because this functionality
100 is now handled by the form JAINGOSP.fmb and the code is included in the form.
101 - Code is modified to remove reference to OPM Tables which are obsoleted in R12 Datamodel
102 and replace with the related Discrete Objects
103
104 --------------------------------------------------------------------------------------------*/
105
106 IF pr_new.BATCH_STATUS = 3 AND pr_old.BATCH_STATUS=1 THEN
107 /* Raise_application_error(-20010,'Cannot Certify the Batch Without Releasing it');
108 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Cannot Certify the Batch Without Releasing it' ; return ;
109 end if ;
110 IF pr_old.batch_status <> pr_new.batch_status THEN
111
112 OPEN c_reg_sel;
113 FETCH c_reg_sel INTO l_reg_sel,l_po_id;
114
115 IF C_REG_SEL%NOTFOUND THEN
116 CLOSE c_reg_sel;
117 RETURN;
118 END IF;
119 CLOSE c_reg_sel;
120
121 l_updated_by := pr_new.last_updated_by;
122
123 IF pr_new.batch_status = 3 THEN
124
125 IF l_reg_sel IS NOT NULL THEN
126
127 OPEN C_Osp_ret_days(pr_new.organization_id );
128 FETCH C_Osp_ret_days into return_days;
129 CLOSE C_Osp_ret_days;
130
131 OPEN C_Vend_Id(l_po_id);
132 FETCH C_Vend_Id INTO l_vend_id, l_location_id, l_location_code; /*Added for bug # 9088563*/
133 CLOSE C_Vend_Id;
134
135 If l_vend_id is null then
136 /* RAISE_APPLICATION_ERROR('-20009','Purchase Order Not Associated');
137 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Purchase Order Not Associated' ; return ;
138 end if ;
139 lv_tran_name := 'OSP_ISSUE' ;
140 INSERT INTO JAI_OPM_TXN_EXTN_HDRS
141 ( TRANSACTION_NAME,
142 TRANSACTION_HEADER_ID,
143 VENDOR_CUSTOMER_ID,
144 TRANSACTION_DATE,
145 ORIGINAL_DUE_DATE,
146 EXTENDED_DUE_DATE,
147 USER_REFERENCE,
148 CREATION_DATE,
149 CREATED_BY,
150 LAST_UPDATE_DATE,
151 LAST_UPDATED_BY,
152 LAST_UPDATE_LOGIN)
153 VALUES
154 ( lv_tran_name, --'OSP_ISSUE',
155 pr_new.batch_id,
156 l_vend_id,
157 sysdate,
158 sysdate + return_days,
159 sysdate + return_days,
160 pr_new.batch_no,
161 sysdate,
162 l_updated_by,
163 sysdate,
164 l_updated_by,
165 null
166 );
167
168 jai_cmn_rg_opm_pkg.create_rg23_entry
169 ( 'I' ,
170 --l_par_orgn,
171 l_location_id ,--Location code replaced by location_id for bug # 9088563 l_location_code, --pr_new.wip_whse_code,
172 pr_new.batch_id,
173 l_vend_id ,
174 sysdate,
175 l_reg_sel,
176 --Fle.Sql.35 Cbabu
177 0,
178 jai_constants.yes,
179 pr_new.organization_id
180 );
181
182 FOR rec IN C_Osp_Rcpt_Dtl LOOP
183 IF NVL(rec.act_quantity ,0) > 0 THEN
184 jai_cmn_rg_opm_pkg.create_rg_i_entry(
185 --l_par_orgn,
186 l_location_id, -- Location code replaced by location_id for bug # 9088563 l_location_code, --pr_new.wip_whse_code,
187 pr_new.batch_id,
188 sysdate,
189 --rec.inventory_item_id,
190 rec.act_quantity,
191 rec.uom_code,
192 l_updated_by,
193 rec.organization_id,
194 rec.inventory_item_id
195 );
196
197 END IF;
198 END LOOP;
199 END IF;
200 ELSIF pr_new.batch_status = 4 THEN
201 OPEN C_Plan_Out_Qty;
202 FETCH C_Plan_Out_Qty INTO l_plan_qty;
203 CLOSE C_Plan_Out_Qty;
204
205 OPEN C_Act_Out_Qty;
206 FETCH C_Act_Out_Qty INTO l_act_tot_qty;
207 CLOSE C_Act_Out_Qty;
208
209 OPEN C_Tot_Excise;
210 FETCH C_Tot_Excise INTO l_tot_excise_payable;
211 CLOSE C_Tot_Excise;
212
213 l_recv_qty := ((nvl(l_act_tot_qty,0) / nvl(l_plan_qty,0))* nvl(l_tot_excise_payable,0));
214
215 UPDATE JAI_OPM_OSP_HDRS
216 SET final_receipt = 'Y',
217 receivable_excise = l_recv_qty
218 WHERE osp_header_id = pr_new.batch_id;
219 END IF;
220 END IF;
221 END ARU_T1 ;
222
223 END JAI_OPM_GBH_TRIGGER_PKG ;
[Home] [Help]