1 PACKAGE RLM_SETUP_TERMS_SV AUTHID CURRENT_USER AS
2 /* $Header: RLMSETTS.pls 120.0 2005/05/26 17:16:25 appldev noship $ */
3
4 --<TPA_PUBLIC_NAME=RLM_TPA_SV>
5 --<TPA_PUBLIC_FILE_NAME=RLMTPDP>
6
7 /*============================================================================
8 PACKAGE NAME: rlm_setup_terms_sv
9
10 DESCRIPTION: Contains procedures that populate RLM setup terms
11 record at three levels: customer, address and
12 customer item.
13
14 CLIENT/SERVER: Server
15
16 LIBRARY NAME: None
17
18 OWNER: JAUTOMO
19
20 PROCEDURE/FUNCTIONS: get_setup_terms()
21 populate_record_cust()
22 populate_record_add()
23 populate_record_item()
24
25 ===========================================================================*/
26 C_SDEBUG NUMBER := rlm_core_sv.C_LEVEL6;
27 C_DEBUG NUMBER := rlm_core_sv.C_LEVEL7;
28
29
30 /* Global type setup_terms_rec_typ is defined as a record here,
31 for other programs to use */
32
33 TYPE setup_terms_rec_typ is RECORD (
34
35 cust_shipto_terms_id RLM_CUST_SHIPTO_TERMS.CUST_SHIPTO_TERMS_ID%TYPE,
36 customer_id RLM_CUST_SHIPTO_TERMS.CUSTOMER_ID%TYPE,
37 cum_control_code RLM_CUST_SHIPTO_TERMS.CUM_CONTROL_CODE%TYPE,
38 cum_org_level_code RLM_CUST_SHIPTO_TERMS.CUM_ORG_LEVEL_CODE%TYPE,
39 cum_shipment_rule_code RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
40 cum_yesterd_time_cutoff RLM_CUST_SHIPTO_TERMS.CUM_YESTERD_TIME_CUTOFF%TYPE,
41 cust_assign_supplier_cd RLM_CUST_SHIPTO_TERMS.CUST_ASSIGN_SUPPLIER_CD%TYPE,
42 customer_rcv_calendar_cd RLM_CUST_SHIPTO_TERMS.CUSTOMER_RCV_CALENDAR_CD%TYPE,
43 supplier_shp_calendar_cd RLM_CUST_SHIPTO_TERMS.SUPPLIER_SHP_CALENDAR_CD%TYPE,
44 unship_firm_cutoff_days RLM_CUST_SHIPTO_TERMS.UNSHIP_FIRM_CUTOFF_DAYS%TYPE,
45 unshipped_firm_disp_cd RLM_CUST_SHIPTO_TERMS.UNSHIPPED_FIRM_DISP_CD%TYPE,
46 inactive_date RLM_CUST_SHIPTO_TERMS.INACTIVE_DATE%TYPE,
47 critical_attribute_key RLM_CUST_SHIPTO_TERMS.CRITICAL_ATTRIBUTE_KEY%TYPE,
48 schedule_hierarchy_code RLM_CUST_SHIPTO_TERMS.SCHEDULE_HIERARCHY_CODE%TYPE,
49 comments RLM_CUST_SHIPTO_TERMS.COMMENTS%TYPE,
50 intransit_time RLM_CUST_SHIPTO_TERMS.INTRANSIT_TIME%TYPE,
51 time_uom_code RLM_CUST_SHIPTO_TERMS.TIME_UOM_CODE%TYPE,
52 ship_from_org_id RLM_CUST_SHIPTO_TERMS.SHIP_FROM_ORG_ID%TYPE,
53 address_id RLM_CUST_ITEM_TERMS.ADDRESS_ID%TYPE,
54 header_id RLM_CUST_ITEM_TERMS.HEADER_ID%TYPE,
55 agreement_id RLM_CUST_ITEM_TERMS.AGREEMENT_ID%TYPE,
56 agreement_name RLM_CUST_ITEM_TERMS.AGREEMENT_NAME%TYPE,
57 future_agreement_id RLM_CUST_ITEM_TERMS.FUTURE_AGREEMENT_ID%TYPE,
58 future_agreement_name RLM_CUST_ITEM_TERMS.FUTURE_AGREEMENT_NAME%TYPE,
59 round_to_std_pack_flag RLM_CUST_ITEM_TERMS.ROUND_TO_STD_PACK_FLAG%TYPE,
60 ship_delivery_rule_name RLM_CUST_ITEM_TERMS.SHIP_DELIVERY_RULE_NAME%TYPE,
61 ship_method RLM_CUST_ITEM_TERMS.SHIP_METHOD%TYPE,
62 std_pack_qty RLM_CUST_ITEM_TERMS.STD_PACK_QTY%TYPE,
63 price_list_id RLM_CUST_ITEM_TERMS.PRICE_LIST_ID%TYPE,
64 use_edi_sdp_code_flag RLM_CUST_ITEM_TERMS.USE_EDI_SDP_CODE_FLAG%TYPE,
65 match_across_key RLM_CUST_SHIPTO_TERMS.MATCH_ACROSS_KEY%TYPE,
66 match_within_key RLM_CUST_SHIPTO_TERMS.MATCH_WITHIN_KEY%TYPE,
67 pln_firm_day_to RLM_CUST_ITEM_TERMS.PLN_FIRM_DAY_TO%TYPE,
68 pln_firm_day_from RLM_CUST_ITEM_TERMS.PLN_FIRM_DAY_FROM%TYPE,
69 pln_forecast_day_from RLM_CUST_ITEM_TERMS.PLN_FORECAST_DAY_FROM%TYPE,
70 pln_forecast_day_to RLM_CUST_ITEM_TERMS.PLN_FORECAST_DAY_TO%TYPE,
71 pln_frozen_day_to RLM_CUST_ITEM_TERMS.PLN_FROZEN_DAY_TO%TYPE,
72 pln_frozen_day_from RLM_CUST_ITEM_TERMS.PLN_FROZEN_DAY_FROM%TYPE,
73 seq_firm_day_from RLM_CUST_ITEM_TERMS.SEQ_FIRM_DAY_FROM%TYPE,
74 seq_firm_day_to RLM_CUST_ITEM_TERMS.SEQ_FIRM_DAY_TO%TYPE,
75 seq_forecast_day_to RLM_CUST_ITEM_TERMS.SEQ_FORECAST_DAY_TO%TYPE,
76 seq_forecast_day_from RLM_CUST_ITEM_TERMS.SEQ_FORECAST_DAY_FROM%TYPE,
77 seq_frozen_day_from RLM_CUST_ITEM_TERMS.SEQ_FROZEN_DAY_FROM%TYPE,
78 seq_frozen_day_to RLM_CUST_ITEM_TERMS.SEQ_FROZEN_DAY_TO%TYPE,
79 shp_firm_day_from RLM_CUST_ITEM_TERMS.SHP_FIRM_DAY_FROM%TYPE,
80 shp_firm_day_to RLM_CUST_ITEM_TERMS.SHP_FIRM_DAY_TO%TYPE,
81 shp_frozen_day_from RLM_CUST_ITEM_TERMS.SHP_FROZEN_DAY_FROM%TYPE,
82 shp_frozen_day_to RLM_CUST_ITEM_TERMS.SHP_FROZEN_DAY_TO%TYPE,
83 shp_forecast_day_from RLM_CUST_ITEM_TERMS.SHP_FORECAST_DAY_FROM%TYPE,
84 shp_forecast_day_to RLM_CUST_ITEM_TERMS.SHP_FORECAST_DAY_TO%TYPE,
85 demand_tolerance_above RLM_CUST_ITEM_TERMS.DEMAND_TOLERANCE_ABOVE%TYPE,
86 demand_tolerance_below RLM_CUST_ITEM_TERMS.DEMAND_TOLERANCE_BELOW%TYPE,
87 customer_contact_id RLM_CUST_ITEM_TERMS.CUSTOMER_CONTACT_ID%TYPE,
88 freight_code RLM_CUST_ITEM_TERMS.FREIGHT_CODE%TYPE,
89 supplier_contact_id RLM_CUST_ITEM_TERMS.SUPPLIER_CONTACT_ID%TYPE,
90 attribute_category RLM_CUST_ITEM_TERMS.ATTRIBUTE_CATEGORY%TYPE,
91 tp_attribute_category RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE_CATEGORY%TYPE,
92 attribute1 RLM_CUST_ITEM_TERMS.ATTRIBUTE1%TYPE,
93 attribute2 RLM_CUST_ITEM_TERMS.ATTRIBUTE2%TYPE,
94 attribute3 RLM_CUST_ITEM_TERMS.ATTRIBUTE3%TYPE,
95 attribute4 RLM_CUST_ITEM_TERMS.ATTRIBUTE4%TYPE,
96 attribute5 RLM_CUST_ITEM_TERMS.ATTRIBUTE5%TYPE,
97 attribute6 RLM_CUST_ITEM_TERMS.ATTRIBUTE6%TYPE,
98 attribute7 RLM_CUST_ITEM_TERMS.ATTRIBUTE7%TYPE,
99 attribute8 RLM_CUST_ITEM_TERMS.ATTRIBUTE8%TYPE,
100 attribute9 RLM_CUST_ITEM_TERMS.ATTRIBUTE9%TYPE,
101 attribute10 RLM_CUST_ITEM_TERMS.ATTRIBUTE10%TYPE,
102 attribute11 RLM_CUST_ITEM_TERMS.ATTRIBUTE11%TYPE,
103 attribute12 RLM_CUST_ITEM_TERMS.ATTRIBUTE12%TYPE,
104 attribute13 RLM_CUST_ITEM_TERMS.ATTRIBUTE13%TYPE,
105 attribute14 RLM_CUST_ITEM_TERMS.ATTRIBUTE14%TYPE,
106 attribute15 RLM_CUST_ITEM_TERMS.ATTRIBUTE15%TYPE,
107 tp_attribute1 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE1%TYPE,
108 tp_attribute2 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE2%TYPE,
109 tp_attribute3 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE3%TYPE,
110 tp_attribute4 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE4%TYPE,
111 tp_attribute5 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE5%TYPE,
112 tp_attribute6 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE6%TYPE,
113 tp_attribute7 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE7%TYPE,
114 tp_attribute8 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE8%TYPE,
115 tp_attribute9 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE9%TYPE,
116 tp_attribute10 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE10%TYPE,
117 tp_attribute11 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE11%TYPE,
118 tp_attribute12 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE12%TYPE,
119 tp_attribute13 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE13%TYPE,
120 tp_attribute14 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE14%TYPE,
121 tp_attribute15 RLM_CUST_ITEM_TERMS.TP_ATTRIBUTE15%TYPE,
122 cust_item_terms_id RLM_CUST_ITEM_TERMS.CUST_ITEM_TERMS_ID%TYPE,
123 customer_item_id RLM_CUST_ITEM_TERMS.CUSTOMER_ITEM_ID%TYPE,
124 calc_cum_flag RLM_CUST_ITEM_TERMS.CALC_CUM_FLAG%TYPE,
125 cust_item_status_code RLM_CUST_ITEM_TERMS.CUST_ITEM_STATUS_CODE%TYPE,
126 pln_mrp_forecast_day_from RLM_CUST_ITEM_TERMS.PLN_MRP_FORECAST_DAY_FROM%TYPE,
127 pln_mrp_forecast_day_to RLM_CUST_ITEM_TERMS.PLN_MRP_FORECAST_DAY_TO%TYPE,
128 shp_mrp_forecast_day_from RLM_CUST_ITEM_TERMS.SHP_MRP_FORECAST_DAY_FROM%TYPE,
129 shp_mrp_forecast_day_to RLM_CUST_ITEM_TERMS.SHP_MRP_FORECAST_DAY_TO%TYPE,
130 seq_mrp_forecast_day_from RLM_CUST_ITEM_TERMS.SEQ_MRP_FORECAST_DAY_FROM%TYPE,
131 seq_mrp_forecast_day_to RLM_CUST_ITEM_TERMS.SEQ_MRP_FORECAST_DAY_TO%TYPE,
132 -- Bug# 1426313
133 msg_name VARCHAR2(30),
134 intransit_calc_basis RLM_CUST_SHIPTO_TERMS.INTRANSIT_CALC_BASIS%TYPE,
135 pln_frozen_flag RLM_CUST_ITEM_TERMS.PLN_FROZEN_FLAG%TYPE,
136 shp_frozen_flag RLM_CUST_ITEM_TERMS.SHP_FROZEN_FLAG%TYPE,
137 seq_frozen_flag RLM_CUST_ITEM_TERMS.SEQ_FROZEN_FLAG%TYPE,
138 issue_warning_drop_parts_flag RLM_CUST_ITEM_TERMS.ISSUE_WARNING_DROP_PARTS_FLAG%TYPE,
139 --
140 -- for blankets
141 blanket_number RLM_CUST_SHIPTO_TERMS.BLANKET_NUMBER%TYPE,
142 release_rule RLM_CUST_SHIPTO_TERMS.RELEASE_RULE%TYPE,
143 release_time_frame RLM_CUST_SHIPTO_TERMS.RELEASE_TIME_FRAME%TYPE,
144 release_time_frame_uom RLM_CUST_SHIPTO_TERMS.RELEASE_TIME_FRAME_UOM%TYPE,
145 exclude_non_workdays_flag RLM_CUST_SHIPTO_TERMS.EXCLUDE_NON_WORKDAYS_FLAG%TYPE,
146 disable_create_cum_key_flag RLM_CUST_ITEM_TERMS.disable_create_cum_key_flag%TYPE );
147
148
149 /*=============================================================================
150 PROCEDURE NAME: get_setup_terms
151
152 DESCRIPTION: This serves as a top level routine from which all
153 other procedures in the package will be called. It
154 will be called to retrieve all setup terms at the
155 specified terms definition level or at the lowest
156 terms definition level if not specified. It sorts
157 out exceptions and branches different cases based
158 on the input parameters to determine which other
159 procedures within the package to call
160
161 PARAMETERS: x_ship_from_org_id IN NUMBER
162 x_customer_id IN NUMBER
163 x_ship_to_address_id IN NUMBER
164 x_customer_item_id IN NUMBER
165 x_terms_definition_level IN OUT NOCOPY VARCHAR2
166 x_terms_rec OUT NOCOPY setup_terms_rec_typ
167 x_return_message OUT NOCOPY VARCHAR2
168 x_return_status OUT NOCOPY BOOLEAN
169
170 Valid input values for x_terms_definition_level: 'CUSTOMER',
171 'ADDRESS',
172 'CUSTOMER_ITEM'
173 ============================================================================*/
174
175 PROCEDURE get_setup_terms (
176 x_ship_from_org_id IN NUMBER,
177 x_customer_id IN NUMBER,
178 x_ship_to_address_id IN NUMBER,
179 x_customer_item_id IN NUMBER,
180 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
181 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
182 x_return_message OUT NOCOPY VARCHAR2,
183 x_return_status OUT NOCOPY BOOLEAN);
184
185 --<TPA_PUBLIC_NAME>
186
187 /*=============================================================================
188 PROCEDURE NAME: populate_record_cust
189
190 DESCRIPTION: This procedure is called by get_setup_terms
191 cover procedure or possibly populate_record_add
192 procedure to populate the x_rla_setup_terms_rec
193 record with the CUSTOMER level terms.
194 Ship_to_address_id and customer_item_id information
195 are not needed by this procedure.
196
197 PARAMETERS: x_ship_from_org_id IN NUMBER
198 x_customer_id IN NUMBER
199 x_terms_definition_level IN OUT NOCOPY NUMBER
200 x_terms_rec OUT NOCOPY setup_terms_rec_typ
201 x_return_message IN OUT NOCOPY VARCHAR2,
202 x_return_status OUT NOCOPY BOOLEAN
203 ============================================================================*/
204
205 PROCEDURE populate_record_cust (
206 x_ship_from_org_id IN NUMBER,
207 x_customer_id IN NUMBER,
208 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
209 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
210 x_return_message IN OUT NOCOPY VARCHAR2,
211 x_return_status OUT NOCOPY BOOLEAN);
212
213 --<TPA_PUBLIC_NAME>
214
215 /*=============================================================================
216 PROCEDURE NAME: populate_record_add
217
218 DESCRIPTION: This procedure is called by get_setup_terms
219 cover procedure and possibly populate_record_item
220 procedure to populate the x_rla_setup_terms_rec
221 record with the ADDRESS level terms.
222 Customer_item_id information is not needed by this
223 procedure.
224
225 PARAMETERS: x_ship_from_org_id IN NUMBER
226 x_customer_id IN NUMBER
227 x_ship_to_address_id IN NUMBER
228 x_terms_definition_level IN OUT NOCOPY NUMBER
229 x_terms_rec OUT NOCOPY setup_terms_rec_typ
230 x_return_message IN OUT NOCOPY VARCHAR2,
231 x_return_status OUT NOCOPY BOOLEAN
232 ============================================================================*/
233
234 PROCEDURE populate_record_add (
235 x_ship_from_org_id IN NUMBER,
236 x_customer_id IN NUMBER,
237 x_ship_to_address_id IN NUMBER,
238 x_customer_item_id IN NUMBER,
239 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
240 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
241 x_return_message IN OUT NOCOPY VARCHAR2,
242 x_return_status OUT NOCOPY BOOLEAN);
243
244 --<TPA_PUBLIC_NAME>
245
246 /*=============================================================================
247 PROCEDURE NAME: populate_record_item
248
249 DESCRIPTION: This procedure is called by get_setup_terms
250 cover procedure to populate the x_rla_setup_terms_rec
251 record with the ADDRESS ITEM level terms.
252
253 PARAMETERS: x_ship_from_org_id IN NUMBER
254 x_customer_id IN NUMBER
255 x_ship_to_address_id IN NUMBER
256 x_customer_item_id IN NUMBER
257 x_terms_definition_level IN OUT NOCOPY NUMBER
258 x_terms_rec OUT NOCOPY setup_terms_rec_typ
259 x_return_message IN OUT NOCOPY VARCHAR2,
260 x_return_status OUT NOCOPY BOOLEAN
261 ============================================================================*/
262
263 PROCEDURE populate_record_item (
264 x_ship_from_org_id IN NUMBER,
265 x_customer_id IN NUMBER,
266 x_ship_to_address_id IN NUMBER,
267 x_customer_item_id IN NUMBER,
268 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
269 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
270 x_return_message IN OUT NOCOPY VARCHAR2,
271 x_return_status OUT NOCOPY BOOLEAN);
272
273
274 --<TPA_PUBLIC_NAME>
275
276 /*=============================================================================
277 PROCEDURE NAME: populate_record_cust_item
278
279 DESCRIPTION: This procedure is called by get_setup_terms
280 cover procedure to populate the x_rla_setup_terms_rec
281 record with the CUSTOMER ITEM level terms.
282
283 PARAMETERS: x_ship_from_org_id IN NUMBER
284 x_customer_id IN NUMBER
285 x_ship_to_address_id IN NUMBER
286 x_customer_item_id IN NUMBER
287 x_terms_definition_level IN OUT NOCOPY NUMBER
288 x_terms_rec OUT NOCOPY setup_terms_rec_typ
289 x_return_message IN OUT NOCOPY VARCHAR2,
290 x_return_status OUT NOCOPY BOOLEAN
291 ============================================================================*/
292
293 PROCEDURE populate_record_cust_item (
294 x_ship_from_org_id IN NUMBER,
295 x_customer_id IN NUMBER,
296 x_ship_to_address_id IN NUMBER,
297 x_customer_item_id IN NUMBER,
298 x_terms_definition_level IN OUT NOCOPY VARCHAR2,
299 x_terms_rec OUT NOCOPY rlm_setup_terms_sv.setup_terms_rec_typ,
300 x_return_message IN OUT NOCOPY VARCHAR2,
301 x_return_status OUT NOCOPY BOOLEAN);
302
303 --<TPA_PUBLIC_NAME>
304
305
306 /*=============================================================================
307 PROCEDURE NAME: GetTPContext
308
309 DESCRIPTION: This procedure returns the tpcontext
310
311 PARAMETERS: x_customer_id IN NUMBER DEFAULT NULL
312 x_ship_to_address_id IN NUMBER DEFAULT NULL
313 x_customer_number OUT NOCOPY VARCHAR2
314 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
315 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2
316 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
317 x_tp_group_code OUT NOCOPY VARCHAR2
318
319 ============================================================================*/
320 PROCEDURE GetTPContext(x_customer_id IN NUMBER DEFAULT NULL,
321 x_ship_to_address_id IN NUMBER DEFAULT NULL,
322 x_customer_number OUT NOCOPY VARCHAR2,
323 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
324 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
325 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
326 x_tp_group_code OUT NOCOPY VARCHAR2);
327 --<TPA_TPS>
328
329
330 END RLM_SETUP_TERMS_SV;
[Home] [Help]