[Home] [Help]
PACKAGE BODY: APPS.GR_XML_REPORTS
Source
1 PACKAGE BODY GR_XML_REPORTS AS
2 /* $Header: GRXREPB.pls 120.0 2005/10/03 07:36:26 ragsriva noship $ */
3 PROCEDURE dispatch_history_report (
4 errbuf OUT NOCOPY VARCHAR2
5 , retcode OUT NOCOPY VARCHAR2
6 , p_organization_id IN NUMBER
7 , p_from_item IN VARCHAR2
8 , p_to_item IN VARCHAR2
9 , p_from_recipient IN VARCHAR2
10 , p_to_recipient IN VARCHAR2
11 , p_from_document_category IN VARCHAR2
12 , p_to_document_category IN VARCHAR2
13 , p_from_date_sent IN VARCHAR2
14 , p_to_date_sent IN VARCHAR2
15 , p_cas_number IN VARCHAR2
16 , p_ingredient_item_id IN NUMBER
17 , p_order_by IN VARCHAR2) IS
18
19 p_from_date DATE;
20 p_to_date DATE;
21 l_xml_query VARCHAR2 (32000);
22 l_queryctx DBMS_XMLGEN.ctxhandle;
23 l_xml_result CLOB;
24 BEGIN
25 p_from_date := fnd_date.canonical_to_date(p_from_date_sent);
26 p_to_date := fnd_date.canonical_to_date(p_to_date_sent);
27
28 l_xml_query :=
29 ' SELECT organization_code '
30 || ' , SYSDATE report_date '
31 || ' , CURSOR (SELECT grdh.* '
32 || ' , fdc.user_name document_category_user_name '
33 || ' FROM gr_dispatch_history_v grdh '
34 || ' , fnd_document_categories_vl fdc '
35 || ' WHERE grdh.organization_id = mp.organization_id '
36 || ' AND grdh.document_category = fdc.NAME(+) ';
37
38
39
40 IF p_from_item IS NOT NULL
41 AND p_to_item IS NOT NULL THEN
42 l_xml_query := l_xml_query || ' AND grdh.item >= ''' || p_from_item || '''';
43 l_xml_query := l_xml_query || ' AND grdh.item <= ''' || p_to_item || '''';
44 ELSIF p_from_item IS NOT NULL THEN
45 l_xml_query := l_xml_query || ' AND grdh.item >= ''' || p_from_item || '''';
46 ELSIF p_to_item IS NOT NULL THEN
47 l_xml_query := l_xml_query || ' AND grdh.item <= ''' || p_to_item || '''';
48 END IF;
49
50 IF p_from_recipient IS NOT NULL
51 AND p_to_recipient IS NOT NULL THEN
52 l_xml_query := l_xml_query || ' AND grdh.recipient_number >= ''' || p_from_recipient || '''';
53 l_xml_query := l_xml_query || ' AND grdh.recipient_number <= ''' || p_to_recipient || '''';
54 ELSIF p_from_recipient IS NOT NULL THEN
55 l_xml_query := l_xml_query || ' AND grdh.recipient_number >= ''' || p_from_recipient || '''';
56 ELSIF p_to_recipient IS NOT NULL THEN
57 l_xml_query := l_xml_query || ' AND grdh.recipient_number <= ''' || p_to_recipient || '''';
58 END IF;
59
60 IF p_from_document_category IS NOT NULL
61 AND p_to_document_category IS NOT NULL THEN
62 l_xml_query := l_xml_query || ' AND grdh.document_category >= ''' || p_from_document_category || '''';
63 l_xml_query := l_xml_query || ' AND grdh.document_category <= ''' || p_to_document_category || '''';
64 ELSIF p_from_document_category IS NOT NULL THEN
65 l_xml_query := l_xml_query || ' AND grdh.document_category >= ''' || p_from_document_category || '''';
66 ELSIF p_to_document_category IS NOT NULL THEN
67 l_xml_query := l_xml_query || ' AND grdh.document_category <= ''' || p_to_document_category || '''';
68 END IF;
69
70 IF p_from_date_sent IS NOT NULL
71 AND p_to_date_sent IS NOT NULL THEN
72 l_xml_query := l_xml_query || ' AND grdh.date_sent >= ''' || p_from_date || '''';
73 l_xml_query := l_xml_query || ' AND grdh.date_sent <= ''' || p_to_date || '''';
74 ELSIF p_from_date_sent IS NOT NULL THEN
75 l_xml_query := l_xml_query || ' AND grdh.date_sent >= ''' || p_from_date || '''';
76 ELSIF p_to_date_sent IS NOT NULL THEN
77 l_xml_query := l_xml_query || ' AND grdh.date_sent <= ''' || p_to_date || '''';
78 END IF;
79
80 IF p_cas_number IS NOT NULL THEN
81 l_xml_query := l_xml_query || ' AND grdh.cas_number = ''' || p_cas_number || '''';
82 END IF;
83
84 IF p_ingredient_item_id IS NOT NULL THEN
85 l_xml_query :=
86 l_xml_query
87 || ' AND grdh.inventory_item_id IN (SELECT product_item_id FROM gr_ingredient_concentrations '
88 || ' WHERE organization_id = ' || p_organization_id
89 || ' AND ingredient_item_id = ' || p_ingredient_item_id
90 || ' ) ';
91 END IF;
92
93 IF p_order_by = '1' THEN
94 l_xml_query := l_xml_query || ' ORDER BY grdh.date_sent ';
95 ELSIF p_order_by = '2' THEN
96 l_xml_query := l_xml_query || ' ORDER BY grdh.recipient ';
97 ELSE
98 l_xml_query := l_xml_query || ' ORDER BY grdh.item ';
99 END IF;
100
101 l_xml_query :=
102 l_xml_query
103 || ' ) DISPATCH_INFO '
104 || ' FROM mtl_parameters mp '
105 || ' WHERE organization_id = ' || p_organization_id;
106
107 fnd_file.put_line (fnd_file.LOG, l_xml_query);
108
109 l_queryctx := DBMS_XMLGEN.newcontext (l_xml_query);
110 l_xml_result := DBMS_XMLGEN.getxml (l_queryctx);
111 xml_transfer (p_xml_clob => l_xml_result);
112 DBMS_XMLGEN.closecontext (l_queryctx);
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure DISPATCH_HISTORY_REPORT ' || SQLCODE || ' ' || SQLERRM);
117 END dispatch_history_report;
118
119 PROCEDURE xml_transfer (
120 p_xml_clob IN CLOB) IS
121 l_file CLOB;
122 file_varchar2 VARCHAR2 (4000);
123 l_len NUMBER;
124 l_limit NUMBER;
125 BEGIN
126 l_file := p_xml_clob;
127 l_limit := 1;
128 l_len := DBMS_LOB.getlength (l_file);
129
130 LOOP
131 IF l_len > l_limit THEN
132 file_varchar2 := DBMS_LOB.SUBSTR (l_file, 4000, l_limit);
133 fnd_file.put (fnd_file.output, file_varchar2);
134 file_varchar2 := NULL;
135 l_limit := l_limit + 4000;
136 ELSE
137 file_varchar2 := DBMS_LOB.SUBSTR (l_file, 4000, l_limit);
138 fnd_file.put (fnd_file.output, file_varchar2);
139 file_varchar2 := NULL;
140 EXIT;
141 END IF;
142 END LOOP;
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure XML_TRANSFER ' || SQLCODE || ' ' || SQLERRM);
147 END xml_transfer;
148
149 END GR_XML_REPORTS;