Home » Developer & Programmer » Data Integration » ORA-06550 Error (OWB11GR2)
ORA-06550 Error [message #540343] |
Sun, 22 January 2012 08:49 |
kand
Messages: 20 Registered: November 2007 Location: USA
|
Junior Member |
|
|
hi all
i'm getting the following error when i'm trying to deploy a mapping:
ORA-06550: PACKAGE BODY, line 684, column 15:
PL/SQL: ORA-00942: table or view does not exist
The GRANTS on the table are fine, what else could be the reason?
i'm using OWB 11GR2
Thanks
|
|
|
|
Re: ORA-06550 Error [message #540354 is a reply to message #540353] |
Sun, 22 January 2012 11:13 |
kand
Messages: 20 Registered: November 2007 Location: USA
|
Junior Member |
|
|
Thanks
Littlefoot , what information do you want , the code?
BEGIN
EXECUTE IMMEDIATE 'SELECT TABLE_NAME FROM OWB$TEMP_TABLES WHERE ROWNUM = 1';
-- The OWB system table exists, now attempt to drop any previously deployed temp tables associated with this package
DECLARE
TYPE StageTableCur_T IS REF CURSOR;
c1 StageTableCur_T;
sql_stmt VARCHAR2(200);
l_TableName VARCHAR2(32);
BEGIN
-- Drop previously deployed temp tables associated with this map, if any
sql_stmt := 'SELECT TABLE_NAME FROM OWB$TEMP_TABLES WHERE PACKAGE_NAME = ''MAP_DW_VVR_ELIGIBLE_IU''';
OPEN c1 FOR sql_stmt;
LOOP
FETCH c1 INTO l_TableName;
EXIT WHEN c1%NOTFOUND;
-- process record
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || l_TableName;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
CLOSE c1;
EXECUTE IMMEDIATE 'DELETE FROM OWB$TEMP_TABLES WHERE PACKAGE_NAME = ''MAP_DW_VVR_ELIGIBLE_IU''';
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
EXCEPTION WHEN OTHERS THEN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE OWB$TEMP_TABLES';
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
EXECUTE IMMEDIATE 'CREATE TABLE OWB$TEMP_TABLES(PACKAGE_NAME VARCHAR2(30), TABLE_NAME VARCHAR2(30))';
END;
/
CREATE OR REPLACE PACKAGE "MAP_DW_VVR_ELIGIBLE_IU" AS
OWB$MAP_OBJECT_ID VARCHAR2(32) := 'MAP_DW_VVR_ELIGIBLE_IU';
sql_stmt VARCHAR2(32767);
get_abort BOOLEAN := FALSE;
get_abort_procedure BOOLEAN := FALSE;
get_trigger_success BOOLEAN := TRUE;
get_errors NUMBER(22) := 0;
get_status NUMBER(22) := 0;
get_error_ratio NUMBER(22) := 0;
get_global_names VARCHAR2(10) := 'FALSE';
get_chunk_iterator NUMBER(22) := 0;
get_exit_chunk_loop BOOLEAN := TRUE;
get_total_processed_rowcount NUMBER(22) := 0;
-- Status variable for Batch cursors
"DW_VVR_ELIGIBLE_U_St" BOOLEAN := FALSE; "DW_VVR_ELIGIBLE_I_St" BOOLEAN := FALSE;
"CST_DEFAULTS_2_C_START_DT" DATE := TRUNC(SYSDATE);"CST_DEFAULTS_3_C_END_DT" DATE := TRUNC(SYSDATE)-1;"CST_DEFA_4_C_DEFAUL" DATE := TO_DATE('08/08/8888', 'MM/DD/YYYY');
-- Function Main
-- Entry point in package ""MAP_DW_VVR_ELIGIBLE_IU""
FUNCTION Main RETURN NUMBER;
END ""MAP_DW_VVR_ELIGIBLE_IU"";
/
CREATE OR REPLACE PACKAGE BODY "MAP_DW_VVR_ELIGIBLE_IU" AS
---------------------------------------------------------------------------
-- Function "DW_VVR_ELIGIBLE_U_Bat"
-- performs batch extraction
-- Returns TRUE on success
-- Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "DW_VVR_ELIGIBLE_U_Bat"
RETURN BOOLEAN IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
BEGIN
MERGE
/*+ APPEND PARALLEL("DW_VVR_ELIGIBLE_U") */
INTO
"DW_VVR_ELIGIBLE" "DW_VVR_ELIGIBLE_U"
USING
(SELECT
/* SPL_DW_VVR_ELIGIBLE.OUT_TO_UPD */
"SPLITTER_INPUT_SUBQUERY"."DW_VVR_ELIGIBLE_ID$1" "DW_VVR_ELIGIBLE_ID",
NULL "MERGE_COL_ALIAS",
NULL "MERGE_COL_ALIAS$1",
NULL "MERGE_COL_ALIAS$2",
NULL "MERGE_COL_ALIAS$3",
NULL "MERGE_COL_ALIAS$4",
NULL "MERGE_COL_ALIAS$5",
NULL "MERGE_COL_ALIAS$6",
NULL "MERGE_COL_ALIAS$7",
NULL "MERGE_COL_ALIAS$8",
NULL "MERGE_COL_ALIAS$9",
NULL "MERGE_COL_ALIAS$10",
"SPLITTER_INPUT_SUBQUERY"."CONT_END_DATE$1" "CONT_END_DATE",
"SPLITTER_INPUT_SUBQUERY"."VALUE$1" "VALUE",
NULL "MERGE_COL_ALIAS$11",
"SPLITTER_INPUT_SUBQUERY"."LOCATION_NAME$1" "LOCATION_NAME",
"SPLITTER_INPUT_SUBQUERY"."CHANNEL_NAME$1" "CHANNEL_NAME",
"SPLITTER_INPUT_SUBQUERY"."TERRITORY_NAME$1" "TERRITORY_NAME",
"SPLITTER_INPUT_SUBQUERY"."PAYMENT_METHOD$1" "PAYMENT_METHOD",
"SPLITTER_INPUT_SUBQUERY"."CONTACT_METHOD$1" "CONTACT_METHOD",
NULL "MERGE_COL_ALIAS$12",
NULL "MERGE_COL_ALIAS$13",
NULL "MERGE_COL_ALIAS$14",
NULL "MERGE_COL_ALIAS$15",
NULL "MERGE_COL_ALIAS$16",
"SPLITTER_INPUT_SUBQUERY"."C_START_DT$1" "C_START_DT",
"SPLITTER_INPUT_SUBQUERY"."C_AUDIT_USR$1" "C_AUDIT_USR",
"SPLITTER_INPUT_SUBQUERY"."DW_SELLER_LOCATION_CD$1" "DW_SELLER_LOCATION_CD",
"SPLITTER_INPUT_SUBQUERY"."DW_SELLER_ID$1" "DW_SELLER_ID",
"SPLITTER_INPUT_SUBQUERY"."CONTACT_METHOD_CHANNEL$1" "CONTACT_METHOD_CHANNEL"
FROM
(SELECT
/* CST_DEFAULTS.OUTGRP1 */
SYSDATE/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_SYSDATE",
REPLACE(get_model_name, '"', '')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_AUDIT_USR$1",
TRUNC(SYSDATE)/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_START_DT$1",
TRUNC(SYSDATE)-1/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_END_DT",
TO_DATE('08/08/8888', 'MM/DD/YYYY')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_DEFAULT_END_DT",
"SRC_VW_VVR_ELIGIBLE"."TRANSACTION_DT" "TRANSACTION_DT",
"SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO",
"SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO",
"SRC_VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID",
"SRC_VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC",
"SRC_VW_VVR_ELIGIBLE"."NAME" "NAME",
"SRC_VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1",
"SRC_VW_VVR_ELIGIBLE"."CITY" "CITY",
"SRC_VW_VVR_ELIGIBLE"."ST" "ST",
"SRC_VW_VVR_ELIGIBLE"."ZIP" "ZIP",
"SRC_VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE",
"SRC_VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE$1",
"SRC_VW_VVR_ELIGIBLE"."VALUE" "VALUE$1",
"SRC_VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME$1",
"SRC_VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME$1",
"SRC_VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME$1",
"SRC_VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD$1",
"SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD$1",
"LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID$1",
"LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT",
"SRC_VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
"SRC_VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS",
"LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT",
"LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT",
"SRC_VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD$1",
"SRC_VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID$1",
"SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL$1"
FROM
(SELECT
/* JNR_SRC_VVR_ELIGIBLE.OUT_DW_VVR_ELIGIBLE */
"VW_SEN_RENEWALS"."TRANSACTION_DT" "TRANSACTION_DT",
"VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO",
"VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO",
"VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID",
"VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC",
"VW_VVR_ELIGIBLE"."NAME" "NAME",
"VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1",
"VW_VVR_ELIGIBLE"."CITY" "CITY",
"VW_VVR_ELIGIBLE"."ST" "ST",
"VW_VVR_ELIGIBLE"."ZIP" "ZIP",
"VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE",
"VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE",
"VW_VVR_ELIGIBLE"."VALUE" "VALUE",
"VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME",
"VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME",
"VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME",
"VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD",
"VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD",
"VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
"VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS",
"VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD",
"VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID",
"VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL"
FROM
(SELECT
/* AGG_VW_SEN_RENEWALS.OUTGRP1 */
"AGG_VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
"AGG_VW_SEN_RENEWALS"."SERVICE_NO" "SERVICE_NO",
"AGG_VW_SEN_RENEWALS"."TRANSACTION_DT$1" "TRANSACTION_DT"
FROM
(SELECT
/* VW_SEN_RENEWALS.INOUTGRP1 */
"VW_SEN_RENEWALS"."SRC_ACCOUNT_ID"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SRC_ACCOUNT_ID */ "SRC_ACCOUNT_ID",
"VW_SEN_RENEWALS"."SERVICE_NO"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SERVICE_NO */ "SERVICE_NO",
MAX("VW_SEN_RENEWALS"."TRANSACTION_DT")/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.TRANSACTION_DT */ "TRANSACTION_DT$1"
FROM
"CDW_STG"."VW_SEN_RENEWALS" "VW_SEN_RENEWALS"
GROUP BY
"VW_SEN_RENEWALS"."SRC_ACCOUNT_ID","VW_SEN_RENEWALS"."SERVICE_NO" /* OPERATOR AGG_VW_SEN_RENEWALS: GROUP BY CLAUSE */) "AGG_VW_SEN_RENEWALS" ) "VW_SEN_RENEWALS"
,
"VW_VVR_ELIGIBLE" "VW_VVR_ELIGIBLE"
WHERE
( "VW_VVR_ELIGIBLE"."ACCOUNT_NO" = "VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" (+) ) AND
( "VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" = "VW_SEN_RENEWALS"."SERVICE_NO" (+)/* OPERATOR JNR_SRC_VVR_ELIGIBLE JOIN CONDITION */ ) ) "SRC_VW_VVR_ELIGIBLE" ,
(SELECT
/* LKP_DW_VVR_ELIGIBLE.INOUTGRP1 */
"LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID",
"LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
"LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" "SERVICE_NO",
"LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT",
"LKP_DW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
"LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT",
"LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT"
FROM
"DW_VVR_ELIGIBLE" "LKP_DW_VVR_ELIGIBLE") "LKP_DW_VVR_ELIGIBLE"
WHERE
( "SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" = "LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" (+) ) AND
( "SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" = "LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" (+)/* OPERATOR JNR_LKP_DW_VVR_ELIGIBLE_ID JOIN CONDITION */ ) ) "SPLITTER_INPUT_SUBQUERY"
WHERE
( "SPLITTER_INPUT_SUBQUERY"."DW_VVR_ELIGIBLE_ID$1" IS NOT NULL ) /* GROUP SPL_DW_VVR_ELIGIBLE.OUT_TO_UPD: SPLIT CONDITION*/
)
"MERGE_SUBQUERY"
ON (
"DW_VVR_ELIGIBLE_U"."DW_VVR_ELIGIBLE_ID" = "MERGE_SUBQUERY"."DW_VVR_ELIGIBLE_ID"
)
WHEN MATCHED THEN
UPDATE
SET
"CONT_END_DT" = "MERGE_SUBQUERY"."CONT_END_DATE",
"VALUE" = "MERGE_SUBQUERY"."VALUE",
"LOCATION_NAME" = "MERGE_SUBQUERY"."LOCATION_NAME",
"CHANNEL_NAME" = "MERGE_SUBQUERY"."CHANNEL_NAME",
"TERRITORY_NAME" = "MERGE_SUBQUERY"."TERRITORY_NAME",
"PAYMENT_METHOD" = "MERGE_SUBQUERY"."PAYMENT_METHOD",
"CONTACT_METHOD" = "MERGE_SUBQUERY"."CONTACT_METHOD",
"DW_UPDATE_DT" = "MERGE_SUBQUERY"."C_START_DT",
"DW_UPDATE_BY" = "MERGE_SUBQUERY"."C_AUDIT_USR",
"DW_SELLER_LOCATION_CD" = "MERGE_SUBQUERY"."DW_SELLER_LOCATION_CD",
"DW_SELLER_ID" = "MERGE_SUBQUERY"."DW_SELLER_ID",
"CHANNEL_NAME_CONTACT_METHOD" = "MERGE_SUBQUERY"."CONTACT_METHOD_CHANNEL"
;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
COMMIT;
RETURN FALSE;
END;
COMMIT;
RETURN TRUE;
END "DW_VVR_ELIGIBLE_U_Bat";
---------------------------------------------------------------------------
-- Function "DW_VVR_ELIGIBLE_I_Bat"
-- performs batch extraction
-- Returns TRUE on success
-- Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "DW_VVR_ELIGIBLE_I_Bat"
RETURN BOOLEAN IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
BEGIN
INSERT
/*+ APPEND PARALLEL("DW_VVR_ELIGIBLE_I") */
INTO
"DW_VVR_ELIGIBLE" "DW_VVR_ELIGIBLE_I"
("DW_VVR_ELIGIBLE_ID",
"SRC_ACCOUNT_ID",
"SERVICE_NO",
"SRC_CONTRACT_ID",
"RATE_PLAN_NAME",
"NAME",
"ADDRESS1",
"CITY",
"STATE_CD",
"POSTAL_CD",
"EMAIL_ADDRESS",
"ACT_DT",
"CONT_END_DT",
"VALUE",
"DW_LOCATION_ID",
"LOCATION_NAME",
"CHANNEL_NAME",
"TERRITORY_NAME",
"PAYMENT_METHOD",
"CONTACT_METHOD",
"RENEW_DT",
"DW_CREATE_DT",
"DW_CREATE_BY",
"DW_UPDATE_DT",
"DW_UPDATE_BY",
"DW_SELLER_LOCATION_CD",
"DW_SELLER_ID",
"CHANNEL_NAME_CONTACT_METHOD")
(SELECT
/* SEQ_DW_VVR_ELIGIBLE_ID.OUTGRP1 */
"SEQ_DW_VVR_ELIGIBLE_ID".NEXTVAL/* OPERATOR SEQ_DW_VVR_ELIGIBLE_ID */ ,
"SPLITTER_INPUT_SUBQUERY$1"."ACCOUNT_NO$2" "ACCOUNT_NO$1",
"SPLITTER_INPUT_SUBQUERY$1"."MAIN_SERVICE_NO$2" "MAIN_SERVICE_NO$1",
"SPLITTER_INPUT_SUBQUERY$1"."CONTRACT_ID$2" "CONTRACT_ID$1",
"SPLITTER_INPUT_SUBQUERY$1"."RP_DESC$2" "RP_DESC$1",
"SPLITTER_INPUT_SUBQUERY$1"."NAME$2" "NAME$1",
"SPLITTER_INPUT_SUBQUERY$1"."ADDRESS1$2" "ADDRESS1$1",
"SPLITTER_INPUT_SUBQUERY$1"."CITY$2" "CITY$1",
"SPLITTER_INPUT_SUBQUERY$1"."ST$2" "ST$1",
"SPLITTER_INPUT_SUBQUERY$1"."ZIP$2" "ZIP$1",
"SPLITTER_INPUT_SUBQUERY$1"."EMAIL_ADDRESS$2" "EMAIL_ADDRESS$1",
"SPLITTER_INPUT_SUBQUERY$1"."ACT_DATE$2" "ACT_DATE$1",
"SPLITTER_INPUT_SUBQUERY$1"."CONT_END_DATE$3" "CONT_END_DATE$2",
"SPLITTER_INPUT_SUBQUERY$1"."VALUE$3" "VALUE$2",
"SPLITTER_INPUT_SUBQUERY$1"."DW_LOCATION_ID$2" "DW_LOCATION_ID$1",
"SPLITTER_INPUT_SUBQUERY$1"."LOCATION_NAME$3" "LOCATION_NAME$2",
"SPLITTER_INPUT_SUBQUERY$1"."CHANNEL_NAME$3" "CHANNEL_NAME$2",
"SPLITTER_INPUT_SUBQUERY$1"."TERRITORY_NAME$3" "TERRITORY_NAME$2",
"SPLITTER_INPUT_SUBQUERY$1"."PAYMENT_METHOD$3" "PAYMENT_METHOD$2",
"SPLITTER_INPUT_SUBQUERY$1"."CONTACT_METHOD$3" "CONTACT_METHOD$2",
CASE
WHEN
NVL( "SPLITTER_INPUT_SUBQUERY$1"."TRANSACTION_DT$2" , TO_DATE('01/01/1900', 'MM/DD/YYYY')) >= "SPLITTER_INPUT_SUBQUERY$1"."C_SYSDATE$2"
THEN
"SPLITTER_INPUT_SUBQUERY$1"."TRANSACTION_DT$2"
ELSE
NULL
END/* ATTRIBUTE EXP_DEFAULTS.OUTGRP1.O_RENEWAL_DT: EXPRESSION */ "O_RENEWAL_DT",
"SPLITTER_INPUT_SUBQUERY$1"."C_SYSDATE$2" "C_SYSDATE$1",
"SPLITTER_INPUT_SUBQUERY$1"."C_AUDIT_USR$4" "C_AUDIT_USR$2",
"SPLITTER_INPUT_SUBQUERY$1"."C_START_DT$3" "C_START_DT$2",
"SPLITTER_INPUT_SUBQUERY$1"."C_AUDIT_USR$4" "C_AUDIT_USR$3",
"SPLITTER_INPUT_SUBQUERY$1"."DW_SELLER_LOCATION_CD$3" "DW_SELLER_LOCATION_CD$2",
"SPLITTER_INPUT_SUBQUERY$1"."DW_SELLER_ID$3" "DW_SELLER_ID$2",
"SPLITTER_INPUT_SUBQUERY$1"."CONTACT_METHOD_CHANNEL$3" "CONTACT_METHOD_CHANNEL$2"
FROM
(SELECT
/* CST_DEFAULTS.OUTGRP1 */
SYSDATE/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_SYSDATE$2",
REPLACE(get_model_name, '"', '')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_AUDIT_USR$4",
TRUNC(SYSDATE)/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_START_DT$3",
TRUNC(SYSDATE)-1/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_END_DT$1",
TO_DATE('08/08/8888', 'MM/DD/YYYY')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_DEFAULT_END_DT$1",
"SRC_VW_VVR_ELIGIBLE"."TRANSACTION_DT" "TRANSACTION_DT$2",
"SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO$2",
"SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO$2",
"SRC_VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID$2",
"SRC_VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC$2",
"SRC_VW_VVR_ELIGIBLE"."NAME" "NAME$2",
"SRC_VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1$2",
"SRC_VW_VVR_ELIGIBLE"."CITY" "CITY$2",
"SRC_VW_VVR_ELIGIBLE"."ST" "ST$2",
"SRC_VW_VVR_ELIGIBLE"."ZIP" "ZIP$2",
"SRC_VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE$2",
"SRC_VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE$3",
"SRC_VW_VVR_ELIGIBLE"."VALUE" "VALUE$3",
"SRC_VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME$3",
"SRC_VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME$3",
"SRC_VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME$3",
"SRC_VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD$3",
"SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD$3",
"LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID$2",
"LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT$1",
"SRC_VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID$2",
"SRC_VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS$2",
"LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT$1",
"LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT$1",
"SRC_VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD$3",
"SRC_VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID$3",
"SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL$3"
FROM
(SELECT
/* JNR_SRC_VVR_ELIGIBLE.OUT_DW_VVR_ELIGIBLE */
"VW_SEN_RENEWALS"."TRANSACTION_DT" "TRANSACTION_DT",
"VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO",
"VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO",
"VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID",
"VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC",
"VW_VVR_ELIGIBLE"."NAME" "NAME",
"VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1",
"VW_VVR_ELIGIBLE"."CITY" "CITY",
"VW_VVR_ELIGIBLE"."ST" "ST",
"VW_VVR_ELIGIBLE"."ZIP" "ZIP",
"VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE",
"VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE",
"VW_VVR_ELIGIBLE"."VALUE" "VALUE",
"VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME",
"VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME",
"VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME",
"VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD",
"VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD",
"VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
"VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS",
"VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD",
"VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID",
"VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL"
FROM
(SELECT
/* AGG_VW_SEN_RENEWALS.OUTGRP1 */
"AGG_VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
"AGG_VW_SEN_RENEWALS"."SERVICE_NO" "SERVICE_NO",
"AGG_VW_SEN_RENEWALS"."TRANSACTION_DT$1" "TRANSACTION_DT"
FROM
(SELECT
/* VW_SEN_RENEWALS.INOUTGRP1 */
"VW_SEN_RENEWALS"."SRC_ACCOUNT_ID"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SRC_ACCOUNT_ID */ "SRC_ACCOUNT_ID",
"VW_SEN_RENEWALS"."SERVICE_NO"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SERVICE_NO */ "SERVICE_NO",
MAX("VW_SEN_RENEWALS"."TRANSACTION_DT")/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.TRANSACTION_DT */ "TRANSACTION_DT$1"
FROM
"CDW_STG"."VW_SEN_RENEWALS" "VW_SEN_RENEWALS"
GROUP BY
"VW_SEN_RENEWALS"."SRC_ACCOUNT_ID","VW_SEN_RENEWALS"."SERVICE_NO" /* OPERATOR AGG_VW_SEN_RENEWALS: GROUP BY CLAUSE */) "AGG_VW_SEN_RENEWALS" ) "VW_SEN_RENEWALS"
,
"VW_VVR_ELIGIBLE" "VW_VVR_ELIGIBLE"
WHERE
( "VW_VVR_ELIGIBLE"."ACCOUNT_NO" = "VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" (+) ) AND
( "VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" = "VW_SEN_RENEWALS"."SERVICE_NO" (+)/* OPERATOR JNR_SRC_VVR_ELIGIBLE JOIN CONDITION */ ) ) "SRC_VW_VVR_ELIGIBLE" ,
(SELECT
/* LKP_DW_VVR_ELIGIBLE.INOUTGRP1 */
"LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID",
"LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
"LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" "SERVICE_NO",
"LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT",
"LKP_DW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
"LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT",
"LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT"
FROM
"DW_VVR_ELIGIBLE" "LKP_DW_VVR_ELIGIBLE") "LKP_DW_VVR_ELIGIBLE"
WHERE
( "SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" = "LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" (+) ) AND
( "SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" = "LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" (+)/* OPERATOR JNR_LKP_DW_VVR_ELIGIBLE_ID JOIN CONDITION */ ) ) "SPLITTER_INPUT_SUBQUERY$1"
WHERE
( "SPLITTER_INPUT_SUBQUERY$1"."DW_VVR_ELIGIBLE_ID$2" IS NULL ) /* GROUP SPL_DW_VVR_ELIGIBLE.OUT_TO_INS: SPLIT CONDITION*/
)
;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
COMMIT;
RETURN FALSE;
END;
COMMIT;
RETURN TRUE;
END "DW_VVR_ELIGIBLE_I_Bat";
FUNCTION Main RETURN NUMBER IS
get_batch_status BOOLEAN := TRUE;
BEGIN
PROCEDURE EXEC_AUTONOMOUS_SQL(CMD IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE (CMD);
COMMIT;
END;
-- Initialize all batch status variables
"DW_VVR_ELIGIBLE_U_St" := FALSE;
"DW_VVR_ELIGIBLE_I_St" := FALSE;
"DW_VVR_ELIGIBLE_I_St" := "DW_VVR_ELIGIBLE_I_Bat";
"DW_VVR_ELIGIBLE_U_St" := "DW_VVR_ELIGIBLE_U_Bat";
RETURN get_status;
END Main;
END ""MAP_DW_VVR_ELIGIBLE_IU"";
/
[mod-edit: code tags fixed by bb]
[Updated on: Sun, 22 January 2012 13:45] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 22:22:13 CDT 2024
|