1 PACKAGE BODY GMF_AR_CHECK_CREDIT as
2 /* $Header: gmfcrhib.pls 115.1 2002/11/11 00:34:42 rseshadr ship $ */
3 cursor cur_ar_check_credit ( st_date date,
4 en_date date,
5 cust_id number ) is
6 select CREDIT_HISTORY_ID, LAST_UPDATE_DATE,
7 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
8 CREATION_DATE, CREATED_BY, CUSTOMER_ID,
9 ON_HOLD, HOLD_DATE, CREDIT_LIMIT, CREDIT_RATING,
10 RISK_CODE, OUTSTANDING_BALANCE, SITE_USE_ID
11 FROM AR_CREDIT_HISTORIES
12 WHERE CUSTOMER_ID = nvl(cust_id, CUSTOMER_ID)
13 AND creation_date between nvl(st_date, creation_date)
14 AND nvl(en_date, creation_date);
15
16 procedure proc_ar_check_credit(st_date in out NOCOPY date,
17 en_date in out NOCOPY date,
18 cust_id in out NOCOPY number,
19 cr_hist_id out NOCOPY number,
20 lst_updt_dt out NOCOPY date,
21 lst_updt_by out NOCOPY number,
22 lst_updt_login out NOCOPY number,
23 create_dat out NOCOPY date,
24 create_by out NOCOPY number,
25 on_hld out NOCOPY varchar2,
26 hld_dt out NOCOPY date,
27 cr_lmt out NOCOPY number,
28 cr_rating out NOCOPY varchar2,
29 risk_cd out NOCOPY varchar2,
30 os_bal out NOCOPY number,
31 sit_use_id out NOCOPY number,
32 row_to_fetch in out NOCOPY number,
33 error_status out NOCOPY number) is
34
35 begin
36
37 IF NOT cur_ar_check_credit%ISOPEN THEN
38 OPEN cur_ar_check_credit(st_date, en_date, cust_id);
39 END IF;
40
41 FETCH cur_ar_check_credit
42 into cr_hist_id,
43 lst_updt_dt, lst_updt_by,
44 lst_updt_login,
45 create_dat, create_by, cust_id,
46 on_hld, hld_dt, cr_lmt,
47 cr_rating, risk_cd, os_bal,
48 sit_use_id;
49
50 if cur_ar_check_credit%NOTFOUND or row_to_fetch = 1 THEN
51 CLOSE cur_ar_check_credit;
52 if cur_ar_check_credit%NOTFOUND then
53 error_status:=100;
54 end if;
55 end if;
56
57 exception
58
59 when others then
60 error_status := SQLCODE;
61
62 END; /* End of procedure proc_ar_check_credit*/
63
64 END GMF_AR_CHECK_CREDIT; /* END GMF_AR_CHECK_CREDIT*/