1 PACKAGE BODY POR_RCV_POST_QUERY_PKG AS
2 /* $Header: PORRCVQB.pls 115.3 2003/01/30 00:30:41 jizhang noship $ */
3
4 procedure getDetails(pItemId IN NUMBER,
5 pOrgId IN NUMBER,
6 pRequestorId IN NUMBER,
7 pDistributionId IN NUMBER,
8 pOrderType IN VARCHAR2,
9 pReqLineId IN OUT NOCOPY NUMBER,
10 pItemNumber OUT NOCOPY VARCHAR2,
11 pRequestorName OUT NOCOPY VARCHAR2,
12 pReqNum OUT NOCOPY VARCHAR2,
13 pReqHeaderId OUT NOCOPY NUMBER,
14 pSPN OUT NOCOPY VARCHAR2,
15 pDistributionNum OUT NOCOPY NUMBER) IS
16 BEGIN
17
18 if ( pItemId > 0 AND pOrgId > 0 ) then
19 getItemNumber(pItemId, pOrgId, pItemNumber);
20 end if;
21
22 if ( pRequestorId > 0 ) then
23 getRequestorName(pRequestorId, pRequestorName);
24 end if;
25
26 if ( pOrderType = 'REQ' ) then
27 -- for Internal Requisition
28 getReqInfoREQ(pReqLineId, pReqNum, pReqHeaderId);
29 else
30 -- for PO
31 getReqInfo(pDistributionId, pReqNum, pReqHeaderId, pReqLineId);
32 getSPN(pDistributionId, pSPN, pDistributionNum);
33 end if;
34
35 END;
36
37 -- convenience function to get Requisition Number
38 function getReqNum(pOrderType IN VARCHAR2,
39 pReqLineId IN Number,
40 pDistributionId IN Number) RETURN VARCHAR2 IS
41 xReqNum VARCHAR2(200);
42 xReqHeaderId Number;
43 xReqLineId Number;
44 BEGIN
45 if ( pOrderType = 'REQ' ) then
46 getReqInfoREQ(pReqLineId, xReqNum, xReqHeaderId);
47 else
48 getReqInfo(pDistributionId, xReqNum, xReqHeaderId, xReqLineId);
49 end if;
50
51 return xReqNum;
52 END getReqNum;
53
54
55 -- get inventory item number
56 procedure getItemNumber(pItemId IN NUMBER,
57 pOrgId IN NUMBER,
58 pItemNum OUT NOCOPY VARCHAR2) IS
59 BEGIN
60
61 begin
62 select concatenated_segments
63 into pItemNum
64 from mtl_system_items_kfv
65 where inventory_item_id = pItemId
66 and organization_id = pOrgId;
67 exception
68 when no_data_found then
69 null;
70 end;
71
72 END;
73
74 -- get requestor name
75 procedure getRequestorName(pRequestorId IN NUMBER,
76 pName OUT NOCOPY VARCHAR2) IS
77 BEGIN
78 begin
79 select full_name
80 into pName
81 from per_people_f
82 where person_id = pRequestorId
83 and nvl(effective_start_date, sysdate) <= sysdate
84 and nvl(effective_end_date, sysdate) >= sysdate;
85 exception
86 when no_data_found then
87 null;
88 end;
89
90 END;
91
92 -- Internal Req: get requisition number
93 procedure getReqInfoREQ(pReqLineId IN NUMBER,
94 pReqNum OUT NOCOPY VARCHAR2,
95 pReqHeaderId OUT NOCOPY NUMBER) IS
96 BEGIN
97 begin
98 select a.segment1, a.requisition_header_id
99 into pReqNum, pReqHeaderId
100 from po_requisition_headers a,
101 po_requisition_lines b
102 where a.requisition_header_id = b.requisition_header_id
103 and b.requisition_line_id = pReqLineId;
104 exception
105 when no_data_found then
106 null;
107 end;
108
109
110 END;
111
112
113 -- PO: get requisition number, req header id and line id
114 procedure getReqInfo(pDistributionId IN NUMBER,
115 pReqNum OUT NOCOPY VARCHAR2,
116 pReqHeaderId OUT NOCOPY NUMBER,
117 pReqLineId OUT NOCOPY NUMBER) IS
118 BEGIN
119 begin
120 select prh.segment1,prl.requisition_line_id,prh.requisition_header_id
121 into pReqNum, pReqLineId, pReqHeaderId
122 from po_requisition_headers prh,
123 po_requisition_lines prl,
124 po_distributions pod,
125 po_req_distributions pord
126 where pod.po_distribution_id= pDistributionId
127 and pord.distribution_id = pod.req_distribution_id
128 and pord.requisition_line_id = prl.requisition_line_id
129 and prl.requisition_header_id = prh.requisition_header_id;
130 exception
131 when no_data_found then
132 null;
133 end;
134
135 END;
136
137 -- PO: get supplier item number and distribution number
138 procedure getSPN(pDistributionId IN NUMBER,
139 pSPN OUT NOCOPY VARCHAR2,
140 pDistributionNum OUT NOCOPY NUMBER) IS
141 BEGIN
142 begin
143 select pol.vendor_product_num, pod.distribution_num
144 into pSPN, pDistributionNum
145 from po_lines pol,
146 po_distributions pod
147 where pod.po_line_id = pol.po_line_id
148 and pod.po_distribution_id = pDistributionId;
149 exception
150 when no_data_found then
151 null;
152 end;
153
154 END;
155
156 -- convenience function to get Order Type
157 function getOrderType(pOrderTypeCode IN VARCHAR2) RETURN VARCHAR2 IS
158 xOrderType VARCHAR2(200);
159 BEGIN
160 select meaning
161 into xOrderType
162 from fnd_lookup_values
163 where lookup_type = 'POR_RCV_ORDER_TYPE'
164 and lookup_code = pOrderTypeCode
165 and language = 'US';
166
167 return xOrderType;
168 END getOrderType;
169
170 function getOrderNumber(pReqHeaderId IN NUMBER) RETURN VARCHAR2 IS
171 xOrderNumber VARCHAR2(50);
172 BEGIN
173
174 SELECT ORDER_NUM
175 INTO xOrderNumber
176 FROM (
177 SELECT DISTINCT PH.SEGMENT1 || DECODE(PR.RELEASE_NUM, NULL, '', '-' || PR.RELEASE_NUM) ORDER_NUM
178 FROM PO_REQUISITION_LINES_ALL PRL,
179 PO_LINE_LOCATIONS_ALL PLL,
180 PO_HEADERS_ALL PH,
181 PO_RELEASES_ALL PR
182 WHERE PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
183 PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
184 PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND
185 PRL.REQUISITION_HEADER_ID = pReqHeaderId
186 UNION ALL
187 SELECT DISTINCT to_char(OSH.ORDER_NUMBER) ORDER_NUM
188 FROM PO_REQUISITION_HEADERS_ALL PORH,
189 PO_REQUISITION_LINES_ALL PORL,
190 OE_ORDER_HEADERS OSH,
191 PO_SYSTEM_PARAMETERS POSP
192 WHERE PORL.SOURCE_TYPE_CODE = 'INVENTORY' AND
193 OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID AND
194 OSH.ORIG_SYS_DOCUMENT_REF = PORH.SEGMENT1 AND
195 PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID AND
196 PORH.REQUISITION_HEADER_ID = pReqHeaderId
197 );
198
199 return xOrderNumber;
200
201 exception
202 when too_many_rows then
203 return fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE');
204 when others then
205 return null;
206
207 END getOrderNumber;
208
209 function getSupplier(pReqHeaderId IN NUMBER) RETURN VARCHAR2 IS
210 xSupplier VARCHAR2(240);
211 BEGIN
212
213 SELECT SUPPLIER
214 INTO xSupplier
215 FROM (
216 SELECT DISTINCT PV.VENDOR_NAME SUPPLIER
217 FROM PO_REQUISITION_LINES_ALL PRL,
218 PO_LINE_LOCATIONS_ALL PLL,
219 PO_HEADERS_ALL PH,
220 PO_VENDORS PV
221 WHERE PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
222 PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
223 PH.VENDOR_ID = PV.VENDOR_ID AND
224 PRL.REQUISITION_HEADER_ID = pReqHeaderId
225 UNION ALL
226 SELECT DISTINCT HAOU.NAME SUPPLIER
227 FROM PO_REQUISITION_LINES_ALL PRL,
228 HR_ALL_ORGANIZATION_UNITS_TL HAOU
229 WHERE PRL.SOURCE_TYPE_CODE = 'INVENTORY' AND
230 HAOU.ORGANIZATION_ID = PRL.SOURCE_ORGANIZATION_ID AND
231 HAOU.LANGUAGE = USERENV('LANG') AND
232 PRL.REQUISITION_HEADER_ID = pReqHeaderId
233 );
234
235 return xSupplier;
236
237 exception
238 when too_many_rows then
239 return fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE');
240 when others then
241 return null;
242 END getSupplier;
243
244
245 END POR_RCV_POST_QUERY_PKG;
[Home] [Help]