[Home] [Help]
PACKAGE: APPS.OE_ITORD_UTIL
Source
1 PACKAGE OE_ITORD_UTIL AS
2 /* $Header: OEITORDS.pls 120.4.12010000.2 2008/08/26 09:12:17 smanian ship $ */
3
4
5 TYPE Item_Orderability_Rec IS RECORD
6 (
7 orderability_id number
8 ,org_id number
9 ,item_category_id number
10 ,inventory_item_id number
11 ,item_level varchar2(1)
12 ,generally_available varchar2(1)
13 ,enable_flag varchar2(1)
14 ,created_by number
15 ,creation_date date
16 ,last_updated_by number
17 ,last_update_date date
18 );
19
20 TYPE Item_Orderability_Rules_Rec IS RECORD
21 (
22 ORDERABILITY_ID NUMBER,
23 RULE_LEVEL VARCHAR2(30),
24 CUSTOMER_ID NUMBER,
25 CUSTOMER_CLASS_ID NUMBER,
26 CUSTOMER_CATEGORY_CODE VARCHAR2(30),
27 REGION_ID NUMBER,
28 ORDER_TYPE_ID NUMBER,
29 SHIP_TO_LOCATION_ID NUMBER,
30 SALES_CHANNEL_CODE VARCHAR2(30),
31 SALES_PERSON_ID NUMBER,
32 END_CUSTOMER_ID NUMBER,
33 BILL_TO_LOCATION_ID NUMBER,
34 DELIVER_TO_LOCATION_ID NUMBER,
35 ENABLE_FLAG VARCHAR2(1),
36 CREATED_BY NUMBER,
37 CREATION_DATE DATE,
38 LAST_UPDATED_BY NUMBER,
39 LAST_UPDATE_DATE DATE,
40 CONTEXT VARCHAR2(250),
41 ATTRIBUTE1 VARCHAR2(250),
42 ATTRIBUTE2 VARCHAR2(250),
43 ATTRIBUTE3 VARCHAR2(250),
44 ATTRIBUTE4 VARCHAR2(250),
45 ATTRIBUTE5 VARCHAR2(250),
46 ATTRIBUTE6 VARCHAR2(250),
47 ATTRIBUTE7 VARCHAR2(250),
48 ATTRIBUTE8 VARCHAR2(250),
49 ATTRIBUTE9 VARCHAR2(250),
50 ATTRIBUTE10 VARCHAR2(250),
51 ATTRIBUTE11 VARCHAR2(250),
52 ATTRIBUTE12 VARCHAR2(250),
53 ATTRIBUTE13 VARCHAR2(250),
54 ATTRIBUTE14 VARCHAR2(250),
55 ATTRIBUTE15 VARCHAR2(250),
56 ATTRIBUTE16 VARCHAR2(250),
57 ATTRIBUTE17 VARCHAR2(250),
58 ATTRIBUTE18 VARCHAR2(250),
59 ATTRIBUTE19 VARCHAR2(250),
60 ATTRIBUTE20 VARCHAR2(250)
61 );
62
63
64 PROCEDURE Insert_Row
65 ( p_item_orderability_rec IN OE_ITORD_UTIL.Item_Orderability_Rec
66 , x_return_status OUT NOCOPY VARCHAR2
67 );
68
69
70 PROCEDURE Update_Row
71 ( p_item_orderability_rec IN OE_ITORD_UTIL.Item_Orderability_Rec
72 , x_return_status OUT NOCOPY VARCHAR2
73 );
74
75 PROCEDURE Insert_Row
76 ( p_item_orderability_rules_rec IN OE_ITORD_UTIL.Item_Orderability_Rules_Rec
77 , x_return_status OUT NOCOPY VARCHAR2
78 , x_rowid OUT NOCOPY ROWID
79 );
80
81 PROCEDURE Update_Row
82 ( p_item_orderability_rules_rec IN OE_ITORD_UTIL.Item_Orderability_Rules_Rec
83 , p_row_id IN ROWID
84 , x_return_status OUT NOCOPY VARCHAR2
85 );
86
87 Procedure REFRESH_MATERIALIZED_VIEW
88 (
89 ERRBUF OUT NOCOPY VARCHAR2,
90 RETCODE OUT NOCOPY VARCHAR2
91 );
92
93 FUNCTION Check_Duplicate_Rules ( l_sql_stmt varchar2)
94 RETURN BOOLEAN;
95
96
97
98 --Following are the attributes based on which item orderability rules can be defined
99 G_CUSTOMER_ID NUMBER;
100 G_CUSTOMER_CLASS_ID NUMBER;
101 G_CUSTOMER_CATEGORY_CODE VARCHAR2(30);
102 G_REGION_ID_LIST VARCHAR2(32000);
103 G_ORDER_TYPE_ID NUMBER;
104 G_SHIP_TO_ORG_ID NUMBER;
105 G_SALES_CHANNEL_CODE VARCHAR2(30);
106 G_SALESREP_ID NUMBER;
107 G_END_CUSTOMER_ID NUMBER;
108 G_INVOICE_TO_ORG_ID NUMBER;
109 G_DELIVER_TO_ORG_ID NUMBER;
110
111
112
113 --Following Variables are used to chace the values to avoid repeated execution of the same sql
114 G_SOLD_TO_ORG_ID NUMBER;
115 G_SHIP_TO_LOCATION_ID NUMBER;
116 G_CUSTOMER_PROFILE_CLASS_ID NUMBER;
117 G_SHIP_TO_ORGANIZATION_ID NUMBER;
118 G_SHIP_TO_REGION_ID_LIST VARCHAR2(32000);
119 G_INVENTORY_ITEM_ID NUMBER;
120 G_ITEM_CATEGORY_ID NUMBER;
121 G_CUST_ID NUMBER;
122 G_CUST_CATEGORY_CODE VARCHAR2(30);
123
124 G_OPERATING_UNIT_ID NUMBER;
125 G_ITEM_VALIDATION_ORG_ID NUMBER;
126
127 G_HEADER_ID NUMBER;
128 G_HDR_ID NUMBER;
129 G_SC_CODE VARCHAR2(30);
130 G_TRX_TYPE_ID NUMBER;
131
132
133
134 Procedure set_globals (
135 P_CUSTOMER_ID IN NUMBER,
136 P_CUSTOMER_CLASS_ID IN NUMBER,
137 P_CUSTOMER_CATEGORY_CODE IN VARCHAR2,
138 P_REGION_ID_LIST IN VARCHAR2,
139 P_ORDER_TYPE_ID IN NUMBER,
140 P_SHIP_TO_ORG_ID IN NUMBER,
141 P_SALES_CHANNEL_CODE IN VARCHAR2,
142 P_SALESREP_ID IN NUMBER,
143 P_END_CUSTOMER_ID IN NUMBER,
144 P_INVOICE_TO_ORG_ID IN NUMBER,
145 P_DELIVER_TO_ORG_ID IN NUMBER
146 );
147
148
149 --Following Functions will return the global variables (Referenced in views )
150 Function get_customer_id
151 Return Number;
152
153 Function get_customer_class_id
154 Return Number;
155
156
157 Function get_customer_category_code
158 Return Varchar2;
159
160 --bug7294798
161 Function get_region_ids
162 Return VARCHAR2;
163
164 Function get_order_type_id
165 Return Number;
166
167 Function get_ship_to_org_id
168 Return Number;
169
170 Function get_sales_channel_code
171 Return Varchar2;
172
173 Function get_salesrep_id
174 Return Number;
175
176 Function get_end_customer_id
177 Return Number;
178
179
180 Function get_invoice_to_org_id
181 Return Number;
182
183 Function get_deliver_to_org_id
184 Return Number;
185
186 Function get_operating_unit_id
187 Return Number;
188
189 Function get_item_validation_org_id
190 Return Number;
191
192 --bug7294798
193 Function get_region_ids ( p_ship_to_org_id IN NUMBER)
194 Return varchar2;
195
196
197 Function get_item_category_id ( p_inventory_item_id IN Number )
198 Return Number;
199
200 Function get_customer_class_id ( p_customer_id IN Number )
201 Return Number;
202
203 Function get_customer_category_code ( p_customer_id IN NUMBER )
204 Return Varchar2;
205
206
207
208 Function Validate_item_orderability ( p_line_rec IN OE_Order_PUB.Line_Rec_Type )
209 Return BOOLEAN;
210
211 Function Validate_item_orderability ( p_org_id IN NUMBER,
212 p_line_id IN NUMBER,
213 p_header_id IN NUMBER,
214 p_inventory_item_id IN NUMBER,
215 p_sold_to_org_id IN NUMBER,
216 p_ship_to_org_id IN NUMBER,
217 p_salesrep_id IN NUMBER,
218 p_end_customer_id IN NUMBER,
219 p_invoice_to_org_id IN NUMBER,
220 p_deliver_to_org_id IN NUMBER )
221 Return BOOLEAN;
222
223 Function get_item_name(p_inventory_item_id IN NUMBER )
224 RETURN VARCHAR2;
225
226 Function get_item_category_name(p_inventory_item_id IN NUMBER )
227 RETURN VARCHAR2;
228
229 FUNCTION GET_RULE_LEVEL_VALUE ( P_RULE_LEVEL varchar2
230 , P_RULE_LEVEL_VALUE varchar2
231 )
232 RETURN VARCHAR2;
233
234 Function Get_Shipto_Location_Id ( p_site_use_id IN NUMBER)
235 Return NUMBER;
236
237 Function get_sales_channel_code (p_header_id IN NUMBER)
238 Return Varchar2;
239
240 Function get_order_type_id (p_header_id IN NUMBER)
241 Return Number;
242
243 END OE_ITORD_UTIL;