eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PO_XML_FPDSNG_PKG

Source


1 PACKAGE BODY PO_XML_FPDSNG_PKG AS
2 /* $Header: PODTFPDB.pls 120.2.12020000.2 2013/02/10 19:01:46 vegajula ship $ */
3 
4   FUNCTION get_location_id(t_orgid NUMBER) RETURN NUMBER IS
5    l_legal_entity_id NUMBER;
6    x_return_status VARCHAR2(500);
7    x_msg_count NUMBER;
8    x_msg_data VARCHAR2(500);
9    x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
10    l_location_id NUMBER;
11   Begin
12    l_legal_entity_id := XLE_UTILITIES_GRP.GET_DefaultLegalContext_OU(t_orgid);
13    XLE_UTILITIES_GRP.Get_LegalEntity_Info(x_return_status, x_msg_count, x_msg_data, null, l_legal_entity_id, x_legalentity_info);
14    l_location_id := x_legalentity_info.location_id;
15 
16   RETURN (l_location_id);
17   END get_location_id;
18 
19   FUNCTION get_location_name(t_orgid NUMBER) RETURN VARCHAR2 IS
20    l_legal_entity_id NUMBER;
21    x_return_status VARCHAR2(500);
22    x_msg_count NUMBER;
23    x_msg_data VARCHAR2(500);
24    x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
25    l_location_name VARCHAR2(500);
26   Begin
27    l_legal_entity_id := XLE_UTILITIES_GRP.GET_DefaultLegalContext_OU(t_orgid);
28    XLE_UTILITIES_GRP.Get_LegalEntity_Info(x_return_status, x_msg_count, x_msg_data, null, l_legal_entity_id, x_legalentity_info);
29    l_location_name := x_legalentity_info.name;
30 
31   RETURN (l_location_name);
32   END get_location_name;
33 
34   FUNCTION get_header_amount_ordered(p_header_id NUMBER, p_draft_id NUMBER) RETURN NUMBER IS
35    order_amount NUMBER;
36   Begin
37    order_amount := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(PO_CORE_S.g_doc_level_HEADER, p_header_id,PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION , NULL, p_draft_id);
38 
39   RETURN (order_amount);
40   END get_header_amount_ordered;
41 
42   FUNCTION get_line_amount_ordered(p_line_id NUMBER, p_draft_id NUMBER) RETURN NUMBER IS
43    order_amount NUMBER;
44   Begin
45    order_amount := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(PO_CORE_S.g_doc_level_LINE, p_line_id,PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION , NULL, p_draft_id);
46 
47   RETURN (order_amount);
48   END get_line_amount_ordered;
49 
50   FUNCTION get_without_opt_amount_ordered(p_header_id NUMBER, p_draft_id NUMBER) RETURN NUMBER IS
51    order_amount NUMBER;
52   Begin
53    order_amount := PO_DOCUMENT_TOTALS_PVT.getAmountOrderedExclOptions(PO_CORE_S.g_doc_level_HEADER, p_header_id, PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION , NULL, p_draft_id);
54 
55   RETURN (order_amount);
56   END get_without_opt_amount_ordered;
57 
58   function get_uda_attr_desc_sql(p_doc_type varchar2, p_pk1_value number,
59                                 p_pk2_value number,
60                                 p_data_type varchar2,
61                                 p_appl_col_name varchar2,
62                                 p_end_user_col_name varchar2,
63                                 p_attr_group varchar2, p_attr_id number)
64                                 return varchar2 is
65 
66   p_application_id  number := 201;
67   p_attr_group_type varchar2(200);
68   p_object_name varchar2(200);
69   p_pk1_column_name   varchar2(200);
70   p_pk2_column_name  varchar2(200) :=   'DRAFT_ID';
71   l_sql varchar2(1000);
72   begin
73   if p_doc_type = 'SHIPMENT' then
74     p_attr_group_type :=  'PO_SHIPMENTS_EXT_ATTRS';
75     p_object_name := 'PO_LINE_LOCATIONS_ALL_EXT_B';
76     p_pk1_column_name := 'LINE_LOCATION_ID';
77   elsif p_doc_type = 'LINES' then
78     p_attr_group_type :=  'PO_LINE_EXT_ATTRS';
79     p_object_name := 'PO_LINES_ALL_EXT_B';
80     p_pk1_column_name := 'PO_LINE_ID';
81   elsif p_doc_type = 'HEADER' then
82     p_attr_group_type :=   'PO_HEADER_EXT_ATTRS';
83     p_object_name := 'PO_HEADERS_ALL_EXT_B';
84     p_pk1_column_name := 'PO_HEADER_ID';
85   end if;
86 
87   if (p_data_type = 'C' or p_data_type = 'A' ) then
88     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
89                  p_application_id || ',' || ' null,  ' ||
90                  p_appl_col_name|| ', null , ''' ||
91                  p_end_user_col_name|| ''',''' ||
92                  p_attr_group_type || ''',''' ||
93                  P_ATTR_GROUP|| ''',' ||
94                  p_attr_id || ',''' ||
95                  p_object_name || ''',''' ||
96                  p_pk1_column_name || ''',' ||
97                  p_pk1_value || ',''' ||
98                  p_pk2_column_name || ''',' ||
99                  p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
100   elsif  p_data_type = 'N' then
101     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
102                  p_application_id || ',' || ' null, null, ' ||
103                  p_appl_col_name|| ', ''' ||
104                  p_end_user_col_name|| ''',''' ||
105                  p_attr_group_type || ''',''' ||
106                  p_attr_group || ''',' ||
107                  p_attr_id || ',''' ||
108                  p_object_name || ''',''' ||
109                  p_pk1_column_name || ''',' ||
110                  p_pk1_value || ',''' ||
111                  p_pk2_column_name || ''',' ||
112                  p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
113   elsif (p_data_type = 'X' or p_data_type = 'Y') then
114      l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
115                  p_application_id || ',' || p_appl_col_name ||
116                  ' , null, null, ''' ||
117                  p_end_user_col_name|| ''',''' ||
118                  p_attr_group_type || ''',''' ||
119                  p_attr_group || ''',' ||
120                  p_attr_id || ',''' ||
121                  p_object_name || ''',''' ||
122                  p_pk1_column_name || ''',' ||
123                  p_pk1_value || ',''' ||
124                  p_pk2_column_name || ''',' ||
125                  p_pk2_value || ') as ' || p_end_user_col_name || '_DESC ' ;
126   end if;
127   return(l_sql);
128   end get_uda_attr_desc_sql;
129 
130   FUNCTION GET_UDA_HEADER_XML_PVT(p_headerId number, p_draft_id number)
131   RETURN XMLType IS
132   CURSOR C_ATTR_GRP IS
133     SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
134           AG.ATTR_GROUP_ID ATTR_GROUP_ID,
135           AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
136           AG.MULTI_ROW MULTI_ROW
137     FROM EGO_FND_DSC_FLX_CTX_EXT AG, po_headers_all h,
138          PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
139     WHERE h.po_header_id = p_headerId
140     AND H.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
141     AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
142     AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
143     AND AG.ATTR_GROUP_ID  = POTU.ATTRIBUTE_GROUP_ID
144     AND potu.attribute_group_id in (select distinct attr_group_id
145                                     from po_headers_all_ext_b
146                                     where po_header_id = p_headerId
147                                     and draft_id = p_draft_id
148                                     and attr_group_id = potu.attribute_group_id)
149     ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
150 
151   CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
152     SELECT   EFDFCE.ATTR_ID                ,
153     EFDFCE.APPLICATION_COLUMN_NAME      ,
154     FCU.END_USER_COLUMN_NAME,
155     fcu.flex_value_set_id,
156     EFDFCE.data_type
157     FROM
158       EGO_FND_DF_COL_USGS_EXT EFDFCE,
159       FND_DESCR_FLEX_COLUMN_USAGES FCU
160     WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
161     AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
162     AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
163     AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
164     AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
165     AND FCU.DISPLAY_FLAG <> 'H';
166 
167   L_SQL VARCHAR2(32767);
168   L_OP VARCHAR2(32767);
169   L_ATTR_GRP NUMBER;
170   L_CTR NUMBER;
171   L_O_CTR NUMBER;
172   l_uda_xml XMLTYPE;
173   BEGIN
174 
175   L_O_CTR := 0;
176   FOR REC IN C_ATTR_GRP LOOP
177     IF REC.MULTI_ROW = 'Y' THEN
178       IF L_O_CTR > 0 THEN
179         L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
180                  || '", XMLAgg(XMLForest(' ;
181       ELSE
182         L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
183       END IF;
184     ELSE
185       IF L_O_CTR > 0 THEN
186         L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
187                   || '", XMLForest(' ;
188       ELSE
189         L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
190       END IF;
191     END IF;
192     L_CTR := 0 ;
193     FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
194         IF L_CTR = 0 THEN
195           L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
196         ELSE
197           L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
198         END IF;
199         if r.flex_value_set_id is not null then
200           if (r.data_type = 'C' or r.data_type = 'A' ) then
201               l_sql := l_sql || ',' || get_uda_attr_desc_sql('HEADER',
202                                      p_headerId,
203                                      p_draft_Id,
204                                      r.data_type,
205                                      r.application_column_name,
206                                      r.end_user_column_name,
207                                      rec.attr_group,
208                                      r.ATTR_ID );
209           end if;
210         end if;
211         L_CTR := L_CTR + 1;
212     END LOOP;
213     IF REC.MULTI_ROW = 'Y' THEN
214       L_SQL := L_SQL || ')))';
215     ELSE
216       L_SQL := L_SQL || ' ))';
217     END IF;
218     L_SQL := L_SQL || ' from po_headers_all_ext_vl where po_header_id = ' ||
219              p_headerId || ' AND draft_id = ' || p_draft_id ||
220              ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
221     L_O_CTR := L_O_CTR + 1;
222   END LOOP;
223 
224   l_op := ' select XMLConcat(' || l_sql || ') from dual';
225   execute immediate l_op into l_uda_xml;
226   RETURN (l_uda_xml);
227   END GET_UDA_HEADER_XML_PVT;
228 
229 
230   FUNCTION GET_UDA_HEADER_XML RETURN XMLType IS
231   begin
232     return(get_uda_header_xml_pvt(headerId, -1));
233   END GET_UDA_HEADER_XML;
234 
235   FUNCTION GET_UDA_HEADER_DRAFTS_XML(p_headerid NUMBER) RETURN XMLType IS
236   begin
237     return(get_uda_header_xml_pvt(p_headerid, p_draft_id));
238   END GET_UDA_HEADER_DRAFTS_XML;
239 
240 
241   function get_addressxml_pvt(p_header_id number, p_draft_id number) return CLOB as
242     l_where_sql varchar2(1000);
243     l_addr_sql varchar2(1000);
244     l_sql varchar2(4000);
245     l_col_name varchar2(50);
246     l_attr_grp_id number;
247     clob_addr CLOB;
248     c_addr SYS_REFCURSOR;
249     l_address varchar2(4000);
250   begin
251     l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
252 
253     begin
254       SELECT 	ag.attr_group_id,
255               efdfce.application_column_name
256       into   l_attr_grp_id, l_col_name
257       FROM ego_fnd_dsc_flx_ctx_ext ag,
258            ego_fnd_df_col_usgs_ext efdfce,
259            fnd_descr_flex_column_usages fcu
260       WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
261       and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
262       and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
263       and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
264       and fcu.application_column_name = efdfce.application_column_name
265       and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
266       and upper(ag.descriptive_flex_context_code) = upper('addresses')
267       and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
268 
269       l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
270 
271       l_addr_sql := 'select ' || l_col_name || ' as address ' ;
272 
273     exception
274     when no_data_found then
275       l_addr_sql := '';
276     end;
277 
278     if l_addr_sql is not null then
279       l_sql := l_addr_sql || l_where_sql;
280 
281       dbms_lob.createtemporary(clob_addr, true);
282       dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
283       open c_addr for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
284       loop
285         fetch c_addr into l_address;
286         exit when c_addr%notfound;
287         if l_address is not null then
288         dbms_lob.writeappend(clob_addr, length(l_address), l_address);
289         end if;
290       end loop;
291       dbms_lob.close(clob_addr);
292     end if;
293     return(clob_addr);
294   end get_addressxml_pvt;
295 
296 
297   function get_contactsxml_pvt(p_header_id number , p_draft_id number) return CLOB as
298     l_cnt_sql varchar2(1000);
299     l_where_sql varchar2(1000);
300     l_sql varchar2(4000);
301     l_cnt varchar2(4000);
302     l_col_name varchar2(50);
303     l_attr_grp_id number;
304     c_cnt SYS_REFCURSOR;
305     clob_cnt CLOB;
306 
307   begin
308     l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
309 
310     begin
311       SELECT 	ag.attr_group_id,
312               efdfce.application_column_name
313       into   l_attr_grp_id, l_col_name
314       FROM ego_fnd_dsc_flx_ctx_ext ag,
315            ego_fnd_df_col_usgs_ext efdfce,
316            fnd_descr_flex_column_usages fcu
317       WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
318       and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
319       and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
320       and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
321       and fcu.application_column_name = efdfce.application_column_name
322       and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
323       and upper(ag.descriptive_flex_context_code) = upper('addresses')
324       and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
325 
326       l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
327 
328       l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
329 
330     exception
331     when no_data_found then
332       l_cnt_sql := '';
333     end;
334 
335     if l_cnt_sql is not null then
336       l_sql := l_cnt_sql || l_where_sql;
337 
338       dbms_lob.createtemporary(clob_cnt, true);
339       dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
340       open c_cnt for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
341       loop
342         fetch c_cnt into l_cnt;
343         exit when c_cnt%notfound;
344         if l_cnt is not null then
345         dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
346         end if;
347       end loop;
348       dbms_lob.close(clob_cnt);
349     end if;
350     return(clob_cnt);
351   end get_contactsxml_pvt;
352 
353   FUNCTION get_addressxml RETURN CLOB AS
354   BEGIN
355     return(get_addressxml_pvt(headerId, -1));
356   END get_addressxml;
357 
358   FUNCTION get_contactsxml RETURN CLOB AS
359   begin
360     return(get_contactsxml_pvt(headerId, -1));
361   END get_contactsxml;
362 
363   FUNCTION get_drafts_addressxml(p_headerId NUMBER) RETURN CLOB AS
364   begin
365     return(get_addressxml_pvt(p_headerId, p_draft_id));
366   END get_drafts_addressxml;
367 
368   FUNCTION get_drafts_contactsxml(p_headerId NUMBER) RETURN CLOB AS
369   begin
370     return(get_contactsxml_pvt(p_headerId, p_draft_id));
371   END get_drafts_contactsxml;
372 
373     FUNCTION get_vendor_address_details(uda_template_id NUMBER, p_header_id NUMBER, p_draft_id NUMBER) RETURN CLOB AS
374   v_clob_addr CLOB;
375   BEGIN
376   SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
377          'SUPPLIER_DTLS', NULL, 'HIDD_ADDRS_XML', 'INTERNAL_VALUE') INTO v_clob_addr FROM dual;
378 
379   RETURN v_clob_addr;
380   END get_vendor_address_details;
381 
382   FUNCTION get_vendor_contact_details(uda_template_id NUMBER, p_header_id NUMBER, p_draft_id NUMBER) RETURN CLOB AS
383   v_clob_addr CLOB;
384   BEGIN
385   SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
386          'SUPPLIER_DTLS', NULL, 'HIDD_CNTCT_XML', 'INTERNAL_VALUE') INTO v_clob_addr FROM dual;
387 
388   RETURN v_clob_addr;
389   END get_vendor_contact_details;
390 
391   FUNCTION get_issuing_off_loc_code(p_header_id number, p_draft_id number) RETURN VARCHAR2 AS
392   l_addr_dodac VARCHAR2 (1000);
393 
394   BEGIN
395       IF (p_draft_id = -1) then
396         l_addr_dodac := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(NULL, 'PO_HEADER_EXT_ATTRS', p_header_id, -1, NULL, NULL, NULL, NULL,
397         'addresses', NULL, 'addresscode', 'ISSUING_OFFICE', 'DISPLAY_VALUE');
398         ELSE
399           l_addr_dodac := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(NULL, 'PO_HEADER_EXT_ATTRS', p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
400           'addresses', NULL, 'addresscode', 'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE');
401       END IF;
402 
403     return(l_addr_dodac);
404 
405   EXCEPTION
406     WHEN Others THEN
407           l_addr_dodac := NULL;
408   end get_issuing_off_loc_code;
409 END;
410