1 PACKAGE BODY GMF_AR_GET_INVOICE_LINES_ID AS
2 /* $Header: gmfinvsb.pls 115.2 2002/11/11 00:39:50 rseshadr ship $ */
3 PROCEDURE get_invoice_lines_id
4 (t_trx_type IN OUT NOCOPY VARCHAR2,
5 t_trx_type_name IN OUT NOCOPY VARCHAR2,
6 t_invoice_number IN OUT NOCOPY VARCHAR2,
7 invoice_index IN NUMBER,
8 t_line_id OUT NOCOPY VARCHAR2,
9 row_to_fetch IN OUT NOCOPY NUMBER,
10 error_status OUT NOCOPY NUMBER) IS
11
12 BEGIN
13
14 if(invoice_index = 1) then
15 if NOT cur_get_inv_lines_id1%ISOPEN then
16 OPEN cur_get_inv_lines_id1(t_trx_type, t_trx_type_name, t_invoice_number);
17 end if;
18
19 FETCH cur_get_inv_lines_id1 INTO
20 t_invoice_number,
21 t_trx_type_name,
22 t_line_id;
23
24 if cur_get_inv_lines_id1%NOTFOUND and
25 cur_get_inv_lines_id1%rowcount > 0 then
26 error_status := 100;
27 close cur_get_inv_lines_id1;
28 elsif cur_get_inv_lines_id1%NOTFOUND and
29 cur_get_inv_lines_id1%rowcount = 0 then
30 error_status := 5;
31 close cur_get_inv_lines_id1;
32 end if;
33
34 if row_to_fetch = 1 and cur_get_inv_lines_id1%ISOPEN then
35 close cur_get_inv_lines_id1;
36 end if;
37 end if;
38
39 if(invoice_index = 6) then
40 if NOT cur_get_inv_lines_id2%ISOPEN then
41 OPEN cur_get_inv_lines_id2(t_trx_type, t_trx_type_name, t_invoice_number);
42 end if;
43
44 FETCH cur_get_inv_lines_id2 INTO
45 t_invoice_number,
46 t_trx_type_name,
47 t_line_id;
48
49 if cur_get_inv_lines_id2%NOTFOUND and
50 cur_get_inv_lines_id2%rowcount > 0 then
51 error_status := 100;
52 close cur_get_inv_lines_id2;
53 elsif cur_get_inv_lines_id2%NOTFOUND and
54 cur_get_inv_lines_id2%rowcount = 0 then
55 error_status := 5;
56 close cur_get_inv_lines_id2;
57 end if;
58
59 if row_to_fetch = 1 and cur_get_inv_lines_id2%ISOPEN then
60 close cur_get_inv_lines_id2;
61 end if;
62 end if;
63
64 if(invoice_index = 7) then
65 if NOT cur_get_inv_lines_id3%ISOPEN then
66 OPEN cur_get_inv_lines_id3(t_trx_type, t_trx_type_name, t_invoice_number);
67 end if;
68
69 FETCH cur_get_inv_lines_id3 INTO
70 t_invoice_number,
71 t_trx_type_name,
72 t_line_id;
73
74 if cur_get_inv_lines_id3%NOTFOUND and
75 cur_get_inv_lines_id3%rowcount > 0 then
76 error_status := 100;
77 close cur_get_inv_lines_id3;
78 elsif cur_get_inv_lines_id3%NOTFOUND and
79 cur_get_inv_lines_id3%rowcount = 0 then
80 error_status := 5;
81 close cur_get_inv_lines_id3;
82 end if;
83
84 if row_to_fetch = 1 and cur_get_inv_lines_id3%ISOPEN then
85 close cur_get_inv_lines_id3;
86 end if;
87 end if;
88
89 if(invoice_index = 8) then
90 if NOT cur_get_inv_lines_id4%ISOPEN then
91 OPEN cur_get_inv_lines_id4(t_trx_type, t_trx_type_name, t_invoice_number);
92 end if;
93
94 FETCH cur_get_inv_lines_id4 INTO
95 t_invoice_number,
96 t_trx_type_name,
97 t_line_id;
98
99 if cur_get_inv_lines_id4%NOTFOUND and
100 cur_get_inv_lines_id4%rowcount > 0 then
101 error_status := 100;
102 close cur_get_inv_lines_id4;
103 elsif cur_get_inv_lines_id4%NOTFOUND and
104 cur_get_inv_lines_id4%rowcount = 0 then
105 error_status := 5;
106 close cur_get_inv_lines_id4;
107 end if;
108
109 if row_to_fetch = 1 and cur_get_inv_lines_id4%ISOPEN then
110 close cur_get_inv_lines_id4;
111 end if;
112 end if;
113
114 if(invoice_index = 9) then
115 if NOT cur_get_inv_lines_id5%ISOPEN then
116 OPEN cur_get_inv_lines_id5(t_trx_type, t_trx_type_name, t_invoice_number);
117 end if;
118
119 FETCH cur_get_inv_lines_id5 INTO
120 t_invoice_number,
121 t_trx_type_name,
122 t_line_id;
123
124 if cur_get_inv_lines_id5%NOTFOUND and
125 cur_get_inv_lines_id5%rowcount > 0 then
126 error_status := 100;
127 close cur_get_inv_lines_id5;
128 elsif cur_get_inv_lines_id5%NOTFOUND and
129 cur_get_inv_lines_id5%rowcount = 0 then
130 error_status := 5;
131 close cur_get_inv_lines_id5;
132 end if;
133
134 if row_to_fetch = 1 and cur_get_inv_lines_id5%ISOPEN then
135 close cur_get_inv_lines_id5;
136 end if;
137 end if;
138
139 if(invoice_index = 2 or invoice_index = 3 or invoice_index = 4 or invoice_index = 5) then
140 if NOT cur_get_inv_lines_id6%ISOPEN then
141 OPEN cur_get_inv_lines_id6(t_trx_type, t_trx_type_name, t_invoice_number);
142 end if;
143
144 FETCH cur_get_inv_lines_id6 INTO
145 t_invoice_number,
146 t_trx_type_name,
147 t_line_id;
148
149 if cur_get_inv_lines_id6%NOTFOUND and
150 cur_get_inv_lines_id6%rowcount > 0 then
151 error_status := 100;
152 close cur_get_inv_lines_id6;
153 elsif cur_get_inv_lines_id6%NOTFOUND and
154 cur_get_inv_lines_id6%rowcount = 0 then
155 error_status := 5;
156 close cur_get_inv_lines_id6;
157 end if;
158
159 if row_to_fetch = 1 and cur_get_inv_lines_id6%ISOPEN then
160 close cur_get_inv_lines_id6;
161 end if;
162 end if;
163
164 EXCEPTION
165
166 when others then
167 error_status := SQLCODE;
168 END get_invoice_lines_id;
169 END GMF_AR_GET_INVOICE_LINES_ID;