[Home] [Help]
PACKAGE BODY: APPS.GMO_OC_TRANS_PKG
Source
1 PACKAGE BODY GMO_OC_TRANS_PKG AS
2 /* $Header: GMOOCTRB.pls 120.1 2007/06/21 06:11:17 rvsingh noship $ */
3 PROCEDURE GENERATE_OC_TRANS_XML(ERRBUF OUT NOCOPY VARCHAR2,
4 RETCODE OUT NOCOPY VARCHAR2,
5 Plant IN NUMBER,
6 Object_type IN NUMBER DEFAULT NULL,
7 Object_id IN NUMBER DEFAULT NULL,
8 Operator_id IN NUMBER DEFAULT NULL,
9 FromDate IN VARCHAR2,
10 ToDate IN VARCHAR2) IS
11 TYPE refcur IS REF CURSOR;
12 l_refcur refcur;
13 l_result XMLType;
14 l_final_clob CLOB ;
15 l_len number;
16 l_xml_data varchar2(10);
17 l_limit number;
18 l_argument_string long ;
19 l_from_date varchar2(15);
20 l_to_date varchar2(15);
21 l_org_name VARCHAR2(4000);
22 l_org_code VARCHAR2(240);
23 l_object_type VARCHAR2(240);
24 l_object VARCHAR2(240);
25 l_operator_name VARCHAR2(240);
26 CURSOR GET_ORG_NAME IS
27 SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE
28 FROM hr_all_organization_units hou,
29 mtl_parameters mp
30 WHERE hou.organization_id = Plant
31 AND NVL(hou.date_to, SYSDATE+1) >= SYSDATE
32 and mp.organization_id = hou.organization_id;
33 CURSOR GET_OBJECT_TYPE(ob_type_id NUMBER) IS
34 SELECT meaning from fnd_lookups where lookup_type = 'GMO_OC_OBJECT_LOV' and lookup_code = ob_type_id;
35 CURSOR GET_OBJECT IS
36 select DECODE(object_type,1,
37 (select concatenated_segments from mtl_system_items_kfv where inventory_item_id = OBJECT_ID and organization_id = Plant),2,
38 (select resources from cr_rsrc_dtl where resource_id = OBJECT_ID),3,
39 (select meaning from fnd_lookups where lookup_type = 'GMO_OC_TRANS_LOV' and lookup_code = OBJECT_ID),null) from dual;
40 BEGIN
41 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CP : GENERATE_XML(OC) : START ');
42 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Input Parameter - Plant :'||Plant||' and Object Type:'||Object_type||' and Object_id :'||Object_id||' From Date :'||FromDate||' To Date :'||ToDate||' Operator_id:'||Operator_id);
43 l_from_date := null;
44 l_to_date := null;
45
46 IF(FromDate IS NOT NULL) THEN
47 l_from_date := substr(FromDate,0,10);
48 END IF;
49 IF(ToDate IS NOT NULL) THEN
50 l_to_date := substr(ToDate,0,10);
51 END IF;
52 FND_FILE.PUT_LINE (FND_FILE.LOG,'l_from_date :'||l_from_date||'l_to_date :'||l_to_date);
53
54 OPEN GET_ORG_NAME;
55 FETCH GET_ORG_NAME INTO l_org_name, l_org_code;
56 CLOSE GET_ORG_NAME;
57 OPEN GET_OBJECT_TYPE(Object_type);
58 FETCH GET_OBJECT_TYPE INTO l_object_type;
59 CLOSE GET_OBJECT_TYPE;
60 OPEN GET_OBJECT;
61 FETCH GET_OBJECT INTO l_object;
62 CLOSE GET_OBJECT;
63 IF operator_id IS NOT NULL THEN
64 l_operator_name := GMO_UTILITIES.GET_USER_DISPLAY_NAME(operator_id);
65 END IF;
66
67 l_argument_string := 'SELECT XMLELEMENT("OPERATORHISTORY", XMLCONCAT(XMLSEQUENCETYPE(
68 XMLTYPE(''<ORGANIZATION>''||:x||''</ORGANIZATION>''),
69 XMLTYPE(''<OBJECTTYPE>''||:y||''</OBJECTTYPE>''),
70 XMLTYPE(''<OBJECT>''||:z||''</OBJECT>''),
71 XMLTYPE(''<FROMDATE>''||:xx||''</FROMDATE>''),
72 XMLTYPE(''<TODATE>''||:yy||''</TODATE>''),
73 XMLTYPE(''<OPERATOR>''||:zz||''</OPERATOR>''))),
74 XMLAGG(XMLELEMENT("OPERATORCERTHEADER",
75 XMLFOREST(:orgName as OrganizationName ,
76 DECODE(oc_trans_hdr.TRANS_OBJECT_ID,1,
77 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_OBJECT_LOV'' and lookup_code = 1),2,
78 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_OBJECT_LOV'' and lookup_code = 2),3,
79 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_OBJECT_LOV'' and lookup_code = 3),null) Transaction_Type,
80 DECODE(oc_trans_hdr.TRANS_OBJECT_ID,1,
81 (select concatenated_segments from mtl_system_items_kfv where inventory_item_id = oc_hdr.OBJECT_ID and organization_id = oc_hdr.ORGANIZATION_ID),2,
82 (select resources from cr_rsrc_dtl where resource_id = oc_hdr.OBJECT_ID),3,
83 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_TRANS_LOV'' and lookup_code = oc_hdr.OBJECT_ID),null) Transaction_Object,
84 DECODE(oc_trans_hdr.TRANS_OBJECT_ID,1,
85 (select description from mtl_system_items_kfv where inventory_item_id = oc_hdr.OBJECT_ID and organization_id = oc_hdr.ORGANIZATION_ID),2,
86 (select resource_desc from cr_rsrc_mst where resources IN (select resources from cr_rsrc_dtl where resource_id = oc_hdr.OBJECT_ID)),3,
87 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_TRANS_LOV'' and lookup_code = oc_hdr.OBJECT_ID),null) Object_Description,
88 FND_MESSAGE.GET_STRING(oc_trans_hdr.user_key_label_product,oc_trans_hdr.USER_KEY_LABEL_TOKEN) User_Key_Label,
89 oc_trans_hdr.USER_KEY_VALUE User_Key_Value,
90 oc_trans_hdr.CREATION_DATE Transaction_Date,
91 oc_trans_hdr.USER_ID User_Id,
92 GMO_UTILITIES.GET_USER_DISPLAY_NAME(oc_trans_hdr.USER_ID) Operator,
93 oc_trans_hdr.OVERRIDER_ID Overrider_Id,
94 GMO_UTILITIES.GET_USER_DISPLAY_NAME(oc_trans_hdr.OVERRIDER_ID) Overrider,
95 oc_trans_hdr.COMMENTS comments,
96 oc_trans_hdr.ERECORD_ID ERecord_Id,
97 ( SELECT XMLAGG(XMLELEMENT("OPER_CERT_DTL",XMLFOREST(DECODE(oc_trans_dtl.QUALIFICATION_TYPE,1,
98 (select unique(name) from OTA_CERTIFICATIONS_VL where certification_id = oc_trans_dtl.QUALIFICATION_ID),2,
99 (select unique(name) from per_competences where competence_id = oc_trans_dtl.QUALIFICATION_ID),null) Qualification,
100 DECODE(oc_trans_dtl.QUALIFICATION_TYPE,1,
101 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_QUAL_LOV'' and lookup_code =1),2,
102 (select meaning from fnd_lookups where lookup_type = ''GMO_OC_QUAL_LOV'' and lookup_code =2),null) Qualification_type,
103 DECODE(oc_trans_dtl.QUALIFICATION_TYPE,2,
104 (select step_value||''-''||name from per_rating_levels where rating_level_id = oc_trans_dtl.PROFICIENCY_LEVEL_ID),null) Proficiency_Level )))
105 FROM GMO_OPERATOR_TRANS_DETAIL oc_trans_dtl
106 WHERE oc_trans_dtl.OPERATOR_CERTIFICATE_ID = oc_trans_hdr.OPERATOR_CERTIFICATE_ID
107 ) as OPER_CERT_DTL_LIST )
108 ) ORDER BY oc_trans_hdr.CREATION_DATE DESC )
109 ) FROM gmo_opert_cert_header oc_hdr, gmo_operator_cert_trans oc_trans_hdr
110 WHERE oc_hdr.header_id = oc_trans_hdr.header_id
111 AND oc_hdr.organization_id = :a';
112 IF (object_type IS NOT NULL) THEN
113 l_argument_string := l_argument_string || ' and oc_hdr.object_type =:b';
114 ELSE
115 l_argument_string := l_argument_string || ' and 1 =:b';
116 END IF;
117 IF(Object_id IS NOT NULL ) THEN
118 l_argument_string := l_argument_string || ' and oc_hdr.Object_id =:c';
119 ELSE
120 l_argument_string := l_argument_string || ' and 1 =:c';
121 END IF;
122 FND_FILE.put_line(FND_FILE.LOG,'SQL Query: '||l_argument_string);
123
124 IF(l_from_date IS NOT NULL) THEN
125 l_argument_string := l_argument_string || ' and TRUNC(oc_trans_hdr.creation_date) >=TO_DATE( :d , ''YYYY/MM/DD'')';
126 else
127 l_argument_string := l_argument_string || ' and 1 = :d ';
128 END IF;
129 IF(l_to_date IS NOT NULL) THEN
130 l_argument_string := l_argument_string || ' and TRUNC(oc_trans_hdr.creation_date) <=TO_DATE( :e , ''YYYY/MM/DD'')';
131 else
132 l_argument_string := l_argument_string || ' and 1 = :e ';
133 END IF;
134
135 IF(Operator_id IS NOT NULL) THEN
136 l_argument_string := l_argument_string || ' and oc_trans_hdr.user_id = :f';
137 ELSE
138 l_argument_string := l_argument_string || ' and 1 = :f';
139 END IF;
140
141 FND_FILE.put_line(FND_FILE.LOG,'SQL Query'||l_argument_string);
142 OPEN l_refcur FOR l_argument_string
143 USING l_org_code, l_object_type, l_object, l_from_date, l_to_date,l_operator_name,l_org_name,Plant, nvl(object_type,1),nvl(object_id,1), nvl(l_from_date, 1), nvl(l_to_date,1),nvl(Operator_id,1);
144 FETCH l_refcur INTO l_result;
145 CLOSE l_refcur;
146
147 l_limit:= 1;
148 l_final_clob := l_result.getClobVal();
149 l_len := DBMS_LOB.GETLENGTH (l_final_clob);
150 FND_FILE.PUT(FND_FILE.LOG, 'Size'||l_len);
151 LOOP
152 IF l_len > l_limit THEN
153 l_xml_data := DBMS_LOB.SUBSTR (l_final_clob,10,l_limit);
154 FND_FILE.PUT(FND_FILE.OUTPUT,l_xml_data);
155 FND_FILE.PUT(FND_FILE.LOG,l_xml_data);
156 l_xml_data := NULL;
157 l_limit:= l_limit + 10;
158 ELSE
159 l_xml_data := DBMS_LOB.SUBSTR (l_final_clob,10,l_limit);
160 FND_FILE.PUT(FND_FILE.OUTPUT, l_xml_data);
161 FND_FILE.PUT(FND_FILE.LOG,l_xml_data);
162 l_xml_data := NULL;
163 EXIT;
164 END IF;
165 END LOOP;
166 fnd_file.put_line(FND_FILE.LOG, 'CP : GENERATE_XML(OC) : FINISH ');
167 EXCEPTION
168 WHEN OTHERS THEN
169 fnd_file.put_line(fnd_file.log,SQLERRM);
170 fnd_file.new_line(fnd_file.log,2);
171 fnd_file.put_line(fnd_file.log, fnd_message.get_string('GMO', 'GMO_OPCERT_XML_ERR') );
172 RETURN;
173 END GENERATE_OC_TRANS_XML;
174 END GMO_OC_TRANS_PKG;