1 PACKAGE FLM_KANBAN_PUB AUTHID CURRENT_USER AS
2 /* $Header: FLMKBNPS.pls 120.1 2011/03/24 08:36:41 sisankar noship $ */
3
4
5 -- This procedure called from flm_kanban_massload package
6 PROCEDURE process_kanban(p_pull_sequence_tbl IN OUT NOCOPY flm_kanban_massload.pull_seq_tbl_type
7 ,p_supplier_tbl IN OUT NOCOPY flm_kanban_massload.kanban_supp_tbl_type
8 ,p_kanban_card_tbl IN OUT NOCOPY flm_kanban_massload.card_int_tbl_type
9 ,x_ret_status OUT NOCOPY VARCHAR2
10 );
11
12 -- Pull sequence UI to call this procedure
13 PROCEDURE process_pull_sequence
14 (p_pull_sequence_id IN OUT NOCOPY NUMBER
15 ,p_generate_cards IN VARCHAR2 DEFAULT 'N'
16 ,p_last_update_date IN DATE DEFAULT NULL
17 ,p_last_updated_by IN NUMBER DEFAULT NULL
18 ,p_creation_date IN DATE DEFAULT NULL
19 ,p_created_by IN NUMBER DEFAULT NULL
20 ,p_last_update_login IN NUMBER DEFAULT NULL
21 ,p_inventory_item_id IN NUMBER DEFAULT NULL
22 ,p_concatenated_segments IN VARCHAR2 DEFAULT NULL
23 ,p_organization_code IN VARCHAR2 DEFAULT NULL
24 ,p_organization_id IN NUMBER DEFAULT NULL
25 ,p_subinventory_name IN VARCHAR2 DEFAULT NULL
26 ,p_source_type IN NUMBER DEFAULT NULL
27 ,p_locator_id IN NUMBER DEFAULT NULL
28 ,p_supplier_id IN NUMBER DEFAULT NULL
29 ,p_supplier_name IN VARCHAR2 DEFAULT NULL
30 ,p_supplier_site_id IN NUMBER DEFAULT NULL
31 ,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
32 ,p_source_organization_id IN NUMBER DEFAULT NULL
33 ,p_source_subinventory IN VARCHAR2 DEFAULT NULL
34 ,p_source_locator_id IN NUMBER DEFAULT NULL
35 ,p_wip_line_code IN VARCHAR2 DEFAULT NULL
36 ,p_wip_line_id IN NUMBER DEFAULT NULL
37 ,p_replenishment_lead_time IN NUMBER DEFAULT NULL
38 ,p_calculate_kanban_flag IN NUMBER DEFAULT NULL
39 ,p_kanban_size IN NUMBER DEFAULT NULL
40 ,p_fixed_lot_multiplier IN NUMBER DEFAULT NULL
41 ,p_safety_stock_days IN NUMBER DEFAULT NULL
42 ,p_number_of_cards IN NUMBER DEFAULT NULL
43 ,p_minimum_order_quantity IN NUMBER DEFAULT NULL
44 ,p_aggregation_type IN NUMBER DEFAULT NULL
45 ,p_allocation_percent IN NUMBER DEFAULT NULL
46 ,p_release_kanban_flag IN NUMBER DEFAULT NULL
47 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
48 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
49 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
50 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
51 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
52 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
53 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
54 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
55 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
56 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
57 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
58 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
59 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
60 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
61 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
62 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
63 ,p_auto_request IN VARCHAR2 DEFAULT NULL
64 ,p_auto_allocate_flag IN VARCHAR2 DEFAULT NULL
65 ,p_replenishment_type IN NUMBER DEFAULT NULL
66 ,p_consolidation IN NUMBER DEFAULT NULL
67 ,p_consolidation_group IN VARCHAR2 DEFAULT NULL
68 ,p_future_card_size IN NUMBER DEFAULT NULL
69 ,p_future_no_of_cards IN NUMBER DEFAULT NULL
70 ,p_planning_effectivity IN DATE DEFAULT NULL
71 ,p_avg_dependent_demand IN NUMBER DEFAULT NULL
72 ,p_avg_independent_demand IN NUMBER DEFAULT NULL
73 ,p_transaction_type IN NUMBER
74 ,x_ret_status OUT NOCOPY VARCHAR2
75 ,x_error_msg OUT NOCOPY VARCHAR2);
76
77 -- procedure for pull sequence public API
78 PROCEDURE process_pull_sequence(p_pull_sequence_rec IN OUT NOCOPY flm_ekanban_pub.pull_sequence_rec_type
79 ,p_supplier_tbl IN OUT NOCOPY flm_ekanban_pub.kanban_supplier_tbl_type
80 ,p_transaction_type IN NUMBER
81 ,p_commit_flag IN VARCHAR2 DEFAULT 'N'
82 ,x_ret_status OUT NOCOPY VARCHAR2
83 ,x_error_msg OUT NOCOPY VARCHAR2);
84
85 -- procedure for multiple suppliers processing
86 -- pull sequence UI to call this
87 PROCEDURE process_suppliers(p_pull_sequence_id IN OUT NOCOPY NUMBER
88 ,p_supplier_id IN NUMBER DEFAULT NULL
89 ,p_supplier_name IN VARCHAR2 DEFAULT NULL
90 ,p_supplier_site_id IN NUMBER DEFAULT NULL
91 ,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
92 ,p_organization_id IN NUMBER DEFAULT NULL
93 ,p_sourcing_percentage IN NUMBER DEFAULT NULL
94 ,p_last_update_date IN DATE DEFAULT NULL
95 ,p_last_updated_by IN NUMBER DEFAULT NULL
96 ,p_creation_date IN DATE DEFAULT NULL
97 ,p_created_by IN NUMBER DEFAULT NULL
98 ,p_last_update_login IN NUMBER DEFAULT NULL
99 ,p_transaction_type IN NUMBER
100 ,x_ret_status OUT NOCOPY VARCHAR2
101 ,x_error_msg OUT NOCOPY VARCHAR2);
102
103 -- Kanban cards UI to call this.
104 PROCEDURE process_cards( p_pull_sequence_id IN NUMBER DEFAULT NULL
105 ,p_kanban_card_id IN OUT NOCOPY NUMBER
106 ,p_kanban_card_number IN VARCHAR2 DEFAULT NULL
107 ,p_inv_item_code IN VARCHAR2 DEFAULT NULL
108 ,p_inventory_item_id IN NUMBER DEFAULT NULL
109 ,p_organization_code IN VARCHAR2 DEFAULT NULL
110 ,p_organization_id IN NUMBER DEFAULT NULL
111 ,p_subinventory_name IN VARCHAR2 DEFAULT NULL
112 ,p_supply_status IN NUMBER DEFAULT NULL
113 ,p_card_status IN NUMBER DEFAULT NULL
114 ,p_kanban_card_type IN NUMBER DEFAULT NULL
115 ,p_source_type IN NUMBER DEFAULT NULL
116 ,p_kanban_size IN NUMBER DEFAULT NULL
117 ,p_locator_id IN NUMBER DEFAULT NULL
118 ,p_supplier_id IN NUMBER DEFAULT NULL
119 ,p_supplier_name IN VARCHAR2 DEFAULT NULL
120 ,p_supplier_site_id IN NUMBER DEFAULT NULL
121 ,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
122 ,p_source_organization_id IN NUMBER DEFAULT NULL
123 ,p_source_subinventory IN VARCHAR2 DEFAULT NULL
124 ,p_source_locator_id IN NUMBER DEFAULT NULL
125 ,p_current_replnsh_cycle_id IN NUMBER DEFAULT NULL
126 ,p_kanban_error_code IN NUMBER DEFAULT NULL
127 ,p_wip_line_code IN VARCHAR2 DEFAULT NULL
128 ,p_wip_line_id IN NUMBER DEFAULT NULL
129 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
130 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
131 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
132 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
133 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
134 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
135 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
136 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
137 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
138 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
139 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
140 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
141 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
142 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
143 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
144 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
145 ,p_last_print_date IN DATE DEFAULT NULL
146 ,p_last_update_date IN DATE DEFAULT NULL
147 ,p_last_update_by IN NUMBER DEFAULT NULL
148 ,p_creation_date IN DATE DEFAULT NULL
149 ,p_created_by IN NUMBER DEFAULT NULL
150 ,p_last_update_login IN NUMBER DEFAULT NULL
151 ,p_request_id IN NUMBER DEFAULT NULL
152 ,p_program_application_id IN NUMBER DEFAULT NULL
153 ,p_program_id IN NUMBER DEFAULT NULL
154 ,p_program_update_date IN DATE DEFAULT NULL
155 ,p_replenishment_count IN NUMBER DEFAULT NULL
156 ,p_max_replenishments IN NUMBER DEFAULT NULL
157 ,p_disable_date IN DATE DEFAULT NULL
158 ,p_replacement_flag IN NUMBER DEFAULT NULL
159 ,p_transaction_type IN NUMBER
160 ,x_ret_status OUT NOCOPY VARCHAR2
161 ,x_err_msg OUT NOCOPY VARCHAR2
162 );
163
164 PROCEDURE process_cards(p_kanban_card_rec IN OUT NOCOPY flm_ekanban_pub.kanban_card_rec_type
165 ,p_transaction_type IN NUMBER
166 ,p_commit_flag IN VARCHAR2 DEFAULT 'N'
167 ,x_ret_status OUT NOCOPY VARCHAR2
168 ,x_err_msg OUT NOCOPY VARCHAR2
169 );
170
171 PROCEDURE process_kanban_cards(p_kanban_card_rec IN OUT NOCOPY flm_ekanban_pub.kanban_card_rec_type
172 ,p_release_kanban_flag IN NUMBER
173 ,p_transaction_type IN NUMBER
174 ,x_ret_status OUT NOCOPY VARCHAR2
175 ,x_err_msg OUT NOCOPY VARCHAR2
176 );
177
178 PROCEDURE Get_Pull_Sequence_Tokens
179 (p_Pull_Sequence_Id Number,
180 p_organization_id number,
181 p_inventory_item_id number,
182 p_locator_id number,
183 x_org_code Out NOCOPY varchar2,
184 x_item_name Out NOCOPY varchar2,
185 x_subinventory Out NOCOPY varchar2,
186 x_loc_name Out NOCOPY varchar2);
187
188 function default_org_id(p_org_code in varchar2)
189 return number;
190
191 function is_org_id_invalid(p_org_id number)
192 return boolean;
193
194 function default_wip_line_id(p_wip_line_code in varchar2
195 ,p_org_id in number)
196 return number;
197
198 function is_wip_line_id_invalid(p_wip_line_id number)
199 return boolean;
200
201 function default_supplier_id(p_supplier_name in varchar2)
202 return number;
203
204 function is_supplier_id_invalid(p_supplier_id number)
205 return boolean;
206
207 function is_supplier_site_id_invalid(p_supplier_id in number
208 ,p_supplier_site_id in number
209 ,p_org_id in number)
210 return boolean;
211
212 function default_supplier_site_id(p_supplier_id in number
213 ,p_supplier_site_code in varchar2
214 ,p_org_id in number)
215 return number;
216
217 function default_inv_item_id(p_org_id in number
218 ,p_conc_segments in varchar2)
219 return number;
220
221 function default_kanban_card_id(p_pull_sequence_id in number
222 ,p_kanban_card_number in varchar2)
223 return number;
224
225 END FLM_KANBAN_PUB;