Loftware Connector For Oracle Filter Example

Description

How do I look up data using a stored procedure?

Solution

Example
This filter is only used by the manual printing from MSCA. It determines which label format to print based on data received by the Connector See Loftware Connector. from an Oracle Quality Collection Plan. The Quality Collection Plan creates an XML output that is passed to the Connector. Due to the non-standard data storage for the Collection Plan, this filter incorporates a Stored Procedure which determines which fields are empty and which fields can be used to retrieve data.

Note: The parameters for the procedure can be passed as variables that are retrieved from an XML tag or the parameters can be static values.
Referenced Stored Procedure
CREATE OR REPLACE FUNCTION Choose_Label_Format_Ma(address1_in VARCHAR2,address2_in VARCHAR2,label_type_in VARCHAR2,ship_from_city_in VARCHAR2) RETURN VARCHAR2
IS
label_format VARCHAR2(40);customer_name VARCHAR2(40);org VARCHAR2(10);testdata XXPRINTER_CFG%ROWTYPE;
BEGIN
-determine customerIF address1_in LIKE 'FRTL%' OR address2_in LIKE 'FRTL-%'THEN customer_name := 'FREIGHTLINER CORPORATION';ELSIF address1_in LIKE '%FREIGHTLINER%' OR address2_in LIKE '%FREIGHTLINER%'THEN customer_name := 'FREIGHTLINER CORPORATION';ELSIF address1_in LIKE '%GENERAL MOTORS%' OR address2_in LIKE '%GENERAL MOTORS%'THEN customer_name := 'GENERAL MOTORS CORPORATION';ELSIF address1_in = 'INTERNATIONAL TRUCK AND ENGINE CORP' OR address2_in = 'INTERNATIONAL TRUCK AND ENGINE CORP'THEN customer_name := 'INTERNATIONAL TRUCK AND ENGINE CORP';ELSIF address1_in LIKE 'INTL-%' OR address2_in = 'INTL-%'THEN customer_name := 'INTERNATIONAL TRUCK AND ENGINE CORP';ELSE customer_name := 'DEFAULT';END IF;
--determine ship from org
IF ship_from_city_in = 'Portland'THEN ORG :='PDX';ELSIF ship_from_city_in = 'Los Angeles'THEN ORG :='LAX';ELSIF ship_from_city_in = 'Seattle'THEN ORG :='SEA';ELSIF ship_from_city_in = 'Detroit'THEN ORG :='DTW';ELSIF ship_from_city_in = 'Boston'THEN ORG :='BOS';ELSIF ship_from_city_in = 'Dallas'THEN ORG :='DFW';
END IF;
--try toretrieve proper label format based on customer nameSELECT * INTO testdata FROM XXPRINTER_CFGWHERE LABEL_TYPE = label_type_in AND org_code = orgAND party_name = customer_name AND ROWNUM<2;
label_format := testdata.label_format;
RETURN label_format;
--try and retrieve default label for label type or print an error label if no label format foundEXCEPTIONWHEN OTHERS THENSELECT * INTO testdata FROM XXPRINTER_CFGWHERE LABEL_TYPE = label_type_in AND org_code = orgAND party_name = 'DEFAULT' AND ROWNUM<2;
label_format := NVL(TESTDATA.LABEL_FORMAT,'ERROR.LWL');
RETURN label_format;
END;
/