1 PACKAGE BODY jai_ar_cr_trigger_pkg AS
2 /* $Header: jai_arcr_trg_pkg.plb 120.1.12000000.1 2007/07/24 06:55:35 rallamse noship $ */
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 BEGIN
34 pv_return_code := jai_constants.successful;
35
36 BEGIN
37 OPEN c_current_sid;
38 FETCH c_current_sid INTO ln_session_id;
39 CLOSE c_current_sid;
40 EXCEPTION
41 WHEN OTHERS THEN
42 NULL;
43 END;
44
45 OPEN c_check_tmp_data(ln_session_id);
46 FETCH c_check_tmp_data INTO r_check_tmp_data;
47 CLOSE c_check_tmp_data;
48
49 IF r_check_tmp_data.loc_cash_receipt_id IS NOT NULL THEN
50
51 IF r_check_tmp_data.receipt_amount <> pr_new.amount OR
52 r_check_tmp_data.customer_id <> pr_new.pay_from_customer OR
53 r_check_tmp_data.currency_code <> pr_new.currency_code OR
54 r_check_tmp_data.exchange_rate <> pr_new.exchange_rate
55 THEN
56 --raise_application_error(-20010,'Either of Receipt Amount or customer details or currency details are changed. Delete the record using Zoom function');
57 pv_return_code := jai_constants.expected_error;
58 pv_return_message := 'Either of Receipt Amount or customer details or currency details are changed. Delete the record using Tools Menu';
59 return;
60 END IF;
61
62 UPDATE jai_ar_cash_receipts_all
63 SET cash_receipt_id = pr_new.cash_receipt_id,
64 temp_sequence_id = NULL
65 WHERE loc_cash_receipt_id = r_check_tmp_data.loc_cash_receipt_id;
66
67 UPDATE jai_cmn_document_taxes
68 SET source_doc_id = pr_new.cash_receipt_id,
69 source_doc_line_id = NULL
70 WHERE source_doc_line_id = r_check_tmp_data.loc_cash_receipt_id
71 AND source_table_name = 'JAI_AR_CASH_RECEIPTS_ALL';
72
73 IF r_check_tmp_data.confirm_flag = 'Y' THEN
74
75 jai_ar_cr_pkg.process_cm_dm(
76 p_event => jai_constants.ar_cash_tax_confirmed,
77 p_new => pr_new,
78 p_old => pr_old,
79 p_process_flag => lv_process_flag,
80 p_process_message => lv_process_message);
81
82 IF NVL(lv_process_flag,'XX') <> jai_constants.successful THEN
83 --raise_application_error(-20011, lv_process_message);
84 pv_return_code := jai_constants.expected_error;
85 pv_return_message := lv_process_message;
86 return;
87 END IF;
88
89
90 END IF;
91
92 ELSE
93 --raise_application_error(-20010,'First process the taxes using Zoom function and then save the form');
94 pv_return_code := jai_constants.expected_error;
95 pv_return_message := 'First process the taxes using Tools Menu and then save the form';
96 return;
97 END IF;
98 EXCEPTION
99 WHEN OTHERS THEN
100 pv_return_code := jai_constants.unexpected_error;
101 pv_return_message := 'Encountered an error in jai_ar_cash_receipts_trg_pkg.ARI_T1 ' || substr(sqlerrm,1,1900);
102
103 END ARI_T1;
104
105
106 PROCEDURE ARU_T1 (pr_old IN t_rec%type ,
107 pr_new IN t_rec%type ,
108 pv_action IN VARCHAR2 ,
109 pv_return_code OUT NOCOPY VARCHAR2 ,
110 pv_return_message OUT NOCOPY VARCHAR2 )
111 IS
112
113 CURSOR c_ar_cash_receipts(cp_cash_receipt_id NUMBER)
114 IS
115 SELECT confirm_flag
116 FROM jai_ar_cash_receipts_all
117 WHERE cash_receipt_id = cp_cash_receipt_id;
118
119 r_ar_cash_receipts c_ar_cash_receipts%ROWTYPE;
120 lv_process_flag VARCHAR2(2);
121 lv_process_message VARCHAR2(1000);
122
123 BEGIN
124 OPEN c_ar_cash_receipts(pr_old.cash_receipt_id);
125 FETCH c_ar_cash_receipts INTO r_ar_cash_receipts;
126 CLOSE c_ar_cash_receipts;
127
128 IF pr_old.amount <> pr_new.amount OR
129 pr_old.pay_from_customer <> pr_new.pay_from_customer OR
130 pr_old.reversal_date IS NULL AND pr_new.reversal_date is NOT NULL THEN
131
132 IF NVL(r_ar_cash_receipts.confirm_flag,'N') IN ('N','I') THEN
133
134 DELETE jai_cmn_document_taxes
135 WHERE source_table_name = 'JAI_AR_CASH_RECEIPTS_ALL'
136 AND source_doc_id = pr_old.cash_receipt_id;
137
138 DELETE jai_ar_cash_receipts_all
139 WHERE cash_receipt_id = pr_old.cash_receipt_id;
140 ELSE
141 IF pr_old.reversal_date IS NULL AND pr_new.reversal_date is NOT NULL THEN
142 jai_ar_cr_pkg.process_cm_dm(
143 p_event => jai_constants.trx_type_rct_rvs,
144 p_new => pr_new,
145 p_old => pr_old,
146 p_process_flag => lv_process_flag,
147 p_process_message => lv_process_message);
148
149 IF NVL(lv_process_flag,'XX') <> jai_constants.successful THEN
150 --raise_application_error(-20011, lv_process_message);
151 pv_return_code := jai_constants.expected_error;
152 pv_return_message := lv_process_message;
153 return;
154 END IF;
155 ELSE
156 --raise_application_error(-20011, 'Either receipt amount or customer details are changed. You can''t change these details. Reverse the receipt');
157 pv_return_code := jai_constants.expected_error;
158 pv_return_message := 'Either receipt amount or customer details are changed. You can''t change these details. Reverse the receipt';
159 return;
160 END IF;
161 END IF;
162
163 ELSE
164 NULL;
165 END IF;
166 EXCEPTION
167 WHEN OTHERS THEN
168 pv_return_code := jai_constants.unexpected_error;
169 pv_return_message := 'Encountered an error in jai_ar_cash_receipts_trg_pkg.ARU_T1 ' || substr(sqlerrm,1,1900);
170 END ARU_T1;
171
172 PROCEDURE ARD_T1 (pr_old IN t_rec%type ,
173 pr_new IN t_rec%type ,
174 pv_action IN VARCHAR2 ,
175 pv_return_code OUT NOCOPY VARCHAR2 ,
176 pv_return_message OUT NOCOPY VARCHAR2 )
177 IS
178
179 CURSOR c_ar_cash_receipts(cp_cash_receipt_id NUMBER)
180 IS
181 SELECT confirm_flag
182 FROM jai_ar_cash_receipts_all
183 WHERE cash_receipt_id = cp_cash_receipt_id;
184
185 r_ar_cash_receipts c_ar_cash_receipts%ROWTYPE;
186 lv_process_flag VARCHAR2(2);
187 lv_process_message VARCHAR2(1000);
188
189 BEGIN
190 OPEN c_ar_cash_receipts(pr_old.cash_receipt_id);
191 FETCH c_ar_cash_receipts INTO r_ar_cash_receipts;
192 CLOSE c_ar_cash_receipts;
193
194 IF NVL(r_ar_cash_receipts.confirm_flag,'N') IN ('N','I') THEN
195 DELETE jai_cmn_document_taxes
196 WHERE source_table_name = 'JAI_AR_CASH_RECEIPTS_ALL'
197 AND source_doc_id = pr_old.cash_receipt_id;
198
199 DELETE jai_ar_cash_receipts_all
200 WHERE cash_receipt_id = pr_old.cash_receipt_id;
201 ELSE
202 --raise_application_error(-20011, 'You can''t delete this receipt. Reverse the receipt');
203 pv_return_code := jai_constants.expected_error;
204 pv_return_message := 'You can''t delete this receipt. Reverse the receipt';
205 return;
206 END IF;
207 EXCEPTION
208 WHEN OTHERS THEN
209 pv_return_code := jai_constants.unexpected_error;
210 pv_return_message := 'Encountered an error in jai_ar_cash_receipts_trg_pkg.ARD_T1 ' || substr(sqlerrm,1,1900);
211 END ARD_T1;
212
213 END;
[Home] [Help]