[Home] [Help]
PACKAGE BODY: APPS.IBE_ACTIVE_QUOTES_ALL_PKG
Source
1 package body IBE_ACTIVE_QUOTES_ALL_PKG as
2 /*$Header: IBEVAQRB.pls 120.1 2005/08/24 21:56:03 appldev ship $ */
3 procedure INSERT_ROW (
4 X_OBJECT_VERSION_NUMBER in NUMBER,
5 /*X_PROGRAM_APPLICATION_ID in NUMBER,
6 X_PROGRAM_ID in NUMBER,
7 X_PROGRAM_UPDATE_DATE in DATE,*/
8 X_QUOTE_HEADER_ID in NUMBER,
9 X_PARTY_ID in NUMBER,
10 X_CUST_ACCOUNT_ID in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER,
16 X_RECORD_TYPE in VARCHAR2,
17 X_ORDER_HEADER_ID in NUMBER,
18 X_CURRENCY_CODE in VARCHAR2,
19 X_ORG_ID in NUMBER := MO_GLOBAL.get_current_org_id()
20 ) is
21
22 L_ORG_ID NUMBER;
23 l_last_update_login NUMBER;
24 l_active_quote_id NUMBER;
25
26 BEGIN
27
28 IF (X_RECORD_TYPE NOT IN ('CART','ORDER')) Then
29 FND_MESSAGE.Set_Name('IBE','IBE_PRMT_REQUIRED_MISSING_G');
30 FND_MSG_PUB.ADD;
31 RAISE FND_API.G_EXC_ERROR;
32 END IF;
33
34 IF (X_RECORD_TYPE = 'CART')
35 THEN
36 IF ((X_PARTY_ID is null OR X_PARTY_ID =FND_API.G_MISS_NUM)
37 OR (X_CUST_ACCOUNT_ID is null OR X_CUST_ACCOUNT_ID = FND_API.G_MISS_NUM))
38 THEN
39 FND_MESSAGE.Set_Name('IBE','IBE_PRMT_REQUIRED_MISSING_G');
40 FND_MSG_PUB.ADD;
41 RAISE FND_API.G_EXC_ERROR;
42 END IF;
43 END IF;
44
45 if (X_RECORD_TYPE = 'ORDER')
46 then
47 if ((X_PARTY_ID is null OR X_PARTY_ID =FND_API.G_MISS_NUM)
48 OR (X_CUST_ACCOUNT_ID is null OR X_CUST_ACCOUNT_ID = FND_API.G_MISS_NUM)
49 OR (X_CURRENCY_CODE is null OR X_CURRENCY_CODE = FND_API.G_MISS_CHAR))
50 then
51 FND_MESSAGE.Set_Name('IBE','IBE_PRMT_REQUIRED_MISSING_G');
52 FND_MSG_PUB.ADD;
53 RAISE FND_API.G_EXC_ERROR;
54 end if;
55 end if;
56
57 l_last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
58 IF ((x_org_id is null ) or (x_org_id = FND_API.G_MISS_NUM)) THEN
59 l_org_id := MO_GLOBAL.get_current_org_id();
60 ELSE
61 l_org_id := x_org_id;
62 END IF;
63
64 select nvl(IBE_ACTIVE_QUOTES_ALL_S1.nextval,0) into l_active_quote_id
65 from dual;
66
67 insert into IBE_ACTIVE_QUOTES_ALL (
68 ACTIVE_QUOTE_ID,
69 /*PROGRAM_APPLICATION_ID,
70 PROGRAM_ID,
71 PROGRAM_UPDATE_DATE,*/
72 OBJECT_VERSION_NUMBER,
73 CREATED_BY,
74 CREATION_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_DATE,
77 LAST_UPDATE_LOGIN,
78 QUOTE_HEADER_ID,
79 PARTY_ID,
80 CUST_ACCOUNT_ID,
81 RECORD_TYPE,
82 ORDER_HEADER_ID,
83 CURRENCY_CODE,
84 ORG_ID
85 )
86 VALUES(
87 DECODE(L_ACTIVE_QUOTE_ID ,FND_API.G_MISS_NUM,NULL,L_ACTIVE_QUOTE_ID),
88 /*DECODE(x_PROGRAM_APPLICATION_ID ,FND_API.G_MISS_NUM,NULL,x_PROGRAM_APPLICATION_ID),
89 DECODE(x_PROGRAM_ID ,FND_API.G_MISS_NUM,NULL,x_PROGRAM_ID),
90 DECODE(x_PROGRAM_UPDATE_DATE ,FND_API.G_MISS_DATE,NULL,x_PROGRAM_UPDATE_DATE),*/
91 DECODE(x_OBJECT_VERSION_NUMBER ,FND_API.G_MISS_NUM,NULL,x_OBJECT_VERSION_NUMBER),
92 DECODE(x_CREATED_BY ,FND_API.G_MISS_NUM,NULL,x_CREATED_BY),
93 DECODE(x_CREATION_DATE ,FND_API.G_MISS_DATE,NULL,x_CREATION_DATE),
94 DECODE(x_LAST_UPDATED_BY ,FND_API.G_MISS_NUM,NULL,x_LAST_UPDATED_BY),
95 DECODE(x_LAST_UPDATE_DATE ,FND_API.G_MISS_DATE,NULL,x_LAST_UPDATE_DATE),
96 DECODE(x_LAST_UPDATE_LOGIN ,FND_API.G_MISS_NUM,NULL,x_LAST_UPDATE_LOGIN),
97 DECODE(x_QUOTE_HEADER_ID ,FND_API.G_MISS_NUM,NULL,x_QUOTE_HEADER_ID),
98 DECODE(x_PARTY_ID ,FND_API.G_MISS_NUM,NULL,x_PARTY_ID),
99 DECODE(x_CUST_ACCOUNT_ID ,FND_API.G_MISS_NUM,NULL,x_CUST_ACCOUNT_ID),
100 DECODE(x_RECORD_TYPE ,FND_API.G_MISS_CHAR,NULL,x_RECORD_TYPE),
101 DECODE(x_ORDER_HEADER_ID ,FND_API.G_MISS_NUM,NULL,x_ORDER_HEADER_ID),
102 DECODE(x_CURRENCY_CODE ,FND_API.G_MISS_CHAR,NULL,x_CURRENCY_CODE),
103 DECODE(l_ORG_ID ,FND_API.G_MISS_NUM,NULL,l_ORG_ID));
104
105 end INSERT_ROW;
106
107 procedure UPDATE_ROW (
108 X_OBJECT_VERSION_NUMBER in NUMBER,
109 X_QUOTE_HEADER_ID in NUMBER,
110 X_PARTY_ID in NUMBER,
111 X_CUST_ACCOUNT_ID in NUMBER,
112 X_ORDER_HEADER_ID in NUMBER,
113 X_RECORD_TYPE in VARCHAR2,
114 X_CURRENCY_CODE in VARCHAR2,
115 X_LAST_UPDATE_DATE in DATE,
116 X_LAST_UPDATED_BY in NUMBER,
117 X_LAST_UPDATE_LOGIN in NUMBER
118 ) is
119 L_ORG_ID NUMBER := 204; --$$check this hard coding$$
120 l_last_update_login NUMBER;
121 begin
122 l_last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
123 l_org_id := MO_GLOBAL.get_current_org_id();
124
125 --$$GET THE ORG_ID HERE$$
126 if (X_RECORD_TYPE = 'CART')
127 then
128
129 update IBE_ACTIVE_QUOTES_ALL set
130 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
131 QUOTE_HEADER_ID = DECODE(X_QUOTE_HEADER_ID,FND_API.G_MISS_NUM,NULL,X_QUOTE_HEADER_ID),
132 ORDER_HEADER_ID = DECODE(X_ORDER_HEADER_ID,FND_API.G_MISS_NUM,NULL,X_ORDER_HEADER_ID),
133 PARTY_ID = X_PARTY_ID,
134 CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID,
135 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
136 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
138 where nvl(ORG_ID,-99) = nvl(l_org_id,-99)
139 AND PARTY_ID = X_PARTY_ID
140 AND CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
141 AND RECORD_TYPE = X_RECORD_TYPE;
142
143 elsif (X_RECORD_TYPE='ORDER') then
144
145 update IBE_ACTIVE_QUOTES_ALL set
146 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
147 QUOTE_HEADER_ID = DECODE(X_QUOTE_HEADER_ID,FND_API.G_MISS_NUM,NULL,X_QUOTE_HEADER_ID),
148 ORDER_HEADER_ID = DECODE(X_ORDER_HEADER_ID,FND_API.G_MISS_NUM,NULL,X_ORDER_HEADER_ID),
149 PARTY_ID = X_PARTY_ID,
150 CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID,
151 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
152 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
154 where nvl(ORG_ID,-99) = nvl(l_org_id,-99)
155 AND PARTY_ID = X_PARTY_ID
156 AND CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
157 AND RECORD_TYPE = X_RECORD_TYPE
158 AND CURRENCY_CODE = X_CURRENCY_CODE;
159 end if;
160
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164 end UPDATE_ROW;
165
166 procedure DELETE_ROW (
167 X_QUOTE_HEADER_ID IN NUMBER,
168 X_PARTY_ID IN NUMBER,
169 X_ORDER_HEADER_ID IN NUMBER,
170 X_CURRENCY_CODE IN VARCHAR2,
171 X_RECORD_TYPE IN VARCHAR2,
172 X_CUST_ACCOUNT_ID IN NUMBER
173 ) is
174 begin
175
176 IF (X_RECORD_TYPE = 'CART')
177 THEN
178 delete from IBE_ACTIVE_QUOTES
179 where quote_header_id = x_quote_header_id
180 and party_id = x_party_id
181 and cust_account_id = x_cust_account_id
182 and record_type = x_record_type;
183
184 ELSIF (X_RECORD_TYPE = 'ORDER')
185 THEN
186 delete from IBE_ACTIVE_QUOTES
187 where order_header_id = x_order_header_id
188 and party_id = x_party_id
189 and cust_account_id = x_cust_account_id
190 and record_type = x_record_type
191 and currency_code = x_currency_code;
192 END IF;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197
198 end DELETE_ROW;
199
200 end IBE_ACTIVE_QUOTES_ALL_PKG;