1 PACKAGE BODY GMF_MEMO_INTERFACE AS
2 /* $Header: gmfarmob.pls 115.10 2002/11/11 00:29:34 rseshadr ship $ */
3 last_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
4 process_errors BOOLEAN;
5 PROCEDURE update_state (p_customer_trx_id IN NUMBER) IS
6 max_id NUMBER;
7 BEGIN
8 SELECT MAX(ams.customer_trx_id)
9 INTO max_id
10 FROM gl_memo_sta ams
11 WHERE ams.updated_flag = 'Y';
12 /* initialize the table if there are no updated values in it. */
13
14 IF max_id IS NULL THEN
15 INSERT INTO gl_memo_sta (customer_trx_id, updated_flag)
16 VALUES (p_customer_trx_id, 'Y');
17 RETURN;
18 END IF;
19 /* dbms_output.put_line('max='||to_char(max_id)||' p='||to_char(p_customer_trx_id)); */
20 IF (p_customer_trx_id > max_id) THEN
21 UPDATE gl_memo_sta ams
22 SET ams.customer_trx_id = p_customer_trx_id
23 WHERE ams.updated_flag = 'Y';
24 END IF;
25 END update_state;
26 PROCEDURE get_next_trx_line (
27 t_init_flag IN OUT NOCOPY NUMBER,
28 t_customer_trx_id OUT NOCOPY NUMBER,
29 t_trx_type OUT NOCOPY VARCHAR2,
30 error_status OUT NOCOPY NUMBER
31 ) IS
32 t_customer_trx_id1 ra_customer_trx_all.customer_trx_id%type;
33 t_trx_type1 ra_cust_trx_types_all.type%type;
34 t_interface_date DATE; /* Bug 2403594 */
35 BEGIN
36 error_status := 0;
37 IF t_init_flag <> 0
38 THEN
39 process_errors := TRUE;
40 t_init_flag := 0;
41 last_trx_id := -1;
42
43 /* Bug 2403594. Insert customer_trx_ids of new memos */
44
45 SELECT nvl(MAX(interface_date),TO_DATE(2440589,'J'))
46 INTO t_interface_date
47 FROM gl_memo_sta;
48
49 INSERT INTO gl_memo_sta
50 (customer_trx_id, updated_flag, interface_date)
51 (
52 SELECT
53 distinct rct.customer_trx_id,'N', NULL
54 FROM ra_customer_trx_all rct,
55 ra_cust_trx_types_all rctt
56 WHERE
57 rct.cust_trx_type_id = rctt.cust_trx_type_id
58 AND rctt.TYPE IN ('CM', 'DM')
59 AND UPPER(rct.complete_flag) = 'Y'
60 AND rct.last_update_date >= trunc(t_interface_date)
61 AND UPPER(rctt.attribute10) = 'YES'
62 AND NOT EXISTS (
63 SELECT 1
64 FROM gl_memo_sta s
65 WHERE s.customer_trx_id = rct.customer_trx_id)
66 );
67
68 COMMIT;
69
70 END IF;
71
72 BEGIN
73 /* get lines with status 'N'.
74 Modified sql script to order rows in sub-query
75 and to select first row from the returned set */
76
77 SELECT customer_trx_id, type
78 INTO t_customer_trx_id1, t_trx_type1
79 FROM (
80 SELECT rct.customer_trx_id,
81 rctt.type
82 FROM ra_customer_trx_all rct,
83 ra_cust_trx_types_all rctt,
84 gl_memo_sta ams
85 WHERE rct.customer_trx_id = ams.customer_trx_id
86 AND rct.customer_trx_id > last_trx_id
87 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
88 AND rctt.type IN ('CM', 'DM')
89 /* B1043050 changed upper to lower and 'N' to 'n' */
90 AND LOWER(ams.updated_flag) = 'n'
91 ORDER BY 1
92 )
93 WHERE ROWNUM = 1;
94 EXCEPTION
95 WHEN NO_DATA_FOUND
96 THEN
97 process_errors := FALSE;
98 t_init_flag := last_trx_id;
99 error_status := 100;
100 RETURN;
101 END;
102
103 last_trx_id := t_customer_trx_id1;
104
105 /* Bug 2403594. Mark transactions */
106
107 UPDATE gl_memo_sta ams
108 SET updated_flag = 'Y',
109 interface_date = sysdate
110 WHERE customer_trx_id = t_customer_trx_id1;
111
112 /* set the values of the OUT variables.*/
113 t_customer_trx_id := t_customer_trx_id1;
114 t_trx_type := t_trx_type1;
115 EXCEPTION
116 WHEN OTHERS
117 THEN
118 error_status := SQLCODE;
119 END get_next_trx_line;
120 PROCEDURE insert_error (
121 t_customer_trx_id IN NUMBER,
122 error_status OUT NOCOPY NUMBER
123 ) IS
124 BEGIN
125 /* Bug 2403594 */
126 UPDATE gl_memo_sta ams
127 SET updated_flag = 'N', interface_date = NULL
128 WHERE customer_trx_id = t_customer_trx_id;
129 EXCEPTION
130 WHEN OTHERS THEN
131 error_status := SQLCODE;
132 END insert_error;
133 PROCEDURE validate_flexfields (
134 t_customer_trx_id IN NUMBER,
135 t_rctl_attribute7 IN VARCHAR2,
136 t_rctl_attribute8 IN VARCHAR2,
137 t_rctl_attribute9 IN VARCHAR2,
138 t_rctl_attribute10 IN VARCHAR2,
139 t_inventory_item_id IN NUMBER,
140 t_rctl_attribute1 IN VARCHAR2,
141 t_rctl_attribute5 IN VARCHAR2,
142 t_rctl_attribute15 IN VARCHAR2
143 ) IS
144 CURSOR tran_type_cur(t_customer_trx_id IN NUMBER) IS
145 SELECT NVL(rctt.attribute10, ' ')
146 FROM ra_cust_trx_types_all rctt,
147 ra_customer_trx_all rct
148 WHERE rct.customer_trx_id = t_customer_trx_id
149 AND rct.cust_trx_type_id = rctt.cust_trx_type_id;
150 t_rctt_attribute10 ra_cust_trx_types_all.attribute10%type;
151
152 CURSOR get_item_no(t_item_id in NUMBER) IS
153 SELECT msi.segment1
154 FROM mtl_system_items msi
155 WHERE msi.inventory_item_id=t_inventory_item_id;
156
157 t_item_no mtl_system_items.segment1%TYPE;
158
159 CURSOR get_item_ind(i_item_no VARCHAR2) IS
160 SELECT dualum_ind, lot_ctl
161 FROM ic_item_mst
162 WHERE item_no = i_item_no;
163
164 t_dualum_ind ic_item_mst.dualum_ind%TYPE;
165 t_lot_ctl ic_item_mst.lot_ctl%TYPE;
166
167 affect_inv EXCEPTION;
168 exp_wrong_item EXCEPTION;
169 no_lot_data EXCEPTION;
170 no_qty_data EXCEPTION;
171 PRAGMA EXCEPTION_INIT(affect_inv,-20101);
172 BEGIN
173 IF NOT tran_type_cur%ISOPEN
174 THEN
175 OPEN tran_type_cur(t_customer_trx_id);
176 END IF;
177 FETCH tran_type_cur
178 INTO t_rctt_attribute10;
179 CLOSE tran_type_cur;
180
181 /* B1043050 changed upper to lower and 'YES' to 'yes' */
182 IF LOWER(t_rctt_attribute10) = 'yes'
183 THEN
184 /* Check for all required values in the descriptive flexfield */
185 IF ( t_rctl_attribute7 IS NULL OR
186 t_rctl_attribute8 IS NULL OR
187 t_rctl_attribute9 IS NULL OR
188 t_rctl_attribute10 IS NULL)
189 THEN
190 RAISE affect_inv;
191 END IF;
192
193 /* Validate the item in descriptive flexfield */
194 IF NOT get_item_no%ISOPEN
195 THEN
196 OPEN get_item_no(t_inventory_item_id);
197 END IF;
198 FETCH get_item_no
199 INTO t_item_no;
200
201 CLOSE get_item_no;
202
203 IF (t_item_no<>t_rctl_attribute7)
204 THEN
205 RAISE exp_wrong_item;
206 END IF;
207
208 /* Bug 1399377
209 REM Get dual uom indicator and lot control indicator */
210 IF NOT get_item_ind%ISOPEN
211 THEN
212 OPEN get_item_ind(t_item_no);
213 END IF;
214 FETCH get_item_ind
215 INTO t_dualum_ind, t_lot_ctl;
216
217 CLOSE get_item_ind;
218
219 /* Verify Lot/Sublot data if item is lot controlled. */
220 IF (t_lot_ctl = 1 AND t_rctl_attribute1 is NULL) THEN
221 RAISE no_lot_data;
222 END IF;
223
224 /* Verify secondary uom qty data if item is dual type 3. */
225 IF (t_dualum_ind = 3 AND t_rctl_attribute15 is NULL) THEN
226 RAISE no_qty_data;
227 END IF;
228
229 END IF;
230 EXCEPTION
231 WHEN exp_wrong_item THEN
232 fnd_message.set_name('GMF','GL_WRONG_ITEM');
233 app_exception.raise_exception;
234 WHEN affect_inv THEN
235 fnd_message.set_name('GMF','GL_INVENTORY_AFFECT');
236 app_exception.raise_exception;
237 WHEN no_lot_data THEN
238 fnd_message.set_name('GMF','GL_NO_LOT_DATA');
239 app_exception.raise_exception;
240 WHEN no_qty_data THEN
241 fnd_message.set_name('GMF','GL_NO_QTY_DATA');
242 app_exception.raise_exception;
243 WHEN OTHERS THEN
244 /* REM RAISE_APPLICATION_ERROR(-20101, 'Transaction affects inventory'); */
245 fnd_message.set_name('GMF','GL_TRIGGER_EXCEPTION');
246 /* REM B1033070 Changed substrb to substr, Get first 512 chars and not bytes.
247 REM fnd_message.set_token('TRIGGER_NAME',substrb('GMF_MEMO_INTERFACE'||SQLERRM,1,512)); */
248 fnd_message.set_token('TRIGGER_NAME',substr('GMF_MEMO_INTERFACE'||SQLERRM,1,512));
249 app_exception.raise_exception;
250 END validate_flexfields;
251 END GMF_MEMO_INTERFACE;
[Home] [Help]