1 PACKAGE BODY PO_SHIPMENTS_SV7 as
2 /* $Header: POXPOS7B.pls 115.5 2003/10/17 22:42:33 prpeters ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: get_dest_type_dist()
7
8 ===========================================================================*/
9 function get_dest_type_dist(X_po_header_id IN number,
10 X_po_line_id IN number,
11 X_line_location_id IN number)
12 return boolean is
13
14 X_distribs_exist boolean;
15 X_Progress varchar2(3) := '';
16 X_count number;
17
18 begin
19
20 X_Progress := '010';
21
22 /* Check if there are any distributions who have
23 ** destination type as SHOP FLOOR or INVENTORY */
24
25 SELECT count(*)
26 INTO X_count
27 FROM po_distributions
28 WHERE line_location_id = X_line_location_id
29 AND po_line_id = X_po_line_id
30 AND po_header_id = X_po_header_id
31 AND destination_type_code IN ('SHOP FLOOR','INVENTORY')
32 AND distribution_type <> 'AGREEMENT'; -- <Encumbrance FPJ>
33
34
35 if X_count > 0 then
36 X_distribs_exist := TRUE;
37 else
38 X_distribs_exist := FALSE;
39 end if;
40
41 return(X_distribs_exist);
42
43 exception
44
45 when no_data_found then
46 X_distribs_exist := FALSE;
47 return(X_distribs_exist);
48 when others then
49 po_message_s.sql_error('get_dest_type_dist',X_Progress,sqlcode);
50 raise;
51
52 end get_dest_type_dist;
53
54 /*===========================================================================
55
56 PROCEDURE NAME: get_original_date()
57
58 ===========================================================================*/
59 procedure get_original_date(X_line_location_id IN number,
60 X_Promised_Date IN OUT NOCOPY DATE) is
61
62 X_Progress varchar2(3) := '';
63
64 begin
65
66 X_Progress := '010';
67
68 /* Get the Promised date from PLL_ARCHIVE
69 ** for given line_location_id where the revision_num
70 ** is minimum */
71
72 /* Bug# 3199923, Added the where promised_date is NULL
73 to pick the revision where the promised_date was
74 initially update to a value other than NULL*/
75
76 select plla.promised_date
77 into X_promised_date
78 from po_line_locations_archive plla
79 where plla.line_location_id = X_line_location_id
80 and plla.revision_num = (select min(revision_num)
81 from po_line_locations_archive plla2
82 where plla2.line_location_id = X_line_location_id
83 and plla2.promised_date is not NULL);
84
85
86 exception
87
88 when no_data_found then
89 null;
90 when others then
91 po_message_s.sql_error('get_original_date',X_Progress,sqlcode);
92 raise;
93
94 end get_original_date;
95
96 /*===========================================================================
97
98 PROCEDURE NAME: get_dist_num()
99
100 ===========================================================================*/
101 procedure get_dist_num(X_line_location_id IN number,
102 X_dist_num IN OUT NOCOPY number,
103 X_code_combination_id IN OUT NOCOPY number) is
104
105 X_Progress varchar2(3) := '';
106 begin
107
108 X_Progress := '010';
109
110 select count(*)
111 into X_dist_num
112 from po_distributions
113 where line_location_id = X_line_location_id;
114
115 X_Progress := '020';
116
117 if X_dist_num = 1 then
118 select code_combination_id
119 into X_code_combination_id
120 from po_distributions
121 where line_location_id = X_line_location_id;
122 end if;
123
124 exception
125
126 when no_data_found then
127 null;
128 when others then
129 po_message_s.sql_error('get_original_date',X_Progress,sqlcode);
130 raise;
131
132 end get_dist_num;
133
134 /*============================================================================
135
136 PROCEDURE NAME: check_available_quantity()
137
138 =============================================================================*/
139
140 procedure check_available_quantity(X_source_shipment_id IN NUMBER,
141 X_orig_quantity IN NUMBER,
142 X_quantity IN NUMBER) is
143
144 X_Progress varchar2(3) := '';
145 X_available_quantity NUMBER ;
146 X_unreleased_quantity NUMBER;
147
148 begin
149
150 X_Progress := '010';
151
152 SELECT (pll.quantity - nvl(pll.quantity_cancelled,0)) -
153 (nvl(sum(pll2.quantity - nvl(pll2.quantity_cancelled,0)),0) -
154 nvl(X_orig_quantity,0) +
155 nvl(X_quantity,0)),
156 (pll.quantity - nvl(pll.quantity_cancelled,0)) -
157 (nvl(sum(pll2.quantity - nvl(pll2.quantity_cancelled,0) -
158 nvl(X_orig_quantity,0)),0))
159 INTO X_unreleased_quantity,
160 X_available_quantity
161 FROM po_line_locations pll2,
162 po_line_locations pll
163 WHERE pll.line_location_id = pll2.source_shipment_id(+)
164 AND pll.line_location_id = X_source_shipment_id
165 AND pll.shipment_type <> 'PRICE BREAK'
166 GROUP BY (pll.quantity - nvl(pll.quantity_cancelled,0));
167
168
169 if X_unreleased_quantity < 0 then
170 po_message_s.app_error('PO_PO_QTY_EXCEEDS_UNREL','QUANTITY',to_char(X_quantity),
171 'UNRELEASED', to_char(X_available_quantity));
172 end if;
173
174 exception
175
176 when no_data_found then
177 null;
178 when others then
179 po_message_s.sql_error('check_available_quantity',X_Progress,sqlcode);
180 raise;
181
182 end check_available_quantity;
183
184 END PO_SHIPMENTS_SV7;
[Home] [Help]