1 PACKAGE CSP_PLANNER_NOTIFICATIONS AUTHID CURRENT_USER AS
2 /* $Header: cspvppns.pls 115.7 2003/09/08 23:35:53 phegde noship $ */
3 --
4 -- Purpose: This package will hold all APIs related to the creation of
5 -- planner notifications and recommendations for the notifications
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- phegde 16th April 2002 Created new Package Specification
10
11 TYPE item_list_rectype IS RECORD
12 (inventory_item_id NUMBER
13 ,category_set_id NUMBER
14 ,item_hi VARCHAR2(1000)
15 ,item_lo VARCHAR2(1000)
16 ,d_cutoff DATE
17 ,s_cutoff DATE
18 ,repitem VARCHAR2(80)
19 ,dd_loc_id NUMBER -- default deliver to loc
20 ,net_rsv NUMBER
21 ,net_unrsv NUMBER
22 ,net_wip NUMBER
23 ,include_po NUMBER
24 ,include_wip NUMBER
25 ,include_iface_sup NUMBER
26 ,include_nonnet_sub NUMBER
27 ,lot_control NUMBER
28 ,sort VARCHAR2(2) := 1
29 ,employee_id NUMBER);
30
31 /* TYPE item_list_tbl IS TABLE OF item_list_rectype
32 INDEX BY BINARY_INTEGER;
33 */
34 TYPE excess_parts_rectype IS RECORD
35 (source_org_id NUMBER
36 ,source_subinv VARCHAR2(30)
37 ,inventory_item_id NUMBER
38 ,quantity NUMBER
39 ,repair_supplier_id NUMBER);
40
41 TYPE business_rule_rectype IS RECORD
42 (IO_Excess_Value NUMBER
43 ,IO_Repair_Value NUMBER
44 ,IO_Recommend_Value NUMBER
45 ,IO_Tracking_Signal_Max NUMBER
46 ,IO_Tracking_Signal_Min NUMBER
47 ,REQ_Excess_Value NUMBER
48 ,REQ_Repair_Value NUMBER
49 ,REQ_Recommend_Value NUMBER
50 ,REQ_Tracking_Signal_Max NUMBER
51 ,REQ_Tracking_Signal_Min NUMBER
52 ,WIP_Excess_Value NUMBER
53 ,WIP_Repair_Value NUMBER
54 ,WIP_Recommend_Value NUMBER
55 ,WIP_Tracking_Signal_Max NUMBER
56 ,WIP_Tracking_Signal_Min NUMBER);
57
58 TYPE business_rule_tbl IS TABLE OF business_rule_rectype
59 INDEX BY BINARY_INTEGER;
60
61 TYPE excess_parts_tbl IS TABLE OF excess_parts_rectype
62 INDEX BY BINARY_INTEGER;
63
64
65 PROCEDURE create_notifications
66 ( errbuf OUT NOCOPY varchar2
67 ,retcode OUT NOCOPY number
68 ,p_api_version IN NUMBER
69 ,p_organization_id IN NUMBER
70 ,p_level IN NUMBER
71 ,p_notif_for_io IN NUMBER
72 ,p_notif_for_po IN NUMBER
73 ,p_notif_for_wip IN NUMBER
74 ,p_category_set_id IN NUMBER
75 ,p_category_Struct_id IN NUMBER
76 ,p_Category_lo IN VARCHAR2
77 ,p_category_hi IN VARCHAR2
78 ,p_item_lo IN VARCHAR2
79 ,p_item_hi IN VARCHAR2
80 ,p_planner_lo IN VARCHAR2
81 ,p_planner_hi IN VARCHAR2
82 ,p_buyer_lo IN VARCHAR2
83 ,p_buyer_hi IN VARCHAR2
84 ,p_d_cutoff_date IN VARCHAR2
85 ,p_d_offset IN NUMBER
86 ,p_s_cutoff_date IN VARCHAR2
87 ,p_s_offset IN NUMBER
88 ,p_restock IN NUMBER
89 ,p_repitem IN VARCHAR2
90 ,p_dd_loc_id IN NUMBER -- default deliver to loc
91 ,p_net_rsv IN NUMBER
92 ,p_net_unrsv IN NUMBER
93 ,p_net_wip IN NUMBER
94 ,p_include_po IN NUMBER
95 ,p_include_wip IN NUMBER
96 ,p_include_iface_sup IN NUMBER
97 ,p_include_nonnet_sub IN NUMBER
98 ,p_lot_control IN NUMBER
99 ,p_sort IN VARCHAR2 := '1'
100 );
101
102 PROCEDURE Calculate_Excess(
103 p_organization_id IN NUMBER
104 ,p_item_rec IN csp_planner_notifications.item_list_rectype
105 ,p_called_from IN VARCHAR2 := 'NOTIF'
106 ,p_notification_id IN NUMBER := null
107 ,p_order_by_date IN DATE := sysdate
108 ,x_excess_parts_tbl OUT NOCOPY csp_planner_notifications.excess_parts_tbl
109 ,x_return_status OUT NOCOPY VARCHAR2
110 ,x_msg_data OUT NOCOPY VARCHAR2
111 ,x_msg_count OUT NOCOPY NUMBER);
112 END; -- Package spec
[Home] [Help]