1 PACKAGE INV_LOT_API_PKG
2 /* $Header: INVVLTPS.pls 120.0.12020000.2 2012/10/23 02:55:03 xzhixong ship $ */
3 AS
4
5 g_ret_sts_success CONSTANT VARCHAR2(1) := 'S';
6 g_ret_sts_error CONSTANT VARCHAR2(1) := 'E';
7 g_ret_sts_unexp_error CONSTANT VARCHAR2(1) := 'U';
8 g_miss_num CONSTANT NUMBER := 9.99e125;
9 g_miss_char CONSTANT VARCHAR2(1) := CHR(0);
10 g_miss_date CONSTANT DATE := TO_DATE('1', 'j');
11 osfm_form_no_validate CONSTANT NUMBER := 1;
12 osfm_open_interface CONSTANT NUMBER := 2;
13 osfm_form_validate CONSTANT NUMBER := 3;
14 inv CONSTANT NUMBER := 4;
15 G_WMS_INSTALLED VARCHAR2(10);
16
17 /*Exception definitions */
18 g_exc_error EXCEPTION;
19 g_exc_unexpected_error EXCEPTION;
20
21 /*Local variable for stoRing the INV:DEBUG TRACE profile value */
22 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
23
24
25
26 -- This procedure populates the parent and child lot records and
27 -- returns them to the Create_Inv_Lot procedure.
28 PROCEDURE Populate_Lot_Records (
29 x_return_status OUT NOCOPY VARCHAR2
30 , x_msg_count OUT NOCOPY NUMBER
31 , x_msg_data OUT NOCOPY VARCHAR2
32 , x_child_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
33 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
34 , p_copy_lot_attribute_flag IN VARCHAR2
35 , p_source IN NUMBER
36 , p_api_version IN NUMBER
37 , p_init_msg_list IN VARCHAR2
38 , p_commit IN VARCHAR2
39 );
40
41
42 --Over load Populate_Lot_Records add new parameter p_cacuLate_flag
43 -- If p_cacuLate_flag = 'T' then re cacluated the attribute for the null value
44 --14192172
45 PROCEDURE Populate_Lot_Records (
46 x_return_status OUT NOCOPY VARCHAR2
47 , x_msg_count OUT NOCOPY NUMBER
48 , x_msg_data OUT NOCOPY VARCHAR2
49 , x_child_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
50 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
51 , p_copy_lot_attribute_flag IN VARCHAR2
52 , p_source IN NUMBER
53 , p_api_version IN NUMBER
54 , p_init_msg_list IN VARCHAR2
55 , p_commit IN VARCHAR2
56 , p_caculate_flag IN VARCHAR2
57 );
58
59 -- This procedure populates the grade code, origination type, expiration date,
60 -- retest date, expiration action code, expiration action date,hold date and
61 -- maturity date based on the values from mtl_system_items for a given
62 -- organization and inventory item id.
63 PROCEDURE Set_Msi_Default_Attr (
64 p_lot_rec IN OUT NOCOPY mtl_lot_numbers%ROWTYPE
65 , x_return_status OUT NOCOPY VARCHAR2
66 , x_msg_count OUT NOCOPY NUMBER
67 , x_msg_data OUT NOCOPY VARCHAR2
68 ) ;
69
70 -- This procedure performs the validations on the lot and defaults the missing attributes
71 -- as per the logic existing in the earlier create_inv_lot procedure.It returns the populate
72 -- lot record.
73 PROCEDURE Validate_Lot_Attributes(
74 x_return_status OUT NOCOPY VARCHAR2
75 , x_msg_count OUT NOCOPY NUMBER
76 , x_msg_data OUT NOCOPY VARCHAR2
77 , x_lot_rec IN OUT NOCOPY Mtl_Lot_Numbers%ROWTYPE
78 , p_source IN NUMBER
79 ) ;
80
81 -- This procedure validates the OMP related attributes like validations for parent lot , origination
82 -- type, grade code, expiration action date/code, retest date, maturity date , hold date.
83 PROCEDURE Validate_Additional_Attr(
84 x_return_status OUT NOCOPY VARCHAR2
85 , x_msg_count OUT NOCOPY NUMBER
86 , x_msg_data OUT NOCOPY VARCHAR2
87 , p_inventory_item_id IN NUMBER
88 , p_organization_id IN NUMBER
89 , p_lot_number IN VARCHAR2
90 , p_source IN NUMBER
91 , p_grade_code IN VARCHAR2
92 , p_retest_date IN DATE
93 , p_maturity_date IN DATE
94 , p_parent_lot_number IN VARCHAR2
95 , p_origination_date IN DATE
96 , p_origination_type IN NUMBER
97 , p_expiration_action_code IN VARCHAR2
98 , p_expiration_action_date IN DATE
99 , p_expiration_date IN DATE
100 , p_hold_date IN DATE
101 ) ;
102
103
104 PROCEDURE Delete_Lot(
105 x_return_status OUT NOCOPY VARCHAR2
106 , x_msg_count OUT NOCOPY NUMBER
107 , x_msg_data OUT NOCOPY VARCHAR2
108 , p_inventory_item_id IN NUMBER
109 , p_organization_id IN NUMBER
110 , p_lot_number IN VARCHAR2
111 ) ;
112
113 /** INVCONV ANTHIYAG 04-Nov-2004 Start **/
114 -- This Function would be used to check whether the lot number passed
115 -- exists in the database or not. As this uses Pragma Autonomous_transaction
116 -- it would check for the lots existing prior to the uncommitted transactions
117
118 FUNCTION Check_Existing_Lot_Db
119 (
120 p_org_id IN NUMBER
121 ,p_inventory_item_id IN NUMBER
122 ,p_lot_number IN VARCHAR2
123 ) RETURN BOOLEAN;
124
125 /** INVCONV ANTHIYAG 04-Nov-2004 End **/
126
127 END INV_LOT_API_PKG ;