William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

oracle.sql

-- This is a william demo of PL/SQL in FedEx for transportation application.

SET SERVEROUTPUT ON SIZE 100000 FORMAT WRAPPED
SET LINESIZE 1000
SET DEFINE 

ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:TRUE,COUNTRY:4';
BEGIN
	DBMS_OUTPUT.PUT_LINE('------------------------');
	$IF $$DEBUG $THEN
		DBMS_OUTPUT.PUT_LINE('+  debug: ON           +');
	$ELSE
		DBMS_OUTPUT.PUT_LINE('+  debug: OFF          +');
	$END
	
	$IF $$COUNTRY=1 $THEN
		DBMS_OUTPUT.PUT_LINE('+  country: JP/NZ      +');
	$ELSIF $$COUNTRY=2 $THEN
		DBMS_OUTPUT.PUT_LINE('+  country: CN      +');
	$ELSIF $$COUNTRY=3 $THEN
		DBMS_OUTPUT.PUT_LINE('+  country: KR      +');
	$ELSIF $$COUNTRY=4 $THEN
		DBMS_OUTPUT.PUT_LINE('+  country: AU      +');
	$END
	
	DBMS_OUTPUT.PUT_LINE('------------------------');
END;
/

CREATE OR REPLACE TYPE SCAN_INFO AS OBJECT(
	AddressChangeFlag	VARCHAR2(5),
	AgencyCode 		VARCHAR2(20),
	AirbillFormType 	VARCHAR2(4),
	AirbillNumber 		VARCHAR2(12),
	AirbillType 		VARCHAR2(4),
	ArrivalDate		DATE,
	AuthorizedBy		VARCHAR2(20),
	BrokerClassificationCode	VARCHAR2(10),
	Comments		VARCHAR2(255),
	ConsDestLoc		VARCHAR2(5),
	ConsFormType		VARCHAR2(4),
	ConsId			VARCHAR2(12),
	ConsOid			VARCHAR2(12),
	ConsTotalPiece		NUMBER,
	ContainerId		VARCHAR2(12), 
	ContainerInd		VARCHAR2(1),
	CountryCode		VARCHAR2(2),
	CustomsReleasedFlag 	VARCHAR2(1),
	DeclarationCode 	VARCHAR2(1),
	DetainLoc		VARCHAR2(10),
	DestCountry		VARCHAR2(2),
	DestLocationCode	VARCHAR2(5),
	ErrorNumber 		NUMBER,
	FromConsFlag		VARCHAR2(1),
	HandlingUnitOid 	NUMBER,
	HAWB 			VARCHAR2(12),
	HAWBFormType 		VARCHAR2(4),
	HAWBUnknownFlag		VARCHAR2(1),
	InputDevice 		VARCHAR2(10),
	InputMethodCode		VARCHAR2(2),
	InterceptCode 		VARCHAR2(20),
	InViewName 		VARCHAR2(20),
	LocationCode 		VARCHAR2(5),
	LocStatNbr		VARCHAR2(2),
	PrinterCode		VARCHAR2(20),
	ReleaseCode		VARCHAR2(5),
	ReturnMessage 		VARCHAR2(60),
	ReturnAWBNumber		VARCHAR2(12),
	ShipmentOid 		NUMBER,
	ShuttleID		VARCHAR2(5),
	SplitNumber		NUMBER,
	StatMenuCd		VARCHAR2(5),
	TimeZone		NUMBER,
	UserLogon 		VARCHAR2(20),
	WarehouseCode 		VARCHAR2(3),
--customized for china, added by luoliang 12/03/2007
	ShipmentWeight NUMBER,
	ShipmentValueAmt NUMBER,
	CustomsReturnedCode NUMBER,
	CiqReturnedCode NUMBER
);
/

CREATE OR REPLACE TYPE CCII_STATE_INFO AS OBJECT(
	AbScanned		VARCHAR2(12),
	AbFormTypeScanned	VARCHAR2(4),
	AbTypeScanned		VARCHAR2(4),
	DamagedFlag		VARCHAR2(4),
	DiverterCode		VARCHAR2(5),
	DuplicateFlag 		VARCHAR2(1),
	InterceptedFlag		VARCHAR2(20),
	IntransitFlag 		VARCHAR2(1),
	IsPieceManifested 	VARCHAR2(2),
	MPSFlag 		VARCHAR2(10),
	OverageFlag 		VARCHAR2(10),
	PositiveSort		VARCHAR2(5),
	RouteDate		DATE,
	RouteLegNumber		NUMBER(2,0),
	RouteLegOid		NUMBER(10,0),
	RouteNumber		VARCHAR2(6),
	RouteLegStatusCode	VARCHAR2(1),
	ScanTypeCode 		VARCHAR2(20),
	ServiceCode 		VARCHAR2(5),
	SortDate		DATE,
	StagingArea		VARCHAR2(4),
	TravelStatus 		VARCHAR2(10),
	UploadStatus_FedExShuttleFlag	VARCHAR2(1)
);
/

CREATE OR REPLACE TYPE PRINT_INFO AS OBJECT(
	ADDRESS_CHANGE_FLIGHT_DATE	DATE,
	ADDRESS_CHANGE_FLIGHT_NUMBER	VARCHAR2(6),
	ADDRESS_CHANGE_NAME		VARCHAR2(35),
	ADDRESS_CHANGE_COMPANY		VARCHAR2(35),
	ADDRESS_CHANGE_ADDRESS1		VARCHAR2(35),
	ADDRESS_CHANGE_ADDRESS2		VARCHAR2(35),
	ADDRESS_CHANGE_COMMENTS		VARCHAR2(255),
	ADDRESS_CHANGE_DATE		DATE,
	BrokerEntryNumber 		VARCHAR2(14),
	ClearanceStatus			VARCHAR2(5),
	DateStr				VARCHAR2(9),
	FLGHTDATA			VARCHAR2(20),
	HandlingCode1			VARCHAR2(5),
	HandlingCode2			VARCHAR2(5),
	HandlingCode3			VARCHAR2(5),
	MAWB				VARCHAR2(12),
	PieceQuantity 			NUMBER,
	PiorityIntercept1		VARCHAR2(5),
	PiorityIntercept2		VARCHAR2(5),
	PiorityIntercept3		VARCHAR2(5),
	PiorityIntercept4		VARCHAR2(5),
	ServiceCode 			VARCHAR2(5),
	TimeStr				VARCHAR2(5)
);
/

/* ########################## Header ########################## */
CREATE OR REPLACE PACKAGE scanclient AS

 	--Declare DATA TYPE here
	TYPE typePieceArray IS TABLE OF  ENT.PIECE%ROWTYPE INDEX BY BINARY_INTEGER;
	TYPE typeActivityCodeArray IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
	TYPE VARCHAR_ARRAY IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
	TYPE typeConsRecord IS RECORD (
				CONS_OID_NBR	NUMBER(10),
				TRACKING_NBR	VARCHAR2(12),
				FORM_TYPE_CD	VARCHAR2(4),
				CONS_NBR	VARCHAR2(12),
				CONS_FORM_TYPE_CD	VARCHAR2(4),
				OID_NBR		NUMBER(10),
				OID_TYPE_CD	VARCHAR2(1)
				);
	TYPE typeConsArray IS TABLE OF typeConsRecord INDEX BY BINARY_INTEGER;
	TYPE typeDupPieceRecord IS RECORD (
				PIECE_OID_NBR	NUMBER(10),
				ROUTE_NBR	VARCHAR2(10),
				ROUTE_DT	DATE,
				PIECE_QTY	NUMBER(7),
				LBS_WGT		NUMBER(9,1),
				CONSIGNEE_COMPANY_NM	VARCHAR2(35),
				COMMODITY_DESC	VARCHAR2(255)
				);
	TYPE typeDupPieceArray IS TABLE OF typeDupPieceRecord INDEX BY BINARY_INTEGER;
        
	--Declare variable here
	globalScan SCAN_INFO := SCAN_INFO('',	--AddressChangeFlag
					'',	--AgencyCode
					'',	--AirbillFormType
					'',	--AirbillNumber
					'',	--AirbillType
					'',	--ArrivalDate
					'',	--AuthorizedBy
					'',	--BrokerClassificationCode
					'',	--Comments
					'',	--ConsDestLoc
					'',	--ConsFormType,
					'',	--ConsId
					'',	--ConsOid
					0,	--ConsTotalPiece
					'',	--ContainerId, 
					'',	--ContainerInd,
					'', 	--CountryCode
					'',	--CustomsReleasedFlag
					'',	--DeclarationCode
					'',	--DetainLoc
					'',	--DestCountry
					'',	--DestLocationCode
					0,	--ErrorNumber
					'',	--FromConsFlag
					0,	--HandlingUnitOid
					'',	--HAWB
					'',	--HAWBFormType
					'',	--HAWBUnknownFlag
					'',	--InputDevice
					'',	--InputMethodCode
					'',	--InterceptCode
					'',	--InViewName
					'',	--LocationCode
					'00',	--LocStatNbr
					'',	--PrinterCode
					'',	--ReleaseCode
					'',	--ReturnMessage
					'',	--ReturnAWBNumber
					0,	--ShipmentOid
					'',	--ShuttleID
					0,	--SplitNumber
					'',	--StatMenuCd
					'',	--TimeZone
					'',	--UserLogon
					'', --WarehouseCode
--customized for china, added by luoliang 12/03/2007
					0, --ShipmentWeight 
					0, --ShipmentValueAmt 
					0, --CustomsReturnedCode 
					0);--CiqReturnedCode
	globalCCIIState CCII_STATE_INFO := CCII_STATE_INFO('',	--AbScanned
							'',	--AbFormTypeScanned
							'',	--AbTypeScanned
							'',	--DamagedFlag
							'',	--DiverterCode
							'',	--DuplicateFlag
							'',	--InterceptedFlag
							'',	--IntransitFlag
							'',	--IsPieceManifested
							'',	--MPSFlag
							'',	--OverageFlag
							'TRUE',	--PositiveSort
							'',	--RouteDate
							'',	--RouteLegNumber
							'',	--RouteLegOid
							'',	--RouteLegStatusCode
							'',	--RouteNumber
							'',	--ScanTypeCode
							'',	--ServiceCode
							'',	--SortDate
							'',	--StagingArea
							'',	--TravelStatus
							'');	--UploadStatus_FedExShuttleFlag
	globalPrint PRINT_INFO := PRINT_INFO('',--ADDRESS_CHANGE_FLIGHT_DATE
					'',	--ADDRESS_CHANGE_FLIGHT_NUMBER
					'',	--ADDRESS_CHANGE_NAME
					'',	--ADDRESS_CHANGE_COMPANY
					'',	--ADDRESS_CHANGE_ADDRESS1
					'',	--ADDRESS_CHANGE_ADDRESS2
					'',	--ADDRESS_CHANGE_COMMENTS
					'',	--ADDRESS_CHANGE_DATE
					'',	--BrokerEntryNumber
					'',	--ClearanceStatus
					'',	--DateStr
					'',	--FLGHTDATA
					'',	--HandlingCode1
					'',	--HandlingCode2
					'',	--HandlingCode3
					'',	--MAWB
					'',	--PieceQuantity
					'',	--PiorityIntercept1
					'',	--PiorityIntercept2
					'',	--PiorityIntercept3
					'',	--PiorityIntercept4
					'',	--ServiceCode
					'');	--TimeStr

	globalPieceArray typePieceArray;
	globalActivityCodeArray typeActivityCodeArray;
	globalConsArray typeConsArray;
	globalDupPieceArray typeDupPieceArray; 
	
FUNCTION readConfig(locParamName VARCHAR2,
		locLocationCode VARCHAR2,
		locDefaultValue VARCHAR2)
		RETURN VARCHAR2; 
PROCEDURE debugMsg(msg VARCHAR2); 
FUNCTION MatchString(
	locStr VARCHAR2,
	locStrKey VARCHAR2
)
RETURN NUMBER; 
FUNCTION splitFunc(
	locStr IN VARCHAR2,
	locSep IN VARCHAR2
)
RETURN VARCHAR_ARRAY; 
PROCEDURE findTrackingNumber; 
PROCEDURE findHandlingUnit; 
PROCEDURE shipmentOverage; 
PROCEDURE handlingUnitOverage; 
PROCEDURE createIntercept; 
PROCEDURE getContext; 
PROCEDURE overageHandling; 
PROCEDURE duplicateProcessing; 
PROCEDURE verifyRoute; 
PROCEDURE VerifyArrivalScan; 
PROCEDURE verifyDepartureScan; 
PROCEDURE findExternalShipmentInfoAPAC; 
PROCEDURE verifyAddressChange; 
PROCEDURE verifyRelease; 
PROCEDURE verifyShuttle; 
PROCEDURE createAccsShipStatus; PROCEDURE findIntercept; 
PROCEDURE findInboundRouteInfo; 
PROCEDURE findHandlingInstruction; 
PROCEDURE FindMPSShortage; 
PROCEDURE FindShuttle; 
PROCEDURE createConsSummary; 
PROCEDURE createCons; 
PROCEDURE removeCons; 
PROCEDURE FindCons(locCh VARCHAR2); 
PROCEDURE findConsSummary; 
PROCEDURE verifyShipmentInCons; 
PROCEDURE findStationId; 
PROCEDURE verifyStationId; 
PROCEDURE postScan; 
PROCEDURE postActivity; 
PROCEDURE outputLabel(locLabelType IN VARCHAR2); 
PROCEDURE outputVariable; 
PROCEDURE showResult; 
PROCEDURE bondin (AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2); 
PROCEDURE bondout (AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2); 
PROCEDURE batchAdd (AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2); 
PROCEDURE batchRemove (AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2); 
PROCEDURE deSelect (AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2); 
PROCEDURE cage (AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2,
	Comments IN VARCHAR2); 
PROCEDURE queryScan(AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2); 
PROCEDURE corpScan(AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2,
	SComment IN VARCHAR2,
	AuthorizedBy IN VARCHAR2,
	DestLocationCode IN VARCHAR2,
	LocStatNbr IN VARCHAR2); 
PROCEDURE handlingUnderbond;

END scanclient;
/
SHOW ERRORS



/* ########################### Body ############################ */
CREATE OR REPLACE PACKAGE BODY scanclient AS

 FUNCTION readConfig(locParamName VARCHAR2,
        locLocationCode VARCHAR2,
        locDefaultValue VARCHAR2)       
        RETURN VARCHAR2                 
AS                                      
        locParamValue VARCHAR2(64);
        locRecordCount NUMBER;                          
BEGIN   
        SELECT
                COUNT(*)
        INTO
                locRecordCount
        FROM    
                ENT.SITE_PARAM
        WHERE
                LOCATION_CD = locLocationCode
                AND PARAM_NM = locParamName
                AND GROUP_NM = 'SCANCLIENT';
        IF locRecordCount != 1 THEN
                debugMsg('configuration data not available for ' || locParamName);
                RETURN locDefaultValue;
        ELSE
                SELECT
                        PARAM_VALUE
                INTO
                        locParamValue
                FROM
                        ENT.SITE_PARAM
                WHERE
                        LOCATION_CD = locLocationCode
                        AND PARAM_NM = locParamName
                        AND GROUP_NM = 'SCANCLIENT';
                RETURN locParamValue;
        END IF;
END readConfig; 


PROCEDURE debugMsg(msg VARCHAR2)
AS
BEGIN
        DBMS_OUTPUT.PUT_LINE('[' || TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') || '] - ' || msg);
END debugMsg; 
FUNCTION MatchString(
	locStr VARCHAR2,
	locStrKey VARCHAR2
)
RETURN NUMBER
IS
	locTmp VARCHAR2(100);
	locTmpKey VARCHAR2(50);
BEGIN
	debugMsg('MatchString');
	locTmp := ',' || locStr || ',';
	locTmpKey := ',' || locStrKey || ',';
	IF INSTR(locTmp,locStrKey)>0 THEN
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
END MatchString; 
FUNCTION splitFunc(
	locStr IN VARCHAR2,
	locSep IN VARCHAR2
)
RETURN VARCHAR_ARRAY
IS
	locPsn PLS_INTEGER;
	locCounter PLS_INTEGER := 1;
	locTmpStr VARCHAR2(255) := locStr;
	locVarcharArray VARCHAR_ARRAY;
BEGIN
	LOOP
		locPsn := INSTR(locTmpStr,locSep);
		EXIT WHEN locPsn IS NULL OR locPsn = 0;
		locVarcharArray(locCounter) := SUBSTR(locTmpStr,1,locPsn - 1);
		locTmpStr := SUBSTR(locTmpStr,locPsn + 1);
		locCounter := locCounter + 1;
	END LOOP;
	locVarcharArray(locCounter) := locTmpStr;
	
	RETURN locVarcharArray;
	
END splitFunc; 


PROCEDURE findTrackingNumber
AS
	
	locRowCount NUMBER := 0;
	locRouteNumber VARCHAR2(6);
	locRouteDate DATE;
	locRouteLegNumber NUMBER(2,0);
	locCRNFlag BOOLEAN := FALSE;
	locMPSFlag BOOLEAN := FALSE;
	locPieceRecord ENT.PIECE%ROWTYPE;
	locPieceCCRecord ENT.PIECE_CORE_CLEARANCE%ROWTYPE;
	TYPE typePieceCCArray IS TABLE OF  ENT.PIECE_CORE_CLEARANCE%ROWTYPE INDEX BY BINARY_INTEGER;
	locPieceCCArray typePieceCCArray;

	TYPE typeShipmentRecord IS RECORD (SHIPMENT_OID_NBR NUMBER(10,0),
					DEST_LOCATION_CD VARCHAR2(5),
					AWB_NBR VARCHAR2(12),
					SHIPMENT_DT DATE,
					PIECE_QTY NUMBER(7,0),
					SERVICE_CD VARCHAR2(5),
					FORM_TYPE_CD VARCHAR2(4),
					INTRANSIT_FLG VARCHAR2(1),	
					RETURN_AWB_NBR VARCHAR2(12),
					OVERAGE_REASON_CD VARCHAR2(1),
					LATE_ARRIVAL_FLG VARCHAR2(1),
					CUSTOMS_RELEASE_FLG VARCHAR2(1),
					DECLARED_FLG VARCHAR2(1),
					BROKER_ENTRY_NBR VARCHAR2(14),
					KILO_WGT NUMBER(9,1),
					DEST_CUSTOMS_VALUE_AMT NUMBER(14,2)
					);

	locShipmentRecord typeShipmentRecord;

BEGIN
	debugMsg('------ findTrackingNumber ------'); 
	FOR tmpPieceRecord IN (
		SELECT
			*
		FROM
			ENT.PIECE
		WHERE
			TRACKING_NBR = globalScan.AirbillNumber
			AND PIECE_STATUS_CD = 'A'
	)
	LOOP
		DECLARE
			END_LOOP EXCEPTION;
		BEGIN
			SELECT
				COUNT(*)
			INTO
				locRowCount
			FROM
				ENT.SHIPMENT
			WHERE
				ENT.SHIPMENT.SHIPMENT_OID_NBR = tmpPieceRecord.SHIPMENT_OID_NBR;
			IF locRowCount = 0 THEN
				RAISE END_LOOP;
			END IF;

			BEGIN
				SELECT
                			ROUTE_NBR,ROUTE_LEG_NBR,ROUTE_DT
        			INTO
                			locRouteNumber, locRouteLegNumber, locRouteDate
        			FROM
                			ENT.PIECE_ROUTE_LEG
        			WHERE
                			PIECE_ROUTE_LEG.PIECE_OID_NBR = tmpPieceRecord.PIECE_OID_NBR;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				RAISE END_LOOP;
			END;

			SELECT
				count(*)
			INTO
				locRowCount
			FROM
				ENT.ROUTE_LEG
			WHERE
				ROUTE_LEG.ROUTE_NBR = locRouteNumber
				AND ROUTE_LEG.ROUTE_DT = locRouteDate
				AND ROUTE_LEG.ROUTE_LEG_NBR = locRouteLegNumber
				AND ROUTE_LEG.SORT_DT BETWEEN TRUNC(SYSDATE_GMT + (globalScan.TimeZone/24)) - 					TO_NUMBER(readConfig('MAX_CAGE_DAYS',globalScan.LocationCode,'30'))
				AND TRUNC(SYSDATE_GMT + (globalScan.TimeZone/24) +0.9999);
			IF locRowCount = 0 THEN
				RAISE END_LOOP;
			END IF;

			BEGIN
				SELECT
					*
				INTO
					locPieceCCArray(locPieceCCArray.COUNT+1)
				FROM
					ENT.PIECE_CORE_CLEARANCE
				WHERE
					ENT.PIECE_CORE_CLEARANCE.PIECE_OID_NBR = tmpPieceRecord.PIECE_OID_NBR
					AND ENT.PIECE_CORE_CLEARANCE.LOCATION_CD = globalScan.LocationCode;
					-- AND LOCATION_CD in ( LocationList )		
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					RAISE END_LOOP;
			END;		
			globalPieceArray(globalPieceArray.COUNT+1) := tmpPieceRecord;
		EXCEPTION
			WHEN END_LOOP THEN
				NULL;
		END;
	END LOOP;	

	debugMsg('PieceArray.Count: ' || globalPieceArray.COUNT);
	IF globalPieceArray.COUNT = 0 THEN
		debugMsg('No PieceRecord for Tracking Number' || globalScan.AirbillNumber);
		globalCCIIState.OverageFlag := 'Y';
		IF globalScan.AirbillFormType IS NULL THEN
			globalScan.ErrorNumber := 89; --EM_FORM_ID_REQD
			RAISE_APPLICATION_ERROR(-20089,'EM_FORM_ID_REQD');
		END IF;
		IF globalScan.AirbillType = 'G' OR globalScan.AirbillType = '6' OR globalScan.AirbillFormType = '0491'
		OR globalScan.AirbillFormType = '491' THEN
			locCRNFlag := TRUE;
			debugMsg('CRNFlag: TRUE');
		ELSE
			locCRNFlag := FALSE;
			debugMsg('CRNFlag: FALSE');
		END IF;
		IF locCRNFlag AND readConfig(CONCAT(globalScan.InViewName ,'_ALLOW_MPS_OVERAGE_PROCESSING'),
			globalScan.LocationCode,'N') = 'Y' THEN
			globalScan.InterceptCode := readConfig('MPSOVERAGE_INTERCEPT_CODE',globalScan.LocationCode,NULL);
			globalScan.AgencyCode := readConfig('MPSOVERAGE_AGENCY_CODE',globalScan.LocationCode,NULL);
		ELSE
			globalScan.InterceptCode := readConfig('OVERAGE_INTERCEPT_CODE',globalScan.LocationCode,NULL);
			globalScan.AgencyCode := readConfig('OVERAGE_AGENCY_CODE',globalScan.LocationCode,NULL);
		END IF;
		IF locCRNFlag AND globalScan.HAWB IS NULL THEN
			globalScan.HAWB := readConfig('DEFAULT_HAWB',globalScan.LocationCode,'0');
			globalScan.HAWBFormType := readConfig('DEFAULT_HAWB_FORM_TYPE',globalScan.LocationCode,'430');
			globalScan.ErrorNumber := 2; --IM_OVERAGE
			--noMasterOverage;
		ELSE
			IF globalCCIIState.TravelStatus = 'D' THEN
				globalScan.ErrorNumber := 20; --IM_DOMESTIC_PKG
			ELSE
				globalScan.ErrorNumber := 2; --IM_OVERAGE
			END IF;
		END IF;
		RETURN;
	END IF;

	IF globalPieceArray.COUNT > 1 THEN
		globalCCIIState.DuplicateFlag := 'Y';
		debugMsg('Duplicate Piece Found');
		globalScan.ErrorNumber := 4; --IM_DUP
		RETURN;
	ELSE
		locPieceRecord := globalPieceArray(1);
		locPieceCCRecord := locPieceCCArray(1);
	END IF;

	--The fetch returned a single record or a single active record if its not active then its an overage
	globalScan.HandlingUnitOid := locPieceRecord.PIECE_OID_NBR;
	globalScan.ShipmentOid := locPieceRecord.SHIPMENT_OID_NBR;

	IF NOT locPieceCCArray.EXISTS(1) THEN
		debugMsg('No Core Clearance data, this is a mis-sort');
		globalCCIIState.OverageFlag := readConfig('MISORT_STATE_OVERAGE_FLAG',globalScan.LocationCode,'S');
	END IF;

	debugMsg('Getting Shipment Record by ShipmentOid: ' || globalScan.ShipmentOid);

	BEGIN
		SELECT
			S.SHIPMENT_OID_NBR SHIPMENT_OID_NBR,
			S.DEST_LOCATION_CD DEST_LOCATION_CD,
			S.AWB_NBR AWB_NBR,
			S.SHIPMENT_DT SHIPMENT_DT,
			S.PIECE_QTY PIECE_QTY,
			S.SERVICE_CD SERVICE_CD,
			S.FORM_TYPE_CD FORM_TYPE_CD,
			SCC.INTRANSIT_FLG INTRANSIT_FLG,
			SCC.RETURN_AWB_NBR RETURN_AWB_NBR,
			SCC.OVERAGE_REASON_CD OVERAGE_REASON_CD,
			SCC.LATE_ARRIVAL_FLG LATE_ARRIVAL_FLG,
			SCC.CUSTOMS_RELEASE_FLG CUSTOMS_RELEASE_FLG,
			SCC.DECLARED_FLG DECLARED_FLG,
			SCC.BROKER_ENTRY_NBR BROKER_ENTRY_NBR,
			S.KILO_WGT,
			S.DEST_CUSTOMS_VALUE_AMT
		INTO
			locShipmentRecord
		FROM
			ENT.SHIPMENT S, ENT.SHIPMENT_CORE_CLEARANCE SCC
		WHERE
			S.SHIPMENT_OID_NBR = SCC.SHIPMENT_OID_NBR(+)
			AND SCC.LOCATION_CD(+) = globalScan.LocationCode
			AND S.SHIPMENT_OID_NBR = globalScan.ShipmentOid;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			globalScan.ErrorNumber := 57; --EM_DB_ERROR
			debugMsg('No ShipmentRecord found');
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
	END;

	globalScan.HAWB := locShipmentRecord.AWB_NBR;
	globalScan.HAWBFormType := locShipmentRecord.FORM_TYPE_CD;
	globalScan.CustomsReleasedFlag := locShipmentRecord.CUSTOMS_RELEASE_FLG;
	globalScan.DeclarationCode := locShipmentRecord.DECLARED_FLG;
	globalPrint.PieceQuantity := locShipmentRecord.PIECE_QTY;
	globalPrint.BrokerEntryNumber := locShipmentRecord.BROKER_ENTRY_NBR;
	globalPrint.ServiceCode := locShipmentRecord.SERVICE_CD;

	--customized for china
	globalScan.ShipmentWeight := locShipmentRecord.KILO_WGT;
	globalScan.ShipmentValueAmt := locShipmentRecord.DEST_CUSTOMS_VALUE_AMT;

	IF locPieceCCRecord.OVERAGE_REASON_CD = 'M' AND locPieceRecord.PIECE_STATUS_CD IS NULL THEN
		globalCCIIState.OverageFlag := 'M';
		IF globalCCIIState.TravelStatus = 'D' THEN
			globalScan.ErrorNumber := 20; --IM_DOMESTIC_PKG
		ELSE
			globalScan.ErrorNumber := 2; -- IM_OVERAGE
		END IF;
	END IF;

	globalCCIIState.IsPieceManifested := locPieceRecord.MANIFEST_STATUS_CD;

	IF locCRNFlag AND locPieceRecord.TRACKING_NBR != locShipmentRecord.AWB_NBR THEN
		locMPSFlag := TRUE;
		globalCCIIState.MPSFlag := 'CRN';
	ELSIF locShipmentRecord.PIECE_QTY > 1 AND locPieceRecord.TRACKING_NBR = locShipmentRecord.AWB_NBR THEN
		locMPSFlag := TRUE;
		globalCCIIState.MPSFlag := 'MASTER';
	END IF;
	IF locMPSFlag AND ( readConfig('SITE_MPS_STAGING',globalScan.LocationCode,NULL) = 'ALWAYS' OR
			readConfig('SITE_MPS_STAGING',globalScan.LocationCode,NULL) = 'INTERCEPT' ) THEN
		IF globalScan.InterceptCode IS NULL AND globalScan.AgencyCode IS NULL THEN
			globalScan.InterceptCode := readConfig('MPS_INTERCEPT_CODE',globalScan.LocationCode,NULL);
			globalScan.AgencyCode := readConfig('MPS_AGENCY_CODE',globalScan.LocationCode,NULL);
		END IF;
	END IF;
	IF locShipmentRecord.INTRANSIT_FLG IS NULL OR locShipmentRecord.INTRANSIT_FLG = 'N' THEN
		globalCCIIState.IntransitFlag := 'N';
	ELSE
		globalCCIIState.IntransitFlag := 'Y';
		globalScan.ErrorNumber := 24; --IM_INTRANSIT
	END IF;
	globalCCIIState.ServiceCode := locShipmentRecord.SERVICE_CD;

	--Insure that the Form Type has a valid entry
	IF globalScan.AirbillFormType IS NULL OR globalScan.AirbillFormType = 0 THEN
		IF globalCCIIState.MPSFlag = 'CRN' THEN
			globalScan.AirbillFormType := readConfig('DEFAULT_MPS_FORM_TYPE',globalScan.LocationCode,'0440');
		ELSE
			globalScan.AirbillFormType := readConfig('DEFAULT_FORM_TYPE',globalScan.LocationCode,'0400');
		END IF;
	END IF;
		
END findTrackingNumber; 
PROCEDURE findHandlingUnit
AS
	locOverageReasonCode	VARCHAR2(1);
	locPieceStatusCode	VARCHAR2(1);
BEGIN
	debugMsg('------ findHandlingUnit ------');
	SELECT
		P.SHIPMENT_OID_NBR,
		P.TRACKING_NBR,
		P.FORM_TYPE_CD,
		P.PIECE_STATUS_CD,
		PCC.OVERAGE_REASON_CD
	INTO
		globalScan.ShipmentOid,
		globalScan.AirbillNumber,
		globalScan.AirbillFormType,
		locPieceStatusCode,
		locOverageReasonCode
	FROM
		ENT.PIECE P, ENT.PIECE_CORE_CLEARANCE PCC
	WHERE
		P.PIECE_OID_NBR = PCC.PIECE_OID_NBR(+)
		AND PCC.LOCATION_CD(+) = globalScan.LocationCode
		AND P.PIECE_OID_NBR = globalScan.HandlingUnitOid;
	globalCCIIState.AbScanned := globalScan.AirbillNumber;
	globalCCIIState.AbFormTypeScanned := globalScan.AirbillFormType;

	SELECT
		S.AWB_NBR,
		S.FORM_TYPE_CD,
		SCC.CUSTOMS_RELEASE_FLG,
		SCC.DECLARED_FLG,
		S.PIECE_QTY,
		SCC.BROKER_ENTRY_NBR,
		S.SERVICE_CD
	INTO
		globalScan.HAWB,
		globalScan.HAWBFormType,
		globalScan.CustomsReleasedFlag,
		globalScan.DeclarationCode,
		globalPrint.PieceQuantity,
		globalPrint.BrokerEntryNumber,
		globalPrint.ServiceCode
	FROM
		ENT.SHIPMENT S, ENT.SHIPMENT_CORE_CLEARANCE SCC
	WHERE
		S.SHIPMENT_OID_NBR = SCC.SHIPMENT_OID_NBR(+)
		AND SCC.LOCATION_CD(+) = globalScan.LocationCode
		AND S.SHIPMENT_OID_NBR = globalScan.ShipmentOid;
		
	IF locOverageReasonCode = 'M' AND locPieceStatusCode IS NULL THEN
		debugMsg('Setting state OverageFlag to: M');
		globalCCIIState.OverageFlag := 'M';
		globalScan.ErrorNumber := 2;
	END IF;

END findHandlingUnit; 
PROCEDURE shipmentOverage
AS
	locShipmentOid NUMBER;
BEGIN
	debugMsg('------ shipmentOverage ---------'); 
	IF globalScan.AirbillType = 'G' OR globalScan.AirbillType = '6' OR globalScan.AirbillFormType = '0491'
	OR globalScan.AirbillFormType = '491' THEN
		globalCCIIState.MPSFlag := 'CRN';
		globalScan.HAWB := '0';	--Default HAWB for CRN overage
	ELSE
		globalCCIIState.MPSFlag := 'MASTER';
		globalScan.HAWB := globalScan.AirbillNumber;
	END IF;

	SELECT
		ENT.SHIPMENT_SEQ.nextval
	INTO
		locShipmentOid
	FROM
		dual;
	debugMsg('Inserting Shipment record...');
	
	INSERT INTO ENT.SHIPMENT 
		(SHIPMENT_OID_NBR, 
		AWB_NBR, 
		SHIPMENT_DT, 
		PIECE_QTY, 
		HANDLING_UNIT_QTY, 
		CUSTOMS_VALUE_AMT, 
		CARRIAGE_VALUE_AMT, 
		SERVICE_CD, 
		ORIGIN_LOCATION_COUNTRY_CD, 
		DEST_LOCATION_COUNTRY_CD, 
		SHIPPER_COMPANY_NM, 
		SHIPPER_POSTAL_CD, 
		SHIPPER_COUNTRY_CD, 
		CONSIGNEE_COMPANY_NM, 
		CONSIGNEE_POSTAL_CD, 
		CONSIGNEE_STATE_CD, 
		CONSIGNEE_COUNTRY_CD, 
		SHIPMENT_DESC, 
		DEST_LOCATION_CD, 
		ORIGIN_LOCATION_CD, 
		FORM_TYPE_CD, 
		TRANSACTION_SEQ_TMSTP, 
		MANIFEST_STATUS_CD, 
		SDL_FLG, 
		HAL_FLG, 
		DUTIABLE_FLG, 
		SPECIAL_HANDLING1_CD, 
		SPECIAL_HANDLING2_CD, 
		SPECIAL_HANDLING3_CD, 
		SPECIAL_HANDLING4_CD, 
		SPECIAL_HANDLING5_CD, 
		LOCAL_CUSTOMS_VALUE_AMT, 
		HAWB_SERIAL_NBR, 
		ROD_FLG ) 
	VALUES(
		locShipmentOid,
		globalScan.HAWB,
		SYSDATE_GMT,
		1,	
		0,
		0.0,
		0.0,
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		globalScan.AirbillFormType,
		SYSDATE_GMT,
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		0.0,
		'0',
		'N' );
	
	debugMsg('Inserting Shipment_Core_Clearance record...');
	INSERT INTO ENT.SHIPMENT_CORE_CLEARANCE 
		(SHIPMENT_OID_NBR, 
		RETURN_AWB_NBR, 
		LAST_MODIFIED_NM, 
		LAST_MODIFIED_TMSTP, 
		OVERAGE_REASON_CD, 
		BROKER_ENTRY_NBR, 
		CUSTOMS_REVIEWED_FLG, 
		CUSTOMS_RELEASE_FLG, 
		LOCATION_CD, 
		DECLARED_FLG, 
		DECLARED_TRANSACTION_SEQ_TMSTP, 
		INTRANSIT_FLG, 
		ENTRY_CATEGORY_TYPE_CD, 
		ENTRY_CATEGORY_SOURCE_CD ) 
	VALUES
		(locShipmentOid,
		'',
		'',
		SYSDATE_GMT,
		'M',
		'',
		'N',
		'N',
		globalScan.LocationCode,
		'N',
		SYSDATE_GMT,
		'N',
		'',
		'');
	IF globalScan.ShipmentOid = 0 THEN
		globalScan.ShipmentOid := locShipmentOid;
	END IF;
	
EXCEPTION
	WHEN OTHERS THEN
		debugMsg('EXCEPTION: Insert Shipment or Shipment_Core_Clearance failed');
		debugMsg('EXCEPTION: sql exception. SQLcode=' || SQLCODE);
		debugMsg(SQLERRM);
		ROLLBACK;
END shipmentOverage; 
PROCEDURE handlingUnitOverage
AS
	locPieceOid NUMBER;
BEGIN
	debugMsg('------ handlingUnitOverage ---------'); 
	globalScan.DetainLoc := globalScan.LocationCode;
	SELECT
		ENT.PIECE_SEQ.nextval
	INTO
		locPieceOid
	FROM
	dual;
	debugMsg('Inserting Piece record ...');
	
	INSERT INTO ENT.PIECE
		(PIECE_OID_NBR,
		TRACKING_NBR,
		FORM_TYPE_CD,
		SHIPMENT_OID_NBR,
		HARMONIZED_TARIFF_NBR,
		TOTAL_WGT,
		TOTAL_WGT_TYPE_CD,
		CUSTOMS_VALUE_AMT,
		ENTRY_STATUS_CD,
		AWB_TYPE_CD,
		LAST_MODIFIED_NM,
		TRANSACTION_SEQ_TMSTP,
		MANIFEST_STATUS_CD,
		PIECE_STATUS_CD,
		DEST_LOCATION_CD)
	VALUES
		(locPieceOid,
		globalScan.AirbillNumber,
		globalScan.AirbillFormType,
		globalScan.ShipmentOid,
		'',
		0.0,
		'',
		0.0,
		'',
		'',
		'SCANCLIENT',
		SYSDATE_GMT,
		'',
		'',
		'');
	
	INSERT INTO ENT.PIECE_CORE_CLEARANCE
		(PIECE_OID_NBR, 
		ENTRY_CATEGORY_TYPE_CD, 
		RECURRING_SELECT_FLG, 
		LAST_MODIFIED_TMSTP, 
		LAST_USER_NM, 
		DETAINMENT_LOCATION_CD, 
		OVERAGE_REASON_CD, 
		STAGING_AREA_CD, 
		LOCATION_CD, 
		WAREHOUSE_CD, 
		SHUTTLE_CD ) 
	VALUES
		(locPieceOid,
		'', 
		'',
		SYSDATE_GMT,
		globalScan.UserLogon, 
		globalScan.DetainLoc, 
		'M',
		'', 
		globalScan.LocationCode, 
		'', 
		'');
	IF globalScan.HandlingUnitOid = 0 THEN
		globalScan.HandlingUnitOid := locPieceOid;
	END IF;
	
EXCEPTION
	WHEN OTHERS THEN
		debugMsg('EXCEPTION: Insert Piece or Piece_Core_Clearance failed');
		debugMsg('EXCEPTION: sql exception. SQLcode=' || SQLCODE);
		debugMsg(SQLERRM);
		ROLLBACK;
END handlingUnitOverage; 
PROCEDURE createIntercept
AS
	locCount NUMBER;
	locInterceptOid NUMBER;
BEGIN
	debugMsg('------ createIntercept ------');
	IF globalScan.InterceptCode IS NULL OR globalScan.AgencyCode IS NULL THEN
		debugMsg('InterceptCode or AgencyCode is null, should not call createIntercept.');
		RETURN;
	END IF;

	SELECT
		COUNT(*)
	INTO
		locCount
	FROM
		ENT.INTERCEPT_DEF
	WHERE
		INTERCEPT_CD = globalScan.InterceptCode
		AND LOCATION_CD = globalScan.LocationCode
		AND RECORD_ACTIVE_FLG = 'Y';
	IF locCount != 1 THEN
		debugMsg('Intercept definition does not exist');
		globalScan.ErrorNumber := 105;
		RAISE_APPLICATION_ERROR(-20105,'INTERCEPT_ERROR');
	END IF;
	
	SELECT
		COUNT(*)
	INTO
		locCount
	FROM
		ENT.INTERCEPT
	WHERE
		INTERCEPT_LOCATION_CD = AGENCY_LOCATION_CD
		AND SHIPMENT_OID_NBR = globalScan.ShipmentOid
		AND ACTIVE_FLG = 'Y'
		AND AGENCY_CD = globalScan.AgencyCode
		AND INTERCEPT_CD = globalScan.InterceptCode
		AND INTERCEPT_LOCATION_CD = globalScan.LocationCode;
	IF locCount = 1 THEN
		debugMsg('Agency/Intercept/Shipment combo already exists, doing nothing');
		RETURN;
	END IF;

	BEGIN
		SELECT
			ENT.INTERCEPT_SEQ.nextval
		INTO
			locInterceptOid
		FROM
        		dual;
		debugMsg('Inserting Intercept record, InterceptOid: ' || locInterceptOid);
		INSERT INTO ENT.INTERCEPT 
			(INTERCEPT_OID_NBR,
			SHIPMENT_OID_NBR,
			PIECE_OID_NBR,
			INTERCEPT_CD,
			ACTIVE_FLG,
			CREATE_DT,
			CREATE_EMPLOYEE_NBR,
			CREATE_PROCESS_NM,
			UPDATE_DT,
			UPDATE_EMPLOYEE_NBR,
			UPDATE_PROCESS_NM,
			STAGING_AREA_PRIORITY_NBR,
			TRANSACTION_DT,
			AGENCY_CD,
			RECURRING_CRITERIA_FLG,
			INTERCEPT_LOCATION_CD,
			AGENCY_LOCATION_CD)
		VALUES
        		(locInterceptOid,
			globalScan.ShipmentOid,
			globalScan.HandlingUnitOid,
			globalScan.InterceptCode,
			'Y',
			SYSDATE_GMT,
			globalScan.UserLogon,
			'ScanClient',
			SYSDATE_GMT,
			'',
			'',
			0,
			SYSDATE_GMT,
			globalScan.AgencyCode,
			'Y',
			globalScan.LocationCode,
			globalScan.LocationCode);
	EXCEPTION
		WHEN OTHERS THEN
			debugMsg('create intercept record failed');
			ROLLBACK;
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
	END;

	--SCANCLIENT.AddActivity(readConfig('INTERCEPT_CREATED_ACTIVITY_CODE',globalScan.LocationCode,'INT_AS'),globalScan.InterceptCode,globalScan.AgencyCode);
	locCount := globalActivityCodeArray.COUNT;
	globalActivityCodeArray(locCount+1) := (readConfig('INTERCEPT_CREATED_ACTIVITY_CODE',globalScan.LocationCode,'INT_AS') || '|' ||						globalScan.InterceptCode || '|' || globalScan.AgencyCode);
	globalCCIIState.InterceptedFlag := 'Y';
	
	IF globalScan.ErrorNumber != 1 AND globalScan.ErrorNumber != 2 THEN
		globalScan.ErrorNumber := 6;
		globalScan.ReturnMessage := 'INTERCEPT';
	END IF;

END createIntercept; 
PROCEDURE getContext
AS

BEGIN
	debugMsg('------ getContext ------');
	IF globalScan.HAWBUnknownFlag = 'T' THEN
		--SCANCLIENT.noMasterOverage;
		RETURN;
	END IF;

	IF globalScan.ShipmentOid != 0 THEN
		debugMsg('Error:ShipmentOid not 0,quiting...');
		globalScan.ErrorNumber := 61;
		RAISE_APPLICATION_ERROR(-20061, 'START_OVER');
	END IF;

	IF globalScan.HAWB IS NOT NULL THEN
		--SCANCLIENT.masterOverage;
		RETURN;
	END IF;

	IF globalScan.HandlingUnitOid != 0 THEN
		debugMsg('HandlingUnitOid not 0, finding HandlingUnit...');
		SCANCLIENT.findHandlingUnit;
		RETURN;
	END IF;

	IF globalScan.ConsId IS NOT NULL AND globalScan.AirbillNumber IS NULL THEN
		debugMsg('ConsId ' || globalScan.ConsId || ' scanned');
		SELECT
			CONS_OID_NBR,
			TRACKING_NBR,
			FORM_TYPE_CD,
			CONS_NBR,
			CONS_FORM_TYPE_CD,
			OID_NBR,
			OID_TYPE_CD	BULK COLLECT
		INTO
			globalConsArray
		FROM
			CCII.CONS
		WHERE
			CONS_NBR = globalScan.ConsId
			AND DECONS_FLG IS NULL;
		IF globalConsArray.EXISTS(1) THEN
			globalScan.AirbillNumber := globalConsArray(1).TRACKING_NBR;	
			globalScan.AirbillFormType := globalConsArray(1).FORM_TYPE_CD;	
			globalCCIIState.AbScanned := globalScan.AirbillNumber;
			globalCCIIState.AbFormTypeScanned := globalScan.AirbillFormType;
		ELSE
			debugMsg('Cons not found');
			globalScan.ErrorNumber := 77;
			RAISE_APPLICATION_ERROR(-20077,'CONS_NOT_FOUND');
		END IF;
	END IF;

	IF globalScan.AirbillNumber IS NULL THEN
		debugMsg('Error: No AirbillNumber, quiting...');
		globalScan.ErrorNumber := 61;
		RAISE_APPLICATION_ERROR(-20061, 'START_OVER');
	END IF;

	IF globalScan.HandlingUnitOid = 0 THEN
		debugMsg('HandingUnitOid is 0, finding tracking number');
		SCANCLIENT.findTrackingNumber;
	END IF;

	debugMsg('Done, quiting getContext');
		
END getContext; 
PROCEDURE overageHandling
AS
	locCount	NUMBER(2,0);
BEGIN
	IF globalCCIIState.OverageFlag IS NULL THEN
		debugMsg('Overage Flag is null, should not call overageHandling');
		RETURN;
	END IF;

	SELECT
		COUNT(*)
	INTO
		locCount
	FROM
		ENT.PIECE
	WHERE
		TRACKING_NBR = globalScan.AirbillNumber
		AND LAST_MODIFIED_NM = 'SCANCLIENT'
		AND PIECE_TYPE_CD IS NULL
		AND TRANSACTION_SEQ_TMSTP > SYSDATE_GMT - TO_NUMBER(readConfig('MAX_CAGE_DAYS',globalScan.LocationCode,'30'));
	
	IF locCount > 0 AND (globalScan.ShipmentOid = 0 OR globalScan.HandlingUnitOid = 0 ) THEN
		SELECT
			PIECE_OID_NBR,
			SHIPMENT_OID_NBR
		INTO
			globalScan.HandlingUnitOid,
			globalScan.ShipmentOid
		FROM
			ENT.PIECE
		WHERE
			TRACKING_NBR = globalScan.AirbillNumber
			AND LAST_MODIFIED_NM = 'SCANCLIENT'
			AND PIECE_TYPE_CD IS NULL
			AND TRANSACTION_SEQ_TMSTP > SYSDATE_GMT - TO_NUMBER(readConfig('MAX_CAGE_DAYS',globalScan.LocationCode,'30'))
			AND ROWNUM < 2;
	END IF;
		

	$IF $$COUNTRY=3 $THEN
		CASE globalCCIIState.OverageFlag
		WHEN 'Y' THEN
			IF locCount = 0 THEN
				debugMsg('Creating shell records...');
				SCANCLIENT.shipmentOverage;
				SCANCLIENT.handlingUnitOverage;
			END IF;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		WHEN 'M' THEN
			globalScan.InterceptCode := readConfig('OVERAGE_INTERCEPT_CODE',globalScan.LocationCode,'O');
			globalScan.AgencyCode := readConfig('OVERAGE_AGENCY_CODE',globalScan.LocationCode,'O');
			SCANCLIENT.createIntercept;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		WHEN 'SORTDATE' THEN
			IF locCount = 0 THEN
				debugMsg('Creating shell records...');
				SCANCLIENT.shipmentOverage;
				SCANCLIENT.handlingUnitOverage;
			END IF;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		ELSE
			IF globalScan.ShipmentOid = 0 THEN
				SCANCLIENT.shipmentOverage;
			END IF;
			IF globalScan.handlingUnitOid = 0 THEN
				SCANCLIENT.handlingUnitOverage;
			END IF;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		END CASE;
	$ELSE
		CASE globalCCIIState.OverageFlag
		WHEN 'Y' THEN
			IF locCount = 0 THEN
				debugMsg('Creating shell records...');
				SCANCLIENT.shipmentOverage;
				SCANCLIENT.handlingUnitOverage;
			END IF;
			globalScan.InterceptCode := readConfig('OVERAGE_INTERCEPT_CODE',globalScan.LocationCode,'O');
			globalScan.AgencyCode := readConfig('OVERAGE_AGENCY_CODE',globalScan.LocationCode,'O');
			--SCANCLIENT.createIntercept;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		WHEN 'M' THEN
			globalScan.InterceptCode := readConfig('OVERAGE_INTERCEPT_CODE',globalScan.LocationCode,'O');
			globalScan.AgencyCode := readConfig('OVERAGE_AGENCY_CODE',globalScan.LocationCode,'O');
			--SCANCLIENT.createIntercept;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		WHEN 'SORTDATE' THEN
			IF locCount = 0 THEN
				debugMsg('Creating shell records...');
				SCANCLIENT.shipmentOverage;
				SCANCLIENT.handlingUnitOverage;
			END IF;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		ELSE
			IF globalScan.ShipmentOid = 0 THEN
				SCANCLIENT.shipmentOverage;
			END IF;
			IF globalScan.handlingUnitOid = 0 THEN
				SCANCLIENT.handlingUnitOverage;
			END IF;
			SCANCLIENT.outputLabel('Y');
			globalScan.ErrorNumber := 2;
		END CASE;
	$END


END overageHandling; 
PROCEDURE duplicateProcessing
AS
BEGIN
	debugMsg('------ duplicateProcessing ------');
	IF globalPieceArray.EXISTS(2) THEN
		FOR indx IN globalPieceArray.FIRST .. globalPieceArray.LAST
		LOOP
			globalDupPieceArray(indx).PIECE_OID_NBR := globalPieceArray(indx).PIECE_OID_NBR;
			SELECT
				PIECE_QTY,
				LBS_WGT,
				CONSIGNEE_COMPANY_NM
			INTO	
				globalDupPieceArray(indx).PIECE_QTY,
				globalDupPieceArray(indx).LBS_WGT,
				globalDupPieceArray(indx).CONSIGNEE_COMPANY_NM
			FROM
				ENT.SHIPMENT
			WHERE
				SHIPMENT_OID_NBR = globalPieceArray(indx).SHIPMENT_OID_NBR;
			globalDupPieceArray(indx).CONSIGNEE_COMPANY_NM := REPLACE(globalDupPieceArray(indx).CONSIGNEE_COMPANY_NM,CHR(34),'');
			globalDupPieceArray(indx).CONSIGNEE_COMPANY_NM := REPLACE(globalDupPieceArray(indx).CONSIGNEE_COMPANY_NM,CHR(39),'');

			SELECT
				ROUTE_NBR,
				ROUTE_DT
			INTO
				globalDupPieceArray(indx).ROUTE_NBR,
				globalDupPieceArray(indx).ROUTE_DT
			FROM
				ENT.PIECE_ROUTE_LEG
			WHERE
				PIECE_OID_NBR = globalPieceArray(indx).PIECE_OID_NBR;

			SELECT
				COMMODITY_DESC
			INTO
				globalDupPieceArray(indx).COMMODITY_DESC
			FROM
				ENT.SHIPMENT_COMMODITY
			WHERE
				SHIPMENT_OID_NBR = globalPieceArray(indx).SHIPMENT_OID_NBR;
			globalDupPieceArray(indx).COMMODITY_DESC := REPLACE(globalDupPieceArray(indx).COMMODITY_DESC,CHR(34),'');
			globalDupPieceArray(indx).COMMODITY_DESC := REPLACE(globalDupPieceArray(indx).COMMODITY_DESC,CHR(39),'');
		END LOOP;		
		globalScan.ErrorNumber := 46;
		RAISE_APPLICATION_ERROR(-20046,'DUP_PIECE');
	ELSE
		debugMsg('Not duplicate Piece, why call me');
	END IF;

END duplicateProcessing; 
PROCEDURE verifyRoute
AS
BEGIN
	debugMsg('------ verifyRoute ------'); 
	BEGIN
		SELECT
			ROUTE_LEG.ROUTE_NBR,
			ROUTE_LEG.ROUTE_DT,
			ROUTE_LEG.ROUTE_LEG_NBR,
			ROUTE_LEG.SORT_DT,
			ROUTE_LEG.ROUTE_LEG_STATUS_CD,
			ROUTE_LEG.ROUTE_LEG_OID_NBR
		INTO
			globalCCIIState.RouteNumber,
			globalCCIIState.RouteDate,
			globalCCIIState.RouteLegNumber,
			globalCCIIState.SortDate,
			globalCCIIState.RouteLegStatusCode,
			globalCCIIState.RouteLegOid
		FROM
			ENT.ROUTE_LEG, ENT.PIECE_ROUTE_LEG
		WHERE
			PIECE_ROUTE_LEG.PIECE_OID_NBR = globalScan.HandlingUnitOid
			AND ROUTE_LEG.ROUTE_DT = PIECE_ROUTE_LEG.ROUTE_DT
			AND ROUTE_LEG.ROUTE_NBR = PIECE_ROUTE_LEG.ROUTE_NBR
			AND ROUTE_LEG.ROUTE_LEG_NBR = PIECE_ROUTE_LEG.ROUTE_LEG_NBR
			AND ROUTE_LEG.DEST_LOCATION_CD = globalScan.LocationCode;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			debugMsg('Route Number not found in DB');
			globalScan.ErrorNumber := 143;
			RAISE_APPLICATION_ERROR(-20143,'ROUTE_NOT_FND');
	END;

	IF TRUNC(globalCCIIState.SortDate)  TRUNC(SYSDATE_GMT + (globalScan.TimeZone/24))  THEN
		debugMsg('Sort Date Overage');
		globalCCIIState.OverageFlag := 'SORTDATE';
		$IF $$COUNTRY=3 $THEN
			SCANCLIENT.overageHandling;
		$ELSE
			SCANCLIENT.overageHandling;
			globalScan.AgencyCode := readConfig('DATEOVERAGE_AGENCY_CODE',globalScan.LocationCode,'');
			globalScan.InterceptCode := readConfig('DATEOVERAGE_INTERCEPT_CODE',globalScan.LocationCode,'');
		$END
	ELSE
		IF INSTR(readConfig('ROUTE_STATUS_OKAY_TO_SCAN_LIST',globalScan.LocationCode,'Y'), globalCCIIState.RouteLegStatusCode) = 0 THEN
			debugMsg('Route not closed');
			globalScan.ErrorNumber := 109;
			globalScan.ReturnMessage := CONCAT(globalCCIIState.RouteNumber,TO_CHAR(globalCCIIState.RouteDate +globalScan.TimeZone/24, 'DD-MON-YY'));
			RAISE_APPLICATION_ERROR(-20109,'ROUTE_NOT_CLOSED');
		END IF;
	END IF;

END verifyRoute; 
PROCEDURE VerifyArrivalScan
AS
	locCount Number(4,0);
BEGIN
	debugMsg('------ VerifyArrivalScan ------');
	IF globalScan.HandlingUnitOid = 0 THEN
		SELECT
			COUNT(*)
		INTO
			locCount
		FROM
			ENT.SCAN
		WHERE
			SCAN_TYPE_CD = readConfig('ARRIVAL_SCAN_NAME',globalScan.LocationCode,'BIN')
			AND SHIPMENT_OID_NBR = globalScan.ShipmentOid 
			AND LOCATION_CD = globalScan.LocationCode;
		IF locCount = 1 THEN
			SELECT
				SCAN_DT
			INTO
				globalScan.ArrivalDate
			FROM
				ENT.SCAN
			WHERE
				SCAN_TYPE_CD = readConfig('ARRIVAL_SCAN_NAME',globalScan.LocationCode,'BIN')
				AND SHIPMENT_OID_NBR = globalScan.ShipmentOid 
				AND LOCATION_CD = globalScan.LocationCode;
		END IF;
			
	ELSE
		SELECT
			COUNT(*)
		INTO
			locCount
		FROM
			ENT.SCAN
		WHERE
			SCAN_TYPE_CD = readConfig('ARRIVAL_SCAN_NAME',globalScan.LocationCode,'BIN')
			AND SHIPMENT_OID_NBR = globalScan.ShipmentOid 
			AND LOCATION_CD = globalScan.LocationCode
			AND PIECE_OID_NBR = globalScan.HandlingUnitOid;
		IF locCount = 1 THEN
			SELECT
				SCAN_DT
			INTO
				globalScan.ArrivalDate
			FROM
				ENT.SCAN
			WHERE
				SCAN_TYPE_CD = readConfig('ARRIVAL_SCAN_NAME',globalScan.LocationCode,'BIN')
				AND SHIPMENT_OID_NBR = globalScan.ShipmentOid 
				AND LOCATION_CD = globalScan.LocationCode
				AND PIECE_OID_NBR = globalScan.HandlingUnitOid;
		END IF;
	END IF;

	IF locCount = 0 THEN
		debugMsg('No scan record found');
		IF globalScan.InViewName = 'BOUTVIEW' OR globalScan.InViewName = 'QUERYVIEW' OR globalScan.InViewName = 'CONSVIEW' 
		OR globalScan.InViewName = 'CAGEVIEW' OR globalScan.InViewName = 'DESELECTVIEW' 
		AND readConfig('PREVIOUS_ARRIVAL_REQUIRED',globalScan.LocationCode,'TRUE') = 'TRUE' THEN
			--IF readConfig('NO_PREV_ARRIVAL_EXCEPT_ACTIVITY_CODE',globalScan.LocationCode,NULL) IS NOT NULL THEN
				--SCANCLIENT.addExceptionActivity(readConfig('NO_PREV_ARRIVAL_EXCEPT_ACTIVITY_CODE',globalScan.LocationCode,NULL));
			--END IF;
			globalScan.ErrorNumber := 129;
			RAISE_APPLICATION_ERROR(-20129,'NO_ARRIVAL_SCAN');
		END IF;
	ELSE
		IF globalScan.InViewName = 'BINVIEW' AND readConfig('PREVIOUS_ARRIVAL_ALLOWED',globalScan.LocationCode,'FALSE') = 'FALSE' THEN
			--IF readConfig('PREV_ARRIVAL_EXCEPT_ACTIVITY_CODE',globalScan.LocationCode,NULL) IS NOT NULL THEN
				--SCANCLIENT.addExceptionActivity(readConfig(PREV_ARRIVAL_EXCEPT_ACTIVITY_CODE',globalScan.LocationCode,NULL));
			--END IF;
		debugMsg('Error: PREVIOUS_ARRIVAL Found');
		globalScan.ErrorNumber := 127;
		RAISE_APPLICATION_ERROR(-20127,'PREVIOUS_ARRIVAL');
		END IF;
	END IF;
					
END VerifyArrivalScan; 
PROCEDURE verifyDepartureScan
AS
	locCount NUMBER(4);
BEGIN
	debugMsg('------ verifyDepartureScan ---------'); 

	IF globalScan.HandlingUnitOid = 0 THEN
		SELECT
			COUNT(SCAN_OID_NBR)
		INTO 
			locCount
		FROM 
			ENT.SCAN 
		WHERE 
			SCAN_TYPE_CD = readConfig('DEPARTURE_SCAN_NAME',globalScan.CountryCode,'BOUT') 
			AND SHIPMENT_OID_NBR = globalScan.ShipmentOid
			AND LOCATION_CD = globalScan.LocationCode;
	ELSE
		SELECT
			COUNT(SCAN_OID_NBR)
		INTO 
			locCount
		FROM 
			ENT.SCAN 
		WHERE 
			SCAN_TYPE_CD = readConfig('DEPARTURE_SCAN_NAME',globalScan.CountryCode,'BOUT') 
			AND SHIPMENT_OID_NBR = globalScan.ShipmentOid
			AND LOCATION_CD = globalScan.LocationCode
			AND PIECE_OID_NBR = globalScan.HandlingUnitOid;
	END IF;

	IF locCount=0 THEN
		debugMsg('No scan record found');
		$IF $$COUNTRY = 3 $THEN
			IF globalScan.InViewName = 'STATVIEW' and globalScan.LocStatNbr = '68' THEN
				globalScan.ErrorNumber := 130; --"EM_NO_DEPART_SCAN"
				RAISE_APPLICATION_ERROR(-20130,'NO_DEPART_SCAN');
			END IF;
		$ELSE
			IF INSTR('BOUTVIEW|QUERYVIEW|CAGEVIEW|CONSVIEW',globalScan.InViewName)>0 THEN
				IF readConfig('PREVIOUS_DEPARTURE_REQUIRED',globalScan.LocationCode,'FALSE')='TRUE' THEN
					globalScan.ErrorNumber := 130; --"EM_NO_DEPART_SCAN"
					RAISE_APPLICATION_ERROR(-20130,'NO_DEPART_SCAN');
				ELSE
					IF readConfig('NO_PREVIOUS_DEPARTURE_SET_IM',globalScan.LocationCode,'FALSE') = 'TRUE' THEN
						globalScan.ErrorNumber := 10;  --"IM_NO_DEPART_SCAN"
					END IF;
				END IF;
			END IF;		
		$END
	ELSE
		debugMsg('Departure scan record found');
		IF INSTR('BOUTVIEW|QUERYVIEW|CAGEVIEW|CONSVIEW',globalScan.InViewName)>0 THEN
			IF readConfig('PREVIOUS_DEPARTURE_ALLOWED',globalScan.LocationCode,'FALSE')='TRUE' THEN
				IF readConfig('PREVIOUS_DEPARTURE_SET_IM',globalScan.LocationCode,'FALSE') = 'TRUE' THEN
					globalScan.ErrorNumber := 16; --"IM_DEPART_SCAN"
				END IF;
			ELSE
				globalScan.ErrorNumber := 128; --"EM_PREVIOUS_DEPART"
				RAISE_APPLICATION_ERROR(-20128,'PREVIOUS_DEPART');
				
			END IF;
		END IF;	
	END IF;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('No scan record found');
		globalScan.ErrorNumber := 142; 
		RAISE_APPLICATION_ERROR(-20142,'SCAN_NOT_FND');
END verifyDepartureScan; PROCEDURE findExternalShipmentInfoAPAC
AS
BEGIN
	debugMsg('------ findExternalShipmentInfoAPAC ------'); 
	BEGIN
		SELECT
			CLASSIFICATION,
			DECLARED_FLG,
			NVL(MAWB,'*null*'),
			STATUSCODE
		INTO
			globalScan.BrokerClassificationCode,
			globalScan.DeclarationCode,
			globalPrint.MAWB,
			globalScan.CustomsReturnedCode
		FROM
			ACCS_CORE_SHIPMENT_REF
		WHERE
			SHIPMENT_OID = globalScan.ShipmentOid;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			debugMsg('No ACCS ShipmentInfo found');
			IF (globalScan.InViewName = 'BOUTVIEW' OR globalScan.InViewName = 'QUERYVIEW') AND globalCCIIState.OverageFlag = 'M' THEN
				debugMsg('Bond-out/Query scan for an overage shipment is not allowed');
				globalScan.ErrorNumber := 204;
				RAISE_APPLICATION_ERROR(-20204,'OVERAGE');
			END IF;
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
	END;

	CASE 
	WHEN globalScan.CountryCode = 'JP' THEN
		IF globalScan.DeclarationCode IS NULL OR globalScan.DeclarationCode = 'N' THEN
			globalPrint.ClearanceStatus := 'NDEC';
			RETURN;
		END IF;
		
		--Base on accsShipStatus_STATUS_CD,set accsShipInfo_CLEARANCE_STATUS and accsShipInfo_BOND_TRANSFERED

		IF globalScan.BrokerClassificationCode IS NOT NULL THEN
			IF globalScan.BrokerClassificationCode = 'DOC' OR globalScan.BrokerClassificationCode = 'EXP' THEN
				globalScan.ReleaseCode := 'Y';
				globalScan.DeclarationCode := 'Y';
			END IF;
		ELSE
			globalScan.ReleaseCode := 'Y';
			globalScan.DeclarationCode := 'Y';
			globalScan.CustomsReleasedFlag := 'Y';
		END IF;
	WHEN globalScan.CountryCode = 'KR' THEN
		CASE globalScan.BrokerClassificationCode
		WHEN 'DOC' THEN
			globalPrint.ClearanceStatus := 'CLEAR';
		WHEN 'B' THEN
			globalPrint.ClearanceStatus := 'HOLD';
		WHEN 'D' THEN
			globalPrint.ClearanceStatus := 'HOLD';
		WHEN 'F' THEN
			globalPrint.ClearanceStatus := 'HOLD';
		ELSE
			NULL;
		END CASE;
		IF globalScan.BrokerClassificationCode IS NOT NULL THEN
			IF globalScan.BrokerClassificationCode = 'DOC' OR globalScan.BrokerClassificationCode = 'A' THEN
				globalScan.ReleaseCode := 'Y';
				globalScan.DeclarationCode := 'Y';
			END IF;
		ELSE
			globalScan.ReleaseCode := 'Y';
			globalScan.DeclarationCode := 'Y';
			globalScan.CustomsReleasedFlag := 'Y';
		END IF;
	ELSE
		NULL;
	END CASE;
	
END findExternalShipmentInfoAPAC; 
PROCEDURE verifyAddressChange
AS

BEGIN
	debugMsg('------ verifyAddressChange ------');
	IF globalCCIIState.ServiceCode = 'ID' THEN
		debugMsg('IPD shipment, verify address by PieceOid');
		SELECT
			DEST_LOC_CD,
			CONSIGNEE_CONTACT_NM,
			CONSIGNEE_COMPANY_NM,
			REPLACE(CONSIGNEE_ADDRESS1_DESC,CHR(10),' '),
			REPLACE(CONSIGNEE_ADDRESS2_DESC,CHR(10),' '),
			REPLACE(REMARK_DESC,CHR(10),' '),
			LAST_MODIFIED_TMSTP
		INTO
			globalScan.DestLocationCode,
			globalPrint.ADDRESS_CHANGE_NAME,
			globalPrint.ADDRESS_CHANGE_COMPANY,
			globalPrint.ADDRESS_CHANGE_ADDRESS1,
			globalPrint.ADDRESS_CHANGE_ADDRESS2,
			globalPrint.ADDRESS_CHANGE_COMMENTS,
			globalPrint.ADDRESS_CHANGE_DATE
		FROM
			ACCS.ACCS_CORE_DELIVERY_ADDRESS
		WHERE
			OID_NBR = globalScan.HandlingUnitOid 
			AND OID_TYPE_CD = 'P'
			AND REF_COUNTRY_CD = globalScan.CountryCode;
	ELSE
		SELECT
			DEST_LOC_CD,
			CONSIGNEE_CONTACT_NM,
			CONSIGNEE_COMPANY_NM,
			REPLACE(CONSIGNEE_ADDRESS1_DESC,CHR(10),' '),
			REPLACE(CONSIGNEE_ADDRESS2_DESC,CHR(10),' '),
			REPLACE(REMARK_DESC,CHR(10),' '),
			LAST_MODIFIED_TMSTP
		INTO
			globalScan.DestLocationCode,
			globalPrint.ADDRESS_CHANGE_NAME,
			globalPrint.ADDRESS_CHANGE_COMPANY,
			globalPrint.ADDRESS_CHANGE_ADDRESS1,
			globalPrint.ADDRESS_CHANGE_ADDRESS2,
			globalPrint.ADDRESS_CHANGE_COMMENTS,
			globalPrint.ADDRESS_CHANGE_DATE
		FROM
			ACCS.ACCS_CORE_DELIVERY_ADDRESS
		WHERE
			OID_NBR = globalScan.ShipmentOid
			AND OID_TYPE_CD = 'S'
			AND REF_COUNTRY_CD = globalScan.CountryCode;
	END IF;

	IF globalPrint.ADDRESS_CHANGE_NAME IS NOT NULL AND globalPrint.ADDRESS_CHANGE_COMPANY IS NOT NULL THEN
		globalScan.AddressChangeFlag := 'TRUE';
	END IF;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('No Address change found');
				
END verifyAddressChange; 
PROCEDURE verifyRelease
AS
	locCh VARCHAR2(20);
	locCount NUMBER(4);
	locStagingArea VARCHAR2(4);
	locDiverterCode VARCHAR2(4);
BEGIN
	debugMsg('------ VerifyRelease ---------'); 

	IF globalCCIIState.PositiveSort = 'FALSE' THEN
		debugMsg('This site is NOT positive sort, quiting verifyRelease...');
		RETURN;
	END IF;
	IF globalCCIIState.OverageFlag = 'Y' OR globalScan.ShipmentOid = 0 AND globalScan.InViewName  'IPAVIEW' THEN
		debugMsg('It is Overage or no ShipmentOid, quiting verifyRelease...');
		globalScan.ReturnMessage := 'Not found';
		globalScan.ErrorNumber := 7; --"IM_NOT_FOUND"
		RETURN;
	END IF;
	IF globalScan.CustomsReleasedFlag  'Y' THEN
		debugMsg('It is not released, need a positive sort intercept');
		globalScan.ReturnMessage := 'NOT_RELEASED';
		globalScan.ErrorNumber := 6; --IM_RETURN_MESSAGE
		globalCCIIState.InterceptedFlag := 'POSITIVESORT';
		IF globalScan.CountryCode  'KR' THEN
			globalScan.AgencyCode := readConfig('DEFAULT_AGENCY_CODE',globalScan.LocationCode,'FX');
			globalScan.InterceptCode := readConfig('DEFAULT_INTERCEPT_CODE',globalScan.LocationCode,'ND');
			--getStageAreaForIntercept
			locCh := readConfig('SITE_STAGE_AGENCY_OR_INTERCEPT',globalScan.LocationCode,'INTERCEPT');
			IF locCh = 'AGENCY' THEN
				debugMsg('Getting staging area by  agency code...');
				--getAgency.sql
				SELECT
					COUNT(AGENCY_CD)
				INTO
					locCount
				FROM 
					ENT.AGENCY
				WHERE 
					AGENCY_CD = globalScan.AgencyCode
					AND LOCATION_CD = globalScan.LocationCode;
				IF locCount = 0 THEN
					debugMsg('Agency Code ' || globalScan.AgencyCode || ' not found in db');
					globalScan.ErrorNumber := 57;
					RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
				END IF;
				
				--getAgencyStagingArea.sql
				BEGIN
					SELECT
						STAGING_AREA_CD
					INTO
						locStagingArea
					FROM
						ENT.AGENCY_STAGING_AREA
					WHERE
						LOCATION_CD = globalScan.LocationCode
						AND WAREHOUSE_CD = globalScan.WarehouseCode
						AND AGENCY_CD = globalScan.AgencyCode;
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
					debugMsg('Staging Area Agency Code  ' || globalScan.AgencyCode || ' not found in db');
					globalScan.ErrorNumber := 57;
					RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
				END;
				globalCCIIState.StagingArea := locStagingArea;
				
			ELSIF locCh = 'INTERCEPT' THEN
				debugMsg('Getting staging area by  intercept code...');
				--getInterceptDef.sql
				SELECT
					COUNT(INTERCEPT_CD)
				INTO 
					locCount
				FROM 
					ENT.INTERCEPT_DEF 
				WHERE 
					INTERCEPT_CD = globalScan.InterceptCode
					AND LOCATION_CD = globalScan.LocationCode;
				IF locCount = 0 THEN
					debugMsg('Agency Code ' || globalScan.InterceptCode || ' not found in db');
					globalScan.ErrorNumber := 57;
					RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
				END IF;
				--getInterceptStagingArea.sql
				BEGIN
					SELECT
						STAGING_AREA_CD
					INTO
						locStagingArea
					FROM
						ENT.INTERCEPT_STAGING_AREA
					WHERE
						LOCATION_CD = globalScan.LocationCode
						AND WAREHOUSE_CD = globalScan.WarehouseCode
						AND INTERCEPT_CD = globalScan.InterceptCode;
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
					debugMsg('Staging Area Intercept Code  ' || globalScan.InterceptCode || ' not found in db');
					globalScan.ErrorNumber := 57;
					RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
				END;
				globalCCIIState.StagingArea := locStagingArea;
			ELSE
				debugMsg('Error: Configuration SITE_STAGE_AGENCY_OR_INTERCEPT ' || locCh || ' wrong');
				globalScan.ErrorNumber := 64;
				RAISE_APPLICATION_ERROR(-20064,'CONFIG_FILE_ERROR');
			END IF;
			
			--getDiverterForStageArea
			debugMsg('getDiverterForStageArea..');
			BEGIN
				SELECT
					DIVERTER_NBR
				INTO
					locDiverterCode
				FROM
					CCII.STAGING_AREA
				WHERE
					STAGING_AREA_CD = globalCCIIState.StagingArea
					AND LOCATION_CD = globalScan.LocationCode;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				debugMsg('Staging area definition not found in db');
				globalScan.ErrorNumber := 57;
				RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
			END;
			globalCCIIState.DiverterCode := locDiverterCode;
			debugMsg('Got diverter code =' || locDiverterCode);
			
		END IF;
		globalPrint.PiorityIntercept1 := globalScan.InterceptCode;
	END IF;
	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('No scan record found');
		globalScan.ErrorNumber := 142; 
		RAISE_APPLICATION_ERROR(-20142,'SCAN_NOT_FND');
END verifyRelease; 
PROCEDURE verifyShuttle
AS

	locStr VARCHAR(100);
	locNum NUMBER(1);
	
	locShuttleStatusCd VARCHAR(2);
	locShuttleTypeCd VARCHAR(2);
BEGIN
	debugMsg('------ VerifyShuttle ---------'); 

	IF globalScan.DestLocationCode IS NULL THEN
		debugMsg('No DestLocationCode found');
		globalScan.ErrorNumber := 142; --"EM_SHUTTLE_NOT_FND"
		RAISE_APPLICATION_ERROR(-20142,'SHUTTLE_NOT_FND');
	END IF;
	
	SELECT
		SHUTTLE_TYPE_CD,
		SHUTTLE_STATUS_CD
	INTO
		locShuttleTypeCd,
		locShuttleStatusCd
	FROM 
		CCII.SHUTTLE
	WHERE 
		LOCATION_CD = globalScan.LocationCode AND
		DEST_LOCATION_CD = globalScan.DestLocationCode AND
		SHUTTLE_CD = globalScan.ShuttleID;
	
	locStr := readConfig('VALID_SHUTTLE_STATUS_CODES',globalScan.LocationCode,'AR,LO,NA');
	locNum := MatchString(locStr,locShuttleStatusCd);
	IF locNum =0 THEN
		debugMsg('Error: Shuttle status not found in default configuration');
		globalScan.ErrorNumber := 137; --"EM_SHUTTLE_STATUS"
		RAISE_APPLICATION_ERROR(-20137,'SHUTTLE_STATUS');
	END IF;
	
	locStr := readConfig('VALID_FEDEX_SHUTTLE_TYPE_CD',globalScan.LocationCode,'FD');
	locNum := MatchString(locStr,locShuttleTypeCd); --1: true, 0 : false
	debugMsg('FedexShuttleFlag:' || locNum );
	globalCCIIState.UploadStatus_FedExShuttleFlag := locNum;
	
	debugMsg('Done,quiting verifyShuttle...');
	
	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('No Shuttle record found');
		globalScan.ErrorNumber := 142; --"EM_SHUTTLE_NOT_FND"
		RAISE_APPLICATION_ERROR(-20142,'SHUTTLE_NOT_FND');
END VerifyShuttle; 
PROCEDURE createAccsShipStatus
AS
	locStatusTypeCode VARCHAR2(5);
BEGIN
	debugMsg('------ createAccsShipStatus ------'); 
	IF globalScan.InViewName = 'BINVIEW' THEN
		locStatusTypeCode := 'SAN';
	ELSIF globalScan.InViewName = 'BOUTVIEW' THEN
		locStatusTypeCode := 'BON';
	END IF;

	INSERT INTO ACCS.ACCS_CORE_SHP_STATUS (
		GRP_OID_NBR,
		OID_TYPE_CD,
		STATUS_TYPE_CD,
		LOCATION_CD,
		REF_COUNTRY_CD,
		STATUS_CD,
		GRP_NM,
		LOCAL_STATUS_CD,
		LAST_MODIFIED_NM,
		LAST_MODIFIED_TMSTP
	)
	VALUES(
		globalScan.HandlingUnitOid,
        	'P',
		locStatusTypeCode,
		globalScan.LocationCode,
		globalScan.CountryCode,
		'S',
		'',
		'',
		'ScanClient',
		SYSDATE_GMT
	);

EXCEPTION
	WHEN OTHERS THEN
		debugMsg('Insert ACCS_CORE_SHP_STATUS failed');
		globalScan.ErrorNumber := 57;
		--RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');

END createAccsShipStatus; PROCEDURE findIntercept
AS
	TYPE typeInterceptRecord IS RECORD (
			INTERCEPT_CD  VARCHAR2(5),
			AGENCY_CD VARCHAR2(5));
	TYPE typeInterceptArray IS TABLE OF typeInterceptRecord INDEX BY BINARY_INTEGER;

	locInterceptArray typeInterceptArray;
	
BEGIN
	debugMsg('------ findIntercept ------');
        IF globalScan.ShipmentOid = 0 AND globalScan.HandlingUnitOid = 0 THEN
                debugMsg('Error: FindIntercept called without valid shipment or hu oid');
		globalScan.ErrorNumber := 61;
                RAISE_APPLICATION_ERROR(-61,'START_OVER');
        END IF;

	--step 1 ,get Intercept
	IF globalScan.ShipmentOid = 0 THEN
	        SELECT
	            I.INTERCEPT_CD,I.AGENCY_CD BULK COLLECT 
	        INTO
	            locInterceptArray
		FROM
			ENT.INTERCEPT I, ENT.AGENCY A, ENT.INTERCEPT_DEF ID
		WHERE
			I.PIECE_OID_NBR = globalScan.HandlingUnitOid 
			AND I.INTERCEPT_LOCATION_CD = globalScan.LocationCode 
			AND I.AGENCY_LOCATION_CD = globalScan.LocationCode 
			AND I.ACTIVE_FLG = 'Y' 
			AND I.AGENCY_CD = A.AGENCY_CD 
			AND I.INTERCEPT_LOCATION_CD  = A.LOCATION_CD 
			AND I.INTERCEPT_LOCATION_CD  = ID.LOCATION_CD 
			AND I.INTERCEPT_CD = ID.INTERCEPT_CD
		ORDER BY
			ID.INTERCEPT_PRIORITY_NBR desc,
			A.CLEARANCE_PRIORITY_CD,
			A.AGENCY_CD;
	ELSE
	        SELECT
	            I.INTERCEPT_CD,I.AGENCY_CD BULK COLLECT 
	        INTO
	            locInterceptArray
		FROM
			ENT.INTERCEPT I, ENT.AGENCY A, ENT.INTERCEPT_DEF ID
		WHERE
			(I.PIECE_OID_NBR = globalScan.HandlingUnitOid OR I.SHIPMENT_OID_NBR = globalScan.ShipmentOid)
			AND I.INTERCEPT_LOCATION_CD = globalScan.LocationCode 
			AND I.AGENCY_LOCATION_CD = globalScan.LocationCode 
			AND I.ACTIVE_FLG = 'Y' 
			AND I.AGENCY_CD = A.AGENCY_CD 
			AND I.INTERCEPT_LOCATION_CD  = A.LOCATION_CD 
			AND I.INTERCEPT_LOCATION_CD  = ID.LOCATION_CD 
			AND I.INTERCEPT_CD = ID.INTERCEPT_CD
		ORDER BY
			ID.INTERCEPT_PRIORITY_NBR desc,
			A.CLEARANCE_PRIORITY_CD,
			A.AGENCY_CD;
	END IF;
		
	------step2 check is null
	IF locInterceptArray.COUNT = 0 THEN
		debugMsg('Intercept is null');
		globalCCIIState.InterceptedFlag := 'C';
		IF globalScan.ErrorNumber != 1 AND globalScan.ErrorNumber != 2 THEN
			globalScan.ErrorNumber := 3;  --"IM_CLEARED"  
		END IF;
		RETURN;
	END IF;
		
	----set MPS Flag
	IF globalScan.AirbillType = 'G' OR globalScan.AirbillType = '6' OR 
	globalScan.AirbillFormType = '0491' OR globalScan.AirbillFormType = '491' THEN
		globalCCIIState.MPSFlag := 'CRN';
	END IF;

	--mpsIntercepts start

	--#mpsIntercepts end
		
		
	IF globalScan.CountryCode != 'KR' THEN
		--getStageAreaForIntercept
		NULL;	
		--#end of getStageAreaForIntercept
		
		--getDiverterForStageArea
		NULL;
		--#end of getDiverterForStageArea
	END IF;
	
	--getInterceptPrintData
	DECLARE
		end_getInterceptPrintData EXCEPTION;
	BEGIN
		IF locInterceptArray.EXISTS(1) THEN
			globalPrint.PiorityIntercept1 := locInterceptArray(1).INTERCEPT_CD;
		ELSE
			RAISE end_getInterceptPrintData;
		END IF;

		IF locInterceptArray.EXISTS(2) THEN
			globalPrint.PiorityIntercept2 := locInterceptArray(2).INTERCEPT_CD;
		ELSE
			RAISE end_getInterceptPrintData;
		END IF;

		IF locInterceptArray.EXISTS(3) THEN
			globalPrint.PiorityIntercept3 := locInterceptArray(3).INTERCEPT_CD;
		ELSE
			RAISE end_getInterceptPrintData;
		END IF;

		IF locInterceptArray.EXISTS(4) THEN
			globalPrint.PiorityIntercept4 := locInterceptArray(4).INTERCEPT_CD;
		ELSE
			RAISE end_getInterceptPrintData;
		END IF;
	EXCEPTION
		WHEN end_getInterceptPrintData THEN
			NULL;
	END;
	--end getInterceptPrintData
	
	--Intercept Code "FO" and "INS" are hard-coded here as Broker Selection Option in JP
	$IF $$COUNTRY=1 $THEN
		IF ( locInterceptArray(1).INTERCEPT_CD = 'FO' OR locInterceptArray(1).INTERCEPT_CD = 'INS' ) AND globalCCIIState.MPSFlag = 'CRN' THEN
			globalCCIIState.InterceptedFlag := 'N';
			globalScan.InterceptCode := '';
		ELSE
			globalCCIIState.InterceptedFlag := 'Y';
			globalScan.InterceptCode := locInterceptArray(1).INTERCEPT_CD;
			globalScan.AgencyCode := locInterceptArray(1).AGENCY_CD;
		END IF;
	--For AU, if the intercept code is "K" and "K" only, the shipment is "CLEAR"
	$ELSIF $$COUNTRY=4 $THEN
		IF locInterceptArray.COUNT = 1 AND locInterceptArray(1).INTERCEPT_CD = 'K' THEN
			globalCCIIState.InterceptedFlag := 'C';
			globalScan.InterceptCode := locInterceptArray(1).INTERCEPT_CD;
		ELSE
			globalCCIIState.InterceptedFlag := 'Y';
			FOR indx IN locInterceptArray.FIRST .. locInterceptArray.LAST
			LOOP
				globalScan.InterceptCode := (globalScan.InterceptCode || ' ' || locInterceptArray(indx).INTERCEPT_CD);
				globalScan.AgencyCode := (globalScan.AgencyCode || '' || locInterceptArray(indx).AGENCY_CD);
			END LOOP;

			IF globalScan.ErrorNumber != 1 AND globalScan.ErrorNumber != 2 THEN
				globalScan.ErrorNumber := 6;
				globalScan.ReturnMessage := ('INTERCEPT ' || globalScan.InterceptCode);
			END IF;
		END IF;
			
	$ELSE
		globalCCIIState.InterceptedFlag := 'Y';
		FOR indx IN locInterceptArray.FIRST .. locInterceptArray.LAST
		LOOP
			globalScan.InterceptCode := (globalScan.InterceptCode || ' ' || locInterceptArray(indx).INTERCEPT_CD);
			globalScan.AgencyCode := (globalScan.AgencyCode || '' || locInterceptArray(indx).AGENCY_CD);
		END LOOP;

		IF globalScan.ErrorNumber != 1 AND globalScan.ErrorNumber != 2 THEN
			globalScan.ErrorNumber := 6;
			globalScan.ReturnMessage := ('INTERCEPT ' || globalScan.InterceptCode);
		END IF;
	$END

END findIntercept; 
PROCEDURE findInboundRouteInfo
AS
BEGIN
	debugMsg('------ findInboundRouteInfo ------'); 
	SELECT
		ROUTE_LEG.ROUTE_NBR,
		ROUTE_LEG.ROUTE_DT,
		ROUTE_LEG.ROUTE_LEG_NBR,
		ROUTE_LEG.SORT_DT,
		ROUTE_LEG.ROUTE_LEG_STATUS_CD,
		ROUTE_LEG.ROUTE_LEG_OID_NBR
	INTO
		globalCCIIState.RouteNumber,
		globalCCIIState.RouteDate,
		globalCCIIState.RouteLegNumber,
		globalCCIIState.SortDate,
		globalCCIIState.RouteLegStatusCode,
		globalCCIIState.RouteLegOid
	FROM
		ENT.ROUTE_LEG, ENT.PIECE_ROUTE_LEG
	WHERE
		PIECE_ROUTE_LEG.PIECE_OID_NBR = globalScan.HandlingUnitOid
		AND ROUTE_LEG.ROUTE_DT = PIECE_ROUTE_LEG.ROUTE_DT
		AND ROUTE_LEG.ROUTE_NBR = PIECE_ROUTE_LEG.ROUTE_NBR
		AND ROUTE_LEG.ROUTE_LEG_NBR = PIECE_ROUTE_LEG.ROUTE_LEG_NBR
		AND ROUTE_LEG.DEST_LOCATION_CD = globalScan.LocationCode;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('Route Number not found in DB');
		globalScan.ErrorNumber := 143;
		RAISE_APPLICATION_ERROR(-20143,'ROUTE_NOT_FND');

END findInboundRouteInfo; 
PROCEDURE findHandlingInstruction
AS
	TYPE typeHandlingInstructionArray IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
	locHandlingInstructionArray typeHandlingInstructionArray;
BEGIN
	debugMsg('------ findHandlingInstruction ------');
	
	SELECT
		HANDLING_INSTRUCTION_CD BULK COLLECT
	INTO
		locHandlingInstructionArray
	FROM
		ENT.HANDLING_INSTRUCTION
	WHERE
		SHIPMENT_OID_NBR = globalScan.ShipmentOid
		AND LOCATION_CD = globalScan.LocationCode
		AND ACTIVE_FLG = 'Y';
	IF NOT locHandlingInstructionArray.EXISTS(1) THEN
		debugMsg('Handling Instruction is null');
	END IF;

	--getHandlingCodePrintData
        DECLARE
                end_getHandlingCodePrintData EXCEPTION;
        BEGIN   
		IF locHandlingInstructionArray.EXISTS(1) THEN
			globalPrint.HandlingCode1 := locHandlingInstructionArray(1);
		ELSE    
			RAISE end_getHandlingCodePrintData;
		END IF;

		IF locHandlingInstructionArray.EXISTS(2) THEN
			globalPrint.HandlingCode2 := locHandlingInstructionArray(2);
		ELSE    
			RAISE end_getHandlingCodePrintData;
		END IF;

		IF locHandlingInstructionArray.EXISTS(3) THEN
			globalPrint.HandlingCode3 := locHandlingInstructionArray(3);
		ELSE    
			RAISE end_getHandlingCodePrintData;
		END IF;
	EXCEPTION
                WHEN end_getHandlingCodePrintData THEN
			NULL;
	END;
        --end getHandlingCodePrintData
	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('Handling Instruction is null');

END findHandlingInstruction; 
PROCEDURE FindMPSShortage
AS
	locCount NUMBER(4);
BEGIN
	debugMsg('------ FindMPSShortage ---------');
	IF globalCCIIState.MPSFlag IS NULL OR INSTR('MASTER|CRN',globalCCIIState.MPSFlag) = 0 THEN
		debugMsg('This is not a MPS shipment, quiting findMPSShortage...');
		RETURN;
	END IF;
	IF globalScan.ShipmentOid IS NULL OR globalScan.ShipmentOid=0 THEN
		debugMsg('s_ShipmentOid is empty or 0, quiting findMPSShortage...');
		RETURN;
	END IF;
	
	SELECT 
		COUNT(SCAN_OID_NBR)
	INTO
		locCount
	FROM 
		ENT.SCAN
	WHERE 
		SCAN_TYPE_CD = 'BIN' AND SHIPMENT_OID_NBR = globalScan.ShipmentOid AND LOCATION_CD = globalScan.LocationCode;

	IF locCount  globalPrint.PieceQuantity THEN
		debugMsg('MPS shortage for MAWB' || globalScan.HAWB );
		globalScan.ErrorNumber := 201;
		RAISE_APPLICATION_ERROR(-20201,'EM_MPS_SHORTAGE');
	END IF;
END FindMPSShortage; 
PROCEDURE FindShuttle
AS
BEGIN
	debugMsg('------ FindShuttle ---------'); 

	IF globalScan.DestLocationCode IS NULL THEN
		debugMsg('No DestLocationCode found');
		globalScan.ErrorNumber := 142; --"EM_SHUTTLE_NOT_FND"
		RAISE_APPLICATION_ERROR(-20142,'SHUTTLE_NOT_FND');
	END IF;
	SELECT
		SHUTTLE_CD,
		SLIDE_AREA_CD
	INTO
		globalScan.ShuttleID,
		globalCCIIState.DiverterCode
	FROM 
		CCII.SHUTTLE
	WHERE 
		LOCATION_CD = globalScan.LocationCode 
		AND DEST_LOCATION_CD = globalScan.DestLocationCode 
		AND WAREHOUSE_CD = globalScan.WarehouseCode;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('No Shuttle record found');
		globalScan.ErrorNumber := 142; --"EM_SHUTTLE_NOT_FND"
		RAISE_APPLICATION_ERROR(-20142,'SHUTTLE_NOT_FND');
END FindShuttle; 
PROCEDURE createConsSummary
AS
	locCount NUMBER;
	locOidNumber NUMBER;
BEGIN
	debugMsg('------ createConsSummary ------');
	IF globalScan.ConsId IS NULL THEN
		debugMsg('No ConsId provided');
		globalScan.ErrorNumber := 81;
		RAISE_APPLICATION_ERROR(-20081,'INVALID_CONS');
	END IF;

	IF globalScan.SplitNumber IS NOT NULL AND globalScan.SplitNumber > TO_CHAR(readConfig('SPLIT_MAX',globalScan.LocationCode,'80')) THEN
		debugMsg('Invalid split number');
		globalScan.ErrorNumber := 90;
		RAISE_APPLICATION_ERROR(-20090,'INVALID_SPLIT');
	END IF;

	SELECT
		COUNT(*)
	INTO
		locCount	
	FROM
		CCII.CONS_SUMMARY
	WHERE
		CONS_NBR = globalScan.ConsId;
	IF locCount = 1 THEN	
		SELECT
			CONS_OID_NBR,
			CONS_FORM_TYPE_CD,
			CONTAINER_NBR,
			CONS_CD,
			DEST_LOCATION_CD,
			TOTAL_PIECE_QTY
		INTO
			globalScan.ConsOid,
			globalScan.ConsFormType,
			globalScan.ContainerId,
			globalScan.ContainerInd,
			globalScan.ConsDestLoc,
			globalScan.ConsTotalPiece
		FROM
			CCII.CONS_SUMMARY
		WHERE
			CONS_NBR = globalScan.ConsId;
	ELSE
		IF globalScan.ContainerInd IS NOT NULL THEN
			CASE globalScan.ContainerInd
			WHEN 'U' THEN	--If this is a ULD set the form type to 0501
				globalScan.ConsFormType := '0501';
			WHEN 'C' THEN	--If this is a CAGE set the form type to 0502
				globalScan.ConsFormType := '0502';
			WHEN 'B' THEN	--If this is a BAG container
				globalScan.ConsFormType := '0503';
				globalScan.ContainerId := 'BAG';
			ELSE
				globalScan.ConsFormType := '0504';
			END CASE;
		ELSE
			globalScan.ConsFormType := '0504';
			globalScan.ContainerId := 'OTHER';
			globalScan.ContainerInd := 'O';
		END IF;
		IF globalScan.ContainerId IS NULL THEN
			globalScan.ContainerId := 'PALLET';
		END IF;
		IF globalScan.ConsTotalPiece IS NULL THEN
			globalScan.ConsTotalPiece := 0;
		END IF;
	
		--Split number will always be 0 for import. For export, additional codes needed here to handle split

		BEGIN
			SELECT
				CCII.CONS_SUMMARY_SEQ.nextval
			INTO
				locOidNumber
			FROM
				dual;
			INSERT INTO CCII.CONS_SUMMARY
				(CONS_OID_NBR,
				CONS_NBR,
				CONS_FORM_TYPE_CD,
				CREATE_DT,
				FLIGHT_NBR,
				FLIGHT_ORIGIN_LOC_CD,
				FLIGHT_DEST_LOC_CD,
				FLIGHT_ACTUAL_ARRIVAL_DT,
				CONTAINER_NBR,
				CONS_CD,
				DEST_LOCATION_CD,
				DEST_COUNTRY_CD,
				NOI_DEST_LOC_CD,
				EMPLOYEE_NBR,
				SCAN_LOC_CD,
				SCAN_DT,
				UPDATE_DT,
				TOTAL_PIECE_QTY,
				REG_NOTICE_QTY,
				INTERCEPT_QTY,
				RELEASE_QTY,
				CLEARANCE_PIECES_QTY,
				MANIFESTED_QTY,
				UNIQUE_QTY)
			VALUES(
				locOidNumber,
				globalScan.ConsId,
				globalScan.ConsFormType,
				SYSDATE_GMT,
				'',
				'',
				'',
				SYSDATE_GMT,
				globalScan.ContainerId,
				globalScan.ContainerInd,
				globalScan.ConsDestLoc,
				'',
				'',
				globalScan.UserLogon,
				globalScan.LocationCode,
				SYSDATE_GMT,
				SYSDATE_GMT,
				0,
				0,
				0,
				0,
				0,
				0,
				0);
			globalScan.ConsOid := locOidNumber;
		EXCEPTION
			WHEN OTHERS THEN
				debugMsg('Failed to create ConsSummary record');
				globalScan.ErrorNumber := 57;
				RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
		END;
	END IF;
	
END createConsSummary; 
PROCEDURE createCons
AS
	locConsOidNbr NUMBER(10);
	locFormTypeCd VARCHAR2(4);
	locConsInfoFlag NUMBER(1);
	locDeconsFlag VARCHAR2(1);
	locTrackingNbr VARCHAR2(20);
	locSummaryConsOidNbr NUMBER(10);
	locSorPorCOidNumber NUMBER(10);
	locSorPorC VARCHAR2(20);
	locUpdateConsSummary NUMBER(1);
	locConsSummaryTotalPieceQty NUMBER(4);
BEGIN
	debugMsg('------ CreateCons ---------'); 
	locConsInfoFlag := 0;
	locUpdateConsSummary := 0;
	BEGIN
		SELECT
			CONS_OID_NBR,
			FORM_TYPE_CD,
			DECONS_FLG,
			TRACKING_NBR
		INTO
			locConsOidNbr,
			locFormTypeCd,
			locDeconsFlag,
			locTrackingNbr
		FROM
			CCII.CONS 
		WHERE 
			CONS_OID_NBR = globalScan.ConsOid 
			AND TRACKING_NBR = globalCCIIState.AbScanned;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			locConsInfoFlag := 1;			
	END;
	IF locConsInfoFlag=0 AND INSTR('0501|0503',locFormTypeCd) > 0 AND locConsOidNbr  globalScan.HandlingUnitOid THEN
		debugMsg('cons_OID_NBR:'|| locConsOidNbr ||' ; s_HandlingUnitOid:' || globalScan.HandlingUnitOid);
		locConsInfoFlag := 1;
	END IF;

	IF locConsInfoFlag=1 THEN --false
		--createCons_func
		IF globalCCIIState.AbTypeScanned = '3' THEN
			locSummaryConsOidNbr := 0;
			BEGIN
				SELECT
					CONS_OID_NBR
				INTO
					locSummaryConsOidNbr
				FROM 
					CCII.CONS_SUMMARY 
				WHERE
					CONS_NBR = globalCCIIState.AbScanned;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					NULL;
			END;
			locSorPorCOidNumber := locSummaryConsOidNbr;
			locSorPorC := 'C';
		ELSIF globalScan.HandlingUnitOid  0 THEN
			locSorPorCOidNumber := globalScan.HandlingUnitOid;
			locSorPorC := 'P';
		ELSIF globalScan.ShipmentOid  0 THEN
			locSorPorCOidNumber := globalScan.ShipmentOid;
			locSorPorC := 'S';
		ELSE
			debugMsg('Error: Both HandlingUnitOid and ShipmentOid are 0.');
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
		END IF;
		--createCons.sql
		
		INSERT INTO CCII.CONS
			(CONS_OID_NBR,
			TRACKING_NBR,
			FORM_TYPE_CD,
			CONS_NBR,
			CONS_FORM_TYPE_CD,
			OID_NBR,
			OID_TYPE_CD,
			GMT_SCAN_DT,
			DECONS_FLG,
			UPDATE_SOURCE_DESC,
			UPDATE_DT)
		VALUES (
			globalScan.ConsOid,
			globalCCIIState.AbScanned,
			globalCCIIState.AbTypeScanned,
			globalScan.ConsId,
			globalScan.ConsFormType,
			locSorPorCOidNumber,
			locSorPorC,
			SYSDATE_GMT,
			'',
			SUBSTR(globalScan.Comments,0,9), --
			SYSDATE_GMT);
		--updateConsSummary
		locUpdateConsSummary := 1;
	ELSIF locDeconsFlag = 'D' THEN --CONS entry exists with the Airbill Number in CONSView
		locDeconsFlag := '';
		--updateCons.sql
		UPDATE
			CCII.CONS
		SET
			DECONS_FLG = '',
			UPDATE_DT = SYSDATE_GMT
		WHERE
			CONS_OID_NBR = locConsOidNbr
			AND TRACKING_NBR = locTrackingNbr;
		locUpdateConsSummary := 1;
	ELSE  --Airbill is already here and it is not Deleted, send a message back
		IF globalCCIIState.AbScanned = globalScan.AirbillNumber THEN  --Seems this is always true
			globalScan.ErrorNumber := 126;
			RAISE_APPLICATION_ERROR(-20126,'ALREADY_ADDED');
		END IF;
	END IF;

	--updateConsSummary	
	IF locUpdateConsSummary=1 THEN
		debugMsg('Updating ConsSummary...');
		--getConsSummary.sql
		BEGIN
			SELECT
				TOTAL_PIECE_QTY
			INTO
				locConsSummaryTotalPieceQty
			FROM 
				CCII.CONS_SUMMARY 
			WHERE
				CONS_OID_NBR = globalScan.ConsOid;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				debugMsg('Error: No ConsSummary found for ConsOid: ' || globalScan.ConsOid ||', quiting createConsSummary...');
				globalScan.ErrorNumber := 57;
				RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
		END;
		locConsSummaryTotalPieceQty := locConsSummaryTotalPieceQty+1;
		--updateConsSummary.sql
		UPDATE
			CCII.CONS_SUMMARY
		SET
			TOTAL_PIECE_QTY = locConsSummaryTotalPieceQty,
			UPDATE_DT = SYSDATE_GMT
		WHERE
			CONS_OID_NBR = globalScan.ConsOid;
		globalScan.ConsTotalPiece := locConsSummaryTotalPieceQty;
		
	END IF;

END createCons; 
PROCEDURE removeCons
AS
	locConsOidNbr NUMBER(10);
	locFormTypeCd VARCHAR2(4);
	locDeconsFlag VARCHAR2(1);
	locTrackingNbr VARCHAR2(20);
	locConsSummaryTotalPieceQty NUMBER(4);
BEGIN
	debugMsg('------ RemoveCons ---------'); 
	--getConsInfo.sql
	BEGIN
	SELECT
		CONS_OID_NBR,
		FORM_TYPE_CD,
		DECONS_FLG,
		TRACKING_NBR
	INTO
		locConsOidNbr,
		locFormTypeCd,
		locDeconsFlag,
		locTrackingNbr
	FROM
		CCII.CONS 
	WHERE
		CONS_OID_NBR = globalScan.ConsOid
		AND TRACKING_NBR = globalScan.AirbillNumber;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			debugMsg('DB Error, quiting removeCons...');
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
	END;
	
	IF INSTR('0501|0503',locFormTypeCd)=0 AND globalScan.HandlingUnitOidlocConsOidNbr THEN
		globalScan.ErrorNumber := 125;
		RAISE_APPLICATION_ERROR(-20125,'AB_NOT_IN_CONS');
	END IF;
	IF locDeconsFlag='D' THEN
		debugMsg('AWB already marked as deleted from CON');
		globalScan.ReturnMessage := 'AWB already marked as deleted from CONS';
		globalScan.ErrorNumber := 125;
		RAISE_APPLICATION_ERROR(-20125,'AB_NOT_IN_CONS');
	ELSE
		locDeconsFlag := 'D';
		globalScan.ErrorNumber := 11; --"IM_DEL_FROM_CONS"
		--updateCons.sql
		UPDATE
			CCII.CONS
		SET
			DECONS_FLG = locDeconsFlag,
			UPDATE_DT = SYSDATE_GMT
		WHERE
			CONS_OID_NBR = locConsOidNbr
			AND TRACKING_NBR = locTrackingNbr;
		--getConsSummary.sql
		BEGIN
			SELECT
				TOTAL_PIECE_QTY
			INTO
				locConsSummaryTotalPieceQty
			FROM 
				CCII.CONS_SUMMARY 
			WHERE
				CONS_OID_NBR = globalScan.ConsOid;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
			debugMsg('DB Error,no ConsSummary entry found, quiting removeCons...');
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
		END;
		IF locConsSummaryTotalPieceQty>0 THEN 
			locConsSummaryTotalPieceQty := locConsSummaryTotalPieceQty -1;
		END IF;
		UPDATE
			CCII.CONS_SUMMARY
		SET
			TOTAL_PIECE_QTY = locConsSummaryTotalPieceQty,
			UPDATE_DT = SYSDATE_GMT
		WHERE
			CONS_OID_NBR = globalScan.ConsOid;
		globalScan.ConsTotalPiece := locConsSummaryTotalPieceQty;
		
	END IF;
	
END removeCons; 
PROCEDURE FindCons(locCh VARCHAR2)
AS
	locCount NUMBER(4);
	locOidNbr NUMBER(10);
	locConsNbr NUMBER(12);
	locConsFormTypeCd VARCHAR2(4);
	locDeconsFlg VARCHAR2(1);
BEGIN
	debugMsg('------ FindCons ---------'); 
	IF locCh = '-c' THEN -- find Cons with ConsOid and AirbillNumber. If not found,return Error Message
		BEGIN
			SELECT
				OID_NBR,
				CONS_FORM_TYPE_CD, 	
				DECONS_FLG
			INTO
				locOidNbr,
				locConsFormTypeCd,
				locDeconsFlg
			FROM
				CCII.CONS 
			WHERE 
				CONS_OID_NBR = globalScan.ConsOid
				AND TRACKING_NBR = globalScan.AirbillNumber;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				debugMsg('Error: Cons not found for ConsOid:'|| globalScan.ConsOid ||'; TrackingNumber:');
				globalScan.ErrorNumber := 125;
				RAISE_APPLICATION_ERROR(-20125,'AB_NOT_IN_CONS');
		END;

		IF INSTR('0501|0503',locConsFormTypeCd)0 AND globalScan.HandlingUnitOidlocOidNbr THEN
			globalScan.ErrorNumber := 125; 
			RAISE_APPLICATION_ERROR(-20125,'EM_AB_NOT_IN_CONS');
		END IF;
		IF locDeconsFlg = 'D' THEN
			debugMsg('AWB already marked as deleted from CONS');
			globalScan.ErrorNumber := 125; 
			RAISE_APPLICATION_ERROR(-20125,'EM_AB_NOT_IN_CONS');
		END IF;
		
	ELSIF locCh = '-a' THEN --find if this tracking number belongs to any Cons. If yes, return Error Message
		BEGIN
			SELECT
				OID_NBR,
				CONS_NBR
			INTO
				locOidNbr,
				locConsNbr
			FROM
				CCII.CONS 
			WHERE 
				TRACKING_NBR = globalScan.AirbillNumber
				AND (UPDATE_DT > SYSDATE_GMT - TO_NUMBER(readConfig('MAX_CAGE_DAYS',globalScan.LocationCode,'30')))
				AND DECONS_FLG IS NULL;
			debugMsg('Error: this package is in CONS ' || locOidNbr || locConsNbr );
			-- set ConsId = locConsNbr so that it can be displayed in the UI
			globalScan.ConsId := locConsNbr;
			globalScan.ErrorNumber := 206; 
			RAISE_APPLICATION_ERROR(-20206,'EM_AB_IN_CONS '||locOidNbr);
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				NULL;
		END;
	ELSE
		NULL;
	END IF;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('No Cons record found');
		globalScan.ErrorNumber := 57; 
		RAISE_APPLICATION_ERROR(-20142,'DB_ERROR');
END FindCons; 
PROCEDURE findConsSummary
AS
	locConsOidNbr NUMBER(10);
	locContainerNbr VARCHAR2(11);
	locConsCd VARCHAR2(1);
	locDestLocationCd VARCHAR2(5);
	locDestCountryCd VARCHAR2(2);
BEGIN
	debugMsg('------ FindConsSummary ---------'); 
	SELECT
		CONS_OID_NBR, 
		CONTAINER_NBR, 
		CONS_CD, 	
		DEST_LOCATION_CD, 
		DEST_COUNTRY_CD,
		TOTAL_PIECE_QTY
	INTO
		globalScan.ConsOid,
		globalScan.ContainerId,
		globalScan.ContainerInd,
		globalScan.DestLocationCode,
		globalScan.DestCountry,
		globalScan.ConsTotalPiece
	FROM 
		CCII.CONS_SUMMARY 
	WHERE 
		CONS_NBR = globalScan.ConsId;
	debugMsg('ConsSummary found for ConsId' || locConsOidNbr);
		
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		debugMsg('ConsSummary not found. Set s_ConsId to 0');
		globalScan.ConsId := 0;		
END findConsSummary; 
PROCEDURE verifyShipmentInCons
AS
	locOidNbr NUMBER(10);
	locOidTypeCd VARCHAR2(1);
	locPieceShipmentOidNbr NUMBER(10);
BEGIN
	debugMsg('------ verifyShipmentInCons ---------'); 
	BEGIN
		SELECT
			*
		INTO
			locOidNbr,
			locOidTypeCd
		FROM
		(
			SELECT
				OID_NBR,
				OID_TYPE_CD
			FROM
				CCII.CONS
			WHERE
				CONS_NBR = globalScan.ConsId 
				AND DECONS_FLG IS NULL
		)
		WHERE ROWNUM<=1;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			RETURN;
	END;
	
	IF locOidTypeCd = 'S' THEN
		IF locOidNbr  globalScan.ShipmentOid THEN
			debugmsg('This package ShipmentOid: '|| globalScan.ShipmentOid ||'; ConsId ShipmentOid: '|| locOidNbr);
			debugMsg('This package is not in the same shipment as the ConsId package');
			globalScan.ErrorNumber := 135;
			RAISE_APPLICATION_ERROR(-20135,'NOT_IN_SHIPMENT');
		END IF;
	ELSIF locOidTypeCd = 'P' THEN
		BEGIN
			SELECT 
				P.SHIPMENT_OID_NBR
			INTO
				locPieceShipmentOidNbr
			FROM 
				ENT.PIECE P, ENT.PIECE_CORE_CLEARANCE PCC 
			WHERE  
				P.PIECE_OID_NBR = PCC.PIECE_OID_NBR(+)  
				AND PCC.LOCATION_CD(+) = globalScan.LocationCode
				AND P.PIECE_OID_NBR = locOidNbr;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				debugMsg('No piece information found for piece oid' || locOidNbr);
				RETURN;
		END;
		IF locPieceShipmentOidNbr != globalScan.ShipmentOid THEN
			debugmsg('This package ShipmentOid: '|| globalScan.ShipmentOid ||'; ConsId ShipmentOid: '|| locOidNbr);
			debugMsg('This package is not in the same shipment as the ConsId package');
			globalScan.ErrorNumber := 135;
			RAISE_APPLICATION_ERROR(-20135,'NOT_IN_SHIPMENT');
		END IF;
	ELSE
		debugMsg('Error: cons_OID_TYPE_CD is neither S nor P, quitting verifyShipmentInCons...');
		globalScan.ErrorNumber := 135;
		RAISE_APPLICATION_ERROR(-20135,'NOT_IN_SHIPMENT');
	END IF;
	
END verifyShipmentInCons; 
PROCEDURE findStationId
AS
	locStationId VARCHAR(20);
BEGIN
	debugMsg('------ FindStationId ---------'); 

	SELECT
		DEST_LOC_CD
	INTO
		locStationId
	FROM 
		ENT.PIECE P, 
		ACCS.ACCS_CORE_DELIVERY_ADDRESS ACDA 
	WHERE 
		P.TRACKING_NBR = globalScan.AirbillNumber
		AND (ACDA.OID_NBR = P.PIECE_OID_NBR OR ACDA.OID_NBR = P.SHIPMENT_OID_NBR);
		
EXCEPTION 
	WHEN NO_DATA_FOUND THEN
		globalScan.ErrorNumber := 203;
		debugMsg('Error: Record not found in accs_core_delivery_address, quitting...');
		RAISE_APPLICATION_ERROR(-20203,'ID_NOT_FOUND');
	

END findStationId; 
PROCEDURE verifyStationId
AS
	locStationId VARCHAR(20);
BEGIN
	debugMsg('------ VerifyStationId ---------'); 

	IF globalScan.DestLocationCode IS NULL  THEN
		globalScan.ErrorNumber := 202;
		debugMsg('Error: DestLocationCode not pass in, quitting...');
		RAISE_APPLICATION_ERROR(-20202,'ID_NOT_ASSIGN');
	END IF;
	SELECT
		DEST_LOC_CD
	INTO
		locStationId		
	FROM 
		ENT.PIECE P, 
		ACCS.ACCS_CORE_DELIVERY_ADDRESS ACDA 
	WHERE 
		P.TRACKING_NBR = globalScan.AirbillNumber
		AND (ACDA.OID_NBR = P.PIECE_OID_NBR OR ACDA.OID_NBR = P.SHIPMENT_OID_NBR)
		AND ACDA.DEST_LOC_CD = globalScan.DestLocationCode;
	debugMsg('DestLocationCode match with pass in Station Id.');
	debugMsg('Done,quiting verifyStationId...');
	
EXCEPTION 
	WHEN NO_DATA_FOUND THEN
		globalScan.ErrorNumber := 131;
		debugMsg('DestLocationCode is not match with pass in station id, quitting...');
		RAISE_APPLICATION_ERROR(-20131,'WRONG_SHUTTLE');
END verifyStationId; 
PROCEDURE postScan
AS
	locActivityCode VARCHAR2(6);
	locOidNumber 	NUMBER;
	locCount	NUMBER;
BEGIN
	debugMsg('------ postScan ------');
	BEGIN
		SELECT
			ACTIVITY_CD
		INTO
			locActivityCode
		FROM
			CCII.SCAN_DEF
		WHERE
			SCAN_TYPE_CD = globalCCIIState.ScanTypeCode
			AND LOC_STAT_NBR = globalScan.LocStatNbr
			AND LOCATION_CD = globalScan.LocationCode
			AND RECORD_ACTIVE_FLG = 'Y';
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			debugMsg('Scan definition not found');
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
	END;

	IF locActivityCode IS NOT NULL THEN
		locCount := globalActivityCodeArray.COUNT + 1;
		globalActivityCodeArray(locCount) := (locActivityCode || '|' || '|');
	END IF;

	BEGIN
		SELECT
			ENT.SCAN_SEQ.nextval
		INTO
			locOidNumber
		FROM
			dual;
		INSERT INTO ENT.SCAN
			(SCAN_OID_NBR,
			PIECE_OID_NBR,
			SHIPMENT_OID_NBR,
			SCAN_DT,
			EMPLOYEE_NBR,
			SCAN_TYPE_CD,
			LOC_STAT_NBR,
			STAGING_AREA_CD,
			STAGING_AREA_FLG,
			FROM_CONS_FLG,
			SORT_DT,
			SCAN_LOCATION_DESC,
			LOCATION_CD,
			WAREHOUSE_CD,
			INPUT_METHOD_CD,
			COMMENT_DESC )
		VALUES
			(locOidNumber,
			globalScan.HandlingUnitOid,
			globalScan.ShipmentOid,
			SYSDATE_GMT,
			globalScan.UserLogon,
			globalCCIIState.ScanTypeCode,
			globalScan.LocStatNbr,
			globalCCIIState.StagingArea,
			'',
			globalScan.FromConsFlag,
			SYSDATE_GMT,
			'',
			globalScan.LocationCode,
			globalScan.WarehouseCode,
			globalScan.InputMethodCode,
			globalScan.Comments);
	EXCEPTION
		WHEN OTHERS THEN
			debugMsg('Failed to create scan record');
			globalScan.ErrorNumber := 57;
			RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
	END;
END postScan; 
PROCEDURE postActivity
AS
	locVarArray VARCHAR_ARRAY;
	locActivityCode VARCHAR2(12);
	locInterceptCode VARCHAR2(12);
	locAgencyCode VARCHAR2(12);
	locOidType VARCHAR2(1);
	locOidNumber NUMBER(12,0);
	locActivityOid NUMBER(12,0);
	locActivityDesc VARCHAR2(80);
BEGIN
	debugMsg('------ postActivity ------');
	FOR indx IN globalActivityCodeArray.FIRST .. globalActivityCodeArray.LAST
	LOOP
		locVarArray := splitFunc(globalActivityCodeArray(indx),CHR(124));
		IF locVarArray.EXISTS(1) THEN
			locActivityCode := locVarArray(1);
		ELSE
			EXIT;
		END IF;
		IF locVarArray.EXISTS(2) THEN
			locInterceptCode := locVarArray(2);
		END IF;
		IF locVarArray.EXISTS(3) THEN
			locAgencyCode := locVarArray(3);
		END IF;

		BEGIN
			SELECT
				OID_TYPE_CD,
				ACTIVITY_TEXT_TEMPLATE_DESC
			INTO
				locOidType,
				locActivityDesc
			FROM 
				ENT.ACTIVITY_DEF
			WHERE
				ACTIVITY_CD = locActivityCode
				AND LOCATION_CD = globalScan.LocationCode;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				debugMsg('Activity definition not found');
				globalScan.ErrorNumber := 57;
				RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
		END;
		CASE locOidType
		WHEN 'S' THEN
			locOidNumber := globalScan.ShipmentOid;
		WHEN 'P' THEN
			locOidNumber := globalScan.HandlingUnitOid;
		WHEN 'R' THEN
			locOidNumber := globalCCIIState.RouteLegOid;
		ELSE
			locOidNumber := 0;
		END CASE;

		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'TrkNbr'||CHR(92)||CHR(92),globalScan.AirbillNumber);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'CONSID'||CHR(92)||CHR(92),globalScan.ConsId);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'LocStatNbr'||CHR(92)||CHR(92),globalScan.LocStatNbr);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'StageArea'||CHR(92)||CHR(92),globalCCIIState.StagingArea);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'DetainLoc'||CHR(92)||CHR(92),globalScan.DetainLoc);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'HAWB'||CHR(92)||CHR(92),globalScan.HAWB);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'ROUTENUMBER'||CHR(92)||CHR(92),globalCCIIState.RouteNumber);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'USERLOGON'||CHR(92)||CHR(92),globalScan.UserLogon);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'IntCd'||CHR(92)||CHR(92),globalScan.InterceptCode);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'AgencyCd'||CHR(92)||CHR(92),globalScan.AgencyCode);
		locActivityDesc := REPLACE(locActivityDesc,CHR(92)||CHR(92)||'SCANCD'||CHR(92)||CHR(92),globalCCIIState.ScanTypeCode);

		BEGIN
			SELECT
				ENT.CLEARANCE_ACTIVITY_SEQ.nextval
			INTO
				locActivityOid
			FROM
				dual;

			debugMsg('Inserting CLEARANCE_ACTIVITY record...');
			INSERT INTO ENT.CLEARANCE_ACTIVITY
				(ACTIVITY_OID_NBR,
				ACTIVITY_CD,
				ACTIVITY_DATA_DESC,
				ACTIVITY_TMSTP,
				ACTIVITY_MSEC_TMSTP,
				OID_NBR,
				OID_TYPE_CD,
				PROCESS_DESC,
				PROCESS_SOURCE_TMSTP,
				PROCESS_SOURCE_DESC,
				LAST_USER_NM,
				AGENCY_CD,
				AGENCY_LOCATION_CD,
				ACTIVITY_LOCATION_CD)
			VALUES
				(locActivityOid,
				locActivityCode,
				locActivityDesc,
				SYSDATE_GMT,
				0,
				locOidNumber,
				locOidType,
				globalCCIIState.ScanTypeCode,
				SYSDATE_GMT,
				'ScanClient',
				globalScan.UserLogon,
				locAgencyCode,
				'',
				globalScan.LocationCode);
		EXCEPTION
			WHEN OTHERS THEN
				debugMsg('Failed to insert Clearance Activity record');
				globalScan.ErrorNumber := 57;
				RAISE_APPLICATION_ERROR(-20057,'DB_ERROR');
		END;
	END LOOP;
				
END postActivity; 
PROCEDURE outputLabel(locLabelType IN VARCHAR2)
AS
BEGIN
	globalPrint.ADDRESS_CHANGE_FLIGHT_DATE := TO_CHAR(globalCCIIState.RouteDate, 'DD-MON-YY');
	globalPrint.ADDRESS_CHANGE_FLIGHT_NUMBER := globalCCIIState.RouteNumber;
	globalPrint.FLGHTDATA := CONCAT(globalPrint.ADDRESS_CHANGE_FLIGHT_NUMBER,globalPrint.ADDRESS_CHANGE_FLIGHT_DATE);

	CASE locLabelType
	WHEN 'A' THEN
		DBMS_OUTPUT.PUT_LINE('ADDRESS_LABEL' || CHR(2) || globalScan.AirbillNumber);
	WHEN 'C' THEN
		DBMS_OUTPUT.PUT_LINE('CLEARANCE_LABEL' || CHR(2) || globalScan.AirbillNumber);
	WHEN 'D' THEN
		DBMS_OUTPUT.PUT_LINE('DAMAGE_LABEL' || CHR(2) || globalScan.AirbillNumber);
	WHEN 'M' THEN
		DBMS_OUTPUT.PUT_LINE('MPSOVERAGE_LABEL' || CHR(2) || globalScan.AirbillNumber);
	WHEN 'Y' THEN
		DBMS_OUTPUT.PUT_LINE('OVERAGE_LABEL' || CHR(2) || globalScan.AirbillNumber);
	ELSE
		NULL;
	END CASE;

END outputLabel; 
PROCEDURE outputVariable
AS
BEGIN
	debugMsg('------ outputVariable ------');
	DBMS_OUTPUT.PUT_LINE('SCAN_OBJECT' || CHR(2) || 				globalScan.AddressChangeFlag || CHR(2) || 				globalScan.AgencyCode || CHR(2) || 				globalScan.AirbillFormType || CHR(2) || 				globalScan.AirbillNumber || CHR(2) || 				globalScan.AirbillType || CHR(2) || 				globalScan.ArrivalDate || CHR(2) || 				globalScan.AuthorizedBy || CHR(2) || 				globalScan.BrokerClassificationCode || CHR(2) || 				globalScan.Comments || CHR(2) || 				globalScan.ConsId || CHR(2) || 				globalScan.ConsOid || CHR(2) || 				globalScan.ConsTotalPiece || CHR(2) || 				globalScan.CountryCode || CHR(2) || 				globalScan.CustomsReleasedFlag || CHR(2) || 				globalScan.DeclarationCode || CHR(2) || 				globalScan.DetainLoc || CHR(2) || 				globalScan.DestCountry || CHR(2) || 				globalScan.DestLocationCode || CHR(2) || 				globalScan.ErrorNumber || CHR(2) || 				globalScan.FromConsFlag || CHR(2) || 				globalScan.HandlingUnitOid || CHR(2) || 				globalScan.HAWB || CHR(2) || 				globalScan.HAWBFormType || CHR(2) || 				globalScan.HAWBUnknownFlag || CHR(2) || 				globalScan.InputDevice || CHR(2) || 				globalScan.InputMethodCode || CHR(2) || 				globalScan.InterceptCode || CHR(2) || 				globalScan.InViewName || CHR(2) || 				globalScan.LocationCode || CHR(2) || 				globalScan.LocStatNbr || CHR(2) || 				globalScan.PrinterCode || CHR(2) || 				globalScan.ReleaseCode || CHR(2) || 				globalScan.ReturnMessage || CHR(2) || 				globalScan.ReturnAWBNumber || CHR(2) || 				globalScan.ShipmentOid || CHR(2) || 				globalScan.ShuttleID || CHR(2) || 				globalScan.SplitNumber || CHR(2) || 				globalScan.StatMenuCd || CHR(2) || 				globalScan.TimeZone|| CHR(2) || 				globalScan.UserLogon || CHR(2) || 				globalScan.WarehouseCode || CHR(2) || 				globalScan.ShipmentWeight || CHR(2) || 				globalScan.ShipmentValueAmt || CHR(2) || 				globalScan.CustomsReturnedCode || CHR(2) || 				globalScan.CiqReturnedCode);

	DBMS_OUTPUT.PUT_LINE('CCIISTATE_OBJECT' || CHR(2) || 				globalCCIIState.AbScanned || CHR(2) || 				globalCCIIState.AbFormTypeScanned || CHR(2) || 				globalCCIIState.AbTypeScanned || CHR(2) || 				globalCCIIState.DamagedFlag || CHR(2) || 				globalCCIIState.DiverterCode || CHR(2) || 				globalCCIIState.DuplicateFlag || CHR(2) || 				globalCCIIState.InterceptedFlag || CHR(2) || 				globalCCIIState.IntransitFlag || CHR(2) || 				globalCCIIState.IsPieceManifested || CHR(2) || 				globalCCIIState.MPSFlag || CHR(2) || 				globalCCIIState.OverageFlag || CHR(2) || 				globalCCIIState.PositiveSort || CHR(2) || 				globalCCIIState.RouteDate || CHR(2) || 				globalCCIIState.RouteLegNumber || CHR(2) || 				globalCCIIState.RouteLegOid || CHR(2) || 				globalCCIIState.RouteNumber || CHR(2) || 				globalCCIIState.RouteLegStatusCode || CHR(2) || 				globalCCIIState.ScanTypeCode || CHR(2) || 				globalCCIIState.ServiceCode || CHR(2) || 				globalCCIIState.SortDate || CHR(2) || 				globalCCIIState.StagingArea || CHR(2) || 				globalCCIIState.TravelStatus || CHR(2) || 				globalCCIIState.UploadStatus_FedExShuttleFlag);

	DBMS_OUTPUT.PUT_LINE('PRINT_OBJECT' || CHR(2) || 				globalPrint.ADDRESS_CHANGE_FLIGHT_DATE || CHR(2) || 				globalPrint.ADDRESS_CHANGE_FLIGHT_NUMBER || CHR(2) || 				globalPrint.ADDRESS_CHANGE_NAME || CHR(2) || 				globalPrint.ADDRESS_CHANGE_COMPANY || CHR(2) || 				globalPrint.ADDRESS_CHANGE_ADDRESS1 || CHR(2) || 				globalPrint.ADDRESS_CHANGE_ADDRESS2 || CHR(2) || 				globalPrint.ADDRESS_CHANGE_COMMENTS || CHR(2) || 				globalPrint.ADDRESS_CHANGE_DATE || CHR(2) || 				globalPrint.BrokerEntryNumber || CHR(2) || 				globalPrint.ClearanceStatus || CHR(2) || 				globalPrint.DateStr || CHR(2) || 				globalPrint.FLGHTDATA || CHR(2) || 				globalPrint.HandlingCode1 || CHR(2) || 				globalPrint.HandlingCode2 || CHR(2) || 				globalPrint.HandlingCode3 || CHR(2) || 				globalPrint.MAWB || CHR(2) || 				globalPrint.PieceQuantity || CHR(2) || 				globalPrint.PiorityIntercept1 || CHR(2) || 				globalPrint.PiorityIntercept2 || CHR(2) || 				globalPrint.PiorityIntercept3 || CHR(2) || 				globalPrint.PiorityIntercept4 || CHR(2) || 				globalPrint.ServiceCode || CHR(2) || 				globalPrint.TimeStr);
	
	IF globalActivityCodeArray.EXISTS(1) THEN
		FOR indx IN globalActivityCodeArray.FIRST .. globalActivityCodeArray.LAST
		LOOP
			DBMS_OUTPUT.PUT_LINE('ACTIVITY_CODE' || CHR(2) || 					globalActivityCodeArray(indx) );
		END LOOP;
	END IF;

	IF globalDupPieceArray.EXISTS(1) THEN
		FOR indx IN globalDupPieceArray.FIRST .. globalDupPieceArray.LAST
		LOOP
			$IF $$COUNTRY = 3 $THEN
				DBMS_OUTPUT.PUT_LINE('DUP_PIECE' || '|' || 					globalDupPieceArray(indx).PIECE_OID_NBR || '|' || 					globalDupPieceArray(indx).ROUTE_NBR || '|' || 					TO_CHAR(globalDupPieceArray(indx).ROUTE_DT,'DD/MM') || '|' || 					globalDupPieceArray(indx).COMMODITY_DESC);
			$ELSE	
				DBMS_OUTPUT.PUT_LINE('DUP_PIECE' || '|' || 					globalDupPieceArray(indx).PIECE_OID_NBR || '|' || 					globalDupPieceArray(indx).PIECE_QTY || '|' || 					globalDupPieceArray(indx).LBS_WGT || '|' || 					globalDupPieceArray(indx).CONSIGNEE_COMPANY_NM || '|' || 					globalDupPieceArray(indx).COMMODITY_DESC);
			$END
		END LOOP;
	END IF;

	IF globalConsArray.EXISTS(1) THEN
		FOR indx IN globalConsArray.FIRST .. globalConsArray.LAST
		LOOP
			DBMS_OUTPUT.PUT_LINE('CONS_INFO' || '|' || 					globalConsArray(indx).CONS_OID_NBR || '|' || 					globalConsArray(indx).TRACKING_NBR || '|' || 					globalConsArray(indx).FORM_TYPE_CD || '|' || 					globalConsArray(indx).CONS_NBR || '|' || 					globalConsArray(indx).CONS_FORM_TYPE_CD || '|' || 					globalConsArray(indx).OID_NBR || '|' || 					globalConsArray(indx).OID_TYPE_CD); 
		END LOOP;
	END IF;
END outputVariable; PROCEDURE showResult
AS
	locCount NUMBER := 0;
BEGIN
	debugMsg('----- SCAN OBJECT -----');
	debugMsg('AddressChangeFlag: ' || globalScan.AddressChangeFlag);
	debugMsg('AgencyCode: ' || globalScan.AgencyCode);
	debugMsg('AirbillFormType: ' || globalScan.AirbillFormType);
	debugMsg('AirbillNumber: ' || globalScan.AirbillNumber);
	debugMsg('AirbillType: ' || globalScan.AirbillType);
	debugMsg('ArrivalDate: ' || TO_CHAR(globalScan.ArrivalDate,'DD-MON-YY HH24:MI:SS'));
	debugMsg('AuthorizedBy: ' || globalScan.AuthorizedBy);
	debugMsg('BrokerClassificationCode: ' || globalScan.BrokerClassificationCode);
	debugMsg('Comments: ' || globalScan.Comments);
	debugMsg('ConsDestLoc: ' || globalScan.ConsDestLoc);
	debugMsg('ConsFormType: ' || globalScan.ConsFormType);
	debugMsg('ConsId: ' || globalScan.ConsId);
	debugMsg('ConsOid: ' || globalScan.ConsOid);
	debugMsg('ConsTotalPiece: ' || globalScan.ConsTotalPiece);
	debugMsg('ContainerId: ' || globalScan.ContainerId);
	debugMsg('ContainerInd: ' || globalScan.ContainerInd);
	debugMsg('CountryCode: ' || globalScan.CountryCode);
	debugMsg('CustomsReleasedFlag: ' || globalScan.CustomsReleasedFlag);
	debugMsg('DeclarationCode: ' || globalScan.DeclarationCode);
	debugMsg('DetainLoc: ' || globalScan.DetainLoc);
	debugMsg('DestCountry: ' || globalScan.DestCountry);
	debugMsg('DestLocationCode: ' || globalScan.DestLocationCode);
	debugMsg('ErrorNumber: ' || globalScan.ErrorNumber);
	debugMsg('FromConsFlag: ' || globalScan.FromConsFlag);
	debugMsg('HandlingUnitOid: ' || globalScan.HandlingUnitOid);
	debugMsg('HAWB: ' || globalScan.HAWB);
	debugMsg('HAWBFormType: ' || globalScan.HAWBFormType);
	debugMsg('InputDevice: ' || globalScan.InputDevice);
	debugMsg('InputMethodCode: ' || globalScan.InputMethodCode);
	debugMsg('InterceptCode: ' || globalScan.InterceptCode);
	debugMsg('InViewName: ' || globalScan.InViewName);
	debugMsg('LocationCode: ' || globalScan.LocationCode);
	debugMsg('LocStatNbr: ' || globalScan.LocStatNbr);
	debugMsg('PrinterCode: ' || globalScan.PrinterCode);
	debugMsg('ReleaseCode: ' || globalScan.ReleaseCode);
	debugMsg('ReturnMessage: ' || globalScan.ReturnMessage);
	debugMsg('ReturnAWBNumber: ' || globalScan.ReturnAWBNumber);
	debugMsg('ShipmentOid: ' || globalScan.ShipmentOid);
	debugMsg('ShuttleID: ' || globalScan.ShuttleID);
	debugMsg('SplitNumber: ' || globalScan.SplitNumber);
	debugMsg('StatMenuCd: ' || globalScan.StatMenuCd);
	debugMsg('TimeZone: ' || globalScan.TimeZone);
	debugMsg('UserLogon: ' || globalScan.UserLogon);
	debugMsg('WarehouseCode: ' || globalScan.WarehouseCode);
	debugMsg('ShipmentWeight: ' || globalScan.ShipmentWeight);
	debugMsg('ShipmentValueAmt: '|| globalScan.ShipmentValueAmt);
	debugMsg('CustomsReturnedCode: '|| globalScan.CustomsReturnedCode);
	debugMsg('CiqReturnedCode: '|| globalScan.CiqReturnedCode);

	debugMsg('----- CCII_STATE OBJECT -----');
	debugMsg('AbScanned: ' || globalCCIIState.AbScanned);
	debugMsg('AbFormTypeScanned: ' || globalCCIIState.AbFormTypeScanned);
	debugMsg('AbTypeScanned: ' || globalCCIIState.AbTypeScanned);
	debugMsg('DamagedFlag: ' || globalCCIIState.DamagedFlag);
	debugMsg('DiverterCode: ' || globalCCIIState.DiverterCode);
	debugMsg('DuplicateFlag: ' || globalCCIIState.DuplicateFlag);
        debugMsg('InterceptedFlag: ' || globalCCIIState.InterceptedFlag);
	debugMsg('IntransitFlag: ' || globalCCIIState.IntransitFlag);
	debugMsg('IsPieceManifested: ' || globalCCIIState.IsPieceManifested);
	debugMsg('MPSFlag: ' || globalCCIIState.MPSFlag);
	debugMsg('OverageFlag: ' || globalCCIIState.OverageFlag);
	debugMsg('PositiveSort: ' || globalCCIIState.PositiveSort);
	debugMsg('RouteDate: ' || TO_CHAR(globalCCIIState.RouteDate,'DD-MON-YY HH24:MI:SS'));
	debugMsg('RouteLegNumber: ' || globalCCIIState.RouteLegNumber);
	debugMsg('RouteLegOid: ' || globalCCIIState.RouteLegOid);
	debugMsg('RouteNumber: ' || globalCCIIState.RouteNumber);
	debugMsg('RouteLegStatusCode: ' || globalCCIIState.RouteLegStatusCode);
	debugMsg('ScanTypeCode: ' || globalCCIIState.ScanTypeCode);
	debugMsg('ServiceCode: ' || globalCCIIState.ServiceCode);
	debugMsg('SortDate: ' || TO_CHAR(globalCCIIState.SortDate,'DD-MON-YY HH24:MI:SS'));
	debugMsg('StagingArea: ' || globalCCIIState.StagingArea);
	debugMsg('TravelStatus: ' || globalCCIIState.TravelStatus);
	debugMsg('UploadStatus_FedExShuttleFlag: ' || globalCCIIState.UploadStatus_FedExShuttleFlag);

	debugMsg('----- PRINT OBJECT -----');
	debugMsg('ADDRESS_CHANGE_FLIGHT_DATE: ' || globalPrint.ADDRESS_CHANGE_FLIGHT_DATE);
	debugMsg('ADDRESS_CHANGE_FLIGHT_NUMBER: ' || globalPrint.ADDRESS_CHANGE_FLIGHT_NUMBER);
	debugMsg('ADDRESS_CHANGE_NAME: ' || globalPrint.ADDRESS_CHANGE_NAME);
	debugMsg('ADDRESS_CHANGE_COMPANY: ' || globalPrint.ADDRESS_CHANGE_COMPANY);
	debugMsg('ADDRESS_CHANGE_ADDRESS1: ' || globalPrint.ADDRESS_CHANGE_ADDRESS1);
	debugMsg('ADDRESS_CHANGE_ADDRESS2: ' || globalPrint.ADDRESS_CHANGE_ADDRESS2);
	debugMsg('ADDRESS_CHANGE_COMMENTS: ' || globalPrint.ADDRESS_CHANGE_COMMENTS);
	debugMsg('ADDRESS_CHANGE_DATE: ' || TO_CHAR(globalPrint.ADDRESS_CHANGE_DATE,'DD-MON-YY HH24:MI:SS'));
	debugMsg('BrokerEntryNumber: ' || globalPrint.BrokerEntryNumber);
	debugMsg('ClearanceStatus: ' || globalPrint.ClearanceStatus);
	debugMsg('DateStr: ' || globalPrint.DateStr);
	debugMsg('FLGHTDATA: ' || globalPrint.FLGHTDATA);
	debugMsg('HandlingCode1: ' || globalPrint.HandlingCode1);
	debugMsg('HandlingCode2: ' || globalPrint.HandlingCode2);
	debugMsg('HandlingCode3: ' || globalPrint.HandlingCode3);
	debugMsg('MAWB: ' || globalPrint.MAWB);
	debugMsg('PieceQuantity: ' || globalPrint.PieceQuantity);
	debugMsg('PiorityIntercept1: ' || globalPrint.PiorityIntercept1);
	debugMsg('PiorityIntercept2: ' || globalPrint.PiorityIntercept2);
	debugMsg('PiorityIntercept3: ' || globalPrint.PiorityIntercept3);
	debugMsg('PiorityIntercept4: ' || globalPrint.PiorityIntercept4);
	debugMsg('ServiceCode: ' || globalPrint.ServiceCode);
	debugMsg('TimeStr: ' || globalPrint.TimeStr);

	IF globalPieceArray.COUNT > 0 THEN
		debugMsg('----- Piece Array -----');
		FOR indx IN globalPieceArray.FIRST .. globalPieceArray.LAST
		LOOP
			debugMsg('PieceOid: ' || globalPieceArray(1).PIECE_OID_NBR);
		END LOOP;
	END IF;

	IF globalActivityCodeArray.COUNT > 0 THEN
		debugMsg('----- Activity Code Array -----');
		FOR indx IN globalActivityCodeArray.FIRST .. globalActivityCodeArray.LAST
		LOOP
			debugMsg('Activity Code: ' || globalActivityCodeArray(indx));	
		END LOOP;
	END IF;
END showResult; 
PROCEDURE bondin (AirbillNumber IN VARCHAR2,
        AirbillFormType IN VARCHAR2,
        AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
        UserLogon IN VARCHAR2,
        LocationCode IN VARCHAR2,
        WarehouseCode IN VARCHAR2,
        InViewName IN VARCHAR2,
        ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2)
AS
BEGIN
	globalScan.AirbillNumber := AirbillNumber;
	globalScan.AirbillFormType := AirbillFormType;
	globalScan.AirbillType := AirbillType;
	globalScan.HandlingUnitOid := HandlingUnitOid;
	globalScan.UserLogon := UserLogon;
	globalScan.LocationCode := LocationCode;
	globalScan.WarehouseCode := WarehouseCode;
	globalScan.InViewName := InViewName;
	globalCCIIState.ScanTypeCode := ScanTypeCode;
	globalScan.PrinterCode := PrinterCode;
	globalCCIIState.AbScanned := globalScan.AirbillNumber;
	globalCCIIState.AbFormTypeScanned := globalScan.AirbillFormType;
	globalScan.CountryCode := readConfig('COUNTRY_CODE',globalScan.LocationCode,'KR');

	globalScan.TimeZone := readConfig('TIMEZONE_HOUR',globalScan.LocationCode,9);
	globalPrint.DateStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'DDMONYYYY');
	globalPrint.TimeStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'HH24:MI');

	SCANCLIENT.getContext;

	IF globalCCIIState.DuplicateFlag = 'Y' THEN
		SCANCLIENT.duplicateProcessing;
		NULL;
	END IF;

	IF globalCCIIState.OverageFlag IS NOT NULL THEN
		SCANCLIENT.overageHandling;
	END IF;
	
	SCANCLIENT.verifyArrivalScan;

	IF globalCCIIState.OverageFlag IS NULL OR (globalCCIIState.OverageFlag != 'Y' AND globalCCIIState.OverageFlag != 'M' 
	AND globalCCIIState.OverageFlag != 'D' AND globalCCIIState.OverageFlag != 'DELETED' AND globalCCIIState.OverageFlag != 'S' ) THEN
		SCANCLIENT.verifyRoute;
	END IF;

	IF ( globalCCIIState.MPSFlag = 'MASTER' OR globalCCIIState.MPSFlag = 'CRN' ) AND ( readConfig('SITE_MPS_STAGING',globalScan.LocationCode,'') = 'ALWAYS' 
	OR readConfig('SITE_MPS_STAGING',globalScan.LocationCode,'') = 'INTERCEPT' ) THEN
		SCANCLIENT.createIntercept;
	END IF;

	IF globalCCIIState.InterceptedFlag IS NULL OR globalCCIIState.InterceptedFlag != 'NOMATCH' THEN
		SCANCLIENT.findIntercept;
	END IF;

	SCANCLIENT.findHandlingInstruction;

	IF globalScan.PrinterCode IS NOT NULL AND globalCCIIState.OverageFlag IS NULL THEN
		SCANCLIENT.findExternalShipmentInfoAPAC;
		SCANCLIENT.verifyAddressChange;
		IF globalCCIIState.DamagedFlag = 'TRUE' THEN
			debugMsg('Shipment damaged, going to print damage label');
			SCANCLIENT.outputLabel('D');
		END IF;
		IF globalScan.AddressChangeFlag = 'TRUE' THEN
			debugMsg('Address changed, going to print AddressChange label');
			SCANCLIENT.outputLabel('A');
		END IF;
		SCANCLIENT.outputLabel('C');
	END IF;
	
	IF globalCCIIState.OverageFlag IS NULL THEN
		SCANCLIENT.createAccsShipStatus;
	ELSE
		debugMsg('Overage shipment, no AccsShipStatus record creation');
	END IF;

	DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
				globalScan.InViewName || CHR(124) ||
				globalCCIIState.ScanTypeCode || CHR(124) ||
				globalScan.LocStatNbr);

	--Customization for AU
	$IF $$COUNTRY=4 $THEN
		--Auto STAT 64 for shipment with 'N' selection code
		IF MatchString(globalScan.InterceptCode,'N') = 1 THEN
			DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
						'STATVIEW' || CHR(124) ||
						'STAT' || CHR(124) ||
						'64');
		END IF;
			
		--Auto STAT 63 for shipment with 'J' selection code
		IF MatchString(globalScan.InterceptCode,'J') = 1 THEN
			DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
						'STATVIEW' || CHR(124) ||
						'STAT' || CHR(124) ||
						'63');
		END IF;

		--Auto create a bond-out record if let's 'CLEAR'
		IF globalCCIIState.InterceptedFlag = 'C' AND globalCCIIState.OverageFlag IS NULL THEN
			globalCCIIState.ScanTypeCode := 'BOUT';
			SCANCLIENT.postScan;
		END IF;
	$END
	
	SCANCLIENT.showResult;
	SCANCLIENT.outputVariable;

EXCEPTION
	WHEN OTHERS THEN
		debugMsg(SQLCODE || SQLERRM);
		SCANCLIENT.outputVariable;
		SCANCLIENT.showResult;

END bondin; 
PROCEDURE bondout (AirbillNumber IN VARCHAR2,
        AirbillFormType IN VARCHAR2,
        AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
        UserLogon IN VARCHAR2,
        LocationCode IN VARCHAR2,
        WarehouseCode IN VARCHAR2,
        InViewName IN VARCHAR2,
        ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2)
AS
	locCount NUMBER;
BEGIN
	globalScan.AirbillNumber := AirbillNumber;
	globalScan.AirbillFormType := AirbillFormType;
	globalScan.AirbillType := AirbillType;
	globalScan.HandlingUnitOid := HandlingUnitOid;
	globalScan.ConsId := ConsId;
	globalScan.UserLogon := UserLogon;
	globalScan.LocationCode := LocationCode;
	globalScan.WarehouseCode := WarehouseCode;
	globalScan.InViewName := InViewName;
	globalCCIIState.ScanTypeCode := ScanTypeCode;
	globalScan.PrinterCode := PrinterCode;
	globalScan.CountryCode := readConfig('COUNTRY_CODE',globalScan.LocationCode,'KR');

	globalScan.TimeZone := readConfig('TIMEZONE_HOUR',globalScan.LocationCode,9);
	globalPrint.DateStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'DDMONYYYY');
	globalPrint.TimeStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'HH24:MI');

	SCANCLIENT.getContext;

	IF globalCCIIState.DuplicateFlag = 'Y' THEN
		SCANCLIENT.duplicateProcessing;
		NULL;
	END IF;

	IF globalCCIIState.OverageFlag = 'Y' THEN
		debugMsg('Bond out an overage shipment is not allowed');
		globalScan.ErrorNumber := 204;
		RAISE_APPLICATION_ERROR(-20204,'OVERAGE');
	END IF;
	
	SCANCLIENT.verifyArrivalScan;

	SCANCLIENT.verifyDepartureScan;
	
	SCANCLIENT.findInboundRouteInfo;

	SCANCLIENT.findExternalShipmentInfoAPAC;

	IF globalScan.BrokerClassificationCode = 'DOC' OR globalScan.BrokerClassificationCode = 'A' THEN
		debugMsg('Bond out for DOC or Class A is not allowed');
		globalScan.ErrorNumber := 205;
		RAISE_APPLICATION_ERROR(-20204,'BOND_OUT_NOT_ALLOWED');
	END IF;

	SCANCLIENT.findMPSShortage;

	SCANCLIENT.findIntercept;

	IF globalCCIIState.InterceptedFlag = 'C' THEN
		SCANCLIENT.verifyRelease;
	END IF;

	CASE globalScan.CountryCode
	WHEN 'JP' THEN
		IF (globalCCIIState.OverageFlag IS NOT NULL) AND globalCCIIState.OverageFlag != 'M' AND globalCCIIState.OverageFlag != 'Y'
		AND globalCCIIState.OverageFlag != 'SORTDATE' THEN
			SCANCLIENT.findShuttle;
			SCANCLIENT.verifyShuttle;
		END IF;
	ELSE
		NULL;
	END CASE;

	IF globalCCIIState.InterceptedFlag IS NOT NULL AND globalCCIIState.InterceptedFlag != 'C' THEN
		locCount := globalActivityCodeArray.COUNT + 1;
		globalActivityCodeArray(locCount) := (readConfig('DEPARTURE_NOT_CLEARED_ACTIVITY_CODE',globalScan.LocationCode,'RBOUT') || '|' || '|');
		--SCANCLIENT.addActivity(readConfig('DEPARTURE_NOT_CLEARED_ACTIVITY_CODE',globalScan.LocationCode,'RBOUT'));
		SCANCLIENT.postActivity;
	END IF;

	SCANCLIENT.findHandlingInstruction;

	IF globalScan.ConsId IS NOT NULL THEN
		FOR indx IN globalConsArray.FIRST .. globalConsArray.LAST
		LOOP
			globalScan.AirbillNumber := globalConsArray(indx).TRACKING_NBR;
			globalScan.AirbillFormType := globalConsArray(indx).FORM_TYPE_CD;
			globalScan.HandlingUnitOid:= globalConsArray(indx).OID_NBR;

			SCANCLIENT.verifyAddressChange;
			IF globalScan.AddressChangeFlag = 'TRUE' AND globalScan.ArrivalDate < globalPrint.ADDRESS_CHANGE_DATE THEN
				SCANCLIENT.outputLabel('A');
			END IF;
			IF globalCCIIState.InterceptedFlag = 'C' THEN
				globalPrint.ClearanceStatus := 'CLEAR';
				SCANCLIENT.createAccsShipStatus;
				SCANCLIENT.outputLabel('C');
			END IF;
		END LOOP;
	ELSE
		SCANCLIENT.findCons('-a');
		SCANCLIENT.verifyAddressChange;
		IF globalScan.AddressChangeFlag = 'TRUE' AND globalScan.ArrivalDate  Underbond ');
			SCANCLIENT.handlingUnderbond;
		END IF;
	$END
	
	SCANCLIENT.showResult;
	SCANCLIENT.outputVariable;

EXCEPTION
	WHEN OTHERS THEN
		debugMsg(SQLCODE || SQLERRM);
		SCANCLIENT.outputVariable;
		SCANCLIENT.showResult;

END bondout; 
PROCEDURE batchAdd (AirbillNumber IN VARCHAR2,
        AirbillFormType IN VARCHAR2,
        AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
        UserLogon IN VARCHAR2,
        LocationCode IN VARCHAR2,
        WarehouseCode IN VARCHAR2,
        InViewName IN VARCHAR2,
        ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2)
AS
BEGIN
	globalScan.AirbillNumber := AirbillNumber;
	globalScan.AirbillFormType := AirbillFormType;
	globalScan.AirbillType := AirbillType;
	globalScan.HandlingUnitOid := HandlingUnitOid;
	globalScan.ConsId := ConsId;
	globalScan.UserLogon := UserLogon;
	globalScan.LocationCode := LocationCode;
	globalScan.WarehouseCode := WarehouseCode;
	globalScan.InViewName := InViewName;
	globalCCIIState.ScanTypeCode := ScanTypeCode;
	globalScan.PrinterCode := PrinterCode;
	globalCCIIState.AbScanned := globalScan.AirbillNumber;
	globalCCIIState.AbFormTypeScanned := globalScan.AirbillFormType;
	globalScan.CountryCode := readConfig('COUNTRY_CODE',globalScan.LocationCode,'KR');

	globalScan.TimeZone := readConfig('TIMEZONE_HOUR',globalScan.LocationCode,9);
	globalPrint.DateStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'DDMONYYYY');
	globalPrint.TimeStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'HH24:MI');

	IF globalScan.ConsId IS NOT NULL THEN
		SCANCLIENT.createConsSummary;
	END IF;
	IF globalScan.ConsOid IS NULL OR globalScan.ConsOid = 0 THEN
		debugMsg('Error, ConsOid is not set');
		globalScan.ErrorNumber := 61;
		RAISE_APPLICATION_ERROR(-20061, 'START_OVER');
	END IF;

	SCANCLIENT.getContext;

	IF globalCCIIState.DuplicateFlag = 'Y' THEN
		SCANCLIENT.duplicateProcessing;
		NULL;
	END IF;

	IF globalCCIIState.OverageFlag IS NOT NULL THEN
		IF globalCCIIState.OverageFlag = 'Y' THEN
			SCANCLIENT.shipmentOverage;
			SCANCLIENT.handlingUnitOverage;
			IF globalScan.CountryCode != 'KR' THEN
				globalScan.InterceptCode := readConfig('OVERAGE_INTERCEPT_CODE',globalScan.LocationCode,'O');
				globalScan.AgencyCode := readConfig('OVERAGE_AGENCY_CODE',globalScan.LocationCode,'O');
				SCANCLIENT.createIntercept;
			END IF;
		END IF;
		IF globalScan.ShipmentOid = 0 THEN
			SCANCLIENT.shipmentOverage;
		END IF;
		IF globalScan.handlingUnitOid = 0 THEN
			SCANCLIENT.handlingUnitOverage;
		END IF;
		IF globalCCIIState.OverageFlag = 'M' THEN
			globalScan.InterceptCode := readConfig('OVERAGE_INTERCEPT_CODE',globalScan.LocationCode,'O');
			globalScan.AgencyCode := readConfig('OVERAGE_AGENCY_CODE',globalScan.LocationCode,'O');
			SCANCLIENT.createIntercept;
		END IF;
	END IF;

	IF globalScan.AirbillNumber IS NOT NULL THEN
		SCANCLIENT.verifyArrivalScan;
		SCANCLIENT.verifyDepartureScan;
		IF globalScan.CountryCode != 'AU' THEN
			SCANCLIENT.verifyShipmentInCons;
		END IF;
		SCANCLIENT.createCons;	
		IF globalScan.CountryCode != 'AU' THEN
			SCANCLIENT.postScan;		
			SCANCLIENT.postActivity;
		END IF;

		 --Customization for AU
        	IF globalScan.CountryCode = 'AU' THEN
                	DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
                        	        globalScan.InViewName || CHR(124) ||
                                	globalCCIIState.ScanTypeCode || CHR(124) ||
	                                globalScan.LocStatNbr);
	        END IF;
	END IF;
	
	SCANCLIENT.showResult;
	SCANCLIENT.outputVariable;

EXCEPTION
	WHEN OTHERS THEN
		debugMsg(SQLCODE || SQLERRM);
		IF globalScan.ErrorNumber < 31 THEN
			globalScan.ErrorNumber := 57;
		END IF;
		SCANCLIENT.outputVariable;
		SCANCLIENT.showResult;

END batchAdd; 
PROCEDURE batchRemove (AirbillNumber IN VARCHAR2,
        AirbillFormType IN VARCHAR2,
        AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
        UserLogon IN VARCHAR2,
        LocationCode IN VARCHAR2,
        WarehouseCode IN VARCHAR2,
        InViewName IN VARCHAR2,
        ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2)
AS
BEGIN
	globalScan.AirbillNumber := AirbillNumber;
	globalScan.AirbillFormType := AirbillFormType;
	globalScan.AirbillType := AirbillType;
	globalScan.HandlingUnitOid := HandlingUnitOid;
	globalScan.ConsId := ConsId;
	globalScan.UserLogon := UserLogon;
	globalScan.LocationCode := LocationCode;
	globalScan.WarehouseCode := WarehouseCode;
	globalScan.InViewName := InViewName;
	globalCCIIState.ScanTypeCode := ScanTypeCode;
	globalScan.PrinterCode := PrinterCode;
	globalCCIIState.AbScanned := globalScan.AirbillNumber;
	globalCCIIState.AbFormTypeScanned := globalScan.AirbillFormType;
	globalScan.CountryCode := readConfig('COUNTRY_CODE',globalScan.LocationCode,'KR');

	globalScan.TimeZone := readConfig('TIMEZONE_HOUR',globalScan.LocationCode,9);
	globalPrint.DateStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'DDMONYYYY');
	globalPrint.TimeStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'HH24:MI');

	SCANCLIENT.findTrackingNumber;

	IF globalScan.ConsId IS NOT NULL THEN
		SCANCLIENT.findConsSummary;
	END IF;

	IF globalScan.ConsOid IS NOT NULL AND globalScan.ConsOid != 0 THEN
		SCANCLIENT.findCons('-c');
	END IF;

	SCANCLIENT.removeCons;
	IF globalScan.CountryCode != 'AU' THEN
                        SCANCLIENT.postScan;
                        SCANCLIENT.postActivity;
        END IF;
	
	--Customization for AU
        IF globalScan.CountryCode = 'AU' THEN
        	DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
                	globalScan.InViewName || CHR(124) ||
                        globalCCIIState.ScanTypeCode || CHR(124) ||
                        globalScan.LocStatNbr);
        END IF;

	SCANCLIENT.showResult;
	SCANCLIENT.outputVariable;

EXCEPTION
	WHEN OTHERS THEN
		debugMsg(SQLCODE || SQLERRM);
		IF globalScan.ErrorNumber 0 THEN
			globalScan.CustomsReleasedFlag := 'Y';
			NULL;
		ELSE
			SCANCLIENT.verifyRelease;
		END IF;
	END IF;
	
	SCANCLIENT.findHandlingInstruction;
	
	IF globalCCIIState.InterceptedFlag = 'C' AND globalScan.CustomsReleasedFlag = 'Y'  THEN
		globalPrint.ClearanceStatus := 'CLEAR';
	END IF;
	
	SCANCLIENT.verifyAddressChange;
	
	IF globalScan.AddressChangeFlag = 'TRUE' THEN
		SCANCLIENT.outputLabel('A');
	END IF;
	
	SCANCLIENT.outputLabel('C');
	
	debugMsg('Done, quiting queryScan...');
	
	SCANCLIENT.showResult;
	SCANCLIENT.outputVariable;
	
EXCEPTION
	WHEN OTHERS THEN
		debugMsg(SQLCODE || SQLERRM);
		SCANCLIENT.outputVariable;
		SCANCLIENT.showResult;
	
END queryScan; 
PROCEDURE corpScan(AirbillNumber IN VARCHAR2,
	AirbillFormType IN VARCHAR2,
	AirbillType IN VARCHAR2,
	HandlingUnitOid IN NUMBER,
	ConsId IN VARCHAR2,
	UserLogon IN VARCHAR2,
	LocationCode IN VARCHAR2,
	WarehouseCode IN VARCHAR2,
	InViewName IN VARCHAR2,
	ScanTypeCode IN VARCHAR2,
	PrinterCode IN VARCHAR2,
	SComment IN VARCHAR2,
	AuthorizedBy IN VARCHAR2,
	DestLocationCode IN VARCHAR2,
	LocStatNbr IN VARCHAR2)
AS
	locCount NUMBER;
BEGIN
	debugMsg('------ corpScan ---------'); 
	--init
	globalScan.AirbillNumber := AirbillNumber;
	globalScan.AirbillFormType := AirbillFormType;
	globalScan.AirbillType := AirbillType;
	globalScan.HandlingUnitOid := HandlingUnitOid;
	globalCCIIState.AbScanned := AirbillNumber;
	globalCCIIState.AbFormTypeScanned := AirbillFormType;
	globalCCIIState.AbTypeScanned := AirbillType;
	globalScan.UserLogon := UserLogon;
	globalScan.LocationCode := LocationCode;
	globalScan.WarehouseCode := WarehouseCode;
	globalScan.PrinterCode := PrinterCode;
	globalScan.ErrorNumber := 0;
	globalScan.ShipmentOid := 0;
	globalScan.HandlingUnitOid := HandlingUnitOid;
	globalScan.ConsId := ConsId;
	globalScan.LocStatNbr := LocStatNbr;
	globalScan.DestLocationCode := DestLocationCode;
	globalScan.Comments := REPLACE(SComment,CHR(13),' ');

	globalScan.CountryCode := readConfig('COUNTRY_CODE',globalScan.LocationCode,'KR');
	globalCCIIState.PositiveSort := readConfig('POSITIVE_SORT',globalScan.LocationCode,'TRUE');	
        globalScan.TimeZone := readConfig('TIMEZONE_HOUR',globalScan.LocationCode,9);
        globalPrint.DateStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'DDMONYYYY');
        globalPrint.TimeStr := TO_CHAR(SYSDATE_GMT + globalScan.TimeZone/24,'HH24:MI');

	IF globalScan.LocStatNbr = '00' THEN --If is a comment scan
		globalScan.InViewName := 'CMTVIEW';
		globalCCIIState.ScanTypeCode := 'CMT';
		debugMsg('Perform Comment Scan; Comment = ' || SComment );
	ELSE --If is a stat scan
		globalScan.InViewName := 'STATVIEW';
		globalCCIIState.ScanTypeCode := 'STAT';
		debugMsg('Perform Stat Scan: LocStatNbr '|| globalScan.LocStatNbr);
	END IF;
	
	-- For Korea, only return to sender, same tracking number
	IF globalScan.LocStatNbr = '14' THEN -- If is a return shipment scan
		globalScan.ReturnAWBNumber := AirbillNumber;
		globalScan.AuthorizedBy := AuthorizedBy;
		globalScan.StatMenuCd := 1;
		NULL;
	END IF;
	
	--do it
	SCANCLIENT.getContext;
	IF globalCCIIState.DuplicateFlag = 'Y' THEN
		SCANCLIENT.duplicateProcessing;
	END IF;
	
	IF globalScan.LocStatNbr = '68' THEN
		--SCANCLIENT.findStationId;
		--SCANCLIENT.verifyStationId;
		SCANCLIENT.findShuttle;
		SCANCLIENT.verifyShuttle;

		$IF $$COUNTRY = 3 $THEN
			SELECT
				COUNT(*)
			INTO
				locCount
			FROM
				ENT.SCAN
			WHERE
				SHIPMENT_OID_NBR = globalScan.ShipmentOid
				AND PIECE_OID_NBR =  globalScan.HandlingUnitOid
				AND SCAN_TYPE_CD = 'STAT'
				AND LOC_STAT_NBR = '65';
			IF locCount = 0 THEN
				debugMsg('This package has not posted stat 65');
				globalScan.ErrorNumber := 207;
				RAISE_APPLICATION_ERROR(-20207,'STAT_65_NOT_POSTED');
			END IF;
		
		--Set the DetainLoc as the DestLocationCode so that it be stored in SCAN_LOCATION_DESC in ENT.SCAN
		globalScan.DetainLoc := globalScan.DestLocationCode;

		$END
			
		IF globalCCIIState.UploadStatus_FedExShuttleFlag = 0 THEN 
			--don't post stationid if non-fedex shuttle
			globalScan.DestLocationCode := '';
		END IF;
	END IF;
	

	IF globalScan.LocStatNbr = '67' THEN
		$IF $$COUNTRY = 3 $THEN
			SELECT
				COUNT(*)
			INTO
				locCount
			FROM
				ENT.SCAN
			WHERE
				SHIPMENT_OID_NBR = globalScan.ShipmentOid
				AND PIECE_OID_NBR =  globalScan.HandlingUnitOid
				AND SCAN_TYPE_CD = 'STAT'
				AND LOC_STAT_NBR = '65';
			IF locCount = 0 THEN
				debugMsg('This package has not posted stat 65');
				globalScan.ErrorNumber := 207;
				RAISE_APPLICATION_ERROR(-20207,'STAT_65_NOT_POSTED');
			END IF;

			globalScan.DestLocationCode := '';
			--Set the DetainLoc as the QUUX so that it be stored in SCAN_LOCATION_DESC in ENT.SCAN
			globalScan.DetainLoc := 'QUUX';
		$ELSE
			NULL;	
		$END
	END IF;

	
	--sendScanUpload
	DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
				globalScan.InViewName || CHR(124) ||
				globalCCIIState.ScanTypeCode || CHR(124) ||
				globalScan.LocStatNbr || CHR(124) ||
				globalScan.Comments);
	
	--Attach a comment scan to any corp scan for KR
	$IF $$COUNTRY = 3 $THEN
		IF globalScan.LocStatNbr != '00' THEN
			DBMS_OUTPUT.PUT_LINE('SCAN_UPLOAD' || CHR(2) ||
						'CMTVIEW' || CHR(124) ||
						'CMT' || CHR(124) ||
						'00' || CHR(124) ||
						globalScan.Comments);
		END IF;
	$END
			
		
	SCANCLIENT.showResult;
	SCANCLIENT.outputVariable;
	
	
EXCEPTION
	WHEN OTHERS THEN
		debugMsg(SQLCODE || SQLERRM);
		SCANCLIENT.outputVariable;
		SCANCLIENT.showResult;
	
END corpScan; 
PROCEDURE handlingUnderbond
AS
	locRouteLegRec ENT.ROUTE_LEG%ROWTYPE;
	locCount NUMBER;
BEGIN
	debugMsg('------ handlingUnderbond ---------'); 
	BEGIN
		SELECT
			*
		INTO
			locRouteLegRec
		FROM
			ENT.ROUTE_LEG
		WHERE
			ROUTE_LEG_OID_NBR = globalCCIIState.RouteLegOid;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			debugMsg('Route Number not found in DB');
			globalScan.ErrorNumber := 143;
			RAISE_APPLICATION_ERROR(-20143,'ROUTE_NOT_FND');
	END;
	
	SELECT
		COUNT(ROUTE_LEG_OID_NBR)
	INTO 
		locCount
	FROM
		ENT.ROUTE_LEG
	WHERE
		ROUTE_DT = locRouteLegRec.ROUTE_DT
		AND ROUTE_NBR = locRouteLegRec.ROUTE_NBR || '_U'
		AND ROUTE_LEG_NBR = locRouteLegRec.ROUTE_LEG_NBR;
	
	IF locCount>0 THEN --if exist ,update
		UPDATE 
			ENT.ROUTE_LEG 
		SET 
			ROUTE_NBR = ROUTE_NBR || '_U',
			DEST_LOCATION_CD = globalScan.DestLocationCode
		WHERE
			ROUTE_LEG_OID_NBR = globalCCIIState.RouteLegOid;
	ELSE --else insert new route
		debugMsg('insert route');
		INSERT INTO
			ENT.ROUTE_LEG
		VALUES(
			ENT.ROUTE_LEG_SEQ.NEXTVAL,
			locRouteLegRec.ROUTE_DT,
			locRouteLegRec.ROUTE_NBR || '_U',
			locRouteLegRec.ROUTE_LEG_NBR,
			locRouteLegRec.MAWB_NBR,
			locRouteLegRec.DEST_LOCATION_CD,
			locRouteLegRec.ORIGIN_LOCATION_CD,
			locRouteLegRec.DEPART_GMT_TMSTP,
			locRouteLegRec.ARRIVAL_GMT_TMSTP,
			locRouteLegRec.DEPART_LOCAL_TMSTP,
			locRouteLegRec.ARRIVAL_LOCAL_TMSTP,
			locRouteLegRec.DEST_COUNTRY_CD,
			locRouteLegRec.ORIGIN_COUNTRY_CD,
			locRouteLegRec.SORT_DT,
			locRouteLegRec.ROUTE_LEG_STATUS_CD,
			locRouteLegRec.BROKER_COMPLETE_FLG,
			locRouteLegRec.CUSTOMS_COMPLETE_FLG
			);
	END IF;
	
	UPDATE
		ENT.PIECE_ROUTE_LEG
	SET 
		ROUTE_NBR = ROUTE_NBR || '_U'
	WHERE 
		PIECE_OID_NBR = globalScan.HandlingUnitOid
		AND ROUTE_LEG_NBR = globalCCIIState.RouteLegNumber;
END handlingUnderbond;

END scanclient;
/
SHOW ERRORS;

GRANT EXECUTE
        ON scanclient TO ACCSUSER;
EXIT
Advertisements

3 responses to “oracle.sql

  1. powercashadvance.com 10/26/2011 at 4:04 pm

    It’s rare to encounter an experienced person in whom you may have some confidence. In the world nowadays, nobody absolutely cares about showing others the answer in this matter. How happy I am to have actually found a really wonderful web site as this. It’s people like you who make a genuine difference currently through the tips they discuss.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: