1 PACKAGE MSC_X_VMI_UTIL_NEW AUTHID CURRENT_USER AS
2 /* $Header: MSCXVMIS.pls 120.1 2005/06/20 04:09:46 appldev ship $ */
3
4 delim constant varchar2(1) := '#';
5 dformat varchar2(20) := 'dd-mon-rrrr';
6 empty_string constant varchar2(33) := '#################################';
7
8 -- CP Order Types
9 REPLENISHMENT CONSTANT NUMBER := 19;
10 REQUISITION CONSTANT NUMBER := 20;
11 SUPPLY_SCHEDULE CONSTANT NUMBER := 2;
12 ALLOCATED_ONHAND CONSTANT NUMBER := 9;
13 UNALLOCATED_ONHAND CONSTANT NUMBER := 10;
14 ASN CONSTANT NUMBER := 15;
15 SHIPMENT_RECEIPT CONSTANT NUMBER := 16;
16 PO CONSTANT NUMBER := 13;
17 SALES_ORDER CONSTANT NUMBER := 14;
18 SAFETY_STOCK CONSTANT NUMBER := 7;
19 ORDER_FORECAST CONSTANT NUMBER := 2;
20 SALES_FORECAST CONSTANT NUMBER := 1;
21 HISTORICAL_SALES CONSTANT NUMBER := 4;
22
23 -- constants used for replenishment status
24 UNRELEASED CONSTANT NUMBER := 0;
25 RELEASED CONSTANT NUMBER := 1;
26 REJECTED CONSTANT NUMBER := 2;
27
28 -- constant used for source org status
29
30
31
32
33 ASN_AUTO_EXPIRE_YES CONSTANT NUMBER := 1;
34 ASN_AUTO_EXPIRE_NO CONSTANT NUMBER := 2;
35
36 -- table for Calculating avg daily demand
37 TYPE t_table_add_data IS TABLE OF NUMBER
38 INDEX BY BINARY_INTEGER;
39 t_table_avg_daily_demand t_table_add_data;
40
41
42 FUNCTION vmi_details_supplier (p_sr_instance_id in number default null
43 , p_inventory_item_id in number default null
44 , p_customer_id in number default null
45 , p_customer_site_id in number default null
46 , p_supplier_id in number default null
47 , p_supplier_site_id in number default null
48 , p_organization_id in number default null
49 , p_tp_supplier_id in number default null
50 , p_tp_supplier_site_id in number default null
51 ) return varchar2;
52
53 FUNCTION vmi_details_customer(
54 p_inventory_item_id in number
55 , p_organization_id IN NUMBER
56 , p_sr_instance_id IN NUMBER
57 , p_customer_id in number default null
58 , p_customer_site_id in number default null
59 , p_supplier_id in number default null
60 , p_supplier_site_id in number default null
61 ) RETURN VARCHAR2;
62
63
64 PROCEDURE vmiCustomerGraphCreate
65 ( p_inventory_item_id IN NUMBER
66 , p_organization_id IN NUMBER
67 , p_sr_instance_id IN NUMBER
68 , p_customer_id IN NUMBER
69 , p_customer_site_id IN NUMBER
70 , p_supplier_id IN NUMBER
71 , p_supplier_site_id IN NUMBER
72 , p_query_id OUT NOCOPY NUMBER
73 );
74
75 Procedure VmiSupplierGraphOnhand
76 ( p_inventory_item_id IN NUMBER,
77 p_customer_id IN NUMBER,
78 p_customer_site_id IN NUMBER,
79 p_supplier_id IN NUMBER,
80 p_supplier_site_id IN NUMBER,
81 p_organization_id IN NUMBER,
82 p_tp_supplier_id IN NUMBER,
83 p_tp_supplier_site_id IN NUMBER,
84 p_sr_instance_id IN NUMBER,
85 p_plan_id IN NUMBER,
86 p_return_code OUT NOCOPY NUMBER,
87 p_err_msg OUT NOCOPY VARCHAR2
88 );
89
90 FUNCTION AVG_DAILY_DEMAND
91 (p_inventory_item_id IN NUMBER,
92 p_customer_id IN NUMBER,
93 p_customer_site_id IN NUMBER,
94 p_supplier_id IN NUMBER,
95 p_supplier_site_id IN NUMBER,
96 p_plan_id IN NUMBER,
97 p_forecast_horizon IN NUMBER)
98 return t_table_add_data ;
99
100 FUNCTION INTRANSIT_LEAD_TIME
101 (p_source_org_id IN NUMBER,
102 p_modeled_org_id IN NUMBER,
103 p_customer_id IN NUMBER,
104 p_customer_site_id IN NUMBER,
105 p_supplier_id IN NUMBER,
106 p_sr_instance_id IN NUMBER,
107 p_consigned_flag IN NUMBER)
108 return NUMBER;
109
110 PROCEDURE vmiCustomerGraphTest;
111
112
113 FUNCTION supplier_avg_daily_usage(
114 p_inventory_item_id in number
115 , p_organization_id in number
116 , p_sr_instance_id in number
117 , p_tp_supplier_id in number default null
118 , p_tp_supplier_site_id in number default null
119 ) return number;
120
121 FUNCTION customer_avg_daily_usage(
122 p_inventory_item_id in number
123 , p_organization_id in number
124 , p_sr_instance_id in number
125 ) return number;
126
127
128
129
130
131 END MSC_X_VMI_UTIL_NEW;
132