1 PACKAGE WSH_ROUTING_REQUEST AUTHID CURRENT_USER as
2 /* $Header: WSHRORQS.pls 120.0 2005/05/26 17:05:02 appldev noship $ */
3
4
5 TYPE tbl_number is table of number index by binary_integer;
6 TYPE tbl_var1 is table of varchar2(1) index by binary_integer;
7 TYPE tbl_var3 is table of varchar2(3) index by binary_integer;
8 TYPE tbl_var15 is table of varchar2(15) index by binary_integer;
9 TYPE tbl_var30 is table of varchar2(30) index by binary_integer;
10 TYPE tbl_var40 is table of varchar2(40) index by binary_integer;
11 TYPE tbl_var60 is table of varchar2(60) index by binary_integer;
12 TYPE tbl_var150 is table of varchar2(150) index by binary_integer;
13 TYPE tbl_var240 is table of varchar2(240) index by binary_integer;
14 TYPE tbl_var500 is table of varchar2(500) index by binary_integer;
15 TYPE tbl_var2000 is table of varchar2(2000) index by binary_integer;
16 TYPE tbl_var32767 is table of varchar2(32767) index by binary_integer;
17 TYPE tbl_date is table of date index by binary_integer;
18
19
20 --Record to store input parameter as passed from Routing Request/Supplier Address Book UI.
21 TYPE In_param_Rec_Type IS RECORD (
22 caller varchar2(2000), --WSH:Shipping ISP:iSupplier Protal.
23 user_id number, --User Id, passed if caller is ISP.
24 txn_type varchar2(2000), --RREQ:Routing Request SAB:Supplier Address Book.
25 date_format varchar2(2000) --UI date format need to be same with server date format.
26 );
27
28
29 --Routing Resquest Header Record type
30 TYPE Header_Rec_Type is Record (
31 Supplier_name tbl_var240,
32 Request_date tbl_date,
33 Request_Number tbl_var40, --vendor merge change
34 Request_revision tbl_number,
35 error_flag tbl_var1
36 );
37
38
39 --Routing Resquest Delivery Record type
40 TYPE Delivery_Rec_type is Record (
41 Header_line_number tbl_number,
42 Ship_From_Address1 tbl_var240,
43 Ship_From_Address2 tbl_var240,
44 Ship_From_Address3 tbl_var240,
45 Ship_From_Address4 tbl_var240,
46 Ship_From_city tbl_var60,
47 Ship_From_state tbl_var60,
48 Ship_From_county tbl_var60,
49 Ship_From_country tbl_var60,
50 Ship_From_province tbl_var60,
51 Ship_From_postal_code tbl_var60,
52 Ship_From_code tbl_var30,
53 Shipper_name tbl_var240,
54 Phone tbl_var40,
55 email tbl_var500,
56 Number_of_containers tbl_number,
57 total_weight tbl_number,
58 weight_uom tbl_var3,
59 total_volume tbl_number,
60 volume_UOM tbl_var3,
61 remark tbl_var500,
62 error_flag tbl_var1
63 );
64
65
66 --Routing Resquest Line Record type
67 TYPE Line_Rec_type is Record (
68 Delivery_line_number tbl_number,
69 Po_Header_number tbl_var150,
70 Po_Release_number tbl_number,
71 PO_Line_number tbl_var150,
72 PO_Shipment_number tbl_number,
73 Po_Operating_unit tbl_var240,
74 Item_quantity tbl_number,
75 Item_uom tbl_var3,
76 weight tbl_number,
77 Weight_uom tbl_var3,
78 volume tbl_number,
79 Volume_UOM tbl_var3,
80 Earliest_pickup_date tbl_date,
81 Latest_pickup_date tbl_date,
82 error_flag tbl_var1
83 );
84
85
86 --Address book Line Record type
87 TYPE Address_Rec_Type is Record (
88 Supplier_name tbl_var240,
89 Ship_From_Address1 tbl_var240,
90 Ship_From_Address2 tbl_var240,
91 Ship_From_Address3 tbl_var240,
92 Ship_From_Address4 tbl_var240,
93 Ship_From_city tbl_var60,
94 Ship_From_state tbl_var60,
95 Ship_From_county tbl_var60,
96 Ship_From_country tbl_var60,
97 Ship_From_province tbl_var60,
98 Ship_From_postal_code tbl_var60,
99 Ship_From_code tbl_var30,
100 Shipper_name tbl_var240,
101 Phone tbl_var40,
102 email tbl_var500,
103 action tbl_var15,
104 error_flag tbl_var1
105 );
106
107 --Record to hold data passed between different api.
108 TYPE detail_att_rec_type IS RECORD (
109 delivery_detail_id number,
110 inventory_item_id number,
111 requested_quantity_uom varchar2(3),
112 requested_quantity number,
113 requested_quantity_uom2 varchar2(3),
114 requested_quantity2 number,
115 item_quantity number,
116 weight number,
117 weight_uom varchar2(3),
118 volume number,
119 volume_uom varchar2(3),
120 Earliest_pickup_date date,
121 Latest_pickup_date date,
122 date_requested date,
123 earliest_dropoff_date date,
124 latest_dropoff_date date,
125 organization_id number,
126 routing_req_id number,
127 prev_routing_req_id number,
128 vendor_id number,
129 party_id number,
130 ship_from_location_id number,
131 party_site_id number,
132 source_header_number varchar2(150),
133 source_header_type_id number,
134 source_header_type_name varchar2(240),
135 org_id number,
136 released_status varchar2(1),
137 source_code varchar2(30),
138 dd_net_weight number,
139 dd_gross_weight number,
140 dd_volume number,
141 dd_wv_frozen_flag varchar2(1)
142 );
143
144
145 -- Start of comments
146 -- API name : Process_File
147 -- Type : Public
148 -- Pre-reqs : None.
149 -- Procedure: API to upload routing request and Supplier Address Book. This api is called
150 -- from Routing Request/Supplier Address Book UI. Api does
151 -- 1.Intilized the message global table.
152 -- 2.Based on transaction type called the corresponding
153 -- wrapper api for processing.
154 -- Parameters :
155 -- IN:
156 -- p_caller IN WSH/ISP
157 -- p_txn_type IN RREQ -For Routing Request, SAB for Supplier Address Book.
158 -- p_user_id IN Passed if caller is ISP.
159 -- p_date_format IN UI date format need to be same with server date format.
160 -- p_file_fields IN List of fields as parse from Routing Request/Supplier Address book file.
161 -- OUT:
162 -- x_message_tbl OUT NOCOPY List of success/error messages return to calling api.
163 -- x_return_status OUT NOCOPY Standard to output api status.
164 -- End of comments
165 PROCEDURE Process_File(
166 p_caller IN VARCHAR2,
167 p_txn_type IN VARCHAR2,
168 p_user_id IN NUMBER,
169 p_date_format IN VARCHAR2,
170 p_file_fields IN WSH_FILE_RECORD_TYPE ,
171 x_message_tbl OUT NOCOPY WSH_FILE_MSG_TABLE,
172 x_return_status OUT NOCOPY varchar2);
173
174
175 END WSH_ROUTING_REQUEST;
[Home] [Help]