1 PACKAGE RLM_XML_API AS
2 /* $Header: RLMXMLPS.pls 120.6 2005/08/16 13:06:34 mnandell noship $*/
3
4 k_DELIVER CONSTANT VARCHAR2(10) := 'DELIVER';
5 k_RECEIPT CONSTANT VARCHAR2(10) := 'RECEIPT';
6 k_SHIPMENT CONSTANT VARCHAR2(10) := 'SHIPMENT';
7 k_RECEIVED CONSTANT VARCHAR2(10) := 'RECEIVED';
8 k_SHIPPED CONSTANT VARCHAR2(10) := 'SHIPPED';
9 k_DEMAND CONSTANT VARCHAR2(10) := 'DEMAND';
10 k_ASOF CONSTANT VARCHAR2(10) := 'AS_OF';
11 k_AHDBHND CONSTANT VARCHAR2(12) := 'AHEAD_BEHIND';
12 k_FINISHED CONSTANT VARCHAR2(10) := 'FINISHED';
13 k_FROMTO CONSTANT VARCHAR2(10) := 'FROM_TO';
14
15 k_ERROR CONSTANT NUMBER := 1;
16 k_SUCCESS CONSTANT NUMBER := 0;
17
18 k_VNULL CONSTANT VARCHAR2(25) := 'THIS_IS_A_NULL_VALUE';
19 k_DNULL CONSTANT DATE := to_date('01/01/1930','dd/mm/yyyy');
20 k_NNULL CONSTANT NUMBER := -19999999999;
21
22 C_SDEBUG CONSTANT NUMBER := rlm_core_sv.C_LEVEL7;
23 C_DEBUG CONSTANT NUMBER := rlm_core_sv.C_LEVEL8;
24
25 TYPE t_Cursor_ref IS REF CURSOR;
26
27 TYPE t_ItemAttribsRec IS RECORD (
28 ship_from_ext RLM_INTERFACE_LINES.cust_ship_from_org_ext%TYPE,
29 ship_to_ext RLM_INTERFACE_LINES.cust_ship_to_ext%TYPE,
30 bill_to_ext RLM_INTERFACE_LINES.cust_bill_to_ext%TYPE,
31 cust_item_ext RLM_INTERFACE_LINES.customer_item_ext%TYPE,
32 item_desc_ext RLM_INTERFACE_LINES.item_description_ext%TYPE,
33 cust_dock_code RLM_INTERFACE_LINES.customer_dock_code%TYPE,
34 hazrd_code_ext RLM_INTERFACE_LINES.hazard_code_ext%TYPE,
35 cust_item_rev RLM_INTERFACE_LINES.customer_item_revision%TYPE,
36 item_note_text RLM_INTERFACE_LINES.item_note_text%TYPE,
37 cust_po_num RLM_INTERFACE_LINES.cust_po_number%TYPE,
38 cust_po_linnum RLM_INTERFACE_LINES.cust_po_line_num%TYPE,
39 cust_po_relnum RLM_INTERFACE_LINES.cust_po_release_num%TYPE,
40 cust_po_date RLM_INTERFACE_LINES.cust_po_date%TYPE,
41 commodity_ext RLM_INTERFACE_LINES.commodity_ext%TYPE,
42 sup_item_ext RLM_INTERFACE_LINES.supplier_item_ext%TYPE);
43
44
45 TYPE t_SchdItemAttribs IS TABLE OF t_ItemAttribsRec
46 INDEX BY BINARY_INTEGER;
47
48 g_SchedItemTab t_SchdItemAttribs;
49
50
51 /*==========================================================================
52 PROCEDURE NAME ValidateScheduleType
53
54 DESCRIPTION This procedure is used to validate the value of the
55 <SCHDULETYP> element. If this is anything other than
56 DEMAND, the XML message is rejected.
57
58 DESIGN REFERENCES rlmxmldld.rtf
59
60 CHANGE HISTORY rlanka 02/22/02 Created
61 ============================================================================*/
62 PROCEDURE ValidateScheduleType(x_SchedType IN VARCHAR2, x_RetCode OUT NOCOPY NUMBER);
63
64
65 /*==========================================================================
66 PROCEDURE NAME SetSSSILineDetails
67
68 DESCRIPTION This procedure is used to set the external values of
69 item_detail_type, item_detail_subtype, date_type_code, and
70 item_detail_quantity for a SSSI schedule
71
72 DESIGN REFERENCES rlmxmldld.rtf
73
74 CHANGE HISTORY rlanka 02/22/02 Created
75 ============================================================================*/
76 PROCEDURE SetSSSILineDetails(x_LineType IN VARCHAR2,
77 x_ReqdDt IN DATE,
78 x_RecdDt IN DATE,
79 x_ShipDt IN DATE,
80 x_ItemQty IN NUMBER,
81 x_RecdQty IN NUMBER,
82 x_ShipQty IN NUMBER,
83 x_DateType IN VARCHAR2,
84 x_ItemUOM IN VARCHAR2,
85 x_RecdUOM IN VARCHAR2,
86 x_ShipUOM IN VARCHAR2,
87 x_StartDt OUT NOCOPY DATE,
88 x_Qty OUT NOCOPY NUMBER,
89 x_Subtype OUT NOCOPY VARCHAR2,
90 x_DateCode OUT NOCOPY VARCHAR2,
91 x_QtyUOM OUT NOCOPY VARCHAR2,
92 x_ErrCode IN OUT NOCOPY NUMBER,
93 x_ErrMsg IN OUT NOCOPY VARCHAR2);
94
95
96
97 /*==========================================================================
98 PROCEDURE NAME SetSPSILineDetails
99
100 DESCRIPTION This procedure is used to set the external values of
101 item_detail_type, item_detail_subtype, date_type_code, and
102 item_detail_quantity for a SPSI schedule
103
104 DESIGN REFERENCES rlmxmldld.rtf
105
106 CHANGE HISTORY rlanka 02/22/02 Created
107 ============================================================================*/
108 PROCEDURE SetSPSILineDetails(x_LineType IN VARCHAR2,
109 x_FromDt IN DATE,
110 x_ToDt IN DATE,
111 x_RecdDt IN DATE,
112 x_ShipDt IN DATE,
113 x_ItemQty IN NUMBER,
114 x_RecdQty IN NUMBER,
115 x_ShipQty IN NUMBER,
116 x_DateType IN VARCHAR2,
117 x_ItemUOM IN VARCHAR2,
118 x_RecdUOM IN VARCHAR2,
119 x_ShipUOM IN VARCHAR2,
120 x_BktType IN VARCHAR2,
121 x_StartDt OUT NOCOPY DATE,
122 x_EndDt OUT NOCOPY DATE,
123 x_Subtype OUT NOCOPY VARCHAR2,
124 x_Qty OUT NOCOPY NUMBER,
125 x_DateCode OUT NOCOPY VARCHAR2,
126 x_QtyUOM OUT NOCOPY VARCHAR2,
127 x_ErrCode IN OUT NOCOPY NUMBER,
128 x_ErrMsg IN OUT NOCOPY VARCHAR2);
129
130
131
132 /*==========================================================================
133 PROCEDURE NAME SetScheduleItemNum
134
135 DESCRIPTION This procedure is used to derive the schedule_item_num
136 for each line inserted. Makes use of the following
137 procedures/functions:
138 (1) Procedure InitializeSchedItemTab
139 (2) Function IsDuplicate
140 (3) Procedure InsertItemAttribsRec
141 (4) Procedure PrintSchedItemTab
142
143 DESIGN REFERENCES rlmxmldld.rtf
144
145 CHANGE HISTORY rlanka 02/22/02 Created
146 ============================================================================*/
147 PROCEDURE SetScheduleItemNum(x_HeaderID IN NUMBER,
148 x_ErrCode IN OUT NOCOPY NUMBER,
149 x_ErrMsg IN OUT NOCOPY VARCHAR2);
150
151
152
153 /*==========================================================================
154 PROCEDURE NAME PrintSchedItemTab
155
156 DESCRIPTION This procedure is used only for debugging purposes
157 and prints the values stored in the g_SchedItemTab
158 table.
159
160 DESIGN REFERENCES
161
162 CHANGE HISTORY rlanka 04/10/02 Created
163 ============================================================================*/
164 PROCEDURE PrintSchedItemTab;
165
166
167 /*==========================================================================
168 PROCEDURE NAME InitializeSchedItemTab
169
170 DESCRIPTION This procedure is used to initialize the
171 g_SchedItemTab table which has a row for every
172 unique combination of SF/ST/BT/CI and other attribs.
173
174 DESIGN REFERENCES
175
176 CHANGE HISTORY rlanka 04/10/02 Created
177 ============================================================================*/
178 PROCEDURE InitializeSchedItemTab (x_HeaderID IN NUMBER);
179
180
181 /*==========================================================================
182 FUNCTION NAME IsDuplicate
183
184 DESCRIPTION This function checks whether the item attribs record
185 currently exists in the g_SchedItemTab table. Return
186 TRUE if present, FALSE otherwise.
187
188 DESIGN REFERENCES
189
190 CHANGE HISTORY rlanka 04/10/02 Created
191 ============================================================================*/
192 FUNCTION IsDuplicate(x_ItemAttribsRec IN t_ItemAttribsRec) RETURN BOOLEAN;
193
194
195
196 /*==========================================================================
197 PROCEDURE NAME InsertItemAttribsRec
198
199 DESCRIPTION This procedure inserts an item attributes record
200 into g_SchedItemTab.
201
202 DESIGN REFERENCES rlmxmldld.rtf
203
204 CHANGE HISTORY rlanka 04/10/02 Created
205 ============================================================================*/
206 PROCEDURE InsertItemAttribRec(x_ItemAttribsRec IN t_ItemAttribsRec);
207
208
209
210 /*==========================================================================
211 PROCEDURE NAME UpdateLineNumbers
212
213 DESCRIPTION This procedure is used to assign values to column
214 line_number.
215
216 DESIGN REFERENCES rlmxmldld.rtf
217
218 CHANGE HISTORY rlanka 02/22/02 Created
219 ============================================================================*/
220 PROCEDURE UpdateLineNumbers(x_HeaderID IN NUMBER,
221 x_ErrCode IN OUT NOCOPY NUMBER,
222 x_ErrMsg IN OUT NOCOPY VARCHAR2);
223
224
225
226 /*==========================================================================
227 PROCEDURE NAME FlexBktAssignment
228
229 DESCRIPTION Updates the start/end date times on a line using the
230 details from the PSFLEXBKT segment on an SPSI schedule
231 if the BKTYPE matches any FLEXBKTID entry.
232
233 DESIGN REFERENCES rlmxmldld.rtf
234
235 CHANGE HISTORY rlanka 02/22/02 Created
236 ============================================================================*/
237 PROCEDURE FlexBktAssignment(x_header_id IN NUMBER,
238 x_ErrCode IN OUT NOCOPY NUMBER,
239 x_ErrMsg IN OUT NOCOPY VARCHAR2);
240
241 --MOAC: Added the following new procedure
242 /*==========================================================================
243 PROCEDURE NAME GetDefaultOU
244
245 DESCRIPTION This procedure is used to get the valu of
246 MO:Default Operating Unit which will be inserted
247 into the rlm tables once the XML transactions are
248 interfaced to RLM.
249
250 DESIGN REFERENCES MultiOrg_TDD.rtf
251
252 CHANGE HISTORY anviswan 03/21/05 Created
253 ============================================================================*/
254
255
256 PROCEDURE GetDefaultOU(x_default_ou IN OUT NOCOPY NUMBER,
257 x_ErrCode IN OUT NOCOPY NUMBER,
258 x_ErrMsg IN OUT NOCOPY VARCHAR2);
259
260 --CR: Added the following new procedure
261 /*==========================================================================
262 PROCEDURE NAME DeriveCustomerId
263
264 DESCRIPTION This procedure is used to Derive the Customer id from the
265 SourceTPLocationCode sent in the envelope and
266 assign to the ece_tp_location_code_ext
267
268 DESIGN REFERENCES TCACustomerRelationships_TDD.rtf
269
270 CHANGE HISTORY mnandell 06/21/05 Created
271 ============================================================================*/
272
273
274 PROCEDURE DeriveCustomerId(x_internalcontrolNum IN NUMBER,
275 x_SourceTPLocCode OUT NOCOPY VARCHAR2,
276 x_CustomerId OUT NOCOPY NUMBER,
277 x_ErrCode IN OUT NOCOPY NUMBER,
278 x_ErrMsg IN OUT NOCOPY VARCHAR2);
279
280 END RLM_XML_API;