1 PACKAGE gme_pending_product_lots_pvt AUTHID CURRENT_USER AS
2 /* $Header: GMEVPPLS.pls 120.8.12000000.2 2007/02/28 19:27:31 nsinghi ship $ */
3
4 g_sequence_increment CONSTANT NUMBER := 100;
5
6 FUNCTION get_last_sequence
7 (p_matl_dtl_id IN NUMBER
8 ,x_return_status OUT NOCOPY VARCHAR2)
9 RETURN NUMBER;
10
11 PROCEDURE get_pending_lot
12 (p_material_detail_id IN NUMBER
13 ,x_return_status OUT NOCOPY VARCHAR2
14 ,x_pending_product_lot_tbl OUT NOCOPY gme_common_pvt.pending_lots_tab);
15
16 PROCEDURE relieve_pending_lot
17 (p_pending_lot_id IN NUMBER
18 ,p_quantity IN NUMBER
19 ,p_secondary_quantity IN NUMBER := NULL
20 ,x_return_status OUT NOCOPY VARCHAR2);
21
22 PROCEDURE create_product_lot
23 (p_organization_id IN NUMBER
24 ,p_inventory_item_id IN NUMBER
25 ,p_parent_lot IN mtl_lot_numbers.lot_number%TYPE := NULL
26 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
27 ,p_generate_lot IN VARCHAR2
28 ,p_generate_parent_lot IN VARCHAR2
29 /* nsinghi bug#4486074 Added the p_expiration_Date parameter. */
30 ,p_expiration_date IN mtl_lot_numbers.expiration_date%TYPE := NULL
31 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
32 ,x_return_status OUT NOCOPY VARCHAR2);
33
34 PROCEDURE create_pending_product_lot
35 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
36 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
37 ,x_return_status OUT NOCOPY VARCHAR2);
38
39 PROCEDURE update_pending_product_lot
40 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
41 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
42 ,x_return_status OUT NOCOPY VARCHAR2);
43
44 PROCEDURE delete_pending_product_lot
45 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
46 ,x_return_status OUT NOCOPY VARCHAR2);
47
48 --Bug#5078853 created the following over loaded procedure
49 PROCEDURE delete_pending_product_lot
50 (p_material_detail_id IN NUMBER
51 ,x_return_status OUT NOCOPY VARCHAR2);
52
53 PROCEDURE validate_material_for_create
54 (p_batch_header_rec IN gme_batch_header%ROWTYPE
55 ,p_material_detail_rec IN gme_material_details%ROWTYPE
56 ,x_return_status OUT NOCOPY VARCHAR2);
57
58 PROCEDURE validate_record_for_create
59 (p_material_detail_rec IN gme_material_details%ROWTYPE
60 ,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
61 ,p_create_lot IN VARCHAR2
62 ,p_generate_lot IN VARCHAR2
63 ,p_generate_parent_lot IN VARCHAR2
64 ,p_parent_lot IN mtl_lot_numbers.lot_number%TYPE := NULL
65 /* nsinghi bug#4486074 Added the p_expiration_Date parameter. */
66 ,p_expiration_date IN mtl_lot_numbers.expiration_date%TYPE := NULL
67 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
68 ,x_return_status OUT NOCOPY VARCHAR2);
69
70 PROCEDURE validate_material_for_update
71 (p_batch_header_rec IN gme_batch_header%ROWTYPE
72 ,p_material_detail_rec IN gme_material_details%ROWTYPE
73 ,x_return_status OUT NOCOPY VARCHAR2);
74
75 PROCEDURE validate_record_for_update
76 (p_material_detail_rec IN gme_material_details%ROWTYPE
77 ,p_db_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
78 ,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
79 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
80 ,x_return_status OUT NOCOPY VARCHAR2);
81
82 PROCEDURE validate_material_for_delete
83 (p_batch_header_rec IN gme_batch_header%ROWTYPE
84 ,p_material_detail_rec IN gme_material_details%ROWTYPE
85 ,x_return_status OUT NOCOPY VARCHAR2);
86
87 PROCEDURE validate_record_for_delete
88 (p_material_detail_rec IN gme_material_details%ROWTYPE
89 ,p_db_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
90 ,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
91 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
92 ,x_return_status OUT NOCOPY VARCHAR2);
93
94 FUNCTION validate_lot_number (p_inv_item_id IN NUMBER
95 ,p_org_id IN NUMBER
96 ,p_lot_number IN VARCHAR2
97 ,x_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
98
99 FUNCTION validate_sequence (p_matl_dtl_rec IN gme_material_details%ROWTYPE
100 ,p_sequence IN NUMBER
101 ,x_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
102
103 FUNCTION validate_quantities
104 (p_matl_dtl_rec IN gme_material_details%ROWTYPE
105 ,p_lot_number IN VARCHAR2
106 ,p_revision IN VARCHAR2
107 ,p_dtl_qty IN OUT NOCOPY NUMBER
108 ,p_sec_qty IN OUT NOCOPY NUMBER
109 ,x_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
110
111 FUNCTION validate_revision (p_item_rec IN mtl_system_items_b%ROWTYPE
112 ,p_revision IN VARCHAR2
113 ,x_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
114
115 FUNCTION validate_reason_id(p_reason_id IN NUMBER
116 ,x_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
117
118 FUNCTION pending_product_lot_exist
119 (p_batch_id IN NUMBER
120 ,p_material_detail_id IN NUMBER) RETURN BOOLEAN;
121
122 -- nsinghi bug#5689035. Added this procedure.
123 PROCEDURE get_pnd_prod_lot_qty (
124 p_mtl_dtl_id IN NUMBER
125 ,x_pnd_prod_lot_qty OUT NOCOPY NUMBER
126 ,x_return_status OUT NOCOPY VARCHAR2);
127
128 END gme_pending_product_lots_pvt;