1 PACKAGE BODY GMF_AP_INVOICE_CHECKS_INFO AS
2 /* $Header: gmfchknb.pls 115.0 99/07/16 04:15:13 porting shi $ */
3 CURSOR invoice_checks( startdate date,
4 enddate date,
5 checkid number) IS
6 SELECT ach.amount,
7 ach.bank_account_num,
8 ach.bank_num,
9 ach.bank_account_type,
10 ach.currency_code,
11 ach.status_lookup_code,
12 ach.stopped_at,
13 ach.released_at,
14 ach.void_date
15 FROM AP_CHECKS_ALL ach
16 WHERE ach.check_id = NVL(checkid,ach.check_id) AND
17 ach.creation_date BETWEEN
18 nvl(startdate,ach.creation_date) AND
19 nvl(enddate,ach.creation_date);
20
21 /* SELECT aip.amount,
22 aip.bank_account_num,
23 aip.bank_num,
24 aip.bank_account_type,
25 ach.currency_code,
26 ach.status_lookup_code
27 FROM AP_INVOICE_PAYMENTS_ALL aip,
28 AP_CHECKS_ALL ach
29 WHERE aip.check_id = NVL(checkid,aip.check_id) AND
30 aip.creation_date BETWEEN
31 nvl(startdate,aip.creation_date) AND
32 nvl(enddate,aip.creation_date)
33 AND ach.check_id = aip.check_id;
34
35 The above statement is chaged on final edition based on DLX request */
36
37 PROCEDURE get_invoice_checks_info( startdate in date,
38 enddate in date,
39 checkid in out number,
40 amount out number,
41 bankaccountnum out varchar2,
42 banknum out varchar2,
43 bankaccounttype out varchar2,
44 checkcurrency out varchar2,
45 checkstatus out varchar2,
46 t_stopped_at in out varchar2,
47 t_released_at in out varchar2,
48 status out varchar2,
49 row_to_fetch in out number,
50 statuscode out number) IS
51 wcheck_status varchar2(250);
52 disp_status varchar2(250);
53 void_date date;
54 Begin
55 IF NOT invoice_checks%ISOPEN THEN
56 OPEN invoice_checks( startdate,enddate,checkid);
57 END IF;
58 FETCH invoice_checks
59 INTO amount,
60 bankaccountnum,
61 banknum ,
62 bankaccounttype,
63 checkcurrency,
64 wcheck_status,
65 t_stopped_at,
66 t_released_at,
67 void_date;
68
69
70 select displayed_field into checkstatus
71 from ap_lookup_codes
72 where lookup_type = 'CHECK STATE'
73 and lookup_code = wcheck_status;
74
75 if void_date is not null then
76 select displayed_field into status
77 from ap_lookup_codes
78 where lookup_type = 'CHECK STATE'
79 and lookup_code = 'VOID';
80 elsif t_stopped_at is null then
81 status := NULL;
82 elsif t_released_at is null and t_stopped_at is not null then
83 select displayed_field into status
84 from ap_lookup_codes
85 where lookup_type = 'STOP PAYMENT STATUS'
86 and lookup_code = 'STOPPED';
87 else
88 select displayed_field into status
89 from ap_lookup_codes
90 where lookup_type = 'STOP PAYMENT STATUS'
91 and lookup_code = 'RELEASED';
92 end if;
93
94
95 if invoice_checks%NOTFOUND then
96 statuscode := 100;
97 end if;
98 IF invoice_checks%NOTFOUND or row_to_fetch = 1 THEN
99 CLOSE invoice_checks;
100 END IF;
101 EXCEPTION
102 WHEN OTHERS THEN
103 statuscode := SQLCODE;
104 End; /* End of procedure get_invoice_checks*/
105 END GMF_AP_INVOICE_CHECKS_INFO;
[Home] [Help]