1 PACKAGE BODY JAI_OPM_GBH_TRIGGER_PKG AS
2 /* $Header: jai_opm_gbh_t.plb 120.0 2005/09/02 09:01:52 rallamse noship $ */
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, 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;
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
62 /* Added by Ramananda for removal of SQL LITERALs */
63 lv_register JAI_OPM_OSP_HDRS.REGISTER_SELECTED%type ;
64 lv_tran_name JAI_OPM_TXN_EXTN_HDRS.transaction_name%type ;
65
66 BEGIN
67 pv_return_code := jai_constants.successful ;
68 /*--------------------------------------------------------------------------------------------
69 Change History
70 --------------------------------------------------------------------------------------------
71 1. Ssumaith - Bug # 2695779 - File Version 712.1
72 Changed the cursor definition C_Vend_Id so that it picks up based on discrete tables .Currently
73 it is using OPM tables . Since 11.5.7 , this change has been done.
74
75 2. Ssumaith - Bug # 2808732
76 Since OPM 11.5.8 (OPM I ) , the table is changed to GME_BATCH_HEADER instead of the
77 table pm_btch_hdr .
78
79 3. ssumaith - bug # 2959256
80 Major dependency issues arise out of this bug.New columns have been added to the JAI_OPM_OSP_HDRS
81 table.The columns added are fin_year, form_number.All further patches using this object will need
82 to have this as a pre-requisite.
83
84 4. ssumaith - Bug # 3015825
85 Aligning with the base apps changes in OPM I and OPM J , when the status of the batch changes to wip
86 ie .. when the batch status = 2 , then transaction date , originla due date and ectended due dates
87 should also be inserted.
88 Presently , this was done at completion state , when the batch status = 3
89
90 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
91 as required for CASE COMPLAINCE. Version 116.1
92
93 13-Jun-2005 File Version: 116.2
94 Ramananda for bug#4428980. Removal of SQL LITERALs is done
95 08-Jul-2005 Brathod
96 Issue: Inventory Convergence Uptake
97 Solution:
98 - Code related to batch_status = 2 (Release) is commented because this functionality
99 is now handled by the form JAINGOSP.fmb and the code is included in the form.
100 - Code is modified to remove reference to OPM Tables which are obsoleted in R12 Datamodel
101 and replace with the related Discrete Objects
102
103 --------------------------------------------------------------------------------------------*/
104
105 IF pr_new.BATCH_STATUS = 3 AND pr_old.BATCH_STATUS=1 THEN
106 /* Raise_application_error(-20010,'Cannot Certify the Batch Without Releasing it');
107 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Cannot Certify the Batch Without Releasing it' ; return ;
108 end if ;
109 IF pr_old.batch_status <> pr_new.batch_status THEN
110
111 OPEN c_reg_sel;
112 FETCH c_reg_sel INTO l_reg_sel,l_po_id;
113
114 IF C_REG_SEL%NOTFOUND THEN
115 CLOSE c_reg_sel;
116 RETURN;
117 END IF;
118 CLOSE c_reg_sel;
119
120 l_updated_by := pr_new.last_updated_by;
121
122 IF pr_new.batch_status = 3 THEN
123
124 IF l_reg_sel IS NOT NULL THEN
125
126 OPEN C_Osp_ret_days(pr_new.organization_id );
127 FETCH C_Osp_ret_days into return_days;
128 CLOSE C_Osp_ret_days;
129
130 OPEN C_Vend_Id(l_po_id);
131 FETCH C_Vend_Id INTO l_vend_id,l_location_code;
132 CLOSE C_Vend_Id;
133
134 If l_vend_id is null then
135 /* RAISE_APPLICATION_ERROR('-20009','Purchase Order Not Associated');
136 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Purchase Order Not Associated' ; return ;
137 end if ;
138 lv_tran_name := 'OSP_ISSUE' ;
139 INSERT INTO JAI_OPM_TXN_EXTN_HDRS
140 ( TRANSACTION_NAME,
141 TRANSACTION_HEADER_ID,
142 VENDOR_CUSTOMER_ID,
143 TRANSACTION_DATE,
144 ORIGINAL_DUE_DATE,
145 EXTENDED_DUE_DATE,
146 USER_REFERENCE,
147 CREATION_DATE,
148 CREATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN)
152 VALUES
153 ( lv_tran_name, --'OSP_ISSUE',
154 pr_new.batch_id,
155 l_vend_id,
156 sysdate,
157 sysdate + return_days,
158 sysdate + return_days,
159 pr_new.batch_no,
160 sysdate,
161 l_updated_by,
162 sysdate,
163 l_updated_by,
164 null
165 );
166
167 jai_cmn_rg_opm_pkg.create_rg23_entry
168 ( 'I' ,
169 --l_par_orgn,
170 l_location_code, --pr_new.wip_whse_code,
171 pr_new.batch_id,
172 l_vend_id ,
173 sysdate,
174 l_reg_sel,
175 --Fle.Sql.35 Cbabu
176 0,
177 jai_constants.yes,
178 pr_new.organization_id
179 );
180
181 FOR rec IN C_Osp_Rcpt_Dtl LOOP
182 IF NVL(rec.act_quantity ,0) > 0 THEN
183 jai_cmn_rg_opm_pkg.create_rg_i_entry(
184 --l_par_orgn,
185 l_location_code, --pr_new.wip_whse_code,
186 pr_new.batch_id,
187 sysdate,
188 --rec.inventory_item_id,
189 rec.act_quantity,
190 rec.uom_code,
191 l_updated_by,
192 rec.organization_id,
193 rec.inventory_item_id
194 );
195
196 END IF;
197 END LOOP;
198 END IF;
199 ELSIF pr_new.batch_status = 4 THEN
200 OPEN C_Plan_Out_Qty;
201 FETCH C_Plan_Out_Qty INTO l_plan_qty;
202 CLOSE C_Plan_Out_Qty;
203
204 OPEN C_Act_Out_Qty;
205 FETCH C_Act_Out_Qty INTO l_act_tot_qty;
206 CLOSE C_Act_Out_Qty;
207
208 OPEN C_Tot_Excise;
209 FETCH C_Tot_Excise INTO l_tot_excise_payable;
210 CLOSE C_Tot_Excise;
211
212 l_recv_qty := ((nvl(l_act_tot_qty,0) / nvl(l_plan_qty,0))* nvl(l_tot_excise_payable,0));
213
214 UPDATE JAI_OPM_OSP_HDRS
215 SET final_receipt = 'Y',
216 receivable_excise = l_recv_qty
217 WHERE osp_header_id = pr_new.batch_id;
218 END IF;
219 END IF;
220 END ARU_T1 ;
221
222 END JAI_OPM_GBH_TRIGGER_PKG ;
[Home] [Help]