[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_NAMED_ACCOUNT
Source
1 PACKAGE BODY JTF_TERR_NAMED_ACCOUNT AS
2 /* $Header: jtftnamb.pls 120.1 2005/06/24 00:25:30 jradhakr ship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- Custom Assignment API
6 --
7 -- NOTES
8 -- ORACLE INTERNAL USE ONLY: NOT for customer use
9 --
10 -- HISTORY
11 -- 03/18/02 SGKUMAR Created
12 -- 03/20/02 SGKUMAR Created procedure insert_qualifiers
13 -- 03/20/02 SGKUMAR Created procedure set_winners
14 -- 04/03/03 SGKUMAR Modified save_quals not to use count(*)
15 -- 2870648 performance bug fix
16 -- End of Comments
17 ----
18 PROCEDURE delete_qual(p_acct_qual_map_id IN NUMBER) AS
19 BEGIN
20 delete from jtf_tty_acct_qual_maps
21 where ACCOUNT_QUAL_MAP_ID = p_acct_qual_map_id;
22 commit;
23 END delete_qual;
24
25 PROCEDURE insert_customer_qual(p_account_id IN NUMBER)
26 AS
27 BEGIN
28 insert into jtf_tty_acct_qual_maps
29 (ACCOUNT_QUAL_MAP_ID,
30 OBJECT_VERSION_NUMBER ,
31 NAMED_ACCOUNT_ID ,
32 QUAL_USG_ID ,
33 COMPARISON_OPERATOR ,
34 VALUE1_CHAR ,
35 VALUE1_NUM
36 , VALUE2_NUM
37 , CREATED_BY ,
38 CREATION_DATE ,
39 LAST_UPDATED_BY ,
40 LAST_UPDATE_DATE ,
41 LAST_UPDATE_LOGIN
42 )
43 VALUES(2015,
44 2,
45 p_account_id,
46 -1012,
47 '=',
48 'Betu',
49 1,
50 94403,
51 94400,
52 sysdate,
53 1,
54 sysdate,
55 1
56 );
57 commit;
58
59 END insert_customer_qual;
60
61 PROCEDURE get_postal_qual_count(p_account_id IN NUMBER,
62 p_qual_count OUT NOCOPY NUMBER) AS
63 qual_count NUMBER;
64 BEGIN
65 select count(*) into qual_count
66 from JTF_TTY_ACCT_QUAL_MAPS
67 where NAMED_ACCOUNT_ID = p_account_id
68 and QUAL_USG_ID = -1007;
69
70 p_qual_count := qual_count;
71 END get_postal_qual_count;
72
73 PROCEDURE get_customer_qual_count(p_account_id IN NUMBER,
74 p_qual_count OUT NOCOPY NUMBER) AS
75 qual_count NUMBER;
76 BEGIN
77 select count(*) into qual_count
78 from JTF_TTY_ACCT_QUAL_MAPS
79 where NAMED_ACCOUNT_ID = p_account_id
80 and QUAL_USG_ID = -1012;
81
82 p_qual_count := qual_count;
83 END get_customer_qual_count;
84 PROCEDURE save_qual(p_acct_id IN NUMBER,
85 p_acct_qual_map_id IN NUMBER,
86 p_qual_usg_id IN NUMBER,
87 p_operator in VARCHAR2,
88 p_value1_char IN VARCHAR2,
89 p_value2_char IN VARCHAR2,
90 p_value1_num IN NUMBER,
91 p_value2_num IN NUMBER,
92 p_user_id in NUMBER
93 )
94 AS
95 qual_count number;
96 BEGIN
97 BEGIN
98 select 1
99 into qual_count
100 from jtf_tty_acct_qual_maps
101 where ACCOUNT_QUAL_MAP_ID = p_acct_qual_map_id
102 and rownum < 2;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 qual_count := 0;
106 END;
107 -- if a new entry, insert
108 if (qual_count = 0) then
109 insert into jtf_tty_acct_qual_maps
110 (ACCOUNT_QUAL_MAP_ID,
111 OBJECT_VERSION_NUMBER ,
112 NAMED_ACCOUNT_ID ,
113 QUAL_USG_ID ,
114 COMPARISON_OPERATOR ,
115 VALUE1_CHAR ,
116 VALUE2_CHAR ,
117 VALUE1_NUM
118 , VALUE2_NUM
119 , CREATED_BY ,
120 CREATION_DATE ,
121 LAST_UPDATED_BY ,
122 LAST_UPDATE_DATE
123 )
124 VALUES(p_acct_qual_map_id,
125 999,
126 p_acct_id,
127 p_qual_usg_id,
128 p_operator,
129 upper(p_value1_char),
130 upper(p_value2_char),
131 p_value1_num,
132 p_value2_num,
133 p_user_id,
134 sysdate,
135 p_user_id,
136 sysdate
137 );
138 else
139 update jtf_tty_acct_qual_maps
140 set OBJECT_VERSION_NUMBER = 999,
141 COMPARISON_OPERATOR = p_operator,
142 VALUE1_CHAR = upper(p_value1_char),
143 VALUE2_CHAR = upper(p_value2_char),
144 VALUE1_NUM = p_value1_num,
145 VALUE2_NUM = p_value2_num,
146 LAST_UPDATED_BY = p_user_id,
147 LAST_UPDATE_DATE = sysdate
148 where ACCOUNT_QUAL_MAP_ID = p_acct_qual_map_id;
149 end if;
150 commit;
151 end save_qual;
152 PROCEDURE update_mapping(p_account_id IN VARCHAR2,
153 p_flag in VARCHAR2)
154 AS
155 BEGIN
156 update jtf_tty_named_accts
157 set MAPPING_COMPLETE_FLAG = p_flag
158 where NAMED_ACCOUNT_ID = p_account_id;
159 update jtf_tty_terr_grp_accts
160 set DN_JNA_MAPPING_COMPLETE_FLAG = p_flag
161 where NAMED_ACCOUNT_ID = p_account_id;
162 commit;
163 END update_mapping;
164 end JTF_TERR_NAMED_ACCOUNT;