1 PACKAGE msc_sce_publish_pkg AS
2 /* $Header: MSCXPUBS.pls 120.0.12010000.3 2008/10/07 09:02:37 hbinjola ship $ */
3
4 G_MSC_CP_DEBUG VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
5 G_NULL_STRING CONSTANT VARCHAR2(10) := '-234567' ; -- bug#7310179
6
7 /* PL/SQL table types */
8 TYPE companyNameList IS TABLE OF msc_companies.company_name%TYPE;
9 TYPE companySiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
10 TYPE itemNameList IS TABLE OF msc_system_items.item_name%TYPE;
11 TYPE itemDescList IS TABLE OF msc_system_items.description%TYPE;
12 TYPE itemUomList IS TABLE OF msc_system_items.uom_code%TYPE;
13 TYPE fndMeaningList IS TABLE OF fnd_lookup_values.meaning%TYPE;
14 TYPE plannerCodeList IS TABLE OF msc_system_items.planner_code%TYPE;
15 TYPE planningGroupList IS TABLE OF msc_supplies.planning_group%TYPE;
16 TYPE numberList IS TABLE OF Number;
17 TYPE dateList IS TABLE OF Date;
18 TYPE orderNumList IS TABLE OF msc_sup_dem_entries.order_number%TYPE; -- bug#7310179
19 TYPE lineNumList IS TABLE OF msc_sup_dem_entries.line_number%TYPE;-- bug#7310179
20
21
22
23 ORDER_FORECAST number := 2;
24 PLANNED_ORDER number := 5;
25 PURCHASE_ORDER number := 1;
26 REQUISITION number := 2;
27 CP_PURCHASE_ORDER_FROM_PLAN number := 22;
28 CP_RELEASED_PLANNED_ORDER number := 23;
29 CP_PLANNED_ORDER number := 24;
30 EXPECTED_INBOUND_SHIPMENT number := 51;
31 CP_PLANNED_INBOUND_SHIPMENT number := 46;
32 CP_RELEASED_INBOUND_SHIPMENT number := 47;
33 -- bug#6893383 CP-SPP Integration
34 SPP_PLAN constant number := 8;
35 PLANNED_NEW_BUY_ORDER constant number := 76;
36 PLANNED_EXTERNAL_REPAIR_ORDER constant number := 78;
37 EXTERNAL_REPAIR_ORDER constant number := 74;
38 RETURNS_FORECAST constant number := 50;
39 DEFECTIVE_OUTBOUND_SHIPMENT constant number := 51;
40 SALES_ORDER constant number := 30;
41 INTRANSIT_SHIPMENT constant number := 11; --bug#7443302
42 INTRANSIT_RECEIPT constant number := 12; --bug#7443302
43 -- dummy order type used only in this package
44 PLANNED_TRANSFER_DEF constant number := 501;
45 ISO_DEF constant number := 502;
46 INTRANSIT_SHIPMENT_DEF constant number := 503; --bug#7443302
47 INTRANSIT_RECEIPT_DEF constant number := 504; --bug#7443302
48
49
50 PROCEDURE publish_plan_orders (
51 p_errbuf out nocopy varchar2,
52 p_retcode out nocopy varchar2,
53 p_plan_id in number,
54 p_org_code in varchar2 default null,
55 p_planner_code in varchar2 default null,
56 p_abc_class in varchar2 default null,
57 p_item_id in number default null,
58 p_item_list in varchar2 default null,
59 p_planning_gp in varchar2 default null,
60 p_project_id in number default null,
61 p_task_id in number default null,
62 p_supplier_id in number default null,
63 p_supplier_site_id in number default null,
64 p_horizon_start in varchar2,
65 p_horizon_end in varchar2,
66 p_auto_version in number default 1,
67 p_version in number default null,
68 p_purchase_order in number default 2,
69 p_requisition in number default 2,
70 p_overwrite in number default 1,
71 p_publish_dos in number default 1 -- bug#6893383 **SPP-Publish dos for defective supplier**
72 );
73
74
75
76 PROCEDURE get_optional_info(
77 t_item_id IN numberList,
78 t_org_id IN numberList,
79 t_sr_instance_id IN numberList,
80 t_source_supp_id IN numberList,
81 t_source_supp_site_id IN numberList,
82 t_uom_code IN itemUomList,
83 t_qty IN numberList,
84 t_planned_order_qty IN numberList,
85 t_released_qty IN numberList,
86 t_base_item_id IN numberList,
87 t_base_item_name IN OUT NOCOPY itemNameList,
88 t_base_item_desc IN OUT NOCOPY itemDescList,
89 t_master_item_name IN OUT NOCOPY itemNameList,
90 t_master_item_desc IN OUT NOCOPY itemDescList,
91 t_supp_item_name IN OUT NOCOPY itemNameList,
92 t_supp_item_desc IN OUT NOCOPY itemDescList,
93 t_tp_uom IN OUT NOCOPY itemUomList,
94 t_tp_qty IN OUT NOCOPY numberList,
95 t_tp_planned_order_qty IN OUT NOCOPY numberList,
96 t_tp_released_qty IN OUT NOCOPY numberList,
97 t_ship_date IN OUT NOCOPY dateList,
98 t_receipt_date IN dateList,
99 t_pub_id IN OUT NOCOPY numberList,
100 t_pub IN OUT NOCOPY companyNameList,
101 t_pub_site_id IN OUT NOCOPY numberList,
102 t_pub_site IN OUT NOCOPY companySiteList,
103 t_supp_id IN OUT NOCOPY numberList,
104 t_supp IN OUT NOCOPY companyNameList,
105 t_supp_site_id IN OUT NOCOPY numberList,
106 t_supp_site IN OUT NOCOPY companySiteList
107 );
108
109
110 PROCEDURE insert_into_sup_dem (
111 t_pub IN companyNameList,
112 t_pub_id IN numberList,
113 t_pub_site IN companySiteList,
114 t_pub_site_id IN numberList,
115 t_item_id IN numberList,
116 t_order_type IN numberList,
117 t_qty IN numberList,
118 t_planned_order_qty IN numberList,
119 t_released_qty IN numberList,
120 t_supp IN companyNameList,
121 t_supp_id IN numberList,
122 t_supp_site IN companySiteList,
123 t_supp_site_id IN numberList,
124 t_owner_item_name IN itemNameList,
125 t_owner_item_desc IN itemDescList,
126 t_base_item_id IN numberList,
127 t_base_item_name IN itemNameList,
128 t_base_item_desc IN itemDescList,
129 t_proj_number IN numberList,
130 t_task_number IN numberList,
131 t_planning_gp IN planningGroupList,
132 t_uom_code IN itemUomList,
133 t_planner_code IN plannerCodeList,
134 t_bucket_type IN numberList,
135 t_key_date IN dateList,
136 t_ship_date IN dateList,
137 t_receipt_date IN dateList,
138 t_master_item_name IN itemNameList,
139 t_master_item_desc IN itemDescList,
140 t_supp_item_name IN itemNameList,
141 t_supp_item_desc IN itemDescList,
142 t_tp_uom IN itemUomList,
143 t_tp_qty IN numberList,
144 t_tp_planned_order_qty IN numberList,
145 t_tp_released_qty IN numberList,
146 p_version IN varchar2,
147 p_designator IN varchar2,
148 p_user_id IN number,
149 p_language_code IN varchar2
150 );
151
152
153 PROCEDURE delete_old_forecast(
154 p_plan_id in number,
155 p_org_id in number,
156 p_sr_instance_id in number,
157 p_planner_code in varchar2,
158 p_abc_class in varchar2,
159 p_item_id in number,
160 p_planning_gp in varchar2,
161 p_project_id in number,
162 p_task_id in number,
163 p_supplier_id in number,
164 p_supplier_site_id in number,
165 p_horizon_start in date,
166 p_horizon_end in date,
167 p_overwrite in number
168 );
169
170 --============CP-SPP Integration START bug#6893383================
171 -- Added this procedure exclusively for inserting returns forecast and Dos order types only
172 PROCEDURE insert_into_sup_dem_rf_dos (
173 t_pub IN companyNameList,
174 t_pub_id IN numberList,
175 t_pub_site IN companySiteList,
176 t_pub_site_id IN numberList,
177 t_item_id IN numberList,
178 t_order_type IN numberList,
179 t_qty IN numberList,
180 t_planned_order_qty IN numberList,
181 t_released_qty IN numberList,
182 t_supp IN companyNameList,
183 t_supp_id IN numberList,
184 t_supp_site IN companySiteList,
185 t_supp_site_id IN numberList,
186 t_owner_item_name IN itemNameList,
187 t_owner_item_desc IN itemDescList,
188 t_base_item_id IN numberList,
189 t_base_item_name IN itemNameList,
190 t_base_item_desc IN itemDescList,
191 t_proj_number IN numberList,
192 t_task_number IN numberList,
193 t_planning_gp IN planningGroupList,
194 t_uom_code IN itemUomList,
195 t_planner_code IN plannerCodeList,
196 t_bucket_type IN numberList,
197 t_key_date IN dateList,
198 t_ship_date IN dateList,
199 t_receipt_date IN dateList,
200 t_order_num IN orderNumList, -- bug#7310179
201 t_line_num IN lineNumList, -- bug#7310179
202 t_master_item_name IN itemNameList,
203 t_master_item_desc IN itemDescList,
204 t_supp_item_name IN itemNameList,
205 t_supp_item_desc IN itemDescList,
206 t_tp_uom IN itemUomList,
207 t_tp_qty IN numberList,
208 t_tp_planned_order_qty IN numberList,
209 t_tp_released_qty IN numberList,
210 p_version IN varchar2,
211 p_designator IN varchar2,
212 p_user_id IN number,
213 p_language_code IN varchar2,
214 p_publish_dos IN number
215 );
216
217 --===============CP-SPP Integration END==========================
218
219 PROCEDURE LOG_MESSAGE(
220 p_string IN VARCHAR2
221 );
222
223 FUNCTION get_message (
224 p_app IN VARCHAR2,
225 p_name IN VARCHAR2,
226 p_lang IN VARCHAR2
227 ) RETURN VARCHAR2;
228
229
230 END msc_sce_publish_pkg;
[Home] [Help]