1 PACKAGE INV_STATUS_PKG AUTHID CURRENT_USER AS
2 /* $Header: INVUPMSS.pls 120.10 2012/01/20 08:42:05 skommine ship $ */
3 --BEGIN SCHANDRU INVERES
4 function get_from_status_code ( p_org_id in number default null,
5 p_item_id in number default null,
6 p_sub_inv in varchar2 default null,
7 p_locator_id in number default null,
8 p_lot in varchar2 default null,
9 p_serial in varchar2 default null) return varchar2 ;
10
11 --END SCHANDRU INVERES
12
13 --Bug11826279, new function for onhand status change
14 function get_onhand_from_status ( p_org_id in number,
15 p_item_id in number,
16 p_sub_inv in varchar2,
17 p_locator_id in number default null,
18 p_lot in varchar2 default null,
19 p_lpn_id in number default null) return varchar2;
20
21
22 PROCEDURE check_lot_range_status(
23 p_org_id IN NUMBER,
24 p_item_id IN NUMBER,
25 p_from_lot IN VARCHAR2,
26 p_to_lot IN VARCHAR2,
27 x_Status OUT nocopy VARCHAR2,
28 x_Message OUT nocopy VARCHAR2,
29 x_Status_Code OUT nocopy VARCHAR2
30 );
31
32 PROCEDURE check_serial_range_status(
33 p_org_id IN NUMBER,
34 p_item_id IN NUMBER,
35 p_from_serial IN VARCHAR2,
36 p_to_serial IN VARCHAR2,
37 x_Status OUT nocopy VARCHAR2,
38 x_Message OUT nocopy VARCHAR2,
39 x_Status_Code OUT nocopy VARCHAR2
40 );
41 --ERES Deferred
42 PROCEDURE post_sign_changes (p_event_name IN VARCHAR
43 ,p_transaction_id IN NUMBER
44 ,p_status_id IN NUMBER
45 ,p_pending_status IN NUMBER DEFAULT 1 );
46
47 PROCEDURE update_status(
48 p_update_method IN NUMBER
49 , p_organization_id IN NUMBER
50 , p_inventory_item_id IN NUMBER
51 , p_sub_code IN VARCHAR2
52 , p_sub_status_id IN NUMBER
53 , p_sub_reason_id IN NUMBER
54 , p_locator_id IN NUMBER
55 , p_loc_status_id IN NUMBER
56 , p_loc_reason_id IN NUMBER
57 , p_from_lot_number IN VARCHAR2
58 , p_to_lot_number IN VARCHAR2
59 , p_lot_status_id IN NUMBER
60 , p_lot_reason_id IN NUMBER
61 , p_from_SN IN VARCHAR2
62 , p_to_SN IN VARCHAR2
63 , p_serial_status_id IN NUMBER
64 , p_serial_reason_id IN NUMBER
65 , x_Status OUT nocopy VARCHAR2
66 , x_Message OUT nocopy VARCHAR2
67 , p_update_from_mobile IN VARCHAR2 DEFAULT 'Y'
68 -- NSRIVAST, INVCONV , Start
69 , p_grade_code IN VARCHAR2 DEFAULT NULL
70 , p_primary_onhand IN NUMBER DEFAULT NULL
71 , p_secondary_onhand IN NUMBER DEFAULT NULL
72 -- NSRIVAST, INVCONV , End
73 , p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
74 , p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
75 , p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
76 , p_lpn_indicator IN NUMBER DEFAULT 1 --bug12621577
77 , p_group_id IN NUMBER DEFAULT -999
78 , p_pending_status IN NUMBER DEFAULT 0 --ERES Deferred
79
80 );
81
82 PROCEDURE invoke_reason_wf(
83 p_update_method IN NUMBER
84 , p_organization_id IN NUMBER
85 , p_inventory_item_id IN NUMBER
86 , p_sub_code IN VARCHAR2
87 , p_sub_status_id IN NUMBER
88 , p_sub_reason_id IN NUMBER
89 , p_locator_id IN NUMBER
90 , p_loc_status_id IN NUMBER
91 , p_loc_reason_id IN NUMBER
92 , p_from_lot_number IN VARCHAR2
93 , p_to_lot_number IN VARCHAR2
94 , p_lot_status_id IN NUMBER
95 , p_lot_reason_id IN NUMBER
96 , p_from_SN IN VARCHAR2
97 , p_to_SN IN VARCHAR2
98 , p_serial_status_id IN NUMBER
99 , p_serial_reason_id IN NUMBER
100 , p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
101 , p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
102 , p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
103 , x_Status OUT nocopy VARCHAR2
104 , x_Message OUT nocopy VARCHAR2);
105 --Bug#5577767 Created this procedure to filter sec qty/uom based on tracking_quantity_ind.
106 PROCEDURE tracking_quantity_ind(p_item_id IN NUMBER,
107 p_org_id IN NUMBER,
108 x_sec_qty IN OUT nocopy NUMBER,
109 x_sec_uom IN OUT nocopy VARCHAR2);
110 --added for lpn status project to check if update transaction will result in mixed or not
111 FUNCTION get_mixed_status(p_lpn_id NUMBER,
112 p_organization_id NUMBER,
113 p_outermost_lpn_id NUMBER,
114 p_inventory_item_id NUMBER,
115 p_lot_number VARCHAR2 := NULL,
116 p_status_id NUMBER)
117 RETURN VARCHAR2;
118 --added for lpn status project to check if update transaction will result in mixed or not
119 FUNCTION get_mixed_status_serial(p_lpn_id NUMBER,
120 p_organization_id NUMBER,
121 p_outermost_lpn_id NUMBER,
122 p_inventory_item_id NUMBER,
123 p_lot_number VARCHAR2 := NULL,
124 p_fm_sn VARCHAR2,
125 p_to_sn VARCHAR2,
126 p_status_id NUMBER)
127 RETURN VARCHAR2;
128
129 END INV_STATUS_PKG;