Home » SQL & PL/SQL » SQL & PL/SQL » Urgent: LOOP
| Urgent: LOOP [message #22956] |
Thu, 07 November 2002 08:37  |
John Murphy
Messages: 8
Registered: November 2002
|
Junior Member |
|
|
I a m trying to run this and I get the following errors:
ora-06550 line 1028 column 5
pls-00103 ENcountered the symbol "LOOP" when expecting one of the following:if
ora-06550 line 1036 column 0
pls -00103 Encountered the end of file symbol when expecting one of the following: begin function package pragma procedure.
Here is the sql:
DELETE FROM EIM_ACCNT_CUT WHERE IF_ROW_BATCH_NUM = '10002';
DELETE FROM EIM_ACCNT_UT WHERE IF_ROW_BATCH_NUM = '10002';
DELETE FROM EIM_CONTACT WHERE IF_ROW_BATCH_NUM = '10002';
DELETE FROM EIM_CONTACT1 WHERE IF_ROW_BATCH_NUM = '10002';
DELETE FROM EIM_QUOTE WHERE IF_ROW_BATCH_NUM = '10002';
DELETE FROM EIM_QUOTE1 WHERE IF_ROW_BATCH_NUM = '10002';
DELETE FROM EIM_QUOTE_DTL WHERE IF_ROW_BATCH_NUM = '10002';
DECLARE
BATCH_NUM_VAR CONSTANT VARCHAR2(10) := '10002';
IF_ROW_STAT_VAR CONSTANT VARCHAR2(20) := 'FOR_IMPORT';
BU_VAR CONSTANT VARCHAR2(50) := 'TXU Organization';
AC_PRTNR_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
AC_ACCNT_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
AC_DISACLEANSE_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_PROSPECT_FLG_VAR CONSTANT VARCHAR2(10) := 'N';
AC_ASGNUSREXCLDFLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_BASE_CURCY_CD_VAR CONSTANT VARCHAR2(20) := 'USD';
AC_CMPT_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_CUST_STAT_CD_VAR CONSTANT VARCHAR2(30) := 'PROSPECT';
AC_DISAALLMAILSFLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_OU_TYPE_CD_VAR CONSTANT VARCHAR2(10) := 'GENERAL';
AC_PARTNER_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_PRREP_ASGN_TYPE_VAR CONSTANT VARCHAR2(30) := 'SYSTEM';
AC_PR_REP_DNRM_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_PR_REP_MANL_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_PR_REP_SYS_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
AC_REVENUECLASS_CD_VAR CONSTANT VARCHAR2(30) := 'CONSUMER';
X_BILLING_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
X_LIFELINE_VAR CONSTANT VARCHAR2(1) := 'N';
X_UPDATED_VAR CONSTANT VARCHAR2(1) := 'N';
ADR_COUNTRY_VAR CONSTANT VARCHAR2(10) := 'USA';
ADR_DISACLEANSEFLG_VAR CONSTANT VARCHAR2(1) := 'N';
POSTN_DIVN_VAR CONSTANT VARCHAR2(50) := 'TXU';
POSTN_NAME_VAR CONSTANT VARCHAR2(50) := 'TXU EIM DataLoad 1';
ROW_STATUS_VAR CONSTANT VARCHAR2(10) := 'N';
AU_REF_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
PRIV_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
WEB_ENROLL_VAR CONSTANT VARCHAR2(1) := 'Y';
M_AC_ACCNT_TYPE_CD_VAR CONSTANT VARCHAR2(10) := 'CUSTOMER';
M_ACC_PR_CON_VAR CONSTANT VARCHAR2(1) := 'Y';
S_AC_ACCNT_TYPE_CD_VAR CONSTANT VARCHAR2(10) := 'SERVICE';
S_X_PREM_SUBTYPE_VAR CONSTANT VARCHAR2(10) := 'RESIDENTIAL';
S_X_SIC_CODE_VAR CONSTANT VARCHAR2(10) := 'RES';
S_ADR_PREMISE_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
B_AC_ACCNT_TYPE_CD_VAR CONSTANT VARCHAR2(10) := 'BILLING';
B_ADR_PREMISE_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
PRIMARY_FINAN_RESP_VAR CONSTANT VARCHAR2(1) := 'Y';
PRIMARY_REL_ACCNT_VAR CONSTANT VARCHAR2(50) := 'MAIN CUSTOMER';
ADDR_COUNTRY_VAR CONSTANT VARCHAR2(10) := 'USA';
ADDR_NAME_LOCK_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
CON_SUPPRESSCALLFL_VAR CONSTANT VARCHAR2(1) := 'N';
CON_SUPPRESSEMAILF_VAR CONSTANT VARCHAR2(1) := 'N';
CON_SUPPRESSFAXFLG_VAR CONSTANT VARCHAR2(1) := 'N';
CON_SUPPRESSMAILFL_VAR CONSTANT VARCHAR2(1) := 'N';
CON_ASGNUSREXCLDFL_VAR CONSTANT VARCHAR2(1) := 'N';
CON_CALL_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
CON_CON_EXPER_CD_VAR CONSTANT VARCHAR2(30) := 'ENGLISH';
CON_DISACLEANSEFLG_VAR CONSTANT VARCHAR2(1) := 'N';
CON_EMAILSRUPD_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
CON_PRIV_FLAG_VAR CONSTANT VARCHAR2(1) := 'N';
SQ_ACTIVE_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
SQ_QUOTE_NUM_VAR CONSTANT VARCHAR2(15) := 'N/A';
SQ_QUOTE_TYPE_VAR CONSTANT VARCHAR2(30) := 'Configuration';
SQ_CURCY_CD_VAR CONSTANT VARCHAR2(20) := 'USD';
SQ_STAT_CD_VAR CONSTANT VARCHAR2(30) := 'Open';
SQ_REV_NUM_VAR CONSTANT NUMBER(1) := '1';
SOLN_ACTION_CD_VAR CONSTANT VARCHAR2(30) := 'ADD';
SOLN_ACTIVECFG_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
SOLN_ACTIVE_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
SOLN_OPENORDER_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
SOLN_STATUS_CD_VAR CONSTANT VARCHAR2(30) := 'PENDING';
SOLN_QTY_REQ_VAR CONSTANT NUMBER(1) := '1';
SOLN_COPIED_FLG_VAR CONSTANT VARCHAR2(1) := 'N';
SOLN_CURR_CUST_FLG_VAR CONSTANT VARCHAR2(1) := 'Y';
X_STATUS_CD_VAR CONSTANT VARCHAR2(30) := 'PENDING START';
SOLN_DESC_TEXT_VAR VARCHAR2(255):= '';
AUTHORIZE_CREDIT_VAR VARCHAR2(30) := '';
RECEIVE_ADD_INFO_VAR VARCHAR2(30) := '';
CON_ADDRESS_VAR VARCHAR2(200):= '';
BILLING_ADDRESS_VAR VARCHAR2(200):= '';
CON_ADDR_NAME_VAR VARCHAR2(100):= '';
M_AC_NAME_VAR VARCHAR2(100):= '';
B_AC_NAME_VAR VARCHAR2(100):= '';
B_ADDR_NAME_VAR VARCHAR2(100):= '';
S_AC_NAME_VAR VARCHAR2(100):= '';
S_ADDR_NAME_VAR VARCHAR2(100):= '';
PRIMARY_FNAME_VAR SERVICE_REQUEST.PRIMARY_FNAME%TYPE;
PRIMARY_MNAME_VAR SERVICE_REQUEST.PRIMARY_MNAME%TYPE;
PRIMARY_LNAME_VAR SERVICE_REQUEST.PRIMARY_LNAME%TYPE;
PRIMARY_SS_VAR SERVICE_REQUEST.PRIMARY_SS%TYPE;
PRIMARY_DOB_VAR SERVICE_REQUEST.PRIMARY_DOB%TYPE;
PRIMARY_DAY_PHONE_VAR SERVICE_REQUEST.PRIMARY_DAY_PHONE%TYPE;
PRIMARY_EVE_PHONE_VAR SERVICE_REQUEST.PRIMARY_EVE_PHONE%TYPE;
PRIMARY_EMAIL_VAR SERVICE_REQUEST.PRIMARY_EMAIL%TYPE;
PRIMARY_DL_NUMBER_VAR SERVICE_REQUEST.PRIMARY_DL_NUMBER%TYPE;
PRIMARY_DL_STATE_VAR SERVICE_REQUEST.PRIMARY_DL_STATE%TYPE;
PRIMARY_REFERRED_BY_VAR SERVICE_REQUEST.PRIMARY_REFERRED_BY%TYPE;
SECONDARY_FNAME_VAR SERVICE_REQUEST.SECONDARY_FNAME%TYPE;
SECONDARY_MNAME_VAR SERVICE_REQUEST.SECONDARY_MNAME%TYPE;
SECONDARY_LNAME_VAR SERVICE_REQUEST.SECONDARY_LNAME%TYPE;
SECONDARY_SS_VAR SERVICE_REQUEST.SECONDARY_SS%TYPE;
REL_ACCT_HOLDER_VAR SERVICE_REQUEST.REL_ACCT_HOLDER%TYPE;
SERVICE_LINE_1_VAR SERVICE_REQUEST.SERVICE_LINE_1%TYPE;
SERVICE_LINE_2_VAR SERVICE_REQUEST.SERVICE_LINE_2%TYPE;
SERVICE_APT_NUMBER_VAR SERVICE_REQUEST.SERVICE_APT_NUMBER%TYPE;
SERVICE_CITY_VAR SERVICE_REQUEST.SERVICE_CITY%TYPE;
SERVICE_STATE_VAR SERVICE_REQUEST.SERVICE_STATE%TYPE;
SERVICE_ZIP_CODE_VAR SERVICE_REQUEST.SERVICE_ZIP_CODE%TYPE;
SERVICE_LIFE_SUPPORT_VAR SERVICE_REQUEST.SERVICE_LIFE_SUPPORT%TYPE;
SERVICE_BEGIN_DATE_VAR SERVICE_REQUEST.SERVICE_BEGIN_DATE%TYPE;
BILLING_LINE_1_VAR SERVICE_REQUEST.BILLING_LINE_1%TYPE;
BILLING_LINE_2_VAR SERVICE_REQUEST.BILLING_LINE_2%TYPE;
BILLING_APT_NUMBER_VAR SERVICE_REQUEST.BILLING_APT_NUMBER%TYPE;
BILLING_PO_BOX_VAR SERVICE_REQUEST.BILLING_PO_BOX%TYPE;
BILLING_CITY_VAR SERVICE_REQUEST.BILLING_CITY%TYPE;
BILLING_STATE_VAR SERVICE_REQUEST.BILLING_STATE%TYPE;
BILLING_ZIP_CODE_VAR SERVICE_REQUEST.BILLING_ZIP_CODE%TYPE;
CURRENT_ESID_NUMBER_VAR SERVICE_REQUEST.CURRENT_ESID_NUMBER%TYPE;
TEL_ADDRESS1_VAR SERVICE_REQUEST.TEL_ADDRESS1%TYPE;
TEL_ADDRESS2_VAR SERVICE_REQUEST.TEL_ADDRESS2%TYPE;
TEL_APTNO_VAR SERVICE_REQUEST.TEL_APTNO%TYPE;
TEL_POBOX_VAR SERVICE_REQUEST.TEL_POBOX%TYPE;
TEL_CITY_VAR SERVICE_REQUEST.TEL_CITY%TYPE;
TEL_STATE_VAR SERVICE_REQUEST.TEL_STATE%TYPE;
TEL_ZIP_VAR SERVICE_REQUEST.TEL_ZIP%TYPE;
DEPOSIT_LEVEL_VAR SERVICE_REQUEST.DEPOSIT_LEVEL%TYPE;
HOME_CITY_LIMITS_VAR SERVICE_REQUEST.HOME_CITY_LIMITS%TYPE;
HOME_SQUARE_FOOTAGE_VAR SERVICE_REQUEST.HOME_SQUARE_FOOTAGE%TYPE;
BILLOPTS_PREF_LANGUAGE_VAR SERVICE_REQUEST.BILLOPTS_PREF_LANGUAGE%TYPE;
BILLOPTS_CONTACT_METHOD_VAR SERVICE_REQUEST.BILLOPTS_CONTACT_METHOD%TYPE;
BILLOPTS_DO_NOT_CALL_LIST_VAR SERVICE_REQUEST.BILLOPTS_DO_NOT_CALL_LIST%TYPE;
BILLOPTS_AUTH_CREDIT_VAR SERVICE_REQUEST.BILLOPTS_AUTH_CREDIT%TYPE;
BILLOPTS_RECEIVE_INFO_VAR SERVICE_REQUEST.BILLOPTS_RECEIVE_INFO%TYPE;
AGENCY_CONTACT_NAME_VAR SERVICE_REQUEST.AGENCY_CONTACT_NAME%TYPE;
AGENCY_EMAIL_VAR SERVICE_REQUEST.AGENCY_EMAIL%TYPE;
SERVICE_REQUEST_ID_VAR SERVICE_REQUEST.SERVICE_REQUEST_ID%TYPE;
REF_GUID_VAR SERVICE_REQUEST.REF_GUID%TYPE;
ENROLLMENT_CHANNEL_VAR SERVICE_REQUEST.ENROLLMENT_CHANNEL%TYPE;
REF_CAMPAIGN_CODE_VAR SERVICE_REQUEST.REF_CAMPAIGN_CODE%TYPE;
SR_SERVICE_TYPE_VAR SERVICE_REQUEST.SR_SERVICE_TYPE%TYPE;
CURSOR LT_Cursor
IS SELECT (
PRIMARY_FNAME
,PRIMARY_MNAME
,PRIMARY_LNAME
,PRIMARY_SS
,PRIMARY_DOB
,PRIMARY_DAY_PHONE
,PRIMARY_EVE_PHONE
,PRIMARY_EMAIL
,PRIMARY_DL_NUMBER
,PRIMARY_DL_STATE
,PRIMARY_REFERRED_BY
,SECONDARY_FNAME
,SECONDARY_MNAME
,SECONDARY_LNAME
,SECONDARY_SS
,REL_ACCT_HOLDER
,SERVICE_LINE_1
,SERVICE_LINE_2
,SERVICE_APT_NUMBER
,SERVICE_CITY
,SERVICE_STATE
,SERVICE_ZIP_CODE
,SERVICE_LIFE_SUPPORT
,SERVICE_BEGIN_DATE
,BILLING_LINE_1
,BILLING_LINE_2
,BILLING_APT_NUMBER
,BILLING_PO_BOX
,BILLING_CITY
,BILLING_STATE
,BILLING_ZIP_CODE
,CURRENT_ESID_NUMBER
,TEL_ADDRESS1
,TEL_ADDRESS2
,TEL_APTNO
,TEL_POBOX
,TEL_CITY
,TEL_STATE
,TEL_ZIP
,DEPOSIT_LEVEL
,HOME_CITY_LIMITS
,HOME_SQUARE_FOOTAGE
,BILLOPTS_PREF_LANGUAGE
,BILLOPTS_CONTACT_METHOD
,BILLOPTS_DO_NOT_CALL_LIST
,BILLOPTS_AUTH_CREDIT
,BILLOPTS_RECEIVE_INFO
,AGENCY_CONTACT_NAME
,AGENCY_EMAIL
,SERVICE_REQUEST_ID
,REF_GUID
,ENROLLMENT_CHANNEL
,REF_CAMPAIGN_CODE
,SR_SERVICE_TYPE
)
FROM
SERVICE_REQUEST
WHERE
STATUS = 'E';
BEGIN
OPEN LT_Cursor;
LOOP
FETCH LT_Cursor
INTO
PRIMARY_FNAME_VAR,
PRIMARY_MNAME_VAR,
PRIMARY_LNAME_VAR,
PRIMARY_SS_VAR,
PRIMARY_DOB_VAR,
PRIMARY_DAY_PHONE_VAR,
PRIMARY_EVE_PHONE_VAR,
PRIMARY_EMAIL_VAR,
PRIMARY_DL_NUMBER_VAR,
PRIMARY_DL_STATE_VAR,
PRIMARY_REFERRED_BY_VAR,
SECONDARY_FNAME_VAR,
SECONDARY_MNAME_VAR,
SECONDARY_LNAME_VAR,
SECONDARY_SS_VAR,
REL_ACCT_HOLDER_VAR,
SERVICE_LINE_1_VAR,
SERVICE_LINE_2_VAR,
SERVICE_APT_NUMBER_VAR,
SERVICE_CITY_VAR,
SERVICE_STATE_VAR,
SERVICE_ZIP_CODE_VAR,
SERVICE_LIFE_SUPPORT_VAR,
SERVICE_BEGIN_DATE_VAR,
BILLING_LINE_1_VAR,
BILLING_LINE_2_VAR,
BILLING_APT_NUMBER_VAR,
BILLING_PO_BOX_VAR,
BILLING_CITY_VAR,
BILLING_STATE_VAR,
BILLING_ZIP_CODE_VAR,
CURRENT_ESID_NUMBER_VAR,
TEL_ADDRESS1_VAR,
TEL_ADDRESS2_VAR,
TEL_APTNO_VAR,
TEL_POBOX_VAR,
TEL_CITY_VAR,
TEL_STATE_VAR,
TEL_ZIP_VAR,
DEPOSIT_LEVEL_VAR,
HOME_CITY_LIMITS_VAR,
HOME_SQUARE_FOOTAGE_VAR,
BILLOPTS_PREF_LANGUAGE_VAR,
BILLOPTS_CONTACT_METHOD_VAR,
BILLOPTS_DO_NOT_CALL_LIST_VAR,
BILLOPTS_AUTH_CREDIT_VAR,
BILLOPTS_RECEIVE_INFO_VAR,
AGENCY_CONTACT_NAME_VAR,
AGENCY_EMAIL_VAR,
SERVICE_REQUEST_ID_VAR,
REF_GUID_VAR,
ENROLLMENT_CHANNEL_VAR,
REF_CAMPAIGN_CODE_VAR,
SR_SERVICE_TYPE_VAR;
EXIT WHEN LT_Cursor%NOTFOUND;
Record_Count := Record_Count + 1;
IF BILLOPTS_AUTH_CREDIT_VAR = 'Y' THEN
AUTHORIZE_CREDIT_VAR := 'AUTHORIZED CREDIT CHECK.';
IF BILLOPTS_AUTH_CREDIT_VAR = 'N' THEN
AUTHORIZE_CREDIT_VAR := 'DENIED CREDIT CHECK.';
IF BILLOPTS_RECEIVE_INFO_VAR = 'Y' THEN
RECEIVE_ADD_INFO_VAR := ' SEND ADDITIONAL PRODUCT/SERVICE INFORMATION. ';
SOLN_DESC_TEXT_VAR := SR_SERVICE_TYPE_VAR || '. ' || AUTHORIZE_CREDIT_VAR || ' DOB:' || PRIMARY_DOB_VAR || '. ' || AGENCY_EMAIL_VAR || RECEIVE_ADD_INFO_VAR || CURRENT_ESID_NUMBER_VAR;
M_AC_NAME_VAR := PRIMARY_LNAME_VAR || ', '|| PRIMARY_FNAME_VAR || ' ' || PRIMARY_MNAME_VAR;
B_AC_NAME_VAR := M_AC_NAME_VAR || ' BILLING';
S_AC_NAME_VAR := SERVICE_LINE_1_VAR || ', ' || SERVICE_CITY_VAR;
B_ADDR_NAME_VAR := BILLING_LINE_1_VAR || ', ' || BILLING_CITY_VAR;
S_ADDR_NAME_VAR := SERVICE_LINE_1_VAR || ', ' || SERVICE_CITY_VAR;
CON_ADDR_NAME_VAR := TEL_ADDRESS1_VAR || ', ' || TEL_CITY_VAR;
CON_ADDRESS_VAR := TEL_ADDRESS1_VAR || ' ' || TEL_APTNO_VAR || TEL_POBOX_VAR;
BILLING_ADDRESS_VAR := BILLING_LINE_1_VAR || ' ' || BILLING_APT_NUMBER_VAR || BILLING_PO_BOX_VAR;
IF REL_ACCNT_HOLDER_VAR IN('3RDPARTY/FINANCIALLY RESPON','GUARANTOR/FINANCIALLY RESPON','SPOUSE','ALSO FINANCIALLY RESPONSIBLE') THEN
X_FINAN_RESPONSIBLE := 'Y';
INSERT INTO SIEBEL.EIM_ACCNT_CUT (
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,AC_NAME
,AC_LOC
,AC_BU
,AC_PRTNR_FLG
,AC_ACCNT_FLG
,AC_DISACLEANSE_FLG
,AC_PROSPECT_FLG
,AC_ACCNT_TYPE_CD
,AC_ASGNUSREXCLDFLG
,AC_BASE_CURCY_CD
,AC_CMPT_FLG
,AC_CUST_STAT_CD
,AC_OU_TYPE_CD
,AC_PARTNER_FLG
,AC_PRREP_ASGN_TYPE
,AC_PR_REP_DNRM_FLG
,AC_PR_REP_MANL_FLG
,AC_PR_REP_SYS_FLG
,AC_REVENUECLASS_CD
,X_BILLING_FLG
,X_LIFELINE
,X_UPDATED
,X_WEB_ENROLL_TXU
,ACC_PR_CON
,AC_DISAALLMAILSFLG
,AC_MAIN_FAX_PH_NUM
,AC_MAIN_PH_NUM
,AP_POSTN_DIVN
,AP_POSTN_NAME
,AP_ROW_STATUS
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,M_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,AC_PRTNR_FLG_VAR
,AC_ACCNT_FLG_VAR
,AC_DISACLEANSE_FLG_VAR
,AC_PROSPECT_FLG_VAR
,M_AC_ACCNT_TYPE_CD_VAR
,AC_ASGNUSREXCLDFLG_VAR
,AC_BASE_CURCY_CD_VAR
,AC_CMPT_FLG_VAR
,AC_CUST_STAT_CD_VAR
,AC_OU_TYPE_CD_VAR
,AC_PARTNER_FLG_VAR
,AC_PRREP_ASGN_TYPE_VAR
,AC_PR_REP_DNRM_FLG_VAR
,AC_PR_REP_MANL_FLG_VAR
,AC_PR_REP_SYS_FLG_VAR
,AC_REVENUECLASS_CD_VAR
,X_BILLING_FLG_VAR
,X_LIFELINE_VAR
,X_UPDATED_VAR
,WEB_ENROLL_VAR
,M_ACC_PR_CON_VAR
,BILLOPTS_DO_NOT_CALL_LIST_VAR
,PRIMARY_EVE_PHONE_VAR
,PRIMARY_DAY_PHONE_VAR
,POSTN_DIVN_VAR
,POSTN_NAME_VAR
,ROW_STATUS_VAR
);
INSERT INTO SIEBEL.EIM_ACCNT_CUT (
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,AC_NAME
,AC_LOC
,AC_BU
,AC_PRTNR_FLG
,AC_ACCNT_FLG
,AC_DISACLEANSE_FLG
,AC_PROSPECT_FLG
,AC_ACCNT_TYPE_CD
,AC_ASGNUSREXCLDFLG
,AC_BASE_CURCY_CD
,AC_CMPT_FLG
,AC_CUST_STAT_CD
,AC_DISAALLMAILSFLG
,AC_OU_TYPE_CD
,AC_PARTNER_FLG
,AC_PRREP_ASGN_TYPE
,AC_PR_REP_DNRM_FLG
,AC_PR_REP_MANL_FLG
,AC_PR_REP_SYS_FLG
,AC_REVENUECLASS_CD
,X_BILLING_FLG
,X_LIFELINE
,X_UPDATED
,ADR_COUNTRY
,ADR_PREMISE_FLG
,ADR_DISACLEANSEFLG
,ADR_STATE
,X_ADDR2
,ADR_ZIPCODE
,ADR_ADDR
,ADR_CITY
,AP_POSTN_DIVN
,AP_POSTN_NAME
,AP_ROW_STATUS
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,B_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,AC_PRTNR_FLG_VAR
,AC_ACCNT_FLG_VAR
,AC_DISACLEANSE_FLG_VAR
,AC_PROSPECT_FLG_VAR
,B_AC_ACCNT_TYPE_CD_VAR
,AC_ASGNUSREXCLDFLG_VAR
,AC_BASE_CURCY_CD_VAR
,AC_CMPT_FLG_VAR
,AC_CUST_STAT_CD_VAR
,AC_DISAALLMAILSFLG_VAR
,AC_OU_TYPE_CD_VAR
,AC_PARTNER_FLG_VAR
,AC_PRREP_ASGN_TYPE_VAR
,AC_PR_REP_DNRM_FLG_VAR
,AC_PR_REP_MANL_FLG_VAR
,AC_PR_REP_SYS_FLG_VAR
,AC_REVENUECLASS_CD_VAR
,X_BILLING_FLG_VAR
,X_LIFELINE_VAR
,X_UPDATED_VAR
,B_ADDR_NAME_VAR
,ADR_COUNTRY_VAR
,B_ADR_PREMISE_FLG_VAR
,ADR_DISACLEANSEFLG_VAR
,BILLING_STATE_VAR
,BILLING_LINE_2_VAR
,BILLING_ZIP_CODE_VAR
,BILLING_ADDRESS_VAR
,BILLING_CITY_VAR
,POSTN_DIVN_VAR
,POSTN_NAME_VAR
,ROW_STATUS_VAR
);
INSERT INTO SIEBEL.EIM_ACCNT_CUT (
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,AC_NAME
,AC_LOC
,AC_BU
,AC_PRTNR_FLG
,AC_ACCNT_FLG
,AC_DISACLEANSE_FLG
,AC_PROSPECT_FLG
,AC_ACCNT_TYPE_CD
,AC_ASGNUSREXCLDFLG
,AC_BASE_CURCY_CD
,AC_CMPT_FLG
,AC_CUST_STAT_CD
,AC_DISAALLMAILSFLG
,AC_OU_TYPE_CD
,AC_PARTNER_FLG
,AC_PRREP_ASGN_TYPE
,AC_PR_REP_DNRM_FLG
,AC_PR_REP_MANL_FLG
,AC_PR_REP_SYS_FLG
,AC_REVENUECLASS_CD
,X_BILLING_FLG
,X_UPDATED
,X_PREM_SUBTYPE
,X_SIC_CODE
,ADR_ADDR_NAME
,ADR_COUNTRY
,ADR_PREMISE_FLG
,ADR_DISACLEANSEFLG
,ADR_STATE
,X_ADDR2
,X_CITY_LIMIT
,ADR_ZIPCODE
,ADR_ADDR
,ADR_CITY
,X_LIFELINE
,AP_POSTN_DIVN
,AP_POSTN_NAME
,AP_ROW_STATUS
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,S_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,AC_PRTNR_FLG_VAR
,AC_ACCNT_FLG_VAR
,AC_DISACLEANSE_FLG_VAR
,AC_PROSPECT_FLG_VAR
,S_AC_ACCNT_TYPE_CD_VAR
,AC_ASGNUSREXCLDFLG_VAR
,AC_BASE_CURCY_CD_VAR
,AC_CMPT_FLG_VAR
,AC_CUST_STAT_CD_VAR
,AC_DISAALLMAILSFLG_VAR
,AC_OU_TYPE_CD_VAR
,AC_PARTNER_FLG_VAR
,AC_PRREP_ASGN_TYPE_VAR
,AC_PR_REP_DNRM_FLG_VAR
,AC_PR_REP_MANL_FLG_VAR
,AC_PR_REP_SYS_FLG_VAR
,AC_REVENUECLASS_CD_VAR
,X_BILLING_FLG_VAR
,X_UPDATED_VAR
,S_X_PREM_SUBTYPE_VAR
,S_X_SIC_CODE_VAR
,S_ADDR_NAME_VAR
,ADR_COUNTRY_VAR
,S_ADR_PREMISE_FLG_VAR
,ADR_DISACLEANSEFLG_VAR
,SERVICE_STATE_VAR
,SERVICE_LINE_2_VAR
,HOME_CITY_LIMITS_VAR
,SERVICE_ZIP_CODE_VAR
,SERVICE_LINE_1_VAR
,SERVICE_CITY_VAR
,SERVICE_LIFE_SUPPORT_VAR
,POSTN_DIVN_VAR
,POSTN_NAME_VAR
,ROW_STATUS_VAR
);
INSERT INTO SIEBEL.EIM_ACCNT_UT(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,NAME
,LOC
,BU
,AU_TOTAL_AREA
,AU_REF_FLG
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,S_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,HOME_SQUARE_FOOTAGE_VAR
,AU_REF_FLG_VAR
);
INSERT INTO SIEBEL.EIM_CONTACT(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,DEPT_ACCNT_BU
,DEPT_ACCNT_LOC
,DEPT_ACCNT_NAME
,CON_BU
,CON_SUPPRESSCALLFL
,CON_SUPPRESSEMAILF
,CON_SUPPRESSFAXFLG
,CON_SUPPRESSMAILFL
,CON_ASGNUSREXCLDFL
,CON_CALL_FLG
,CON_CON_EXPER_CD
,CON_DISACLEANSEFLG
,CON_EMAILSRUPD_FLG
,CON_PRIV_FLAG
,CON_BIRTH_DT
,CON_CON_EXPER_CD
,CON_EMAIL_ADDR
,CON_HOME_PH_NUM
,CON_MID_NAME
,PREF_COMM_METH_CD
,CON_SOCSECURITYNUM
,CON_WORK_PH_NUM
,CON_FST_NAME
,CON_LAST_NAME
,X_LICENSE
,X_LICENSE_ST
,ADDR_ADDR_NAME
,ADDR_DISACLEANSEFL
,ADDR_NAME_LOCK_FLG
,ADDR_COUNTRY
,ADDR_STATE
,X_ADDR2
,X_ADDR3
,ADDR_ZIPCODE
,ADDR_ADDR
,ADDR_CITY
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,CON_SUPPRESSCALLFL_VAR
,CON_SUPPRESSEMAILF_VAR
,CON_SUPPRESSFAXFLG_VAR
,CON_SUPPRESSMAILFL_VAR
,CON_ASGNUSREXCLDFL_VAR
,CON_CALL_FLG_VAR
,CON_CON_EXPER_CD_VAR
,CON_DISACLEANSEFLG_VAR
,CON_EMAILSRUPD_FLG_VAR
,CON_PRIV_FLAG_VAR
,PRIMARY_DOB_VAR
,BILLOPTS_PREF_LANGUAGE_VAR
,PRIMARY_EMAIL_VAR
,PRIMARY_EVE_PHONE_VAR
,PRIMARY_MNAME_VAR
,BILLOPTS_CONTACT_METHOD_VAR
,PRIMARY_SS_VAR
,PRIMARY_DAY_PHONE_VAR
,PRIMARY_FNAME_VAR
,PRIMARY_LNAME_VAR
,PRIMARY_DL_NUMBER_VAR
,PRIMARY_DL_STATE_VAR
,CON_ADDR_NAME_VAR
,ADDR_DISACLEANSEFL_VAR
,ADDR_NAME_LOCK_FLG_VAR
,ADDR_COUNTRY_VAR
,TEL_STATE_VAR
,TEL_ADDRESS2_VAR
,TEL_POBOX_VAR
,TEL_ZIP_VAR
,CON_ADDRESS_VAR
,TEL_CITY_VAR
);
INSERT INTO SIEBEL.EIM_CONTACT1(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,DEPT_ACCNT_BU
,DEPT_ACCNT_LOC
,DEPT_ACCNT_NAME
,CON_BU
,CON_PRIV_FLAG
,CON_MID_NAME
,CON_FST_NAME
,CON_LAST_NAME
,ADDR_ADRPLAST_NAME
,ADDR_ADRP_ACC_BU
,ADDR_ADRP_ACC_LOC
,ADDR_ADRP_ACC_NAME
,ADDR_ADRP_ADDRNAME
,ADDR_ADRP_BU
,ADDR_ADRP_FST_NAME
,ADDR_ADRP_MID_NAME
,ADDR_ADRP_PRIV_FLG
,ADDR_ACCNT_BU
,ADDR_ACCNT_LOC
,ADDR_ACCNT_NAME
,ADDR_START_DT
,PC_POSTN_DIVN
,PC_POSTN_NAME
,PC_ROW_STATUS
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,CON_PRIV_FLAG_VAR
,PRIMARY_MNAME_VAR
,PRIMARY_FNAME_VAR
,PRIMARY_LNAME_VAR
,PRIMARY_LNAME_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,S_ADDR_NAME_VAR
,BU_VAR
,PRIMARY_FNAME_VAR
,PRIMARY_MNAME_VAR
,PRIV_FLG_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,SYSDATE
,POSTN_DIVN_VAR
,POSTN_NAME_VAR
,ROW_STATUS_VAR
);
INSERT INTO SIEBEL.EIM_ACCNT_CUT (
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,AC_NAME
,AC_LOC
,AC_BU
,AC_PRTNR_FLG
,AC_ACCNT_FLG
,AC_DISACLEANSE_FLG
,AC_PROSPECT_FLG
,PO_CON_ACC_BU
,PO_CON_ACC_LOC
,PO_CON_ACC_NAME
,PO_CON_BU
,PO_CON_FST_NAME
,PO_CON_LAST_NAME
,PO_CON_MID_NAME
,PO_CON_PRIV_FLG
,X_FINAN_RESPONSIBLE
,X_RELATION_ACCOUNT
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,M_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,AC_PRTNR_FLG_VAR
,AC_ACCNT_FLG_VAR
,AC_DISACLEANSE_FLG_VAR
,AC_PROSPECT_FLG_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,PRIMARY_FNAME_VAR
,PRIMARY_LNAME_VAR
,PRIMARY_MNAME_VAR
,PRIV_FLG_VAR
,PRIMARY_FINAN_RESP_VAR
,PRIMARY_REL_ACCNT_VAR
);
INSERT INTO SIEBEL.EIM_ACCNT_CUT (
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,AC_NAME
,AC_LOC
,AC_BU
,AC_PRTNR_FLG
,AC_ACCNT_FLG
,AC_DISACLEANSE_FLG
,AC_PROSPECT_FLG
,CO_ADDR_NAME
,CO_ADRP_ACC_BU
,CO_ADRP_ACC_LOC
,CO_ADRP_ACC_NAME
,CO_ADRP_BU
,CO_ADRP_FST_NAME
,CO_ADRP_LAST_NAME
,CO_ADRP_MID_NAME
,CO_ADRP_PRIV_FLG
,CO_CON_ACC_LOC
,CO_CON_ACC_NAME
,CO_CON_BU
,CO_CON_FST_NAME
,CO_CON_LAST_NAME
,CO_CON_MID_NAME
,CO_CON_PRIV_FLG
,CO_START_DT
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,M_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,AC_PRTNR_FLG_VAR
,AC_ACCNT_FLG_VAR
,AC_DISACLEANSE_FLG_VAR
,AC_PROSPECT_FLG_VAR
,CON_ADDR_NAME_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,PRIMARY_FNAME_VAR
,PRIMARY_LNAME_VAR
,PRIMARY_MNAME_VAR
,PRIV_FLG_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,PRIMARY_FNAME_VAR
,PRIMARY_LNAME_VAR
,PRIMARY_MNAME_VAR
,PRIV_FLG_VAR
,SYSDATE
);
INSERT INTO SIEBEL.EIM_CONTACT(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,DEPT_ACCNT_BU
,DEPT_ACCNT_LOC
,DEPT_ACCNT_NAME
,CON_BU
,CON_SUPPRESSCALLFL
,CON_SUPPRESSEMAILF
,CON_SUPPRESSFAXFLG
,CON_SUPPRESSMAILFL
,CON_ASGNUSREXCLDFL
,CON_CALL_FLG
,CON_CON_EXPER_CD
,CON_DISACLEANSEFLG
,CON_EMAILSRUPD_FLG
,CON_PRIV_FLAG
,CON_MID_NAME
,CON_SOCSECURITYNUM
,CON_FST_NAME
,CON_LAST_NAME
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,CON_SUPPRESSCALLFL_VAR
,CON_SUPPRESSEMAILF_VAR
,CON_SUPPRESSFAXFLG_VAR
,CON_SUPPRESSMAILFL_VAR
,CON_ASGNUSREXCLDFL_VAR
,CON_CALL_FLG_VAR
,CON_CON_EXPER_CD_VAR
,CON_DISACLEANSEFLG_VAR
,CON_EMAILSRUPD_FLG_VAR
,CON_PRIV_FLAG_VAR
,SECONDARY_MNAME_VAR
,SECONDARY_SS_VAR
,SECONDARY_FNAME_VAR
,SECONDARY_LNAME_VAR
);
INSERT INTO SIEBEL.EIM_ACCNT_CUT (
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,AC_NAME
,AC_LOC
,AC_BU
,AC_PRTNR_FLG
,AC_ACCNT_FLG
,AC_DISACLEANSE_FLG
,AC_PROSPECT_FLG
,PO_CON_ACC_BU
,PO_CON_ACC_LOC
,PO_CON_ACC_NAME
,PO_CON_BU
,PO_CON_FST_NAME
,PO_CON_LAST_NAME
,PO_CON_MID_NAME
,PO_CON_PRIV_FLG
,X_FINAN_RESPONSIBLE
,X_RELATION_ACCOUNT
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,M_AC_NAME_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,AC_PRTNR_FLG_VAR
,AC_ACCNT_FLG_VAR
,AC_DISACLEANSE_FLG_VAR
,AC_PROSPECT_FLG_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,BU_VAR
,SECONDARY_FNAME_VAR
,SECONDARY_LNAME_VAR
,SECONDARY_MNAME_VAR
,PRIV_FLG_VAR
,X_FINAN_RESPONSIBLE_VAR
,REL_ACCT_HOLDER_VAR
);
INSERT INTO SIEBEL.EIM_QUOTE(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,SQ_NAME
,TARGET_ACCNT_BU
,TARGET_ACCNT_LOC
,TARGET_ACCNT_NAME
,SQ_REV_NUM
,SQ_BU
,SQ_QUOTE_NUM
,SQ_ACTIVE_FLG
,SQ_QUOTE_NUM
,SQ_QUOTE_TYPE
,SQ_CURCY_CD
,SQ_STAT_CD
,X_DRIVER_SO
,X_CHANNEL_SO
,SQ_DESC_TEXT
,X_WEB_ENROLL_TXU
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,SQ_REV_NUM_VAR
,BU_VAR
,SQ_QUOTE_NUM_VAR
,SQ_ACTIVE_FLG_VAR
,SQ_QUOTE_NUM_VAR
,SQ_QUOTE_TYPE_VAR
,SQ_CURCY_CD_VAR
,SQ_STAT_CD_VAR
,PRIMARY_REFERRED_BY_VAR
,ENROLLMENT_CHANNEL_VAR
,AGENCY_CONTACT_NAME_VAR
,WEB_ENROLL_VAR
);
INSERT INTO SIEBEL.EIM_QUOTE1(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,SQ_NAME
,TARGET_ACCNT_BU
,TARGET_ACCNT_LOC
,TARGET_ACCNT_NAME
,SQ_REV_NUM
,SQ_BU
,ACTIVE_CFG_FLG
,OPEN_ORDER_FLG
,QUOTE_NUM
,SOLN_NAME
,SOLN_ACTIVE_FLG
,SOLN_COPIED_FLG
,SOLN_CURR_CUST_FLG
,SOLN_ACTION_CD
,SOLN_ACTIVECFG_FLG
,SOLN_OPENORDER_FLG
,SOLN_STATUS_CD
,SOLN_QTY_REQ
,X_STATUS_CD
,SOLN_DESC_TEXT
,SOLN_START_DT
,X_DEPOSIT_AMT
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,SQ_REV_NUM_VAR
,BU_VAR
,SOLN_ACTIVECFG_FLG_VAR
,SOLN_OPENORDER_FLG_VAR
,SQ_QUOTE_NUM_VAR
,SERVICE_REQUEST_ID_VAR
,SOLN_ACTIVE_FLG_VAR
,SOLN_COPIED_FLG_VAR
,SOLN_CURR_CUST_FLG_VAR
,SOLN_ACTION_CD_VAR
,SOLN_ACTIVECFG_FLG_VAR
,SOLN_OPENORDER_FLG_VAR
,SOLN_STATUS_CD_VAR
,SOLN_QTY_REQ_VAR
,X_STATUS_CD_VAR
,SOLN_DESC_TEXT_VAR
,SERVICE_BEGIN_DATE_VAR
,DEPOSIT_LEVEL_VAR
);
INSERT INTO SIEBEL.EIM_QUOTE_DTL(
ROW_ID
,IF_ROW_BATCH_NUM
,IF_ROW_STAT
,SQ_NAME
,TARGET_ACCNT_BU
,TARGET_ACCNT_LOC
,TARGET_ACCNT_NAME
,SQ_REV_NUM
,SQ_BU
,X_ONLINEORDER_ID_TXU
,X_ONLINEUSER_ID_TXU
,EXT_ATTRIB_03
)
VALUES UPPER(
RECNUM
,BATCH_NUM_VAR
,IF_ROW_STAT_VAR
,SERVICE_REQUEST_ID_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,M_AC_NAME_VAR
,SQ_REV_NUM_VAR
,BU_VAR
,SERVICE_REQUEST_ID_VAR
,REF_GUID_VAR
,REF_CAMPAIGN_CODE_VAR
);
COMMIT;
UPDATE SERVICE_REQUEST
SET STATUS = 'F';
END LOOP;
CLOSE LT_Cursor;
COMMIT;
END;
/
|
|
|
|
| Re: Urgent: LOOP [message #22959 is a reply to message #22956] |
Thu, 07 November 2002 08:45  |
F.Tollenaar
Messages: 2
Registered: November 2002
|
Junior Member |
|
|
If - then ends with END IF !
You forgot them.
(btw, isn't is simpler to code:
r_record service_request%rowtype;
cursor lt_cursor
is
select *
from service_request;
begin
for r_record in lt_cursor
loop
blabla
end loop;
end;
?? )
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 10:11:18 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
