[Home] [Help]
PACKAGE BODY: APPS.PO_POXRQSIN_XMLP_PKG
Source
1 PACKAGE BODY PO_POXRQSIN_XMLP_PKG AS
2 /* $Header: POXRQSINB.pls 120.1 2007/12/25 11:59:00 krreddy noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 function BeforeReport return boolean is
7 begin
8
9 declare
10 l_status po_lookup_codes.displayed_field%type ;
11 l_sort po_lookup_codes.displayed_field%type ;
12 begin
13 if P_status is not null then
14
15 select displayed_field
16 into l_status
17 from po_lookup_codes
18 where lookup_code = P_status
19 and lookup_type = 'AUTHORIZATION STATUS';
20
21 P_status_displayed := l_status ;
22
23 else
24
25 P_status_displayed := '' ;
26
27 end if;
28
29
30 if P_orderby is not null then
31
32 select displayed_field
33 into l_sort
34 from po_lookup_codes
35 where lookup_code = P_orderby
36 and lookup_type = 'SRS ORDER BY';
37
38 P_orderby_displayed := l_sort ;
39
40 else
41
42 P_orderby_displayed := '' ;
43
44 end if;
45 FORMAT_MASK := PO_COMMON_XMLP_PKG.GET_PRECISION(P_QTY_PRECISION);
46 end;
47 DECLARE
48 l_message1 po_lookup_codes.description%TYPE ;
49 Call_variable boolean;
50 l_INDUSTRY Varchar2(100);
51 l_ORACLE_SCHEMA Varchar2(100);
52 Begin
53 /*SRW.USER_EXIT('FND SRWINIT');*/null;
54
55 /*srw.user_exit('FND INSTALLATION OUTPUT_TYPE="STATUS"
56 OUTPUT_FIELD=":P_OE_STATUS"
57 APPS="ONT"') ;*/null;
58
59 Call_variable:= fnd_installation.GET_APP_INFO('PO',P_OE_STATUS,l_INDUSTRY,l_ORACLE_SCHEMA);
60
61
62 if P_OE_STATUS <> 'I' then
63 select description
64 into l_message1
65 from po_lookup_codes
66 where lookup_type = 'SRW MESSAGE'
67 and lookup_code = 'CANNOT RUN PROGRAM' ;
68
69 /*srw.message(1,l_message1) ;*/null;
70
71 return false;
72 end if;
73
74 EXCEPTION WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
75 select description
76 into l_message1
77 from po_lookup_codes
78 where lookup_type = 'SRW MESSAGE'
79 and lookup_code = 'FAILURE TO GET STATUS' ;
80
81 /*srw.message(1,l_message1) ;*/null;
82
83 return false ;
84
85 End;
86
87 BEGIN
88 if (get_p_struct_num <> TRUE )
89 then /*SRW.MESSAGE('1','P Struct Num Init failed');*/null;
90
91 end if;
92
93 null;
94
95
96 null;
97 END; return (TRUE);
98 end;
99
100 function AfterReport return boolean is
101 begin
102
103 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
104 return (TRUE);
105 end;
106
107 procedure get_precision is
108 begin
109 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
110
111 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
112
113 else
114 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
115
116 else
117 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
118
119 else
120 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
121
122 else
123 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
124
125 else
126 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
127
128 else /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
129
130 end if; end if; end if; end if; end if; end if;
131 /*srw.set_attr(0,srw.attr);*/null;
132
133 end;
134
135 function orderby_clauseFormula return VARCHAR2 is
136 begin
137
138 if upper(P_orderby) = 'REQUESTOR' then
139 return ('3');
140 elsif upper(P_orderby) = 'SUBINVENTORY' then
141 return ('4');
142 elsif upper(P_orderby) = 'CREATION DATE' then
143 return ('2');
144 end if;
145 RETURN '3'; end;
146
147 function get_p_struct_num return boolean is
148
149 l_p_struct_num number;
150
151 begin
152 select structure_id
153 into l_p_struct_num
154 from mtl_default_sets_view
155 where functional_area_id = 2 ;
156
157 P_STRUCT_NUM := l_p_struct_num ;
158
159 return(TRUE) ;
160
161 RETURN NULL; exception
162 when others then return(FALSE) ;
163 end;
164
165 function C_backorderedFormula return VARCHAR2 is
166 begin
167
168 if (P_OE_STATUS = 'I') then
169 return ('nvl(wdd.requested_quantity,0)') ;
170 else return('null');
171 end if;
172 RETURN NULL; end;
173
174 function C_fromFormula return VARCHAR2 is
175 begin
176
177 if (P_OE_STATUS = 'I') then return(', oe_order_lines_all oel
178 , oe_order_headers_all oeh
179 , wsh_delivery_details wdd') ;
180 end if;
181
182 RETURN NULL; end;
183
184 function C_whereFormula return VARCHAR2 is
185 begin
186
187 if (P_OE_STATUS = 'I') then return ('AND oel.orig_sys_line_ref = to_char(prl.line_num)
188 AND oeh.orig_sys_document_ref = prh.segment1
189 AND oeh.order_source_id = psp1.order_source_id
190 AND oeh.header_id = oel.header_id
191 AND oel.line_id = wdd.source_line_id(+)
192 AND wdd.source_code(+) = ''OE'' ');
193
194 else return('and 1=1') ;
195 end if;
196
197 RETURN NULL; end;
198
199 function C_interface_whereFormula return VARCHAR2 is
200 begin
201
202
203
204 if (P_OE_STATUS = 'I') then return ('AND exists
205 (select 1 from oe_headers_iface_all OEI
206 WHERE OEI.orig_sys_document_ref = PRH.requisition_header_id
207 AND OEI.order_source_id = 10)');
208 else return('AND 1=2');
209 end if;
210 RETURN NULL; end;
211
212 function C_requiredFormula return VARCHAR2 is
213 begin
214
215 return ('prl.quantity - nvl(prl.quantity_cancelled, 0)');
216 end;
217
218 function c_ship_amountformula(required in number, unit_price in number) return number is
219 begin
220
221 return (required * unit_price);
222
223
224 end;
225
226 function C_ship_qtyFormula return VARCHAR2 is
227 begin
228
229 if (P_OE_STATUS = 'I') then
230 return('nvl(oel.shipped_quantity, 0)');
231 else return ('to_number(null)');
232 end if;
233
234 RETURN NULL; end;
235
236 function P_WHERE_ITEMValidTrigger return boolean is
237 begin
238
239 return (TRUE);
240 end;
241
242 function AfterPForm return boolean is
243 begin
244
245 /*srw.user_exit('FND SRWINIT');*/null;
246
247 begin
248
249
250 P_CREATION_DATE_FROM1 := to_Char(P_CREATION_DATE_FROM,'dd-mon-yy');
251 P_CREATION_DATE_TO1 := to_Char(P_CREATION_DATE_TO,'dd-mon-yy');
252
253 SELECT psp.manual_req_num_type manual_req_num_type
254 into P_req_num_type
255 FROM po_system_parameters psp;
256
257 exception
258 when no_data_found then
259 P_req_num_type := 'ALPHANUMERIC';
260 end;
261
262
263 If P_req_number_from = P_req_number_to THEN
264 P_single_po_print := 1;
265 END IF;
266
267 if ( P_single_po_print = 1 ) then
268 P_WHERE_QUERY := ' prh.segment1 = :P_req_number_from ';
269
270 else
271
272 IF ( P_req_num_type = 'NUMERIC' ) THEN
273
274 P_WHERE_QUERY := ' decode(rtrim(prh.segment1,''0123456789''),NULL,to_number(prh.segment1),-1)
275 BETWEEN decode(rtrim(nvl(nvl(:P_req_number_from,NULL),prh.segment1),''0123456789''),
276 NULL, to_number(nvl(nvl(:P_req_number_from ,NULL)
277 ,prh.segment1)),-1) AND decode(rtrim(nvl(nvl(:P_req_number_to ,NULL)
278 ,prh.segment1),''0123456789''),NULL, to_number(nvl(nvl(:P_req_number_to,NULL)
279 ,prh.segment1)),-1)' ;
280
281 ELSIF (P_REQ_NUM_TYPE = 'ALPHANUMERIC' ) THEN
282
283 IF (P_req_number_from IS NOT NULL AND P_req_number_to IS NOT NULL) THEN
284 P_WHERE_QUERY := ' prh.segment1 >= :P_req_number_from AND prh.segment1 <= :P_req_number_to ';
285
286 ELSIF ( P_req_number_from IS NOT NULL AND P_req_number_to IS NULL ) THEN
287 P_WHERE_QUERY := 'prh.segment1 >= :P_req_number_from ' ;
288
289 ELSIF ( P_req_number_from IS NULL AND P_req_number_to IS NOT NULL ) THEN
290 P_WHERE_QUERY := 'prh.segment1 <= :P_req_number_to ' ;
291
292 ELSE
293 P_WHERE_QUERY := ' 1 = 1 ' ;
294 END IF;
295 END IF;
296 END iF;
297 return (TRUE);
298 return (TRUE);
299 end;
300
301 --Functions to refer Oracle report placeholders--
302
303 END PO_POXRQSIN_XMLP_PKG ;
304