1 PACKAGE MSC_SATP_FUNC AUTHID CURRENT_USER AS
2 /* $Header: MSCSATPS.pls 120.2 2007/12/12 10:39:43 sbnaik ship $ */
3
4
5 --Following Functions are used in calculating delivery lead time at the source
6
7 FUNCTION src_location_id(
8 p_organization_id IN NUMBER,
9 p_customer_id IN NUMBER,
10 p_customer_site_id IN NUMBER
11 )
12 RETURN NUMBER;
13
14
15 -- savirine, Sep 24, 2001: added the parameter p_session_id and p_partner_site_id
16
17 FUNCTION src_interloc_transit_time (
18 p_from_location_id IN NUMBER,
19 p_to_location_id IN NUMBER,
20 p_ship_method IN VARCHAR2,
21 p_session_id IN NUMBER DEFAULT NULL,
22 p_partner_site_id IN NUMBER DEFAULT NULL
23 )
24 RETURN NUMBER;
25
26
27 -- savirine, Sep 24, 2001: added the parameter p_session_id and p_partner_site_id
28
29 FUNCTION src_default_ship_method (
30 p_from_location_id IN NUMBER,
31 p_to_location_id IN NUMBER,
32 p_session_id IN NUMBER DEFAULT NULL,
33 p_partner_site_id IN NUMBER DEFAULT NULL
34 )
35 RETURN VARCHAR2;
36
37
38 -- savirine, Sep 24, 2001: added the parameter p_session_id and p_partner_site_id
39
40 FUNCTION src_default_intransit_time (
41 p_from_location_id IN NUMBER,
42 p_to_location_id IN NUMBER,
43 p_session_id IN NUMBER DEFAULT NULL,
44 p_partner_site_id IN NUMBER DEFAULT NULL
45 )
46 RETURN NUMBER;
47
48
49 FUNCTION src_ship_method (
50 p_from_org_id IN NUMBER,
51 p_to_org_id IN NUMBER
52 )
53 RETURN VARCHAR2;
54
55
56 FUNCTION src_intransit_time (
57 p_from_org_id IN NUMBER,
58 p_to_org_id IN NUMBER
59 )
60 RETURN NUMBER;
61
62 FUNCTION src_prev_work_day ( p_organization_id IN NUMBER,
63 p_date IN DATE)
64 return DATE;
65
66 FUNCTION src_next_work_day ( p_organization_id IN NUMBER,
67 p_date IN DATE)
68 return DATE;
69
70 -- dsting 2833417
71 FUNCTION src_date_offset ( p_organization_id IN NUMBER,
72 p_date IN DATE,
73 p_days IN NUMBER
74 )
75 return DATE;
76
77 -- ngoel 7/31/2001, modified to accept p_index as a parameter to determine
78 -- index length by which ATP_REC_TYP needs to be extended, default is 1.
79
80 PROCEDURE Extend_Atp (
81 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
82 x_return_status OUT NoCopy VARCHAR2,
83 p_index IN NUMBER DEFAULT 1
84 );
85
86
87 PROCEDURE Assign_Atp_Input_Rec (
88 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
89 p_index IN NUMBER,
90 x_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
91 x_return_status OUT NoCopy VARCHAR2
92 );
93
94 PROCEDURE Assign_Atp_Output_Rec (
95 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
96 x_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
97 x_return_status OUT NoCopy VARCHAR2
98 );
99
100
101
102
103 PROCEDURE Extend_Atp_Period (
104 p_atp_period IN OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
105 x_return_status OUT NoCopy VARCHAR2
106 );
107
108
109 PROCEDURE Extend_Atp_Supply_Demand (
110 p_atp_supply_demand IN OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
111 x_return_status OUT NoCopy VARCHAR2,
112 p_index IN NUMBER DEFAULT 1 -- added by rajjain 12/10/2002
113 );
114
115 -- rajjain 12/10/2002
116 PROCEDURE Trim_Atp_Supply_Demand (
117 p_atp_supply_demand IN OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
118 x_return_status OUT NoCopy VARCHAR2,
119 p_index IN NUMBER DEFAULT 1
120 );
121
122 -- ngoel 9/28/2001, added this function for use in View MSC_SCATP_SOURCES_V to support
123 -- Region Level Sourcing.
124
125 FUNCTION Get_Session_id
126 RETURN NUMBER;
127
128 -- savirine, September 05, 2001: Defined the procedure Get_Regions to get the region information. This
129 -- procedure can be called from the following packages: 1) MSCEATPB.pls if the ATP request is from the
130 -- source ERP Instance, 2) MSCGATPB.pls if the ATP request is from the destination APS Instance, and
131 -- 3) MSCOSCWB.pls if the ATP request is coming from Pick Sources/ Global ATP from UI ( Form ).
132
133 -- krajan: Modified name to _OLD. New wrapper added
134 -- See the Spec for Get_Regions for more information
135
136 /*
137 PROCEDURE Get_Regions_Old (
138 p_customer_site_id IN NUMBER,
139 p_calling_module IN number,
140 p_instance_id IN NUMBER,
141 p_session_id IN NUMBER,
142 p_dblink IN VARCHAR2,
143 x_return_status OUT NOCOPY VARCHAR2);
144 */
145 -- For shipping
146 -- Uses the new regions to locations mapping table
147 PROCEDURE Get_Regions_Shipping (
148 p_customer_site_id IN NUMBER,
149 p_calling_module IN number, -- i.e. Source (not 724) or Destination (724)
150 p_instance_id IN NUMBER,
151 p_session_id IN NUMBER,
152 p_dblink IN VARCHAR2,
153 x_return_status OUT NOCOPY VARCHAR2,
154 p_location_id IN NUMBER DEFAULT NULL, -- to get location ID
155 p_location_source IN VARCHAR2 DEFAULT NULL, -- location source
156 p_supplier_site_id IN NUMBER DEFAULT NULL,-- For supplier intransit LT project
157 --2814895
158 p_party_site_id IN NUMBER DEFAULT NULL);
159 -- dsting
160 PROCEDURE get_src_transit_time (
161 p_from_org_id IN NUMBER,
162 p_from_loc_id IN NUMBER,
163 p_to_org_id IN NUMBER,
164 p_to_loc_id IN NUMBER,
165 p_session_id IN NUMBER,
166 p_partner_site_id IN NUMBER,
167 x_ship_method IN OUT NOCOPY VARCHAR2,
168 x_intransit_time OUT NOCOPY NUMBER,
169 p_partner_type IN NUMBER DEFAULT NULL ); --2814895
170
171 -- krajan : new get_regions
172 -- This procedure is a wrapper for Get_regions_Old and Get_regions_shipping
173 -- When data exists in the regions to locations mapping table, this wrapper
174 -- redirects the call to get_regions_shipping. Else the old API is called.
175 PROCEDURE Get_Regions (
176 p_customer_site_id IN NUMBER,
177 p_calling_module IN number, -- i.e. Source (not 724) or Destination (724)
178 p_instance_id IN NUMBER,
179 p_session_id IN NUMBER,
180 p_dblink IN VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 p_location_id IN NUMBER DEFAULT NULL, -- to get location ID
183 p_location_source IN VARCHAR2 DEFAULT NULL, -- location source
184 p_supplier_site_id IN NUMBER DEFAULT NULL, -- For supplier intransit LT project
185 --2814895
186 -- Adding new address of customer and party_site
187 p_postal_code IN VARCHAR2 DEFAULT NULL,
188 p_city IN VARCHAR2 DEFAULT NULL,
189 p_state IN VARCHAR2 DEFAULT NULL,
190 p_country IN VARCHAR2 DEFAULT NULL,
191 p_party_site_id IN NUMBER DEFAULT NULL,
192 p_order_line_ID IN NUMBER DEFAULT NULL --2814895
193 );
194
195 procedure new_extend_atp (
196 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.atp_rec_typ,
197 p_tot_size IN number,
198 x_return_status OUT NOCOPY varchar2
199 );
200
201 /*--------------------------------------------------------------------------
202 | Begin Functions added for ship_rec_cal project
203 +-------------------------------------------------------------------------*/
204
205 FUNCTION Src_Get_Calendar_Code(
206 p_customer_id IN number,
207 p_customer_site_id IN number,
208 p_organization_id IN number,
209 p_ship_method_code IN varchar2,
210 p_calendar_type IN integer -- One of OSC, CRC or VIC
211 ) RETURN VARCHAR2;
212 FUNCTION Src_NEXT_WORK_DAY(
213 p_calendar_code IN varchar2,
214 p_calendar_date IN date
215 ) RETURN DATE;
216
217 FUNCTION Src_PREV_WORK_DAY(
218 p_calendar_code IN varchar2,
219 p_calendar_date IN date
220 ) RETURN DATE;
221
222 FUNCTION Src_DATE_OFFSET(
223 p_calendar_code IN varchar2,
224 p_calendar_date IN date,
225 p_days_offset IN number,
226 p_offset_type IN number
227 ) RETURN DATE;
228
229 FUNCTION SRC_THREE_STEP_CAL_OFFSET_DATE(
230 p_input_date IN Date,
231 p_first_cal_code IN VARCHAR2,
232 p_first_cal_validation_type IN NUMBER,
233 p_second_cal_code IN VARCHAR2,
234 p_offset_days IN NUMBER,
235 p_second_cal_validation_type IN NUMBER,
236 p_third_cal_code IN VARCHAR2,
237 p_third_cal_validation_type IN NUMBER
238 ) RETURN DATE;
239
240 /*--------------------------------------------------------------------------
241 | Procedure for collection enhancement
242 +-------------------------------------------------------------------------*/
243 PROCEDURE get_dblink_profile(
244 x_dblink OUT NOCOPY VARCHAR2,
245 x_instance_id OUT NOCOPY NUMBER,
246 x_return_status OUT NOCOPY VARCHAR2
247 );
248
249 --bug3940999 added procedure for inserting source profile values into source table.
250 PROCEDURE put_src_to_dstn_profiles(
251 p_session_id IN NUMBER,
252 x_return_status OUT NoCopy VARCHAR2
253 );
254
255 --bug3940999 added procedure for getting source profile values into destination table.
256 PROCEDURE get_src_to_dstn_profiles(
257 p_dblink IN VARCHAR2,
258 p_session_id IN NUMBER,
259 x_return_status OUT NoCopy VARCHAR2
260 );
261
262 END MSC_SATP_FUNC;
[Home] [Help]