1 PACKAGE BODY JAI_AP_HA_TRIGGER_PKG AS
2 /* $Header: jai_ap_ha_t.plb 120.10 2012/05/23 08:10:04 zxin ship $ */
3 /*
4 REM +======================================================================+
5 REM NAME BRI_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_AP_HA_BRIUD_T1
8 REM
9 REM NOTES Refers to old trigger JAI_AP_HA_BRI_T3
10 REM
11 REM +======================================================================+
12 */
13 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
14 cursor c_get_cm_details(p_invoice_id number) is
15 select source
16 from ap_invoices_all
17 where invoice_id = p_invoice_id;
18
19 v_source ap_invoices_all.source%type;
20
21
22 BEGIN
23 pv_return_code := jai_constants.successful ;
24
25 /*------------------------------------------------------------------------------------------
26 FILENAME: ja_in_ai_cm_po_hold_trg.sql
27
28 CHANGE HISTORY:
29 S.No Date Author AND Details
30 1 13/05/2005 Aparajita for bug#3604402. Version#619.1
31
32 Whenever a 'PO REQUIRED' hold is placed on the TDS invoices because of
33 the setup of PO matched invoices only for the supplier site,
34 it is automatically set to release as this PO matching is not required.
35
36 2. 29/11/2005 Aparajita for bug#4036241. Version#115.1
37
38 Introduced the call to centralized packaged procedure,
39 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
40
41 3. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
42 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
43
44 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
45 DB Entity as required for CASE COMPLAINCE. Version 116.2
46
47 5. 13-Jun-2005 File Version: 116.3
48 Ramananda for bug#4428980. Removal of SQL LITERALs is done
49
50 Dependency:
51 ----------
52
53 Sl No. Bug Dependent on
54 Bug/Patch set Details
55 -------------------------------------------------------------------------------------------------
56 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
57 4033992.
58 ja_in_util_pkg_s.sql 115.0
59 ja_in_util_pkg_b.sql 115.0
60
61 --------------------------------------------------------------------------------------------------*/
62 --if
63 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AI_CM_PO_HOLD_TRG', p_org_id => pr_new.org_id)
64 -- =
65 -- FALSE
66 --then
67 -- /* India Localization funtionality is not required */
68 -- return;
69 --end if;
70
71
72 open c_get_cm_details(pr_new.invoice_id);
73 fetch c_get_cm_details into v_source;
74 close c_get_cm_details;
75
76 if v_source <> 'INDIA TDS' then -- 'TDS' then --Ramanand for bug#4388958
77 -- only for TDS invoices created by india localization.
78 return;
79 end if;
80
81 -- control comes here only when it is a TDS invoice created by india localization for TDS.
82 pr_new.release_lookup_code := 'TDS Override';
83 pr_new.release_reason := 'TDS Credit Memo need not be matched to any PO - automatically released' ;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 -- raise_application_error(-20000, 'Error - trigger ja_in_ai_cm_po_hold_trg : ' || sqlerrm);
88 /* Added an exception block by Ramananda for bug#4570303 */
89 Pv_return_code := jai_constants.unexpected_error;
90 Pv_return_message := 'Encountered an error in JAI_AP_HA_TRIGGER_PKG.BRI_T1 ' || substr(sqlerrm,1,1900);
91
92 END BRI_T1 ;
93
94 /*
95 REM +======================================================================+
96 REM NAME BRIUD_T1
97 REM
98 REM DESCRIPTION Called from trigger JAI_AP_HA_BRIUD_T1
99 REM
100 REM NOTES Refers to old trigger JAI_AP_HA_BRIUD_T1
101 REM
102 REM +======================================================================+
103 */
104 PROCEDURE BRIUD_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
105 CURSOR c_ap_invoices_all (p_invoice_id number) is
106 SELECT vendor_id,
107 vendor_site_id,
108 invoice_currency_code,
109 exchange_rate,
110 set_of_books_id,
111 source,
112 cancelled_date,
113 gl_date,
114 org_id,
115 --Bug#5131075(4685754). Added the below 3 by Lakshmi Gopalsami
116 invoice_amount,
117 payment_status_flag,
118 invoice_type_lookup_code
119
120 FROM ap_invoices_all
121 WHERE invoice_id = p_invoice_id;
122
123 c_rec_ap_invoices_all c_ap_invoices_all%rowtype;
124 lv_codepath VARCHAR2(1996);
125 lv_is_invoice_validated varchar2(1);
126 lv_process_flag varchar2(20);
127 lv_process_message varchar2(200);
128
129 lb_result boolean;
130 ln_req_id number;
131 ln_org_id ap_invoices_all.org_id%type;
132 lv_request_id number ; -- added, Harshita for Bug#5131075(5346558)
133 BEGIN
134 pv_return_code := jai_constants.successful ;
135 /*------------------------------------------------------------------------------------------
136 FILENAME: ja_in_ap_aha_after_trg.sql
137
138 CHANGE HISTORY:
139 S.No Date Author and Details
140
141 1. 25/03/2004 Aparajita. Bug # 4088186. TDS Clean up. Version#115.0
142 This is the only trigger introduced for all the before event
143 on the table on which this is based.
144
145 2. 4384239 File Version: 116.0
146 Procedures, packages, funtions merged into packages
147
148 3. 4388958 Ramananda for bug# 4388958 File Version: 116.1
149 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
150
151 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
152 DB Entity as required for CASE COMPLAINCE. Version 116.2
153
154 5. 13-Jun-2005 File Version: 116.3
155 Ramananda for bug#4428980. Removal of SQL LITERALs is done
156
157 Dependency:
158 ----------
159
160 Sl No. Bug Dependent on
161 Bug/Patch set Details
162 -------------------------------------------------------------------------------------------------
163 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
164 4033992.
165 ja_in_util_pkg_s.sql 115.0
166 ja_in_util_pkg_b.sql 115.0
167
168
169
170 ------------------------------------------------------------------------------------------ */
171
172 /* if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
173 ln_org_id := pr_new.org_id;
174 elsif pv_action = jai_constants.deleting then
175 ln_org_id := pr_old.org_id;
176 end if;
177 */
178 --if
179 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AHA_AFTER_TRG',
180 -- p_org_id => ln_org_id
181 -- )
182 -- =
183 -- FALSE
184 --then
185 /* India Localization funtionality is not required */
186 -- return;
187 -- end if;
188
189 --Added by Sanjikum for Bug#5131075(4644291)
190 IF pv_action = jai_constants.inserting OR pv_action = jai_constants.deleting THEN
191 RETURN;
192 END IF;
193
194
195 /*
196 || TDS Invoice generation Functionality Bug # 4088186
197 */
198 open c_ap_invoices_all(pr_new.invoice_id);
199 fetch c_ap_invoices_all into c_rec_ap_invoices_all;
200 close c_ap_invoices_all;
201
202 if pv_action = jai_constants.updating and
203 pr_new.release_reason is not null and
204 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /* 'TDS' and --Ramanand for bug#4388958 */
205 c_rec_ap_invoices_all.cancelled_date is null
206 then
207
208 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
209 Don't proceed for TDS invoice creation if the invoice type
210 is either 'CREDIT' or 'DEBIT'
211 */
212
213 If c_rec_ap_invoices_all.invoice_type_lookup_code
214 IN ('CREDIT', 'DEBIT')
215 Then
216 return;
217 End if;
218
219 lv_codepath := null;
220
221 -- added, Harshita for Bug#5131075(5346558)
222
223 IF NVL(jai_ap_tds_generation_pkg.gn_invoice_id, -1) = pr_new.invoice_id THEN
224 lv_request_id := jai_ap_tds_generation_pkg.gv_request_id ;
225 END IF ;
226
227 /* Bug#5131075(4685754). Added by Lakshmi Gopalsami
228 Call the concurrent program if the hold is user
229 releasable and non-postable
230 */
231
232 -- Invoke the concurrent program.
233
234 lb_result := Fnd_Request.set_mode(TRUE);
235
236 ln_req_id := fnd_request.submit_request(
237 'JA',
238 'JAINTDSHD', --replaced JAITDSHD by JAINTDSHD for bug#6598181
239 'India - Generate TDS Invoices after Holds release',
240 '',
241 FALSE,
242 pr_new.invoice_id,
243 c_rec_ap_invoices_all.invoice_amount,
244 c_rec_ap_invoices_all.payment_status_flag,
245 c_rec_ap_invoices_all.invoice_type_lookup_code,
246 c_rec_ap_invoices_all.vendor_id,
247 c_rec_ap_invoices_all.vendor_site_id,
248 c_rec_ap_invoices_all.gl_date,
249 c_rec_ap_invoices_all.invoice_currency_code,
250 c_rec_ap_invoices_all.exchange_rate,
251 c_rec_ap_invoices_all.set_of_books_id,
252 c_rec_ap_invoices_all.org_id,
253 'JAI_AP_HA_TRIGGER_PKG.BRIUD_T1',
254 lv_process_flag,
255 lv_process_message,
256 lv_codepath,
257 lv_request_id -- added, Harshita for Bug#5131075(5346558)
258 );
259
260 -- added, Harshita for Bug#5131075(5346558)
261 jai_ap_tds_generation_pkg.gn_invoice_id := pr_new.invoice_id ;
262 jai_ap_tds_generation_pkg.gv_request_id := ln_req_id ;
263 -- ended, Harshita for Bug#5131075(5346558)
264
265 return;
266
267 /* Removed the reference in old code by Lakshmi for bug#5131075(4685754)*/
268
269 end if; /* updating */
270
271
272 /* Added an exception block by Ramananda for bug#4570303 */
273 EXCEPTION
274 WHEN OTHERS THEN
275 --raise_application_error(-20000, 'Error - ja_in_ap_aha_after_trg on ap_holds_all : ' || sqlerrm);
276 Pv_return_code := jai_constants.unexpected_error;
277 Pv_return_message := 'Encountered an error in JAI_AP_HA_TRIGGER_PKG.ARI_T7 ' || substr(sqlerrm,1,1900);
278
279 END BRIUD_T1 ;
280
281 END JAI_AP_HA_TRIGGER_PKG ;
[Home] [Help]