1 PACKAGE BODY jai_ar_cr_trigger_pkg AS
2 /* $Header: jai_arcr_trg_pkg.plb 120.3 2012/01/20 10:17:38 qioliu ship $ */
3
4 PROCEDURE ARI_T1 (pr_old IN t_rec%type ,
5 pr_new IN t_rec%type ,
6 pv_action IN VARCHAR2 ,
7 pv_return_code OUT NOCOPY VARCHAR2 ,
8 pv_return_message OUT NOCOPY VARCHAR2 )
9 IS
10
11 CURSOR C_CURRENT_SID
12 IS
13 SELECT JAI_AR_CASH_RECEIPTS_S1.CURRVAL
14 FROM DUAL;
15
16 CURSOR C_CHECK_TMP_DATA(PN_TEMP_SEQUENCE_ID NUMBER)
17 IS
18 SELECT LOC_CASH_RECEIPT_ID,
19 RECEIPT_AMOUNT,
20 CUSTOMER_ID,
21 CONFIRM_FLAG,
22 CURRENCY_CODE,
23 EXCHANGE_RATE
24 FROM JAI_AR_CASH_RECEIPTS_ALL
25 WHERE TEMP_SEQUENCE_ID = PN_TEMP_SEQUENCE_ID
26 AND CASH_RECEIPT_ID IS NULL;
27
28 r_check_tmp_data c_check_tmp_data%ROWTYPE;
29 ln_session_id NUMBER;
30 lv_process_flag VARCHAR2(2);
31 lv_process_message VARCHAR2(1000);
32
33 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
34 CURSOR c_get_regime_code (cp_receipt_method_id NUMBER)
35 IS
36 SELECT b.regime_code
37 FROM JAI_RGM_REGISTRATIONS a,
38 JAI_RGM_DEFINITIONS b
39 WHERE a.attribute_code = 'AR_RECEIPT_CLASS'
40 AND a.registration_type = 'OTHERS'
41 AND a.attribute_type_code = 'OTHERS'
42 AND a.regime_id = b.regime_id
43 AND a.attribute_value IN
44 (SELECT r_class.name
45 FROM ar_receipt_classes r_class,
46 ar_receipt_methods r_method
47 WHERE r_class.receipt_class_id = r_method.receipt_class_id
48 AND r_method.receipt_method_id = cp_receipt_method_id
49 );
50 lv_regime_code varchar2(50);
51 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 End*/
52
53 BEGIN
54 pv_return_code := jai_constants.successful;
55
56 BEGIN
57 OPEN c_current_sid;
58 FETCH c_current_sid INTO ln_session_id;
59 CLOSE c_current_sid;
60 EXCEPTION
61 WHEN OTHERS THEN
62 NULL;
63 END;
64
65 OPEN c_check_tmp_data(ln_session_id);
66 FETCH c_check_tmp_data INTO r_check_tmp_data;
67 CLOSE c_check_tmp_data;
68
69 OPEN c_get_regime_code(pr_new.receipt_method_id);
70 FETCH c_get_regime_code INTO lv_regime_code;
71 CLOSE c_get_regime_code;
72
73 IF r_check_tmp_data.loc_cash_receipt_id IS NOT NULL THEN
74
75 IF r_check_tmp_data.receipt_amount <> pr_new.amount OR
76 r_check_tmp_data.customer_id <> pr_new.pay_from_customer OR
77 r_check_tmp_data.currency_code <> pr_new.currency_code OR
78 r_check_tmp_data.exchange_rate <> pr_new.exchange_rate
79 THEN
80 --raise_application_error(-20010,'Either of Receipt Amount or customer details or currency details are changed. Delete the record using Zoom function');
81 pv_return_code := jai_constants.expected_error;
82 pv_return_message := 'Either of Receipt Amount or customer details or currency details are changed. Delete the record using Tools Menu';
83 return;
84 END IF;
85
86 UPDATE jai_ar_cash_receipts_all
87 SET cash_receipt_id = pr_new.cash_receipt_id,
88 temp_sequence_id = NULL
89 WHERE loc_cash_receipt_id = r_check_tmp_data.loc_cash_receipt_id;
90
91 UPDATE jai_cmn_document_taxes
92 SET source_doc_id = pr_new.cash_receipt_id,
93 source_doc_line_id = NULL
94 WHERE source_doc_line_id = r_check_tmp_data.loc_cash_receipt_id
95 AND source_table_name = 'JAI_AR_CASH_RECEIPTS_ALL';
96
97 IF r_check_tmp_data.confirm_flag = 'Y' THEN
98 jai_ar_cr_pkg.process_cm_dm(
99 p_event => jai_constants.trx_type_adv_rcpts,--Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
100 p_new => pr_new,
101 p_old => pr_old,
102 p_process_flag => lv_process_flag,
103 p_process_message => lv_process_message);
104
105 IF NVL(lv_process_flag,'XX') <> jai_constants.successful THEN
106 --raise_application_error(-20011, lv_process_message);
107 pv_return_code := jai_constants.expected_error;
108 pv_return_message := lv_process_message;
109 return;
110 END IF;
111
112
113 END IF;
114
115 ELSIF r_check_tmp_data.loc_cash_receipt_id IS NULL AND upper(nvl(lv_regime_code,'####')) = 'TCS' THEN
116 --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
117 --raise_application_error(-20010,'First process the taxes using Zoom function and then save the form');
118 pv_return_code := jai_constants.expected_error;
119 pv_return_message := 'First process the taxes using Tools Menu and then save the form';
120 return;
121 END IF;
122 EXCEPTION
123 WHEN OTHERS THEN
124 pv_return_code := jai_constants.unexpected_error;
125 pv_return_message := 'Encountered an error in jai_ar_cash_receipts_trg_pkg.ARI_T1 ' || substr(sqlerrm,1,1900);
126
127 END ARI_T1;
128
129
130 PROCEDURE ARU_T1 (pr_old IN t_rec%type ,
131 pr_new IN t_rec%type ,
132 pv_action IN VARCHAR2 ,
133 pv_return_code OUT NOCOPY VARCHAR2 ,
134 pv_return_message OUT NOCOPY VARCHAR2 )
135 IS
136
137 CURSOR c_ar_cash_receipts(cp_cash_receipt_id NUMBER)
138 IS
139 SELECT confirm_flag
140 FROM jai_ar_cash_receipts_all
141 WHERE cash_receipt_id = cp_cash_receipt_id;
142
143 r_ar_cash_receipts c_ar_cash_receipts%ROWTYPE;
144 lv_process_flag VARCHAR2(2);
145 lv_process_message VARCHAR2(1000);
146
147 BEGIN
148 OPEN c_ar_cash_receipts(pr_old.cash_receipt_id);
149 FETCH c_ar_cash_receipts INTO r_ar_cash_receipts;
150 CLOSE c_ar_cash_receipts;
151
152 IF pr_old.amount <> pr_new.amount OR
153 pr_old.pay_from_customer <> pr_new.pay_from_customer OR
154 pr_old.reversal_date IS NULL AND pr_new.reversal_date is NOT NULL THEN
155
156 IF NVL(r_ar_cash_receipts.confirm_flag,'N') IN ('N','I') THEN
157
158 DELETE jai_cmn_document_taxes
159 WHERE source_table_name = 'JAI_AR_CASH_RECEIPTS_ALL'
160 AND source_doc_id = pr_old.cash_receipt_id;
161
162 DELETE jai_ar_cash_receipts_all
163 WHERE cash_receipt_id = pr_old.cash_receipt_id;
164 ELSE
165 IF pr_old.reversal_date IS NULL AND pr_new.reversal_date is NOT NULL THEN
166
167 jai_ar_cr_pkg.process_cm_dm(
168 p_event => jai_constants.trx_type_rct_rvs,
169 p_new => pr_new,
170 p_old => pr_old,
171 p_process_flag => lv_process_flag,
172 p_process_message => lv_process_message);
173
174 IF NVL(lv_process_flag,'XX') <> jai_constants.successful THEN
175 --raise_application_error(-20011, lv_process_message);
176 pv_return_code := jai_constants.expected_error;
177 pv_return_message := lv_process_message;
178 return;
179 END IF;
180 ELSE
181 --raise_application_error(-20011, 'Either receipt amount or customer details are changed. You can''t change these details. Reverse the receipt');
182 pv_return_code := jai_constants.expected_error;
183 pv_return_message := 'Either receipt amount or customer details are changed. You can''t change these details. Reverse the receipt';
184 return;
185 END IF;
186 END IF;
187
188 ELSE
189 NULL;
190 END IF;
191 EXCEPTION
192 WHEN OTHERS THEN
193 pv_return_code := jai_constants.unexpected_error;
194 pv_return_message := 'Encountered an error in jai_ar_cash_receipts_trg_pkg.ARU_T1 ' || substr(sqlerrm,1,1900);
195 END ARU_T1;
196
197 PROCEDURE ARD_T1 (pr_old IN t_rec%type ,
198 pr_new IN t_rec%type ,
199 pv_action IN VARCHAR2 ,
200 pv_return_code OUT NOCOPY VARCHAR2 ,
201 pv_return_message OUT NOCOPY VARCHAR2 )
202 IS
203
204 CURSOR c_ar_cash_receipts(cp_cash_receipt_id NUMBER)
205 IS
206 SELECT confirm_flag
207 FROM jai_ar_cash_receipts_all
208 WHERE cash_receipt_id = cp_cash_receipt_id;
209
210 r_ar_cash_receipts c_ar_cash_receipts%ROWTYPE;
211 lv_process_flag VARCHAR2(2);
212 lv_process_message VARCHAR2(1000);
213
214 BEGIN
215 OPEN c_ar_cash_receipts(pr_old.cash_receipt_id);
216 FETCH c_ar_cash_receipts INTO r_ar_cash_receipts;
217 CLOSE c_ar_cash_receipts;
218
219 IF NVL(r_ar_cash_receipts.confirm_flag,'N') IN ('N','I') THEN
220 DELETE jai_cmn_document_taxes
221 WHERE source_table_name = 'JAI_AR_CASH_RECEIPTS_ALL'
222 AND source_doc_id = pr_old.cash_receipt_id;
223
224 DELETE jai_ar_cash_receipts_all
225 WHERE cash_receipt_id = pr_old.cash_receipt_id;
226 ELSE
227 --raise_application_error(-20011, 'You can''t delete this receipt. Reverse the receipt');
228 pv_return_code := jai_constants.expected_error;
229 pv_return_message := 'You can''t delete this receipt. Reverse the receipt';
230 return;
231 END IF;
232 EXCEPTION
233 WHEN OTHERS THEN
234 pv_return_code := jai_constants.unexpected_error;
235 pv_return_message := 'Encountered an error in jai_ar_cash_receipts_trg_pkg.ARD_T1 ' || substr(sqlerrm,1,1900);
236 END ARD_T1;
237
238 END;
239
[Home] [Help]