DECLARE
MSG VARCHAR2(32767);
SV_ORDER_NO_REC SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
CURSOR C1 IS
SELECT *
FROM XXCUS.XX_TPALL_ORDERS WHERE PROCESS_FLAG=3
AND SV_ORDER_ID IN
-- PASS SV ORDER IDS BELOW
(24453,24471,24386)
---(SELECT * FROM TABLE (CAST ( SV_ORDER_NO_REC AS SYS.DBMS_DEBUG_VC2COLL )))
ORDER BY SV_ORDER_NO;
--AND TRUNC(CREATION_DATE)>=TRUNC(SYSDATE-100);
CURSOR C2(P_PARTY_ID NUMBER) IS -- SHIP TO ERROR CHECK
SELECT *
FROM XXCUS.XX_TPALL_CUST_ADDRESS XTA
WHERE XTA.PARTY_ID =P_PARTY_ID
AND XTA.PROCESS_FLAG = 3;
CURSOR C3(P_PARTY_ID NUMBER) IS -- MEDIA SOURCE ERROR CHECK
SELECT *
FROM XXCUS.XX_TPALL_PARTIES XTP
WHERE XTP.PROCESS_FLAG = 3
AND XTP.ID = P_PARTY_ID;
--AND TRUNC (XTO.CREATION_DATE) >= TRUNC(SYSDATE-1);
CURSOR C4(P_SHIP_TO_PARTY_ID NUMBER) IS
SELECT RAA.* FROM
APPS.RA_ADDRESSES_ALL RAA
,APPS.RA_SITE_USES_ALL RSU
,XXCUS.XX_TPALL_CUST_ADDRESS XTA
WHERE RAA.ADDRESS_ID=RSU.ADDRESS_ID
AND RSU.SITE_USE_ID=XTA.TP_CUST_ACCT_SITE_USE_ID
AND XTA.PARTY_ID= P_SHIP_TO_PARTY_ID
AND UPPER (XTA.ADDRESS_USE_TYPE) = 'SHIP_TO';
C4_REC C4%ROWTYPE;
BEGIN
/*
-- PASS SV_ORDER_IDS BELOW
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(1):=23954;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(2):=23962;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(3):=24300;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(4):=24308;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(5):=24184;
/*SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(6):=10726;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(7):=10821;
*/
DBMS_OUTPUT.PUT_LINE(RPAD('SV ORDER ID.',20)||' ERROR MESSAGE');
FOR C1_REC IN C1 LOOP
IF UPPER(C1_REC.ERROR_MSG) LIKE UPPER('%CALLING OE_ORDER_PUB.PROCESS_ORDER RETURN STATUS%') THEN
--DBMS_OUTPUT.PUT_LINE
--MSG:=(RPAD(C1_REC.SV_ORDER_NO,10)|| ' '||C1_REC.ERROR_MSG);
IF C1_REC.SHIP_TO_PARTY_ID IS NULL THEN
DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||'ORDER FAILED DUE TO ORACLE API VALIDATION ERRORS, SHIP_TO_PARTY_ID IS NULL.');
ELSE
IF C1_REC.SHIP_TO_PARTY_ID IS NOT NULL THEN
OPEN C4(C1_REC.SHIP_TO_PARTY_ID) ;
FETCH C4 INTO C4_REC;
CLOSE C4;
IF C4_REC.CITY IN ('APO','FPO') THEN
DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||'ORDER FAILED DUE TO ORACLE API VALIDATION ERRORS, LOOKS LIKE A MILITARY ORDER WITH FOLLOWING ADDRESS DETAILS; ADDRESS1: '||C4_REC.ADDRESS1||', CITY: '||C4_REC.CITY||', POSTAL CODE: '||C4_REC.POSTAL_CODE||'.');
END IF;
END IF;
--DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||'ORDER FAILED DUE TO ORACLE API VALIDATION ERRORS, CHECK THE ERROR IN STAGING TABLE');
END IF;
ELSIF UPPER(C1_REC.ERROR_MSG) LIKE '%SHIP%TO%' THEN
FOR C2_REC IN C2(C1_REC.PARTY_ID) LOOP
IF UPPER(C2_REC.ERROR_MSG) LIKE UPPER('%COUNTY CODE DOES NOT EXISTS FOR CITY%') THEN
--DBMS_OUTPUT.PUT_LINE
MSG:=(RPAD(C1_REC.SV_ORDER_ID,20)||' COUNTY CODE DOES NOT EXISTS FOR CITY - '
|| UPPER (C2_REC.CITY)
|| ', STATE - '
|| UPPER (C2_REC.STATE)
|| ', POSTAL CODE - '
|| C2_REC.PO_BOX_NUMBER
|| ' COMBINATION.');
DBMS_OUTPUT.PUT_LINE(MSG);
END IF;
END LOOP;
ELSIF UPPER(C1_REC.ERROR_MSG) LIKE UPPER('%CUST ACCOUNT ID DOES NOT EXISTS%') THEN
FOR C3_REC IN C3(C1_REC.PARTY_ID) LOOP
IF UPPER(C3_REC.ERROR_MSG) LIKE UPPER('%INVALID MEDIA SOURCE CODE%') THEN
--DBMS_OUTPUT.PUT_LINE
MSG:=(RPAD(C1_REC.SV_ORDER_ID,20)||' MEDIA SOURCE '||C3_REC.ADDNL_FIELD5 ||' WAS NOT FOUND IN ORACLE. AND HENCE PARTY COULD NOT BE CREATED IN ORACLE WHICH RESULTED IN FAILURE OF THE PROCESS.');
DBMS_OUTPUT.PUT_LINE(MSG);
END IF;
END LOOP;
ELSIF UPPER(C1_REC.ERROR_MSG) LIKE UPPER('%VALIDATION FOR SALES PERSON%') THEN
--DBMS_OUTPUT.PUT_LINE
MSG:=(RPAD(C1_REC.SV_ORDER_ID,20)|| SUBSTR(C1_REC.ERROR_MSG,INSTR(C1_REC.ERROR_MSG,' PLEASE')) ||' DOES NOT EXIST IN ORACLE.');
DBMS_OUTPUT.PUT_LINE(MSG);
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||SUBSTR(C1_REC.ERROR_MSG,1,225));
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR IN GET_ERRORS => '||SQLERRM);
END;
/