1 PACKAGE MRP_KANBAN_PLAN_PK AUTHID CURRENT_USER AS
2 /* $Header: MRPKPLNS.pls 120.3 2011/02/15 06:27:52 sisankar ship $ */
3
4 -- Declare some record types here
5
6 -- this record holds kanban plan information passed from the srs form
7 TYPE kanban_info_rec_type is record (
8 organization_id number,
9 kanban_plan_id number,
10 from_item varchar2(80),
11 to_item varchar2(80),
12 category_set_id number,
13 category_structure_id number,
14 from_category varchar2(80),
15 to_category varchar2(80),
16 bom_effectivity date,
17 start_date date,
18 cutoff_date date,
19 replan_flag number,
20 input_type number,
21 input_designator varchar2(10),
22 kanban_designator varchar2(10)
23 );
24
25 -- this record holds low level codes information from mrp_low_level_codes
26 -- table
27 TYPE llc_rec_type is record (
28 assembly_item_id number,
29 to_subinventory varchar2(10),
30 to_locator_id number,
31 component_item_id number,
32 from_subinventory varchar2(10),
33 from_locator_id number,
34 low_level_code number,
35 component_usage number,
36 component_yield number,
37 operation_yield number,
38 supply_source_type number,
39 replenishment_lead_time number
40 );
41
42 -- this record holds the exploded kanban demand information stored in
43 -- mrp_kanban_demand table
44 TYPE demand_rec_type is record (
45 demand_id number,
46 kanban_plan_id number,
47 organization_id number,
48 inventory_item_id number,
49 subinventory varchar2(10),
50 locator_id number,
51 assembly_item_id number,
52 assembly_subinventory varchar2(10),
53 assembly_locator_id number,
54 demand_date date,
55 demand_quantity number,
56 order_type number,
57 kanban_item_flag varchar2(1)
58 );
59
60 -- Declare global constants and variables
61
62 G_PRODUCTION_KANBAN CONSTANT NUMBER := -1;
63 G_PRODUCTION_SOURCE_TYPE CONSTANT NUMBER := 4;
64 G_SUCCESS CONSTANT NUMBER := 0;
65 G_WARNING CONSTANT NUMBER := 1;
66 G_ERROR CONSTANT NUMBER := 2;
67 G_CALC_KANBAN_SIZE CONSTANT NUMBER := 1;
68 G_CALC_KANBAN_NUMBER CONSTANT NUMBER := 2;
69 G_NO_FCST_CONTROL CONSTANT NUMBER := 3;
70
71 g_kanban_info_rec kanban_info_rec_type;
72 g_debug boolean := FALSE;
73 g_raise_warning boolean := FALSE;
74 g_log_message varchar2(2000);
75 g_stmt_num number;
76
77 -- ========================================================================
78 -- This is the main procedure that controls the flow of the kanban planning
79 -- process.
80 -- ERRBUF and RETCODE are two standard parameters that any PL/SQL
81 -- concurrent program should have. ERRBUF is used to return any error
82 -- messages and RETCODE to return the completion status. RETCODE returns
83 -- 0 for SUCCESS, 1 for SUCCESS with WARNINGS and 2 for ERROR
84 -- ========================================================================
85
86 PROCEDURE PLAN_KANBAN( ERRBUF OUT NOCOPY VARCHAR2,
87 RETCODE OUT NOCOPY NUMBER,
88 p_organization_id IN NUMBER,
89 p_kanban_plan_id IN NUMBER,
90 p_from_item IN VARCHAR2,
91 p_to_item IN VARCHAR2,
92 p_category_set_id IN NUMBER,
93 p_category_structure_id IN NUMBER,
94 p_from_category IN VARCHAR2,
95 p_to_category IN VARCHAR2,
96 p_bom_effectivity IN VARCHAR2,
97 p_start_date IN VARCHAR2,
98 p_cutoff_date IN VARCHAR2,
99 p_replan_flag IN NUMBER);
100
101 -- ========================================================================
102 --this function gets the offset start date to be considered when we look
103 --at forecast demand. for example a weekly forecast demand might have
104 --a start date 2 days before our kanban start date and we would have to
105 --consider a part of this forecast demand for our kanban calculation, else
106 --we would be underestimating our demand
107 -- ========================================================================
108 FUNCTION Get_Offset_Date (
109 p_start_date IN date,
110 p_bucket_type IN number
111 )
112 RETURN DATE;
113
114 FUNCTION Get_Repetitive_Demand(
115 p_schedule_date IN DATE,
116 p_rate_end_date IN DATE,
117 p_repetitive_daily_rate IN NUMBER)
118 RETURN NUMBER;
119
120 function Kanban_Calculation_Pvt (
121 p_average_demand IN NUMBER,
122 p_minimum_order_quantity IN NUMBER,
123 p_fixed_lot_multiplier IN NUMBER,
124 p_safety_stock_days IN NUMBER,
125 p_replenishment_lead_time IN NUMBER,
126 p_kanban_flag IN NUMBER,
127 p_kanban_size IN OUT NOCOPY NUMBER,
128 p_kanban_number IN OUT NOCOPY NUMBER )
129 RETURN BOOLEAN;
130
131 function Kanban_Calculation_Pvt (
132 p_average_demand IN NUMBER,
133 p_allocation IN NUMBER,
134 p_minimum_order_quantity IN NUMBER,
135 p_fixed_lot_multiplier IN NUMBER,
136 p_safety_stock_days IN NUMBER,
137 p_replenishment_lead_time IN NUMBER,
138 p_kanban_flag IN NUMBER,
139 p_kanban_size IN OUT NOCOPY NUMBER,
140 p_kanban_number IN OUT NOCOPY NUMBER )
141 RETURN BOOLEAN;
142
143
144 --now go ahead and define a pragma
145 PRAGMA RESTRICT_REFERENCES (Get_Offset_Date,WNDS,WNPS);
146 PRAGMA RESTRICT_REFERENCES (Get_Repetitive_Demand,WNDS,WNPS);
147
148
149 END MRP_KANBAN_PLAN_PK;
[Home] [Help]