[Home] [Help]
PACKAGE BODY: APPS.PO_POXRQSDD_XMLP_PKG
Source
1 PACKAGE BODY PO_POXRQSDD_XMLP_PKG AS
2 /* $Header: POXRQSDDB.pls 120.2 2008/01/08 07:16:40 dwkrishn noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 function BeforeReport return boolean is
7 begin
8
9 declare
10 l_sort po_lookup_codes.displayed_field%type ;
11 begin
12 FORMAT_MASK := PO_COMMON_XMLP_PKG.GET_PRECISION(P_qty_precision);
13 if P_orderby is not null then
14
15 select displayed_field
16 into l_sort
17 from po_lookup_codes
18 where lookup_code = P_orderby
19 and lookup_type = 'SRS ORDER BY';
20
21 P_orderby_displayed := l_sort ;
22
23 else
24
25 P_orderby_displayed := '' ;
26
27 end if;
28
29
30 end;
31 DECLARE
32 l_message1 po_lookup_codes.description%TYPE ;
33 l_INDUSTRY varchar2(100);
34 l_Oracle_schema varchar2(100);
35 L_boolean_var boolean;
36 Begin
37 /*SRW.USER_EXIT('FND SRWINIT');*/null;
38
39 /*srw.user_exit('FND INSTALLATION OUTPUT_TYPE="STATUS"
40 OUTPUT_FIELD=":P_OE_STATUS"
41 APPS="ONT"') ;*/null;
42
43 L_boolean_var:=fnd_installation.GET_APP_INFO('PO',P_OE_STATUS,l_INDUSTRY,l_Oracle_schema);
44
45 if P_OE_STATUS <> 'I' then
46 select description
47 into l_message1
48 from po_lookup_codes
49 where lookup_type = 'SRW MESSAGE'
50 and lookup_code = 'CANNOT RUN PROGRAM' ;
51
52 /*srw.message(1,l_message1) ;*/null;
53
54 return false;
55 end if;
56
57 EXCEPTION WHEN USER_EXIT_FAILURE /*SRW.USER_EXIT_FAILURE */THEN
58 select description
59 into l_message1
60 from po_lookup_codes
61 where lookup_type = 'SRW MESSAGE'
62 and lookup_code = 'FAILURE TO GET STATUS' ;
63
64 /*srw.message(1,l_message1) ;*/null;
65
66 return false ;
67
68 End;
69 BEGIN
70
71 null;
72 RETURN TRUE;
73 END; return (TRUE);
74 end;
75
76 procedure get_precision is
77 begin
78 /*srw.attr.mask := SRW.FORMATMASK_ATTR;*/null;
79
80 if P_qty_precision = 0 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0';*/null;
81
82 else
83 if P_qty_precision = 1 then /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.0';*/null;
84
85 else
86 if P_qty_precision = 3 then /*srw.attr.formatmask := '-NN,NNN,NNN,NN0.000';*/null;
87
88 else
89 if P_qty_precision = 4 then /*srw.attr.formatmask := '-N,NNN,NNN,NN0.0000';*/null;
90
91 else
92 if P_qty_precision = 5 then /*srw.attr.formatmask := '-NNN,NNN,NN0.00000';*/null;
93
94 else
95 if P_qty_precision = 6 then /*srw.attr.formatmask := '-NN,NNN,NN0.000000';*/null;
96
97 else /*srw.attr.formatmask := '-NNN,NNN,NNN,NN0.00';*/null;
98
99 end if; end if; end if; end if; end if; end if;
100 /*srw.set_attr(0,srw.attr);*/null;
101
102 end;
103
104 function orderby_clauseFormula return VARCHAR2 is
105 begin
106
107 if upper(P_orderby) = 'CREATION DATE' then
108 return('prl.creation_date');
109 elsif upper(P_orderby) = 'REQUESTOR' then
110 return('papf.full_name');
111 elsif upper(P_orderby) = 'SUBINVENTORY' then
112 return('prl.source_subinventory');
113 end if;
114 RETURN 'prl.creation_date'; end;
115
116 function C_shipped_qtyFormula return VARCHAR2 is
117 begin
118
119 if (P_OE_STATUS = 'I') then return('oel.shipped_quantity') ;
120 end if;
121
122 RETURN NULL; end;
123
124 function C_selling_priceFormula return VARCHAR2 is
125 begin
126
127 if (P_OE_STATUS = 'I') then return('oel.unit_selling_price') ;
128 end if;
129 RETURN NULL; end;
130
131 function C_fromFormula return VARCHAR2 is
132 begin
133
134 if (P_OE_STATUS = 'I') then return(', oe_order_lines_all oel
135 , oe_order_headers_all oeh') ;
136 end if;
137
138 RETURN NULL; end;
139
140 function C_whereFormula return VARCHAR2 is
141 begin
142
143 if (P_OE_STATUS = 'I') then return('AND oel.orig_sys_line_ref = to_char(prl.line_num)
144 AND oeh.orig_sys_document_ref = prh.segment1
145 AND oeh.order_source_id = psp1.order_source_id
146 AND oel.header_id = oeh.header_id
147 AND oel.shipped_quantity is not null');
148 end if;
149
150 RETURN NULL; end;
151
152 function c_get_shipped_quantity (Quantity_delivered in number, unit_price in number, Line in number, Req_number in varchar2, p_Order_Source_id in number) return number is
153 begin
154 select round(sum(oel.shipped_quantity),P_qty_precision) Quantity_Shipped
155 , round((nvl(sum(oel.shipped_quantity),0)
156 - nvl(Quantity_delivered,0)),P_qty_precision) Quantity_Variance
157 , sum(nvl(oel.unit_selling_price,0) * nvl(oel.shipped_quantity,0)) -
158 (nvl(unit_price,0) * nvl(Quantity_delivered,0)) Cost_Variance
159 into C_Quantity_Shipped,
160 C_Quantity_Variance,
161 C_Cost_Variance
162 from oe_order_lines_all oel,
163 oe_order_headers_all oeh
164 where oel.orig_sys_line_ref = to_char(Line)
165 AND oeh.orig_sys_document_ref = Req_number
166 AND oeh.order_source_id = p_Order_Source_id
167 AND oel.header_id = oeh.header_id
168 AND oel.shipped_quantity is not null
169 group by oeh.orig_sys_document_ref,oel.orig_sys_line_ref;
170
171 Return(1);
172 exception
173 when no_data_found then
174 Return(1);
175
176 end;
177
178 function get_shipped_quantity(orig_line_num in varchar2,orig_header_num varchar2,psp_order_source_id number)return number is
179 sum_shipped_quantity number;
180 BEGIN
181 select sum(oel.shipped_quantity)
182 into sum_shipped_quantity
183 from oe_order_lines_all oel,
184 oe_order_headers_all oeh
185 where oel.orig_sys_line_ref = orig_line_num
186 AND oeh.orig_sys_document_ref = orig_header_num
187 AND oeh.order_source_id = psp_order_source_id
188 AND oel.header_id = oeh.header_id
189 AND oel.shipped_quantity is not null;
190
191 Return sum_shipped_quantity;
192 END;
193
194 function AfterPForm return boolean is
195 begin
196
197 /*srw.user_exit('FND SRWINIT');*/null;
198
199
200 begin
201 SELECT psp.manual_req_num_type manual_req_num_type
202 into P_req_num_type
203 FROM po_system_parameters psp;
204
205 exception
206 when no_data_found then
207 P_req_num_type := 'ALPHANUMERIC';
208 end;
209
210
211 If P_req_number_from = P_req_number_to THEN
212 P_single_po_print := 1;
213 END IF;
214
215 if ( P_single_po_print = 1 ) then
216 P_WHERE_QUERY := ' prh.segment1 = :P_req_number_from ';
217
218 else
219
220 IF ( P_req_num_type = 'NUMERIC' ) THEN
221
222 P_WHERE_QUERY := ' decode(rtrim(prh.segment1,''0123456789''),NULL,to_number(prh.segment1),-1)
223 BETWEEN decode(rtrim(nvl(nvl(:P_req_number_from,NULL),prh.segment1),''0123456789''),
224 NULL, to_number(nvl(nvl(:P_req_number_from ,NULL)
225 ,prh.segment1)),-1) AND decode(rtrim(nvl(nvl(:P_req_number_to ,NULL)
226 ,prh.segment1),''0123456789''),NULL, to_number(nvl(nvl(:P_req_number_to,NULL)
227 ,prh.segment1)),-1)' ;
228
229 ELSIF (P_REQ_NUM_TYPE = 'ALPHANUMERIC' ) THEN
230
231 IF (P_req_number_from IS NOT NULL AND P_req_number_to IS NOT NULL) THEN
232 P_WHERE_QUERY := ' prh.segment1 >= :P_req_number_from AND prh.segment1 <= :P_req_number_to ';
233
234 ELSIF ( P_req_number_from IS NOT NULL AND P_req_number_to IS NULL ) THEN
235 P_WHERE_QUERY := 'prh.segment1 >= :P_req_number_from ' ;
236
237 ELSIF ( P_req_number_from IS NULL AND P_req_number_to IS NOT NULL ) THEN
238 P_WHERE_QUERY := 'prh.segment1 <= :P_req_number_to ' ;
239
240 ELSE
241 P_WHERE_QUERY := ' 1 = 1 ' ;
242 END IF;
243 END IF;
244 END iF;
245 return (TRUE);
246
247 end;
248
249 function AfterReport return boolean is
250 begin
251 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
252
253 return (TRUE);
254 end;
255
256 --Functions to refer Oracle report placeholders--
257
258 Function C_quantity_shipped_p return number is
259 Begin
260 return C_quantity_shipped;
261 END;
262 Function C_Quantity_Variance_p return number is
263 Begin
264 return C_Quantity_Variance;
265 END;
266 Function C_Cost_variance_p return number is
267 Begin
268 return C_Cost_variance;
269 END;
270 END PO_POXRQSDD_XMLP_PKG ;
271