1 PACKAGE msc_publish_safety_stock_pkg AUTHID CURRENT_USER AS
2 /* $Header: MSCXPSSS.pls 120.1 2005/09/09 00:21:31 shwmathu noship $ */
3
4 /* PL/SQL table types */
5 TYPE companyNameList IS TABLE OF msc_companies.company_name%TYPE;
6 TYPE companySiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
7 TYPE itemNameList IS TABLE OF msc_system_items.item_name%TYPE;
8 TYPE itemDescList IS TABLE OF msc_system_items.description%TYPE;
9 TYPE itemUomList IS TABLE OF msc_system_items.uom_code%TYPE;
10 TYPE fndMeaningList IS TABLE OF fnd_lookup_values.meaning%TYPE;
11 TYPE plannerCodeList IS TABLE OF msc_system_items.planner_code%TYPE;
12 TYPE planningGroupList IS TABLE OF msc_supplies.planning_group%TYPE;
13 TYPE numberList IS TABLE OF Number;
14 TYPE dateList IS TABLE OF Date;
15
16
17 PURCHASE_ORDER CONSTANT INTEGER := 1; /* order type lookup - lookup_type = MRP_ORDER_TYPE */
18 PURCH_REQ CONSTANT INTEGER := 2;
19 WORK_ORDER CONSTANT INTEGER := 3;
20 REPETITIVE_SCHEDULE CONSTANT INTEGER := 4;
21 PLANNED_ORDER CONSTANT INTEGER := 5;
22 MATERIAL_TRANSFER CONSTANT INTEGER := 6;
23 NONSTD_JOB CONSTANT INTEGER := 7;
24 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
25 REQUIREMENT CONSTANT INTEGER := 9;
26 FPO_SUPPLY CONSTANT INTEGER := 10;
27 SHIPMENT CONSTANT INTEGER := 11;
28 RECEIPT_SHIPMENT CONSTANT INTEGER := 12;
29 AGG_REP_SCHEDULE CONSTANT INTEGER := 13;
30 DIS_JOB_BY CONSTANT INTEGER := 14;
31 NON_ST_JOB_BY CONSTANT INTEGER := 15;
32 REP_SCHED_BY CONSTANT INTEGER := 16;
33 PLANNED_BY CONSTANT INTEGER := 17;
34 ON_HAND_QTY CONSTANT INTEGER := 18;
35 FLOW_SCHED CONSTANT INTEGER := 27;
36 FLOW_SCHED_BY CONSTANT INTEGER := 28;
37 PAYBACK_SUPPLY CONSTANT INTEGER :=29;
38
39
40 DEMAND_PAYBACK CONSTANT INTEGER := 27; /* lookup_type = 'MSC_DEMAND_ORIGINATION' */
41 /*in the package body, the query to the msc_demands will be
42 using the lookup_type = 'MSC_DEMAND_ORIGINATION' for the origination_type*/
43
44
45 PAB_SUPPLY CONSTANT INTEGER := 1;
46 PAB_DEMAND CONSTANT INTEGER := 2;
47 PAB_SCRAP_DEMAND CONSTANT INTEGER := 3;
48 PAB_EXP_LOT CONSTANT INTEGER := 4;
49 PAB_ONHAND CONSTANT INTEGER := 5;
50
51 SAFETY_STOCK CONSTANT INTEGER := 7;
52 PROJECTED_AVAILABLE_BALANCE CONSTANT INTEGER := 27;
53
54 PROCEDURE publish_safety_stocks (
55 p_errbuf out nocopy varchar2,
56 p_retcode out nocopy number,
57 p_plan_id in number,
58 p_org_code in varchar2,
59 p_planner_code in varchar2,
60 p_abc_class in varchar2,
61 p_item_id in number,
62 p_planning_gp in varchar2,
63 p_project_id in number,
64 p_task_id in number,
65 p_supplier_id in number,
66 p_supplier_site_id in number,
67 p_horizon_start in varchar2,
68 p_horizon_end in varchar2,
69 p_overwrite in number
70 );
71
72
73 PROCEDURE get_optional_info(
74 p_err OUT nocopy varchar2,
75 p_ret OUT nocopy number,
76 p_plan_id IN number,
77 p_supp_id IN number,
78 p_supp_site_id IN number,
79 t_base_item_id IN NumberList,
80 t_item_id IN numberList,
81 t_org_id IN numberList,
82 t_sr_instance_id IN numberList,
83 t_uom_code IN itemUomList,
84 t_qty IN numberList,
85 t_master_item_name IN OUT NOCOPY itemNameList,
86 t_master_item_desc IN OUT NOCOPY itemDescList,
87 t_pub_id IN OUT NOCOPY numberList,
88 t_pub IN OUT NOCOPY companyNameList,
89 t_pub_site_id IN OUT NOCOPY numberList,
90 t_pub_site IN OUT NOCOPY companySiteList,
91 t_supp_id IN OUT NOCOPY numberList,
92 t_supp IN OUT NOCOPY companyNameList,
93 t_supp_site_id IN OUT NOCOPY numberList,
94 t_supp_site IN OUT NOCOPY companySiteList,
95 t_item_name IN OUT NOCOPY itemNameList,
96 t_item_desc IN OUT NOCOPY itemDescList,
97 t_base_item_name IN OUT NOCOPY itemNameList,
98 t_bucket_index IN OUT NOCOPY numberList
99 );
100
101 PROCEDURE get_total_qty (
102 p_err OUT nocopy varchar2,
103 p_ret OUT nocopy number,
104 t_item_id IN numberList,
105 t_org_id IN numberList,
106 t_sr_instance_id IN numberList,
107 t_pab_type IN numberList,
108 t_key_date IN dateList,
109 t_qty IN numberList,
110 t_total_qty IN OUT NOCOPY numberList,
111 t_temp_qty IN OUT NOCOPY numberList
112 );
113
114
115 PROCEDURE insert_into_sup_dem (
116 p_err OUT nocopy varchar2,
117 p_ret OUT nocopy number,
118 p_plan_id IN number,
119 p_horizon_start IN date,
120 p_horizon_end IN date,
121 p_type IN number,
122 t_sr_instance_id IN numberList,
123 t_org_id IN numberList,
124 t_pub IN companyNameList,
125 t_pub_id IN numberList,
126 t_pub_site IN companySiteList,
127 t_pub_site_id IN numberList,
128 t_base_item_id IN numberList,
129 t_item_id IN numberList,
130 t_bucket_type IN numberList,
131 t_bucket_start IN dateList,
132 t_bucket_end IN dateList,
133 t_bucket_index IN numberList,
134 t_qty IN numberList,
135 t_total_qty IN numberList,
136 t_temp_qty IN numberList,
137 t_item_name IN itemNameList,
138 t_item_desc IN itemDescList,
139 t_base_item_name IN itemNameList,
140 t_proj_number IN numberList,
141 t_task_number IN numberList,
142 t_planning_gp IN planningGroupList,
143 t_uom_code IN itemUomList,
144 t_planner_code IN plannerCodeList,
145 t_key_date IN dateList,
146 t_master_item_name IN itemNameList,
147 t_master_item_desc IN itemDescList,
148 p_version IN varchar2,
149 p_designator IN varchar2,
150 p_user_id IN number,
151 p_language_code IN varchar2
152 );
153
154
155 PROCEDURE delete_old_safety_stock(
156 p_plan_id in number,
157 p_org_id in number,
158 p_sr_instance_id in number,
159 p_planner_code in varchar2,
160 p_abc_class in varchar2,
161 p_item_id in number,
162 p_planning_gp in varchar2,
163 p_project_id in number,
164 p_task_id in number,
165 p_horizon_start in date,
166 p_horizon_end in date,
167 p_overwrite in number
168 );
169
170 PROCEDURE LOG_MESSAGE(
171 p_string IN VARCHAR2
172 );
173
174 FUNCTION get_message (
175 p_app IN VARCHAR2,
176 p_name IN VARCHAR2,
177 p_lang IN VARCHAR2
178 ) RETURN VARCHAR2;
179
180
181 FUNCTION GET_ORDER_TYPE(p_order_type_code in Number) RETURN Varchar2;
182
183 END msc_publish_safety_stock_pkg;
[Home] [Help]