APPS.GMO_OC_TRANS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 27

SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE
FROM hr_all_organization_units hou,
     mtl_parameters mp
WHERE hou.organization_id = Plant
  AND NVL(hou.date_to, SYSDATE+1) >= SYSDATE
  and mp.organization_id = hou.organization_id;

Line: 34

SELECT meaning from fnd_lookups where lookup_type = 'GMO_OC_OBJECT_LOV' and lookup_code = ob_type_id;

Line: 36

select DECODE(object_type,1,
   (select concatenated_segments from mtl_system_items_kfv where inventory_item_id = OBJECT_ID and organization_id = Plant),2,
   (select resources from cr_rsrc_dtl where resource_id = OBJECT_ID),3,
   (select meaning from fnd_lookups where lookup_type = 'GMO_OC_TRANS_LOV' and lookup_code = OBJECT_ID),null)  from dual;

Line: 67

l_argument_string := 'SELECT XMLELEMENT("OPERATORHISTORY", XMLCONCAT(XMLSEQUENCETYPE(
                                          XMLTYPE(''''||:x||''''),
                                          XMLTYPE(''''||:y||''''),
                                          XMLTYPE(''''),
                                          XMLTYPE(''''||:xx||''''),
                                          XMLTYPE(''''||:yy||''''),
                                          XMLTYPE(''''||:zz||''''))),
              XMLAGG(XMLELEMENT("OPERATORCERTHEADER",
	            XMLFOREST(:orgName as  OrganizationName ,
	                DECODE(oc_trans_hdr.TRANS_OBJECT_ID,1,
                  (select meaning from fnd_lookups where lookup_type = ''GMO_OC_OBJECT_LOV'' and lookup_code = 1),2,
                  (select meaning from fnd_lookups where lookup_type = ''GMO_OC_OBJECT_LOV'' and lookup_code = 2),3,
                  (select meaning from fnd_lookups where lookup_type = ''GMO_OC_OBJECT_LOV'' and lookup_code = 3),null) Transaction_Type,
                  DECODE(oc_trans_hdr.TRANS_OBJECT_ID,1,
                  (select concatenated_segments from mtl_system_items_kfv where inventory_item_id = oc_hdr.OBJECT_ID and organization_id = oc_hdr.ORGANIZATION_ID),2,
                  (select resources from cr_rsrc_dtl where resource_id = oc_hdr.OBJECT_ID),3,
                  (select meaning from fnd_lookups where lookup_type = ''GMO_OC_TRANS_LOV'' and lookup_code = oc_hdr.OBJECT_ID),null) Transaction_Object,
                  DECODE(oc_trans_hdr.TRANS_OBJECT_ID,1,
                  (select description from mtl_system_items_kfv where inventory_item_id = oc_hdr.OBJECT_ID and organization_id = oc_hdr.ORGANIZATION_ID),2,
                  (select resource_desc from cr_rsrc_mst where resources IN (select resources from cr_rsrc_dtl where resource_id = oc_hdr.OBJECT_ID)),3,
                  (select meaning from fnd_lookups where lookup_type = ''GMO_OC_TRANS_LOV'' and lookup_code = oc_hdr.OBJECT_ID),null) Object_Description,
                  FND_MESSAGE.GET_STRING(oc_trans_hdr.user_key_label_product,oc_trans_hdr.USER_KEY_LABEL_TOKEN) User_Key_Label,
                  oc_trans_hdr.USER_KEY_VALUE User_Key_Value,
                  oc_trans_hdr.CREATION_DATE Transaction_Date,
                  oc_trans_hdr.USER_ID User_Id,
                  GMO_UTILITIES.GET_USER_DISPLAY_NAME(oc_trans_hdr.USER_ID) Operator,
                  oc_trans_hdr.OVERRIDER_ID Overrider_Id,
                  GMO_UTILITIES.GET_USER_DISPLAY_NAME(oc_trans_hdr.OVERRIDER_ID) Overrider,
                  oc_trans_hdr.COMMENTS comments,
                  oc_trans_hdr.ERECORD_ID ERecord_Id,
                  ( SELECT  XMLAGG(XMLELEMENT("OPER_CERT_DTL",XMLFOREST(DECODE(oc_trans_dtl.QUALIFICATION_TYPE,1,
                  (select unique(name) from OTA_CERTIFICATIONS_VL where certification_id = oc_trans_dtl.QUALIFICATION_ID),2,
                  (select unique(name) from per_competences where competence_id = oc_trans_dtl.QUALIFICATION_ID),null) Qualification,
		                                                DECODE(oc_trans_dtl.QUALIFICATION_TYPE,1,
                                                    (select meaning from fnd_lookups where lookup_type = ''GMO_OC_QUAL_LOV'' and lookup_code =1),2,
                                                    (select meaning from fnd_lookups where lookup_type = ''GMO_OC_QUAL_LOV'' and lookup_code =2),null) Qualification_type,
                                                    DECODE(oc_trans_dtl.QUALIFICATION_TYPE,2,
                                                    (select step_value||''-''||name from per_rating_levels where rating_level_id = oc_trans_dtl.PROFICIENCY_LEVEL_ID),null) Proficiency_Level )))
								    FROM  GMO_OPERATOR_TRANS_DETAIL oc_trans_dtl
                    WHERE oc_trans_dtl.OPERATOR_CERTIFICATE_ID = oc_trans_hdr.OPERATOR_CERTIFICATE_ID
                   ) as OPER_CERT_DTL_LIST )
                   )   ORDER BY oc_trans_hdr.CREATION_DATE DESC )
                 )  FROM gmo_opert_cert_header oc_hdr,  gmo_operator_cert_trans oc_trans_hdr
                   WHERE oc_hdr.header_id = oc_trans_hdr.header_id
                   AND oc_hdr.organization_id = :a';