[Home] [Help]
PACKAGE BODY: APPS.PO_POXPODDR_XMLP_PKG
Source
4 USER_EXIT_FAILURE EXCEPTION;
1 PACKAGE BODY PO_POXPODDR_XMLP_PKG AS
2 /* $Header: POXPODDRB.pls 120.1 2007/12/25 11:13:06 krreddy noship $ */
3
5
6 function AfterReport return boolean is
7 begin
8
9
10 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
11 return (TRUE);
12 end;
13
14 function BeforeReport return boolean is
15 begin
16
17 DECLARE
18 l_sort po_lookup_codes.displayed_field%type;
19 l_yes_no fnd_lookups.meaning%type;
20
21 BEGIN
22 /*SRW.USER_EXIT('FND SRWINIT');*/null;
23
24 P_CREATION_DATE_FROM1:=to_char(P_CREATION_DATE_FROM,'DD-MON-YY');
25 P_CREATION_DATE_TO1:=to_char(P_CREATION_DATE_TO,'DD-MON-YY');
26
27 IF P_ORDERBY is NOT NULL THEN
28
29 SELECT displayed_field
30 INTO l_sort
31 FROM po_lookup_codes
32 WHERE lookup_code = P_ORDERBY
33 AND lookup_type = 'SRS ORDER BY';
34
35 P_ORDERBY_DISP := l_sort;
36
37 ELSE
38
39 P_ORDERBY_DISP := '';
40
41 END IF;
42
43 IF P_FAILED_FUNDS is NULL THEN
44 P_FAILED_FUNDS := 'N';
45 END IF;
46
47 SELECT meaning
48 INTO l_yes_no
49 FROM fnd_lookups
50 WHERE lookup_type = 'YES_NO'
51 AND lookup_code = P_FAILED_FUNDS;
52
53 P_FAILED_FUNDS_DISP := l_yes_no;
54
55
56 null;
57
58
59 null;
60
61
62 RETURN (TRUE);
63
64 END;
65 return (TRUE);
66 end;
67
68 function select_failed_f return character is
69 begin
70 if P_failed_funds = 'Y' then
71 return(',pol.po_header_id, gl1.description Description1');
72 else
73 return(',pol.po_header_id,''''');
74 end if;
75 RETURN NULL; end;
76
77 function where_failed_f return character is
78 begin
79 if P_failed_funds = 'Y' then
80 return('and gcc.code_combination_id = pod.code_combination_id and pod.failed_funds_lookup_code = gl1.lookup_code and pod.failed_funds_lookup_code like ''F%''and gl1.lookup_type =''FUNDS_CHECK_RESULT_CODE''');
81 else
82 return('and gcc.code_combination_id = pod.code_combination_id');
83 end if;
84 RETURN NULL; end;
85
86 function from_failed_f return character is
87 begin
88 if P_failed_funds = 'Y' then
89 return(',gl_code_combinations gcc, gl_lookups gl1');
90 else
91 return(',gl_code_combinations gcc');
92 end if;
93 RETURN NULL; end;
94
95 function orderby_clauseFormula return VARCHAR2 is
96 begin
97
98 if upper(P_orderby) = 'PO NUMBER' then
99 return('decode(psp1.manual_po_num_type,''NUMERIC'',
100 decode(rtrim(poh.segment1,''0123456789''),null,
101 to_number(poh.segment1),-1),null)
102
103 , decode(psp1.manual_po_num_type,''NUMERIC'',
104 null,poh.segment1)');
105 elsif upper(P_orderby) = 'VENDOR' then
106 return('pov.vendor_name');
107 end if;
108
109 RETURN 'decode(psp1.manual_po_num_type,''NUMERIC'',null,poh.segment1), decode(psp1.manual_po_num_type,''NUMERIC'',decode(rtrim(poh.segment1, ''0123456789''),null,to_number(poh.segment1),-1),null)';
110 end;
111
112 procedure get_precision is
113 begin
114 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
115
119 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
116 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
117
118 else
120
121 else
122 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
123
124 else
125 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
126
127 else
128 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
129
130 else
131 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
132
133 else /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
134
135 end if; end if; end if; end if; end if; end if;
136 /*srw.set_attr(0,srw.attr);*/null;
137
138 end;
139
140 function get_dist_func_amount(shipment_type in varchar2, dist_quantity_ordered in number, c_dist_rls_qty in number, unit_price in number, rate in number, order_type_lookup_code in varchar2, dist_amount_ordered in number) return number is
141 begin
142
143
144
145 if (shipment_type = 'PLANNED') then
146
147 return(((dist_quantity_ordered - c_dist_rls_qty)
148 * unit_price) * nvl(rate,1));
149
150 else
151
152
153 IF (order_type_lookup_code IN ('RATE', 'FIXED PRICE')) THEN
154 RETURN (dist_amount_ordered * NVL(rate, 1));
155 ELSE
156 return((dist_quantity_ordered * unit_price) * nvl(rate,1));
157 END IF;
158
159
160 end if;
161
162 RETURN NULL; end get_dist_func_amount;
163
164 function get_dist_cur_amount(shipment_type in varchar2, dist_quantity_ordered in number, c_dist_rls_qty in number, unit_price in number, order_type_lookup_code in varchar2, dist_amount_ordered in number) return number is
165 begin
166
167
168
169 if (shipment_type = 'PLANNED') then
170
171 return(((dist_quantity_ordered - c_dist_rls_qty)
172 * unit_price));
173
174 else
175
176
177 IF (order_type_lookup_code IN ('RATE', 'FIXED PRICE')) THEN
178 RETURN dist_amount_ordered;
179 ELSE
180 return((dist_quantity_ordered * unit_price));
181 END IF;
182
183 end if;
184
185 RETURN NULL; end get_dist_cur_amount;
186
187 function get_ship_quantity(shipment_type in varchar2, ship_qty_ordered in number, c_ship_rls_qty in number) return number is
188 begin
189
190
191
192 if (shipment_type = 'PLANNED') then
193 return (ship_qty_ordered - c_ship_rls_qty);
194 else
195 return (ship_qty_ordered);
196 end if;
197
198
199 RETURN NULL; end get_ship_quantity;
200
201 function AfterPForm return boolean is
202 P_po_num_type po_system_parameters.manual_po_num_type%TYPE;
203 begin
204
205 /*srw.user_exit ('FND SRWINIT');*/null;
206
207 declare
208 l_sysparam_sob_id number;
209 begin
210
211 IF p_ca_set_of_books_id <> -1999
212 THEN
213 BEGIN
214 select decode(mrc_sob_type_code,'R','R','P')
215 into p_mrcsobtype
216 from gl_sets_of_books
217 where set_of_books_id = p_ca_set_of_books_id;
218 EXCEPTION
219 WHEN OTHERS THEN
220 p_mrcsobtype := 'P';
221 END;
222 ELSE
223 p_mrcsobtype := 'P';
224 END IF;
225
226 BEGIN
227 select set_of_books_id
228 into l_sysparam_sob_id
229 from financials_system_parameters;
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 FND_MESSAGE.set_name('SQLGL', 'MRC_SYSTEM_OPTIONS_NOT_FOUND');
233 FND_MESSAGE.set_token('MODULE', 'PO_POXPODDR_XMLP_PKG');
234 /*srw.message(2000, FND_MESSAGE.get);*/null;
235
236 raise;
237 WHEN OTHERS THEN
238 FND_MESSAGE.set_name('SQLGL', 'MRC_TABLE_ERROR');
239 FND_MESSAGE.set_token('MODULE', 'PO_POXPODDR_XMLP_PKG');
240 FND_MESSAGE.set_token('TABLE', 'FINANCIALS_SYSTEM_PARAMETERS');
241 /*srw.message('1000',fnd_message.get);*/null;
242
243 raise;
244 END;
245
246 lp_fin_system_parameters := 'financials_system_parameters';
247 lp_fin_system_parameters_all := 'financials_system_params_all';
248 lp_po_headers := 'po_headers';
249 lp_po_headers_all := 'po_headers_all';
250 lp_po_distributions := 'po_distributions';
251 lp_po_distributions_all := 'po_distributions_all';
252 lp_rcv_shipment_headers := 'rcv_shipment_headers';
253 lp_rcv_transactions := 'rcv_transactions';
254 lp_rcv_sub_ledger_details := 'rcv_sub_ledger_details';
255 lp_rcv_receiving_sub_ledger := 'rcv_receiving_sub_ledger';
256
257 END;
258
259
260
261 begin
262
263 SELECT psp.manual_po_num_type
264 into P_po_num_type
265 FROM po_system_parameters psp;
266
267 exception
268 when no_data_found then
269 P_po_num_type := 'ALPHANUMERIC';
270 end;
271
272 if P_po_num_from = P_po_num_to then
273 where_performance := ' AND poh.segment1 = :P_po_num_from ';
274
275 else
276
277 if (P_po_num_type = 'NUMERIC') then
278 where_performance := ' AND decode(rtrim(poh.segment1,''0123456789''),NULL,to_number(poh.segment1),-1) BETWEEN
279 decode(rtrim(nvl(:P_po_num_from,poh.segment1),''0123456789''),NULL,to_number(nvl(:P_po_num_from,poh.segment1)),-1) AND
280 decode(rtrim(nvl(:P_po_num_to,poh.segment1),''0123456789''),NULL,to_number(nvl(:P_po_num_to,poh.segment1)),-1) ';
281 elsif (P_po_num_type = 'ALPHANUMERIC') and
282 (P_po_num_from is not null) and
283 (P_po_num_to is not null) then
284 where_performance := ' AND poh.segment1 >= :P_po_num_from AND poh.segment1 <= :P_po_num_to ';
285 elsif (P_po_num_type = 'ALPHANUMERIC') and
286 (P_po_num_from is not null) and
287 (P_po_num_to is null) then
288 where_performance := ' AND poh.segment1 >= :P_po_num_from ';
289 elsif (P_po_num_type = 'ALPHANUMERIC') and
290 (P_po_num_from is null) and
291 (P_po_num_to is not null) then
292 where_performance := ' AND poh.segment1 <= :P_po_num_to ' ;
293 elsif (P_po_num_type = 'ALPHANUMERIC') and
294 (P_po_num_from is null) and
295 (P_po_num_to is null) then
296 where_performance := ' AND 1=1 ';
297 end if;
298 end if;
299
300
301
302
303
304
305 return (TRUE);
306 end;
307
308 --Functions to refer Oracle report placeholders--
309
310 Function C_amount_func_sub_round_p return number is
311 Begin
312 return C_amount_func_sub_round;
313 END;
314 Function C_amount_cur_round_p(C_DIST_AMT_CUR in number,PO_CURRENCY_PRECISION in number) return number is
315 Begin
316 C_amount_cur_round := round(C_DIST_AMT_CUR,PO_CURRENCY_PRECISION);
317 return C_amount_cur_round;
318 END;
319 Function C_amount_fun_round_p(C_DIST_AMT_FUNC in number,PO_CURRENCY_PRECISION in number) return number is
320 Begin
321 C_amount_fun_round := round(C_DIST_AMT_FUNC,PO_CURRENCY_PRECISION);
322 return C_amount_fun_round;
323 END;
324 Function C_amount_func_tot_round_p return number is
325 Begin
326 return C_amount_func_tot_round;
327 END;
328 END PO_POXPODDR_XMLP_PKG ;
329