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