1 Package OE_DROP_SHIP_GRP AUTHID CURRENT_USER As
2 /* $Header: OEXUDSHS.pls 120.0.12000000.1 2007/01/16 22:02:22 appldev ship $ */
3
4 Procedure Update_Req_Info(P_API_Version In Number,
5 P_Return_Status out nocopy Varchar2,
6
7 P_Msg_Count out nocopy Number,
8
9 P_MSG_Data out nocopy Varchar2,
10
11 P_Interface_Source_Code In Varchar2,
12 P_Interface_Source_Line_ID In Number,
13 P_Requisition_Header_ID In Number,
14 P_Requisition_Line_ID In Number);
15
16 Procedure Insert_OE_Drop_Ship_Sources
17 ( P_Drop_Ship_Source_ID In Number
18 , P_Header_ID In Number
19 , P_Line_ID In Number
20 , P_Org_ID In Number
21 , P_Destination_Organization_ID In Number
22 , P_Requisition_Header_ID In Number
23 , P_Requisition_Line_ID In Number
24 , P_PO_Header_ID In Number
25 , P_PO_Line_ID In Number
26 , P_Line_Location_ID In Number
27 , P_PO_Release_ID In Number Default Null);
28
29 -- Update_All_Reqs_In_Process is an OE procedure that is called by
30 -- Oracle Purchasing to update requisition information for a drop shipped line.
31 -- This procedure is called in the Requisition Import (ReqImport) process of
32 -- Oracle Purchasing.
33
34 Procedure Update_All_Reqs_In_Process
35 ( P_API_Version In Number
36 , P_Return_Status out nocopy Varchar2
37
38 , P_Msg_Count out nocopy Number
39
40 , P_MSG_Data out nocopy Varchar2
41
42 , P_Requisition_Header_ID In Number
43 , P_Request_Id In Number
44 , P_Process_Flag In Varchar2);
45
46 -- Update_PO_Info is an OE procedure that is called by Oracle Purchasing to
47 -- update purchase order information for a drop shipped line. This procedure
48 -- is called in the Auto create process of Oracle Purchasing
49
50 Procedure Update_PO_Info
51 ( P_API_Version In Number
52 , P_Return_Status out nocopy Varchar2
53
54 , P_Msg_Count out nocopy Number
55
56 , P_MSG_Data out nocopy Varchar2
57
58 , P_Req_Header_ID In Number
59 , P_Req_Line_ID In Number
60 , P_PO_Header_Id In Number
61 , P_PO_Line_Id In Number
62 , P_Line_Location_ID In Number
63 , P_PO_Release_ID In Number Default Null);
64
65 Function Valid_Drop_Ship_Source_ID
66 (P_Drop_Ship_Source_ID In Number)
67 Return Boolean;
68
69 Function Req_Line_Is_Drop_Ship
70 (P_Req_Line_Id In Number)
71 Return Number;
72
73 Function PO_Line_Location_Is_Drop_Ship
74 (P_PO_Line_Location_Id In Number)
75 Return Number;
76
77 --PO will pass in the data po_header_id or po_requisition_header_id
78
79 TYPE PO_ENTITY_ID_TBL_TYPE IS TABLE OF NUMBER;
80
81 --Validation outcome for each document that is passed
82
83 TYPE VAL_STATUS_TBL_TYPE IS TABLE OF VARCHAR2(1);
84
85 Procedure Update_Drop_Ship_Links
86 ( p_api_version IN NUMBER
87 ,p_po_header_id IN NUMBER
88 ,p_po_line_id IN NUMBER
89 ,p_po_line_location_id IN NUMBER
90 ,p_po_release_id IN NUMBER
91 ,p_new_req_hdr_id IN NUMBER
92 ,p_new_req_line_id IN NUMBER
93 ,x_msg_data OUT NOCOPY VARCHAR2
94 ,x_msg_count OUT NOCOPY NUMBER
95 ,x_return_status OUT NOCOPY VARCHAR2
96 );
97
98 Function Is_Receipt_For_Drop_Ship
99 ( p_rcv_transaction_id IN NUMBER
100 )RETURN BOOLEAN;
101
102 Procedure Get_Drop_Ship_Line_ids
103 ( p_po_header_id IN NUMBER
104 , p_po_line_id IN NUMBER
105 , p_po_line_location_id IN NUMBER
106 , p_po_release_id IN NUMBER
107 , p_mode IN NUMBER := null
108 , p_rcv_transaction_id IN NUMBER := null
109 , x_num_lines OUT NOCOPY /* file.sql.39 change */ NUMBER
110 , x_line_id OUT NOCOPY NUMBER
111 , x_header_id OUT NOCOPY NUMBER
112 , x_org_id OUT NOCOPY NUMBER
113 );
114
115 Procedure Get_Order_Line_Status
116 (p_api_version IN NUMBER
117 ,p_po_header_id IN NUMBER
118 ,p_po_line_id IN NUMBER
119 ,p_po_line_location_id IN NUMBER
120 ,p_po_release_id IN NUMBER
121 ,p_mode IN NUMBER
122 ,x_updatable_flag OUT NOCOPY VARCHAR2
123 ,x_on_hold OUT NOCOPY VARCHAR2
124 ,x_order_line_status OUT NOCOPY NUMBER
125 ,x_msg_data OUT NOCOPY VARCHAR2
126 ,x_msg_count OUT NOCOPY NUMBER
127 ,x_return_status OUT NOCOPY VARCHAR2
128 );
129
130 Procedure Purge_Drop_Ship_PO_Links
131 ( p_api_version IN NUMBER
132 ,p_init_msg_list IN VARCHAR2
133 ,p_commit IN VARCHAR2
134 ,p_entity IN VARCHAR2
135 ,p_entity_id_tbl IN PO_ENTITY_ID_TBL_TYPE
136 ,x_msg_count OUT NOCOPY NUMBER
137 ,x_msg_data OUT NOCOPY VARCHAR2
138 ,x_return_status OUT NOCOPY VARCHAR2
139 );
140
141 Procedure Purge_Drop_Ship_PO_Validation
142 ( p_api_version IN NUMBER
143 ,p_init_msg_list IN VARCHAR2
144 ,p_commit IN VARCHAR2
145 ,p_entity IN VARCHAR2
146 ,p_entity_id_tbl IN PO_ENTITY_ID_TBL_TYPE
147 ,x_purge_allowed_tbl OUT NOCOPY VAL_STATUS_TBL_TYPE
148 ,x_msg_count OUT NOCOPY NUMBER
149 ,x_msg_data OUT NOCOPY VARCHAR2
150 ,x_return_status OUT NOCOPY VARCHAR2
151 );
152
153 TYPE Order_Line_Info_Rec_Type IS RECORD
154 ( ship_to_contact_name VARCHAR2(400)
155 ,ship_to_contact_phone VARCHAR2(200)
156 ,ship_to_contact_fax VARCHAR2(200)
157 ,ship_to_contact_email VARCHAR2(2000)
158 ,deliver_to_customer_name VARCHAR2(400)
159 ,deliver_to_customer_address VARCHAR2(2000)
160 ,deliver_to_customer_Location VARCHAR2(2000)
161 ,deliver_to_contact_name VARCHAR2(400)
162 ,deliver_to_contact_phone VARCHAR2(200)
163 ,deliver_to_contact_fax VARCHAR2(200)
164 ,deliver_to_contact_email VARCHAR2(2000)
165 ,shipping_method VARCHAR2(240)
166 ,shipping_instructions VARCHAR2(2000)
167 ,packing_instructions VARCHAR2(2000)
168 ,customer_product_description VARCHAR2(1000)
169 ,customer_po_number VARCHAR2(50)
170 ,customer_po_line_number VARCHAR2(50)
171 ,customer_po_shipment_number VARCHAR2(50)
172 ,ship_to_customer_name VARCHAR2(400)
173 ,ship_to_customer_location VARCHAR2(2000)
174 ,sales_order_number VARCHAR2(240)
175 ,sales_order_line_number VARCHAR2(30)
176 ,sales_order_line_ordered_qty NUMBER
177 ,sales_order_line_shipped_qty NUMBER
178 ,sales_order_line_ordered_qty2 NUMBER -- INVCONV
179 ,sales_order_line_shipped_qty2 NUMBER -- INVCONV
180 ,sales_order_line_status VARCHAR2(240)
181 ,deliver_to_customer_address1 VARCHAR2(240)
182 ,deliver_to_customer_address2 VARCHAR2(240)
183 ,deliver_to_customer_address3 VARCHAR2(240)
184 ,deliver_to_customer_address4 VARCHAR2(240)
185 ,deliver_to_customer_city VARCHAR2(60)
186 ,deliver_to_customer_state VARCHAR2(60)
187 ,deliver_to_customer_zip VARCHAR2(60)
188 ,deliver_to_customer_country VARCHAR2(60)
189 );
190
191
192 PROCEDURE Get_Order_Line_Info
193 ( p_api_version IN NUMBER
194 ,p_po_header_id IN NUMBER
195 ,p_po_line_id IN NUMBER
196 ,p_po_line_location_id IN NUMBER
197 ,p_po_release_id IN NUMBER
198 ,p_mode IN NUMBER
199 ,x_order_line_info_rec OUT NOCOPY Order_Line_Info_Rec_Type
200 ,x_msg_data OUT NOCOPY VARCHAR2
201 ,x_msg_count OUT NOCOPY NUMBER
202 ,x_return_status OUT NOCOPY VARCHAR2);
203
204 End OE_DROP_SHIP_GRP;