1 PACKAGE BODY POR_RCV_POST_QUERY_PKG AS
2 /* $Header: PORRCVQB.pls 120.2 2012/01/03 05:16:07 mzhussai ship $ */
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,pFederalFlag IN VARCHAR2) RETURN VARCHAR2 IS
171 xOrderNumber VARCHAR2(50);
172 BEGIN
173
174 IF pFederalFlag = 'Y' THEN
175 select distinct CLM_DOCUMENT_NUMBER into xOrderNumber from PO_CLMREQ_LINE_AWD_REFS_V where requisition_header_id=pReqHeaderId ;
176 ELSE
177 SELECT ORDER_NUM
178 INTO xOrderNumber
179 FROM (
180 SELECT DISTINCT PH.SEGMENT1 || DECODE(PR.RELEASE_NUM, NULL, '', '-' || PR.RELEASE_NUM) ORDER_NUM
181 FROM PO_REQUISITION_LINES_ALL PRL,
182 PO_LINE_LOCATIONS_ALL PLL,
183 PO_HEADERS_ALL PH,
184 PO_RELEASES_ALL PR
185 WHERE PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
186 PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
187 PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND
188 PRL.REQUISITION_HEADER_ID = pReqHeaderId
189 UNION ALL
190 SELECT DISTINCT TO_CHAR(oe_order_import_interop_pub.Get_Order_Number(POSP.order_source_id,PORL.requisition_header_id,PORL.requisition_line_id)) ORDER_NUM
191 FROM PO_REQUISITION_HEADERS_ALL PORH,
192 PO_REQUISITION_LINES_ALL PORL,
193 PO_SYSTEM_PARAMETERS POSP
194 WHERE PORL.SOURCE_TYPE_CODE = 'INVENTORY' AND
195 PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID AND
196 PORH.REQUISITION_HEADER_ID = pReqHeaderId
197 );
198 END IF;
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,pFederalFlag IN VARCHAR2) RETURN VARCHAR2 IS
210 xSupplier VARCHAR2(240);
211 BEGIN
212
213 IF pFederalFlag = 'Y' THEN
214 select SUPPLIER into xSupplier from
215 (select DISTINCT PV.VENDOR_NAME SUPPLIER FROM PO_CLMREQ_LINE_AWD_REFS_V PH, PO_VENDORS PV
216 where PH.VENDOR_ID = PV.VENDOR_ID AND PH.requisition_header_id=pReqHeaderId );
217 ELSE
218 SELECT SUPPLIER
219 INTO xSupplier
220 FROM (
221 SELECT DISTINCT PV.VENDOR_NAME SUPPLIER
222 FROM PO_REQUISITION_LINES_ALL PRL,
223 PO_LINE_LOCATIONS_ALL PLL,
224 PO_HEADERS_ALL PH,
225 PO_VENDORS PV
226 WHERE PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
227 PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
228 PH.VENDOR_ID = PV.VENDOR_ID AND
229 PRL.REQUISITION_HEADER_ID = pReqHeaderId
230 UNION ALL
231 SELECT DISTINCT HAOU.NAME SUPPLIER
232 FROM PO_REQUISITION_LINES_ALL PRL,
233 HR_ALL_ORGANIZATION_UNITS_TL HAOU
234 WHERE PRL.SOURCE_TYPE_CODE = 'INVENTORY' AND
235 HAOU.ORGANIZATION_ID = PRL.SOURCE_ORGANIZATION_ID AND
236 HAOU.LANGUAGE = USERENV('LANG') AND
237 PRL.REQUISITION_HEADER_ID = pReqHeaderId
238 );
239 END IF;
240 return xSupplier;
241
242 exception
243 when too_many_rows then
244 return fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE');
245 when others then
246 return null;
247 END getSupplier;
248
249
250 END POR_RCV_POST_QUERY_PKG;
[Home] [Help]