warehouse builder's dependance on oem job system.

Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBD0wUZ17770 for ; Thu, 12 Dec 2002 18:58:30 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBD0wT317753 for ; Thu, 12 Dec 2002 18:58:29 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id NAA57258; Thu, 12 Dec 2002 13:40:28 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00518E6A; Thu, 12 Dec 2002 13:09:10 -0800 Message-ID: Date: Thu, 12 Dec 2002 13:09:10 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Chris Stephens Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Chris Stephens Subject: RE: warehouse builder's dependance on oem job system. Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----_=_NextPart_000_01C2A222.1D3697A0" ------_=_NextPart_000_01C2A222.1D3697A0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2A222.1D3697A0" ------_=_NextPart_001_01C2A222.1D3697A0 Content-Type: text/plain; charset="iso-8859-1" here is the tcl that was generated: "-r" "3,2,50,1000,50,wb,10" "-c" "WE8MSWIN1252" the package that is generated is quite long. i have absolutely no idea how to read the tcl. if you could point me somewhere, i could try and figure it out myself. any help is appreciated. attached is the package code if you are willing to take a look... (for some reason my computer associates .pls extensions with real audio) -----Original Message----- Sent: Tuesday, December 10, 2002 1:11 PM To: Multiple recipients of list ORACLE-L Chris, All the owb etl jobs are packages and if you have configured it right, then you could the execute the package_name.main procedure to run the etl process. your best best would be to read the tcl script and the associated parameters for the owb-oem job. You could paste the tcl and the parameters here to help u more. -Mandar -----Original Message----- Sent: Tuesday, December 10, 2002 9:31 AM To: Multiple recipients of list ORACLE-L ok, here's the situation: we are loading our warehouse via etl processes generated by warehouse builder (owb). we went live with this a little over a week ago. up to this point we have been running the jobs manually through owb. for obvious reasons we need to be able to schedule these jobs. the only way (that i know of) to schedule the owb jobs is to deploy them to entreprise manager. the problem is that our oem is VERY unreliable and seems to be related to bugs. we are running oem v9.2 and oracle ee v9.2. when jobs are scheduled through oem, they run sometimes and hang others. this is unexceptable. so my question is: does anyone know of a way to trap the commands that oem sends to the database? the obvious solution would be to just cron execution of the packages owb generates inside the database. this doesn't work though because owb generates funky code that takes parameters, whose values i don't know, for logging purposes. hopefully i explained the situation well enough. any ideas??....i'd like to get rid of the dependance on oem. oem sucks ------_=_NextPart_001_01C2A222.1D3697A0 Content-Type: text/html; charset="iso-8859-1" warehouse builder's dependance on oem job system.

here is the tcl that was generated:

"-r" "3,2,50,1000,50,wb,10" "-c" "WE8MSWIN1252"

the package that is generated is quite long.

i have absolutely no idea how to read the tcl.

if you could point me somewhere, i could try and figure it out myself.

any help is appreciated.

attached is the package code if you are willing to take a look...

(for some reason my computer associates .pls extensions with real audio)

-----Original Message-----
From: Mandar A. Ghosalkar [mailto:mghosalk@byer.com]
Sent: Tuesday, December 10, 2002 1:11 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: warehouse builder's dependance on oem job system.

Chris,

All the owb etl jobs are packages and if you have configured it right, then you could the execute the package_name.main procedure to run the etl process. your best best would be to read the tcl script and the associated parameters for the owb-oem job.

You could paste the tcl and the parameters here to help u more.

-Mandar

-----Original Message-----
From: Chris Stephens [mailto:ChrisStephens@affina.com]
Sent: Tuesday, December 10, 2002 9:31 AM
To: Multiple recipients of list ORACLE-L
Subject: warehouse builder's dependance on oem job system.

ok, here's the situation: we are loading our warehouse via etl processes generated by warehouse builder (owb). we went live with this a little over a week ago. up to this point we have been running the jobs manually through owb. for obvious reasons we need to be able to schedule these jobs. the only way (that i know of) to schedule the owb jobs is to deploy them to entreprise manager. the problem is that our oem is VERY unreliable and seems to be related to bugs. we are running oem v9.2 and oracle ee v9.2. when jobs are scheduled through oem, they run sometimes and hang others. this is unexceptable.

so my question is: does anyone know of a way to trap the commands that oem sends to the database? the obvious solution would be to just cron execution of the packages owb generates inside the database. this doesn't work though because owb generates funky code that takes parameters, whose values i don't know, for logging purposes.

hopefully i explained the situation well enough.

any ideas??....i'd like to get rid of the dependance on oem.

oem sucks

------_=_NextPart_001_01C2A222.1D3697A0-- ------_=_NextPart_000_01C2A222.1D3697A0 Content-Type: application/octet-stream; name="C40_ADMIN_CUSTOMER_MAP.pls" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="C40_ADMIN_CUSTOMER_MAP.pls" /***********************************************************************= ******=0A= -- Oracle Warehouse Builder=0A= -- Generator Version : 9.0.3.35.1=0A= -- Minimum Runtime Repository=0A= -- Version Required : 9.0.3.0.1=0A= -- Created Date : Thu Dec 12 14:53:14 CST 2002=0A= -- Modified Date : Thu Dec 12 14:53:14 CST 2002=0A= -- Created By : owb_rep=0A= -- Modified By : owb_rep=0A= -- Generated Object Type : PL/SQL Package=0A= -- Generated Object Name : C40_ADMIN_CUSTOMER_MAP=0A= ************************************************************************= *****/=0A= -- Copyright(c) 1999-2002 Oracle Corporation.=0A= =0A= CREATE OR REPLACE PACKAGE C40_ADMIN_CUSTOMER_MAP AS=0A= =0A= -- Auditing mode constants=0A= AUDIT_NONE CONSTANT BINARY_INTEGER :=3D 0;=0A= AUDIT_STATISTICS CONSTANT BINARY_INTEGER :=3D 1;=0A= AUDIT_ERROR_DETAILS CONSTANT BINARY_INTEGER :=3D 2;=0A= AUDIT_COMPLETE CONSTANT BINARY_INTEGER :=3D 3;=0A= =0A= -- Operating mode constants=0A= MODE_SET CONSTANT BINARY_INTEGER :=3D 0;=0A= MODE_ROW CONSTANT BINARY_INTEGER :=3D 1;=0A= MODE_ROW_TARGET CONSTANT BINARY_INTEGER :=3D 2;=0A= MODE_SET_FAILOVER_ROW CONSTANT BINARY_INTEGER :=3D 3;=0A= MODE_SET_FAILOVER_ROW_TARGET CONSTANT BINARY_INTEGER :=3D 4;=0A= =0A= -- Variables for auditing=0A= get_runtime_audit_id NUMBER(22) :=3D 0;=0A= get_audit_level BINARY_INTEGER :=3D = AUDIT_ERROR_DETAILS;=0A= get_cycle_date CONSTANT DATE :=3D SYSDATE;=0A= get_model_id CONSTANT NUMBER(22) :=3D 0;=0A= get_model_name CONSTANT VARCHAR2(40) :=3D = 'C40_ADMIN_CUSTOMER_MAP';=0A= get_purge_group CONSTANT VARCHAR2(40) :=3D 'wb';=0A= =0A= -- Processing variables=0A= get_selected NUMBER(22) :=3D 0;=0A= get_inserted NUMBER(22) :=3D 0;=0A= get_updated NUMBER(22) :=3D 0;=0A= get_deleted NUMBER(22) :=3D 0;=0A= get_merged NUMBER(22) :=3D 0;=0A= get_errors NUMBER(22) :=3D 0;=0A= get_abort BOOLEAN :=3D FALSE;=0A= =0A= get_max_errors NUMBER(22) :=3D 50;=0A= get_commit_frequency NUMBER(22) :=3D 50;=0A= get_operating_mode BINARY_INTEGER :=3D = MODE_SET_FAILOVER_ROW;=0A= check_record_cnt NUMBER(22) :=3D 0;=0A= sql_stmt VARCHAR2(32767);=0A= error_stmt VARCHAR2(2000);=0A= =0A= -- Bulk processing variables=0A= get_bulk_size NATURAL :=3D 50;=0A= =0A= -- DML Exceptions=0A= checked_table_not_empty EXCEPTION;=0A= PRAGMA EXCEPTION_INIT(checked_table_not_empty, -111);=0A= invalid_dml EXCEPTION;=0A= PRAGMA EXCEPTION_INIT(invalid_dml, -112);=0A= =0A= -- Status variable for Batch cursors=0A= "C40_ADMIN_CUSTOMER_St" BOOLEAN;=0A= =0A= -- Variables: user defined variables via mapping Variable components=0A= "CONST_HP_CONSTANT" VARCHAR2(4) :=3D 'HP';=0A= "CONST_SYS_DATE" DATE :=3D SYSDATE;=0A= =0A= -- Bulk: types for collection variables=0A= TYPE "T_HZ_PARTIES_PARTY_ID" IS TABLE OF NUMBER(15,0) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PARTY_NUMBER" IS TABLE OF VARCHAR2(30) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_FIRST_NAME" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_LAST_NAME" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_MIDDLE_NAM" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_TITLE" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ADDRESS1" IS TABLE OF VARCHAR2(240) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ADDRESS2" IS TABLE OF VARCHAR2(240) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_CITY" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_STATE" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_POSTAL_CODE" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ATTRIBUTE1" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ATTRIBUTE2" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_EXPR_COMPANY_NAME" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_EXPR_COMPANY_FLAG" IS TABLE OF VARCHAR2(1) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ATTRIBUTE3" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_CUSTOMER_ID" IS TABLE OF NUMBER(15,0) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_ACCOUNT_CODE" IS TABLE OF VARCHAR2(4) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_CUSTOMER_NUMBER" IS TABLE OF VARCHAR2(30) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_FIRST_NAM" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_LAST_NAME" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_MIDDLE_NAME" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_PREFIX" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_ADDRESS_1" IS TABLE OF VARCHAR2(240) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_ADDRESS_2" IS TABLE OF VARCHAR2(240) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_CITY" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_STATE" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_POSTAL_CODE" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_COMPANY_NAME" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_COMPANY_TITLE" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_COMPANY_FLAG" IS TABLE OF VARCHAR2(1) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_IMPORT_DATE" IS TABLE OF DATE INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PARTY_ID$0" IS TABLE OF NUMBER(15,0) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PARTY_NUMBER$0" IS TABLE OF VARCHAR2(30) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_FIRST_NA$0" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_LAST_NAM$0" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_MIDDLE_N$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_PERSON_TITLE$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ADDRESS1$0" IS TABLE OF VARCHAR2(240) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ADDRESS2$0" IS TABLE OF VARCHAR2(240) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_CITY$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_STATE$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_POSTAL_CODE$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_EXPR_COMPANY_NAME$0" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_HZ_PARTIES_ATTRIBUTE3$0" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_EXPR_COMPANY_FLAG$0" IS TABLE OF VARCHAR2(1) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_CUSTOMER_ID$0" IS TABLE OF NUMBER(15,0) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_ACCOUNT_CODE$0" IS TABLE OF VARCHAR2(4) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_CUSTOMER_NUMBER$0" IS TABLE OF VARCHAR2(30) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_FIRST_N$0" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_LAST_NA$0" IS TABLE OF VARCHAR2(150) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_MIDDLE_NAME$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_PREFIX$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_ADDRESS$0" IS TABLE OF VARCHAR2(240) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_ADDRESS$1" IS TABLE OF VARCHAR2(240) INDEX = BY BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_CITY$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMIN_CUSTOMER_STATE$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_POSTAL_CODE$0" IS TABLE OF VARCHAR2(60) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_COMPANY_NAME$0" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_COMPANY_TITLE$0" IS TABLE OF VARCHAR2(150) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_COMPANY_FLAG$0" IS TABLE OF VARCHAR2(1) INDEX BY = BINARY_INTEGER;=0A= TYPE "T_C40_ADMI_IMPORT_DATE$0" IS TABLE OF DATE INDEX BY = BINARY_INTEGER;=0A= =0A= -- Bulk: collection variables=0A= "HZ_PARTIES_PARTY_ID" "T_HZ_PARTIES_PARTY_ID";=0A= "HZ_PARTIES_PARTY_NUMBER" "T_HZ_PARTIES_PARTY_NUMBER";=0A= "HZ_PARTIES_PERSON_FIRST_NAME" "T_HZ_PARTIES_PERSON_FIRST_NAME";=0A= "HZ_PARTIES_PERSON_LAST_NAME" "T_HZ_PARTIES_PERSON_LAST_NAME";=0A= "HZ_PARTIES_PERSON_MIDDLE_NAM" "T_HZ_PARTIES_PERSON_MIDDLE_NAM";=0A= "HZ_PARTIES_PERSON_TITLE" "T_HZ_PARTIES_PERSON_TITLE";=0A= "HZ_PARTIES_ADDRESS1" "T_HZ_PARTIES_ADDRESS1";=0A= "HZ_PARTIES_ADDRESS2" "T_HZ_PARTIES_ADDRESS2";=0A= "HZ_PARTIES_CITY" "T_HZ_PARTIES_CITY";=0A= "HZ_PARTIES_STATE" "T_HZ_PARTIES_STATE";=0A= "HZ_PARTIES_POSTAL_CODE" "T_HZ_PARTIES_POSTAL_CODE";=0A= "HZ_PARTIES_ATTRIBUTE1" "T_HZ_PARTIES_ATTRIBUTE1";=0A= "HZ_PARTIES_ATTRIBUTE2" "T_HZ_PARTIES_ATTRIBUTE2";=0A= "EXPR_COMPANY_NAME" "T_EXPR_COMPANY_NAME";=0A= "EXPR_COMPANY_FLAG" "T_EXPR_COMPANY_FLAG";=0A= "HZ_PARTIES_ATTRIBUTE3" "T_HZ_PARTIES_ATTRIBUTE3";=0A= "C40_ADMI_CUSTOMER_ID" "T_C40_ADMI_CUSTOMER_ID";=0A= "C40_ADMI_ACCOUNT_CODE" "T_C40_ADMI_ACCOUNT_CODE";=0A= "C40_ADMI_CUSTOMER_NUMBER" "T_C40_ADMI_CUSTOMER_NUMBER";=0A= "C40_ADMIN_CUSTOMER_FIRST_NAM" "T_C40_ADMIN_CUSTOMER_FIRST_NAM";=0A= "C40_ADMIN_CUSTOMER_LAST_NAME" "T_C40_ADMIN_CUSTOMER_LAST_NAME";=0A= "C40_ADMI_MIDDLE_NAME" "T_C40_ADMI_MIDDLE_NAME";=0A= "C40_ADMIN_CUSTOMER_PREFIX" "T_C40_ADMIN_CUSTOMER_PREFIX";=0A= "C40_ADMIN_CUSTOMER_ADDRESS_1" "T_C40_ADMIN_CUSTOMER_ADDRESS_1";=0A= "C40_ADMIN_CUSTOMER_ADDRESS_2" "T_C40_ADMIN_CUSTOMER_ADDRESS_2";=0A= "C40_ADMIN_CUSTOMER_CITY" "T_C40_ADMIN_CUSTOMER_CITY";=0A= "C40_ADMIN_CUSTOMER_STATE" "T_C40_ADMIN_CUSTOMER_STATE";=0A= "C40_ADMI_POSTAL_CODE" "T_C40_ADMI_POSTAL_CODE";=0A= "C40_ADMI_COMPANY_NAME" "T_C40_ADMI_COMPANY_NAME";=0A= "C40_ADMI_COMPANY_TITLE" "T_C40_ADMI_COMPANY_TITLE";=0A= "C40_ADMI_COMPANY_FLAG" "T_C40_ADMI_COMPANY_FLAG";=0A= "C40_ADMI_IMPORT_DATE" "T_C40_ADMI_IMPORT_DATE";=0A= "HZ_PARTIES_PARTY_ID$0" "T_HZ_PARTIES_PARTY_ID$0";=0A= "HZ_PARTIES_PARTY_NUMBER$0" "T_HZ_PARTIES_PARTY_NUMBER$0";=0A= "HZ_PARTIES_PERSON_FIRST_NA$0" "T_HZ_PARTIES_PERSON_FIRST_NA$0";=0A= "HZ_PARTIES_PERSON_LAST_NAM$0" "T_HZ_PARTIES_PERSON_LAST_NAM$0";=0A= "HZ_PARTIES_PERSON_MIDDLE_N$0" "T_HZ_PARTIES_PERSON_MIDDLE_N$0";=0A= "HZ_PARTIES_PERSON_TITLE$0" "T_HZ_PARTIES_PERSON_TITLE$0";=0A= "HZ_PARTIES_ADDRESS1$0" "T_HZ_PARTIES_ADDRESS1$0";=0A= "HZ_PARTIES_ADDRESS2$0" "T_HZ_PARTIES_ADDRESS2$0";=0A= "HZ_PARTIES_CITY$0" "T_HZ_PARTIES_CITY$0";=0A= "HZ_PARTIES_STATE$0" "T_HZ_PARTIES_STATE$0";=0A= "HZ_PARTIES_POSTAL_CODE$0" "T_HZ_PARTIES_POSTAL_CODE$0";=0A= "EXPR_COMPANY_NAME$0" "T_EXPR_COMPANY_NAME$0";=0A= "HZ_PARTIES_ATTRIBUTE3$0" "T_HZ_PARTIES_ATTRIBUTE3$0";=0A= "EXPR_COMPANY_FLAG$0" "T_EXPR_COMPANY_FLAG$0";=0A= "C40_ADMI_CUSTOMER_ID$0" "T_C40_ADMI_CUSTOMER_ID$0";=0A= "C40_ADMI_ACCOUNT_CODE$0" "T_C40_ADMI_ACCOUNT_CODE$0";=0A= "C40_ADMI_CUSTOMER_NUMBER$0" "T_C40_ADMI_CUSTOMER_NUMBER$0";=0A= "C40_ADMIN_CUSTOMER_FIRST_N$0" "T_C40_ADMIN_CUSTOMER_FIRST_N$0";=0A= "C40_ADMIN_CUSTOMER_LAST_NA$0" "T_C40_ADMIN_CUSTOMER_LAST_NA$0";=0A= "C40_ADMI_MIDDLE_NAME$0" "T_C40_ADMI_MIDDLE_NAME$0";=0A= "C40_ADMIN_CUSTOMER_PREFIX$0" "T_C40_ADMIN_CUSTOMER_PREFIX$0";=0A= "C40_ADMIN_CUSTOMER_ADDRESS$0" "T_C40_ADMIN_CUSTOMER_ADDRESS$0";=0A= "C40_ADMIN_CUSTOMER_ADDRESS$1" "T_C40_ADMIN_CUSTOMER_ADDRESS$1";=0A= "C40_ADMIN_CUSTOMER_CITY$0" "T_C40_ADMIN_CUSTOMER_CITY$0";=0A= "C40_ADMIN_CUSTOMER_STATE$0" "T_C40_ADMIN_CUSTOMER_STATE$0";=0A= "C40_ADMI_POSTAL_CODE$0" "T_C40_ADMI_POSTAL_CODE$0";=0A= "C40_ADMI_COMPANY_NAME$0" "T_C40_ADMI_COMPANY_NAME$0";=0A= "C40_ADMI_COMPANY_TITLE$0" "T_C40_ADMI_COMPANY_TITLE$0";=0A= "C40_ADMI_COMPANY_FLAG$0" "T_C40_ADMI_COMPANY_FLAG$0";=0A= "C40_ADMI_IMPORT_DATE$0" "T_C40_ADMI_IMPORT_DATE$0";=0A= =0A= -- Procedure Main -- Entry point in package C40_ADMIN_CUSTOMER_MAP=0A= PROCEDURE Main(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, = p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER = DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN = NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL') ; =0A= =0A= END C40_ADMIN_CUSTOMER_MAP;=0A= =0A= /=0A= =0A= CREATE OR REPLACE PACKAGE BODY C40_ADMIN_CUSTOMER_MAP AS=0A= =0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Function "C40_ADMIN_CUSTOMER_Bat" =0A= -- performs batch extraction=0A= -- Returns TRUE on success=0A= -- Returns FALSE on failure=0A= ------------------------------------------------------------------------= ---=0A= FUNCTION "C40_ADMIN_CUSTOMER_Bat" RETURN BOOLEAN IS =0A= batch_selected NUMBER(22) :=3D 0;=0A= batch_errors NUMBER(22) :=3D 0;=0A= batch_inserted NUMBER(22) :=3D 0;=0A= batch_merged NUMBER(22) :=3D 0;=0A= batch_action VARCHAR2(12);=0A= actual_owner VARCHAR2(30);=0A= actual_name VARCHAR2(30);=0A= num_fk_err NUMBER(22);=0A= l_rowkey NUMBER(22) :=3D 0;=0A= l_table VARCHAR2(30) :=3D 'CREATE';=0A= l_rowid ROWID;=0A= l_owner VARCHAR2(30);=0A= l_tablename VARCHAR2(30);=0A= l_constraint VARCHAR2(30);=0A= sql_excp_stmt VARCHAR2(32767);=0A= batch_exception BOOLEAN :=3D FALSE;=0A= get_map_num_rows NUMBER(22) :=3D 0;=0A= TYPE exceptionsCurType IS REF CURSOR;=0A= exceptions_cursor exceptionsCurType;=0A= "C40_ADMIN_CUSTOMER_id" NUMBER(22) :=3D 0;=0A= =0A= BEGIN=0A= IF get_abort THEN=0A= RETURN FALSE;=0A= END IF;=0A= IF get_audit_level !=3D AUDIT_NONE THEN=0A= "C40_ADMIN_CUSTOMER_id" :=3D WBPRTI.auditd_begin(=0A= get_runtime_audit_id,=0A= 0,=0A= '"C40_ADMIN_CUSTOMER_Bat" ',=0A= '*',=0A= '"C40_ADMIN_CUSTOMER"',=0A= NULL,=0A= NULL=0A= );=0A= END IF;=0A= COMMIT;=0A= =0A= EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';=0A= =0A= BEGIN =0A= =0A= -- resolve actual owner and name=0A= WBPRTIU.resolve_name(USER, 'C40_ADMIN_CUSTOMER', actual_owner, = actual_name);=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= -- Truncate the target table=0A= EXECUTE IMMEDIATE 'TRUNCATE TABLE "C40_ADMIN_CUSTOMER"';=0A= END IF;=0A= =0A= COMMIT;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= =0A= batch_action :=3D 'BATCH INSERT';=0A= INSERT=0A= /*+ APPEND PARALLEL("C39_CALL_CUSTOMER", DEFAULT, DEFAULT) */=0A= INTO=0A= "C40_ADMIN_CUSTOMER"=0A= ("CUSTOMER_ID",=0A= "ACCOUNT_CODE",=0A= "CUSTOMER_NUMBER",=0A= "FIRST_NAME",=0A= "LAST_NAME",=0A= "MIDDLE_NAME",=0A= "PREFIX",=0A= "ADDRESS_1",=0A= "ADDRESS_2",=0A= "CITY",=0A= "STATE",=0A= "POSTAL_CODE",=0A= "COMPANY_NAME",=0A= "COMPANY_TITLE",=0A= "COMPANY_FLAG",=0A= "IMPORT_DATE")=0A= (SELECT=0A= /*+ NO_MERGE */=0A= "HZ_PARTIES_HP_CRM_LNK"."PARTY_ID" "PARTY_ID",=0A= C40_ADMIN_CUSTOMER_MAP."CONST_HP_CONSTANT" "HP_CONSTANT",=0A= "HZ_PARTIES_HP_CRM_LNK"."PARTY_NUMBER" "PARTY_NUMBER",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_FIRST_NAME" = "PERSON_FIRST_NAME",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_LAST_NAME" = "PERSON_LAST_NAME",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_MIDDLE_NAME" = "PERSON_MIDDLE_NAME",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_TITLE" "PERSON_TITLE",=0A= "HZ_PARTIES_HP_CRM_LNK"."ADDRESS1" "ADDRESS1",=0A= "HZ_PARTIES_HP_CRM_LNK"."ADDRESS2" "ADDRESS2",=0A= "HZ_PARTIES_HP_CRM_LNK"."CITY" "CITY",=0A= "HZ_PARTIES_HP_CRM_LNK"."STATE" "STATE",=0A= "HZ_PARTIES_HP_CRM_LNK"."POSTAL_CODE" "POSTAL_CODE",=0A= CASE WHEN UPPER("HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1") =3D = 'OTHER'=0A= THEN "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE2"=0A= WHEN UPPER("HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1") =3D 'PERSONAL USE'=0A= THEN NULL=0A= ELSE "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1"=0A= END "COMPANY_NAME",=0A= "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE3" "ATTRIBUTE3",=0A= CASE WHEN UPPER("HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1") NOT IN = ('OTHER', 'PERSONAL USE')=0A= THEN 'Y'=0A= ELSE NULL=0A= END "COMPANY_FLAG",=0A= C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE" "SYS_DATE"=0A= FROM=0A= "HZ_PARTIES"@"HP_CRM_LNK" "HZ_PARTIES_HP_CRM_LNK"=0A= );=0A= batch_inserted :=3D SQL%ROWCOUNT;=0A= batch_selected :=3D SQL%ROWCOUNT;=0A= IF get_errors + batch_errors > get_max_errors THEN=0A= get_abort :=3D TRUE;=0A= END IF;=0A= COMMIT;=0A= END IF;=0A= EXCEPTION WHEN OTHERS THEN=0A= ROLLBACK;=0A= batch_errors :=3D batch_errors + 1;=0A= IF get_errors + batch_errors > get_max_errors THEN=0A= get_abort :=3D TRUE;=0A= END IF;=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN=0A= WBPRTI.error(=0A= get_runtime_audit_id,=0A= 0,=0A= "C40_ADMIN_CUSTOMER_id",=0A= 0,=0A= '"C40_ADMIN_CUSTOMER"',=0A= '*',=0A= NULL,=0A= batch_action,=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= get_errors :=3D get_errors + batch_errors;=0A= get_selected :=3D get_selected + batch_selected;=0A= IF get_audit_level !=3D AUDIT_NONE THEN=0A= WBPRTI.auditd_end(=0A= p_rtd=3D>"C40_ADMIN_CUSTOMER_id",=0A= p_sel=3D>batch_selected,=0A= p_ins=3D>NULL,=0A= p_upd=3D>NULL,=0A= p_del=3D>NULL,=0A= p_err=3D>batch_errors,=0A= p_dis=3D>NULL,=0A= p_mer=3D>NULL=0A= );=0A= END IF;=0A= COMMIT;=0A= batch_exception :=3D TRUE;=0A= END;=0A= =0A= BEGIN =0A= IF NOT batch_exception THEN =0A= IF actual_name IS NOT NULL THEN =0A= SELECT NVL(num_rows, 0) INTO get_map_num_rows=0A= FROM all_tables=0A= WHERE table_name =3D actual_name=0A= AND owner =3D actual_owner;=0A= IF (batch_inserted + batch_merged) >=3D get_map_num_rows THEN = =0A= DBMS_STATS.gather_table_stats( =0A= ownname=3D>actual_owner,=0A= tabname=3D>actual_name,=0A= estimate_percent=3D>10,=0A= granularity=3D>'GLOBAL',=0A= degree=3D>null=0A= );=0A= END IF;=0A= END IF;=0A= END IF;=0A= EXCEPTION WHEN OTHERS THEN =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN=0A= WBPRTI.error(=0A= get_runtime_audit_id,=0A= 0,=0A= "C40_ADMIN_CUSTOMER_id",=0A= 0,=0A= '"C40_ADMIN_CUSTOMER"',=0A= '*',=0A= NULL,=0A= batch_action,=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= END;=0A= =0A= IF batch_exception THEN =0A= RETURN FALSE;=0A= END IF;=0A= get_inserted :=3D get_inserted + batch_inserted;=0A= get_errors :=3D get_errors + batch_errors;=0A= get_selected :=3D get_selected + batch_selected;=0A= get_merged :=3D get_merged + batch_merged;=0A= IF get_audit_level !=3D AUDIT_NONE THEN=0A= WBPRTI.auditd_end(=0A= p_rtd=3D>"C40_ADMIN_CUSTOMER_id",=0A= p_sel=3D>batch_selected,=0A= p_ins=3D>batch_inserted,=0A= p_upd=3D>NULL,=0A= p_del=3D>NULL,=0A= p_err=3D>batch_errors,=0A= p_dis=3D>NULL,=0A= p_mer=3D>batch_merged=0A= );=0A= END IF;=0A= COMMIT;=0A= RETURN TRUE;=0A= END "C40_ADMIN_CUSTOMER_Bat";=0A= =0A= =0A= -- Procedure "HZ_PARTIES_p" is the entry point for map = "HZ_PARTIES_p"=0A= =0A= PROCEDURE "HZ_PARTIES_p" IS=0A= -- Constants for this map=0A= get_map_name CONSTANT VARCHAR2(40) :=3D = '"HZ_PARTIES_p"';=0A= get_source_name CONSTANT VARCHAR2(80) :=3D = SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80);=0A= get_step_number CONSTANT NUMBER(22) :=3D 1;=0A= =0A= get_is_cursor_open BOOLEAN :=3D FALSE;=0A= get_map_selected NUMBER(22) :=3D 0;=0A= get_map_errors NUMBER(22) :=3D 0;=0A= get_map_num_rows NUMBER(22) :=3D 0;=0A= =0A= actual_owner VARCHAR2(30);=0A= actual_name VARCHAR2(30);=0A= =0A= -- Constraint management=0A= num_fk_err NUMBER(22);=0A= l_rowkey NUMBER(22) :=3D 0;=0A= l_table VARCHAR2(30) :=3D 'CREATE';=0A= l_rowid ROWID;=0A= l_owner VARCHAR2(30);=0A= l_tablename VARCHAR2(30);=0A= l_constraint VARCHAR2(30);=0A= sql_excp_stmt VARCHAR2(32767);=0A= TYPE exceptionsCurType IS REF CURSOR;=0A= exceptions_cursor exceptionsCurType;=0A= =0A= normal_action VARCHAR2(20);=0A= error_action VARCHAR2(20);=0A= get_audit_detail_id NUMBER(22) :=3D 0;=0A= get_target_name VARCHAR2(80);=0A= error_column VARCHAR2(80);=0A= error_value VARCHAR2(2000);=0A= =0A= =0A= -- Scalar variables for auditing=0A= "C40_ADMIN_CUSTOMER_id" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_ins" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_upd" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_del" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_err" NUMBER(22) :=3D 0;=0A= =0A= -- Variables for auditing in bulk processing=0A= one_rowkey NUMBER(22) :=3D 0;=0A= get_rowkey NUMBER(22) :=3D 0;=0A= get_rowkey_bulk WBPRTI.NUMBERLIST;=0A= one_rowid ROWID;=0A= get_rowid WBPRTI.ROWIDLIST;=0A= rowkey_bulk_index NUMBER(22) :=3D 0;=0A= x_it_err_count NUMBER(22) :=3D 0;=0A= =0A= "C40_ADMIN_CUSTOMER_srk" WBPRTI.NUMBERLIST;=0A= =0A= -- Bulk processing=0A= error_index NUMBER(22);=0A= update_bulk WBPRTI.NUMBERLIST;=0A= update_bulk_index NUMBER(22) :=3D 0;=0A= insert_bulk_index NUMBER(22) :=3D 0;=0A= last_successful_index NUMBER(22) :=3D 0;=0A= feedback_bulk_limit NUMBER(22) :=3D 0;=0A= =0A= "HZ_PARTIES_si" NUMBER(22) :=3D 0;=0A= "HZ_PARTIES_i" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_si" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_i" NUMBER(22) :=3D 0;=0A= =0A= -- Cursor declaration=0A= CURSOR "HZ_PARTIES_c" IS=0A= SELECT=0A= /*+ NO_MERGE */=0A= "HZ_PARTIES_HP_CRM_LNK"."PARTY_ID" "PARTY_ID$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."PARTY_NUMBER" "PARTY_NUMBER$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_FIRST_NAME" = "PERSON_FIRST_NAME$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_LAST_NAME" "PERSON_LAST_NAME$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_MIDDLE_NAME" = "PERSON_MIDDLE_NAME$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_TITLE" "PERSON_TITLE$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."ADDRESS1" "ADDRESS1$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."ADDRESS2" "ADDRESS2$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."CITY" "CITY$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."STATE" "STATE$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."POSTAL_CODE" "POSTAL_CODE$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1" "ATTRIBUTE1",=0A= "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE2" "ATTRIBUTE2",=0A= "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE3" "ATTRIBUTE3$0"=0A= FROM=0A= "HZ_PARTIES"@"HP_CRM_LNK" "HZ_PARTIES_HP_CRM_LNK"; =0A= =0A= ------------------------------------------------------------------------= ---=0A= -- This procedure records column values of one erroneous source row=0A= -- into an audit trail table named WB_RT_ERROR_SOURCES. Each column = is=0A= -- recorded by one row in the audit trail. To collect all source = column=0A= -- values corresponding to one erroneous source row, query the audit=0A= -- trail and specify:=0A= -- RTA_IID, uniquely identifies one audited run,=0A= -- RTE_ROWKEY, uniquely identifies a source row within and audited = run=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_ES"(error_index IN NUMBER) IS=0A= BEGIN=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 1,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PARTY_ID',0,80),=0A= SUBSTR("HZ_PARTIES_PARTY_ID"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 2,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PARTY_NUMBER',0,80),=0A= SUBSTR("HZ_PARTIES_PARTY_NUMBER"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 3,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_FIRST_NAME',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_FIRST_NAME"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 4,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_LAST_NAME',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_LAST_NAME"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 5,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_MIDDLE_NAME',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_MIDDLE_NAM"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 6,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_TITLE',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_TITLE"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 7,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ADDRESS1',0,80),=0A= SUBSTR("HZ_PARTIES_ADDRESS1"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 8,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ADDRESS2',0,80),=0A= SUBSTR("HZ_PARTIES_ADDRESS2"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 9,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_CITY',0,80),=0A= SUBSTR("HZ_PARTIES_CITY"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 10,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_STATE',0,80),=0A= SUBSTR("HZ_PARTIES_STATE"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 11,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_POSTAL_CODE',0,80),=0A= SUBSTR("HZ_PARTIES_POSTAL_CODE"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 12,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ATTRIBUTE1',0,80),=0A= SUBSTR("HZ_PARTIES_ATTRIBUTE1"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 13,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ATTRIBUTE2',0,80),=0A= SUBSTR("HZ_PARTIES_ATTRIBUTE2"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 14,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ATTRIBUTE3',0,80),=0A= SUBSTR("HZ_PARTIES_ATTRIBUTE3"(error_index),0,2000),=0A= get_step_number=0A= );=0A= RETURN;=0A= END "HZ_PARTIES_ES";=0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Procedure "HZ_PARTIES_ER" registers error for one errorneous row=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_ER"(p_statement IN VARCHAR2, p_column IN = VARCHAR2, p_col_value IN VARCHAR2, p_sqlcode IN NUMBER, p_sqlerrm IN = VARCHAR2, p_auditd_id IN NUMBER, p_error_index IN NUMBER) IS=0A= l_source_target_name VARCHAR2(80);=0A= BEGIN =0A= IF p_auditd_id IS NULL THEN =0A= l_source_target_name :=3D get_source_name;=0A= ELSE =0A= l_source_target_name :=3D get_target_name;=0A= END IF;=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN =0A= WBPRTI.error( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= p_auditd_id,=0A= get_rowkey + p_error_index - 1,=0A= l_source_target_name,=0A= p_column,=0A= p_col_value,=0A= p_statement,=0A= p_sqlcode,=0A= p_sqlerrm,=0A= NULL=0A= );=0A= END IF;=0A= =0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= WBPRTI.register_feedback( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= get_rowkey + p_error_index - 1,=0A= 'ERROR',=0A= l_source_target_name,=0A= 'T',=0A= SUBSTR(p_statement,0,30)=0A= );=0A= END IF;=0A= =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN =0A= "HZ_PARTIES_ES"(p_error_index);=0A= END IF;=0A= END "HZ_PARTIES_ER";=0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Procedure "HZ_PARTIES_SU" opens and initializes data source=0A= -- for map "HZ_PARTIES_p"=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_SU" IS=0A= BEGIN=0A= IF get_abort THEN=0A= RETURN;=0A= END IF;=0A= OPEN "HZ_PARTIES_c";=0A= get_is_cursor_open :=3D TRUE;=0A= END "HZ_PARTIES_SU";=0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Procedure "HZ_PARTIES_RD" fetches a bulk of rows from=0A= -- the data source for map "HZ_PARTIES_p"=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_RD" IS=0A= BEGIN =0A= IF get_abort THEN =0A= RETURN;=0A= END IF;=0A= =0A= BEGIN =0A= "HZ_PARTIES_PARTY_ID".DELETE;=0A= "HZ_PARTIES_PARTY_NUMBER".DELETE;=0A= "HZ_PARTIES_PERSON_FIRST_NAME".DELETE;=0A= "HZ_PARTIES_PERSON_LAST_NAME".DELETE;=0A= "HZ_PARTIES_PERSON_MIDDLE_NAM".DELETE;=0A= "HZ_PARTIES_PERSON_TITLE".DELETE;=0A= "HZ_PARTIES_ADDRESS1".DELETE;=0A= "HZ_PARTIES_ADDRESS2".DELETE;=0A= "HZ_PARTIES_CITY".DELETE;=0A= "HZ_PARTIES_STATE".DELETE;=0A= "HZ_PARTIES_POSTAL_CODE".DELETE;=0A= "HZ_PARTIES_ATTRIBUTE1".DELETE;=0A= "HZ_PARTIES_ATTRIBUTE2".DELETE;=0A= "HZ_PARTIES_ATTRIBUTE3".DELETE;=0A= =0A= FETCH =0A= "HZ_PARTIES_c"=0A= BULK COLLECT INTO =0A= "HZ_PARTIES_PARTY_ID",=0A= "HZ_PARTIES_PARTY_NUMBER",=0A= "HZ_PARTIES_PERSON_FIRST_NAME",=0A= "HZ_PARTIES_PERSON_LAST_NAME",=0A= "HZ_PARTIES_PERSON_MIDDLE_NAM",=0A= "HZ_PARTIES_PERSON_TITLE",=0A= "HZ_PARTIES_ADDRESS1",=0A= "HZ_PARTIES_ADDRESS2",=0A= "HZ_PARTIES_CITY",=0A= "HZ_PARTIES_STATE",=0A= "HZ_PARTIES_POSTAL_CODE",=0A= "HZ_PARTIES_ATTRIBUTE1",=0A= "HZ_PARTIES_ATTRIBUTE2",=0A= "HZ_PARTIES_ATTRIBUTE3"=0A= LIMIT get_bulk_size;=0A= =0A= IF "HZ_PARTIES_c"%NOTFOUND AND "HZ_PARTIES_PARTY_ID".COUNT =3D 0 = THEN =0A= RETURN;=0A= END IF;=0A= =0A= -- register feedback for successful reads=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN =0A= get_rowkey :=3D = WBPRTI.get_rowkey("HZ_PARTIES_PARTY_ID".COUNT);=0A= END IF;=0A= =0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= WBPRTI.register_feedback_bulk( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= get_rowkey,=0A= 'NEW',=0A= get_source_name,=0A= 'S',=0A= 'SELECT',=0A= "HZ_PARTIES_PARTY_ID".COUNT=0A= );=0A= END IF;=0A= get_map_selected :=3D get_map_selected + = "HZ_PARTIES_PARTY_ID".COUNT;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= -- register error=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN=0A= one_rowkey :=3D WBPRTI.get_rowkey;=0A= WBPRTI.error(=0A= get_runtime_audit_id,=0A= get_step_number,=0A= NULL,=0A= one_rowkey,=0A= get_source_name,=0A= '*',=0A= NULL,=0A= 'SELECT',=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= =0A= -- register feedback for the erroneous row=0A= IF get_audit_level =3D AUDIT_COMPLETE THEN=0A= WBPRTI.register_feedback(=0A= get_runtime_audit_id,=0A= get_step_number,=0A= one_rowkey,=0A= 'ERROR',=0A= get_source_name,=0A= 'S',=0A= 'SELECT'=0A= );=0A= END IF;=0A= get_errors :=3D get_errors + get_bulk_size - = "HZ_PARTIES_PARTY_ID".COUNT;=0A= IF get_errors > get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= END "HZ_PARTIES_RD";=0A= =0A= =0A= ------------------------------------------------------------------------= ---=0A= -- "HZ_PARTIES_p" main=0A= ------------------------------------------------------------------------= ---=0A= =0A= BEGIN =0A= IF get_abort THEN =0A= RETURN;=0A= END IF;=0A= =0A= sql_stmt :=3D 'ALTER SESSION DISABLE PARALLEL DML';=0A= EXECUTE IMMEDIATE sql_stmt;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= =0A= IF get_audit_level !=3D AUDIT_NONE THEN =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= "C40_ADMIN_CUSTOMER_id" :=3D =0A= = WBPRTI.auditd_begin(get_runtime_audit_id,get_step_number,get_map_name,=0A= get_source_name,'"C40_ADMIN_CUSTOMER"',NULL,NULL);=0A= END IF;=0A= COMMIT;=0A= END IF;=0A= =0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= -- Truncate the target table=0A= BEGIN =0A= EXECUTE IMMEDIATE 'TRUNCATE TABLE "C40_ADMIN_CUSTOMER"';=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level = =3D AUDIT_COMPLETE THEN =0A= WBPRTI.error( =0A= get_runtime_audit_id,=0A= 1,=0A= "C40_ADMIN_CUSTOMER_id",=0A= 0,=0A= '"C40_ADMIN_CUSTOMER"',=0A= '*',=0A= NULL,=0A= 'TRUNCATE',=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= get_abort :=3D TRUE;=0A= END;=0A= END IF;=0A= =0A= "HZ_PARTIES_si" :=3D 0;=0A= "C40_ADMIN_CUSTOMER_i" :=3D 1;=0A= =0A= IF NOT get_abort THEN =0A= "HZ_PARTIES_SU";=0A= =0A= LOOP =0A= IF "HZ_PARTIES_si" =3D 0 THEN =0A= "HZ_PARTIES_RD"; -- Fetch data from source=0A= END IF;=0A= =0A= -- Processing:=0A= "HZ_PARTIES_i" :=3D "HZ_PARTIES_si";=0A= BEGIN =0A= LOOP =0A= EXIT WHEN "C40_ADMIN_CUSTOMER_i" > get_bulk_size OR = get_abort;=0A= =0A= "HZ_PARTIES_i" :=3D "HZ_PARTIES_i" + 1;=0A= "HZ_PARTIES_si" :=3D "HZ_PARTIES_i";=0A= =0A= IF "HZ_PARTIES_i" > "HZ_PARTIES_PARTY_ID".COUNT THEN =0A= "HZ_PARTIES_si" :=3D 0;=0A= EXIT;=0A= END IF;=0A= =0A= error_stmt :=3D SUBSTR('"EXPR_COMPANY_NAME"("HZ_PARTIES_i") = :=3D CASE WHEN UPPER("HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")) =3D = ''OTHER''=0A= THEN "HZ_PARTIES_ATTRIBUTE2"("HZ_PARTIES_i")=0A= WHEN UPPER("HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")) =3D ''PERSONAL = USE''=0A= THEN NULL=0A= ELSE "HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")=0A= END;',0,2000);=0A= "EXPR_COMPANY_NAME"("HZ_PARTIES_i") :=3D CASE WHEN = UPPER("HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")) =3D 'OTHER'=0A= THEN "HZ_PARTIES_ATTRIBUTE2"("HZ_PARTIES_i")=0A= WHEN UPPER("HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")) =3D 'PERSONAL = USE'=0A= THEN NULL=0A= ELSE "HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")=0A= END;=0A= =0A= error_stmt :=3D SUBSTR('"EXPR_COMPANY_FLAG"("HZ_PARTIES_i") = :=3D CASE WHEN UPPER("HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")) NOT IN = (''OTHER'', ''PERSONAL USE'')=0A= THEN ''Y''=0A= ELSE NULL=0A= END;',0,2000);=0A= "EXPR_COMPANY_FLAG"("HZ_PARTIES_i") :=3D CASE WHEN = UPPER("HZ_PARTIES_ATTRIBUTE1"("HZ_PARTIES_i")) NOT IN ('OTHER', = 'PERSONAL USE')=0A= THEN 'Y'=0A= ELSE NULL=0A= END;=0A= =0A= get_target_name :=3D '"C40_ADMIN_CUSTOMER"';=0A= get_audit_detail_id :=3D "C40_ADMIN_CUSTOMER_id";=0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= BEGIN =0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_CUSTOMER_ID"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_ID"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_CUSTOMER_ID"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PARTY_ID"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_CUSTOMER_ID"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_ID"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_ACCOUNT_CODE"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_HP_CONSTANT"; ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_ACCOUNT_CODE"',0,80);=0A= error_value :=3D = SUBSTR(C40_ADMIN_CUSTOMER_MAP."CONST_HP_CONSTANT",0,2000);=0A= "C40_ADMI_ACCOUNT_CODE"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_HP_CONSTANT";=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_CUSTOMER_NUMBER"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_NUMBER"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_CUSTOMER_NUMBER"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PARTY_NUMBER"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_CUSTOMER_NUMBER"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_NUMBER"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_FIRST_NAM"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_FIRST_NAME"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_FIRST_NAM"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_FIRST_NAME"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_FIRST_NAM"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PERSON_FIRST_NAME"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_LAST_NAME"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_LAST_NAME"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_LAST_NAME"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_LAST_NAME"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_LAST_NAME"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PERSON_LAST_NAME"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_MIDDLE_NAME"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_MIDDLE_NAM"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_MIDDLE_NAME"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_MIDDLE_NAM"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_MIDDLE_NAME"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_MIDDLE_NAM"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_PREFIX"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_TITLE"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_PREFIX"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_TITLE"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_PREFIX"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PERSON_TITLE"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS_1"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ADDRESS1"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS_1"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_ADDRESS1"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_ADDRESS_1"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_ADDRESS1"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS_2"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ADDRESS2"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS_2"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_ADDRESS2"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_ADDRESS_2"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_ADDRESS2"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_CITY"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_CITY"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_CITY"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_CITY"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_CITY"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_CITY"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_STATE"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_STATE"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_STATE"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_STATE"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMIN_CUSTOMER_STATE"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_STATE"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_POSTAL_CODE"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_POSTAL_CODE"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_POSTAL_CODE"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_POSTAL_CODE"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_POSTAL_CODE"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_POSTAL_CODE"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_COMPANY_NAME"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_NAME"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_COMPANY_NAME"',0,80);=0A= error_value :=3D = SUBSTR("EXPR_COMPANY_NAME"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_COMPANY_NAME"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_NAME"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_COMPANY_TITLE"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ATTRIBUTE3"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_COMPANY_TITLE"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_ATTRIBUTE3"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_COMPANY_TITLE"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ATTRIBUTE3"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_COMPANY_FLAG"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_FLAG"("HZ_PARTIES_i"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_COMPANY_FLAG"',0,80);=0A= error_value :=3D = SUBSTR("EXPR_COMPANY_FLAG"("HZ_PARTIES_i"),0,2000);=0A= "C40_ADMI_COMPANY_FLAG"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_FLAG"("HZ_PARTIES_i");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_IMPORT_DATE"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE"; ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_IMPORT_DATE"',0,80);=0A= error_value :=3D = SUBSTR(C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE",0,2000);=0A= "C40_ADMI_IMPORT_DATE"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE";=0A= =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR = get_audit_level =3D AUDIT_COMPLETE THEN =0A= "C40_ADMIN_CUSTOMER_srk"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_i";=0A= END IF;=0A= "C40_ADMIN_CUSTOMER_i" :=3D "C40_ADMIN_CUSTOMER_i" + = 1;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= "HZ_PARTIES_ER"(error_stmt, error_column, = error_value, SQLCODE, SQLERRM, get_audit_detail_id, "HZ_PARTIES_i");=0A= "C40_ADMIN_CUSTOMER_err" :=3D = "C40_ADMIN_CUSTOMER_err" + 1;=0A= IF get_errors + "C40_ADMIN_CUSTOMER_err" > = get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= END IF;=0A= =0A= END LOOP;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= -- Insert DML for "C40_ADMIN_CUSTOMER"=0A= normal_action :=3D 'INSERT';=0A= error_action :=3D 'INSERT';=0A= get_target_name :=3D '"C40_ADMIN_CUSTOMER"';=0A= get_audit_detail_id :=3D "C40_ADMIN_CUSTOMER_id";=0A= "C40_ADMIN_CUSTOMER_si" :=3D 1;=0A= LOOP =0A= EXIT WHEN "C40_ADMIN_CUSTOMER_i" <=3D get_bulk_size AND = "HZ_PARTIES_c"%FOUND AND NOT get_abort;=0A= =0A= get_rowid.DELETE;=0A= =0A= BEGIN =0A= FORALL i IN = "C40_ADMIN_CUSTOMER_si".."C40_ADMIN_CUSTOMER_i" - 1 =0A= INSERT =0A= /*+ APPEND PARALLEL("C39_CALL_CUSTOMER", DEFAULT, = DEFAULT) */=0A= INTO =0A= "C40_ADMIN_CUSTOMER"=0A= ("CUSTOMER_ID",=0A= "ACCOUNT_CODE",=0A= "CUSTOMER_NUMBER",=0A= "FIRST_NAME",=0A= "LAST_NAME",=0A= "MIDDLE_NAME",=0A= "PREFIX",=0A= "ADDRESS_1",=0A= "ADDRESS_2",=0A= "CITY",=0A= "STATE",=0A= "POSTAL_CODE",=0A= "COMPANY_NAME",=0A= "COMPANY_TITLE",=0A= "COMPANY_FLAG",=0A= "IMPORT_DATE")=0A= VALUES =0A= ("C40_ADMI_CUSTOMER_ID"(i),=0A= "C40_ADMI_ACCOUNT_CODE"(i),=0A= "C40_ADMI_CUSTOMER_NUMBER"(i),=0A= "C40_ADMIN_CUSTOMER_FIRST_NAM"(i),=0A= "C40_ADMIN_CUSTOMER_LAST_NAME"(i),=0A= "C40_ADMI_MIDDLE_NAME"(i),=0A= "C40_ADMIN_CUSTOMER_PREFIX"(i),=0A= "C40_ADMIN_CUSTOMER_ADDRESS_1"(i),=0A= "C40_ADMIN_CUSTOMER_ADDRESS_2"(i),=0A= "C40_ADMIN_CUSTOMER_CITY"(i),=0A= "C40_ADMIN_CUSTOMER_STATE"(i),=0A= "C40_ADMI_POSTAL_CODE"(i),=0A= "C40_ADMI_COMPANY_NAME"(i),=0A= "C40_ADMI_COMPANY_TITLE"(i),=0A= "C40_ADMI_COMPANY_FLAG"(i),=0A= "C40_ADMI_IMPORT_DATE"(i))=0A= RETURNING ROWID BULK COLLECT INTO get_rowid;=0A= =0A= error_index :=3D "C40_ADMIN_CUSTOMER_si" + = get_rowid.COUNT;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= error_index :=3D "C40_ADMIN_CUSTOMER_si" + = get_rowid.COUNT;=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR = get_audit_level =3D AUDIT_COMPLETE THEN =0A= "HZ_PARTIES_ER"(error_action, '*', NULL, SQLCODE, = SQLERRM, get_audit_detail_id, = "C40_ADMIN_CUSTOMER_srk"(error_index));=0A= END IF;=0A= "C40_ADMIN_CUSTOMER_err" :=3D = "C40_ADMIN_CUSTOMER_err" + 1;=0A= IF get_errors + "C40_ADMIN_CUSTOMER_err" > = get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= =0A= -- feedback for a bulk of rows=0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= get_rowkey_bulk.DELETE;=0A= rowkey_bulk_index :=3D 1;=0A= FOR rowkey_index IN = "C40_ADMIN_CUSTOMER_si"..error_index - 1 LOOP =0A= get_rowkey_bulk(rowkey_bulk_index) :=3D get_rowkey + = "C40_ADMIN_CUSTOMER_srk"(rowkey_index) - 1;=0A= rowkey_bulk_index :=3D rowkey_bulk_index + 1;=0A= END LOOP;=0A= END IF;=0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= WBPRTI.register_feedback_bulk( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= get_rowkey_bulk,=0A= 'NEW',=0A= get_target_name,=0A= 'T',=0A= normal_action,=0A= get_rowid=0A= );=0A= END IF;=0A= =0A= "C40_ADMIN_CUSTOMER_ins" :=3D "C40_ADMIN_CUSTOMER_ins" + = get_rowid.COUNT;=0A= "C40_ADMIN_CUSTOMER_si" :=3D error_index + 1;=0A= =0A= IF "C40_ADMIN_CUSTOMER_si" >=3D "C40_ADMIN_CUSTOMER_i" OR = get_abort THEN =0A= "C40_ADMIN_CUSTOMER_i" :=3D 1;=0A= EXIT;=0A= END IF;=0A= END LOOP;=0A= END IF;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= "HZ_PARTIES_ER"(error_stmt, '*', NULL, SQLCODE, SQLERRM, = NULL, "HZ_PARTIES_i");=0A= get_errors :=3D get_errors + 1;=0A= IF get_errors > get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= IF get_audit_level !=3D AUDIT_NONE THEN =0A= WBPRTI.auditd_progress( =0A= p_rtd=3D>"C40_ADMIN_CUSTOMER_id",=0A= p_sel=3D>get_map_selected,=0A= p_ins=3D>"C40_ADMIN_CUSTOMER_ins",=0A= p_upd=3D>"C40_ADMIN_CUSTOMER_upd",=0A= p_del=3D>"C40_ADMIN_CUSTOMER_del",=0A= p_err=3D>"C40_ADMIN_CUSTOMER_err",=0A= p_dis=3D>NULL=0A= );=0A= END IF;=0A= COMMIT;=0A= END IF;=0A= =0A= EXIT WHEN get_abort OR ("HZ_PARTIES_c"%NOTFOUND AND = "HZ_PARTIES_i" > "HZ_PARTIES_PARTY_ID".COUNT);=0A= END LOOP;=0A= END IF;=0A= COMMIT;=0A= BEGIN =0A= IF get_is_cursor_open THEN =0A= CLOSE "HZ_PARTIES_c";=0A= END IF;=0A= EXCEPTION WHEN OTHERS THEN =0A= NULL;=0A= END;=0A= =0A= IF NOT get_abort THEN =0A= -- resolve actual owner and name=0A= WBPRTIU.resolve_name(USER, 'C40_ADMIN_CUSTOMER', actual_owner, = actual_name);=0A= =0A= BEGIN =0A= IF actual_name IS NOT NULL THEN =0A= SELECT NVL(num_rows, 0) INTO get_map_num_rows=0A= FROM all_tables=0A= WHERE table_name =3D actual_name=0A= AND owner =3D actual_owner;=0A= IF ("C40_ADMIN_CUSTOMER_ins" + "C40_ADMIN_CUSTOMER_upd" + = "C40_ADMIN_CUSTOMER_del") >=3D get_map_num_rows THEN =0A= DBMS_STATS.gather_table_stats( =0A= ownname=3D>actual_owner,=0A= tabname=3D>actual_name,=0A= estimate_percent=3D>10,=0A= granularity=3D>'GLOBAL',=0A= degree=3D>null=0A= );=0A= END IF;=0A= END IF;=0A= EXCEPTION WHEN OTHERS THEN =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level = =3D AUDIT_COMPLETE THEN=0A= WBPRTI.error(=0A= get_runtime_audit_id,=0A= get_step_number,=0A= 0,=0A= 0,=0A= 'C40_ADMIN_CUSTOMER',=0A= '*',=0A= NULL,=0A= NULL,=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= END;=0A= END IF;=0A= END IF;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St"=0A= AND get_audit_level !=3D AUDIT_NONE THEN =0A= WBPRTI.auditd_end( =0A= p_rtd=3D>"C40_ADMIN_CUSTOMER_id",=0A= p_sel=3D>get_map_selected,=0A= p_ins=3D>"C40_ADMIN_CUSTOMER_ins",=0A= p_upd=3D>"C40_ADMIN_CUSTOMER_upd",=0A= p_del=3D>"C40_ADMIN_CUSTOMER_del",=0A= p_err=3D>"C40_ADMIN_CUSTOMER_err",=0A= p_dis=3D>NULL=0A= );=0A= END IF;=0A= =0A= get_inserted :=3D get_inserted + "C40_ADMIN_CUSTOMER_ins";=0A= get_updated :=3D get_updated + "C40_ADMIN_CUSTOMER_upd";=0A= get_deleted :=3D get_deleted + "C40_ADMIN_CUSTOMER_del";=0A= get_errors :=3D get_errors + "C40_ADMIN_CUSTOMER_err";=0A= get_selected :=3D get_selected + get_map_selected;=0A= COMMIT;=0A= END "HZ_PARTIES_p";=0A= =0A= =0A= -- Procedure "HZ_PARTIES_t" is the entry point for map = "HZ_PARTIES_t"=0A= =0A= PROCEDURE "HZ_PARTIES_t" IS=0A= -- Constants for this map=0A= get_map_name CONSTANT VARCHAR2(40) :=3D = '"HZ_PARTIES_t"';=0A= get_source_name CONSTANT VARCHAR2(80) :=3D = SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80);=0A= get_step_number CONSTANT NUMBER(22) :=3D 1;=0A= =0A= get_is_cursor_open BOOLEAN :=3D FALSE;=0A= get_map_selected NUMBER(22) :=3D 0;=0A= get_map_errors NUMBER(22) :=3D 0;=0A= get_map_num_rows NUMBER(22) :=3D 0;=0A= =0A= actual_owner VARCHAR2(30);=0A= actual_name VARCHAR2(30);=0A= =0A= -- Constraint management=0A= num_fk_err NUMBER(22);=0A= l_rowkey NUMBER(22) :=3D 0;=0A= l_table VARCHAR2(30) :=3D 'CREATE';=0A= l_rowid ROWID;=0A= l_owner VARCHAR2(30);=0A= l_tablename VARCHAR2(30);=0A= l_constraint VARCHAR2(30);=0A= sql_excp_stmt VARCHAR2(32767);=0A= TYPE exceptionsCurType IS REF CURSOR;=0A= exceptions_cursor exceptionsCurType;=0A= =0A= normal_action VARCHAR2(20);=0A= error_action VARCHAR2(20);=0A= get_audit_detail_id NUMBER(22) :=3D 0;=0A= get_target_name VARCHAR2(80);=0A= error_column VARCHAR2(80);=0A= error_value VARCHAR2(2000);=0A= =0A= =0A= -- Scalar variables for auditing=0A= "C40_ADMIN_CUSTOMER_id" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_ins" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_upd" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_del" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_err" NUMBER(22) :=3D 0;=0A= =0A= -- Variables for auditing in bulk processing=0A= one_rowkey NUMBER(22) :=3D 0;=0A= get_rowkey NUMBER(22) :=3D 0;=0A= get_rowkey_bulk WBPRTI.NUMBERLIST;=0A= one_rowid ROWID;=0A= get_rowid WBPRTI.ROWIDLIST;=0A= rowkey_bulk_index NUMBER(22) :=3D 0;=0A= x_it_err_count NUMBER(22) :=3D 0;=0A= =0A= "C40_ADMIN_CUSTOMER_srk" WBPRTI.NUMBERLIST;=0A= =0A= -- Bulk processing=0A= error_index NUMBER(22);=0A= update_bulk WBPRTI.NUMBERLIST;=0A= update_bulk_index NUMBER(22) :=3D 0;=0A= insert_bulk_index NUMBER(22) :=3D 0;=0A= last_successful_index NUMBER(22) :=3D 0;=0A= feedback_bulk_limit NUMBER(22) :=3D 0;=0A= =0A= "HZ_PARTIES_si$0" NUMBER(22) :=3D 0;=0A= "HZ_PARTIES_i$0" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_si" NUMBER(22) :=3D 0;=0A= "C40_ADMIN_CUSTOMER_i" NUMBER(22) :=3D 0;=0A= =0A= -- Cursor declaration=0A= CURSOR "HZ_PARTIES_c$0" IS=0A= SELECT=0A= /*+ NO_MERGE */=0A= "HZ_PARTIES_HP_CRM_LNK"."PARTY_ID" "PARTY_ID$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."PARTY_NUMBER" "PARTY_NUMBER$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_FIRST_NAME" = "PERSON_FIRST_NAME$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_LAST_NAME" "PERSON_LAST_NAME$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_MIDDLE_NAME" = "PERSON_MIDDLE_NAME$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."PERSON_TITLE" "PERSON_TITLE$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."ADDRESS1" "ADDRESS1$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."ADDRESS2" "ADDRESS2$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."CITY" "CITY$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."STATE" "STATE$1",=0A= "HZ_PARTIES_HP_CRM_LNK"."POSTAL_CODE" "POSTAL_CODE$1",=0A= CASE WHEN UPPER("HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1") =3D = 'OTHER'=0A= THEN "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE2"=0A= WHEN UPPER("HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1") =3D 'PERSONAL USE'=0A= THEN NULL=0A= ELSE "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1"=0A= END "COMPANY_NAME$0",=0A= "HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE3" "ATTRIBUTE3$1",=0A= CASE WHEN UPPER("HZ_PARTIES_HP_CRM_LNK"."ATTRIBUTE1") NOT IN = ('OTHER', 'PERSONAL USE')=0A= THEN 'Y'=0A= ELSE NULL=0A= END "COMPANY_FLAG$0"=0A= FROM=0A= "HZ_PARTIES"@"HP_CRM_LNK" "HZ_PARTIES_HP_CRM_LNK"; =0A= =0A= ------------------------------------------------------------------------= ---=0A= -- This procedure records column values of one erroneous source row=0A= -- into an audit trail table named WB_RT_ERROR_SOURCES. Each column = is=0A= -- recorded by one row in the audit trail. To collect all source = column=0A= -- values corresponding to one erroneous source row, query the audit=0A= -- trail and specify:=0A= -- RTA_IID, uniquely identifies one audited run,=0A= -- RTE_ROWKEY, uniquely identifies a source row within and audited = run=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_ES$0"(error_index IN NUMBER) IS=0A= BEGIN=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 1,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PARTY_ID',0,80),=0A= SUBSTR("HZ_PARTIES_PARTY_ID$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 2,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PARTY_NUMBER',0,80),=0A= SUBSTR("HZ_PARTIES_PARTY_NUMBER$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 3,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_FIRST_NAME',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_FIRST_NA$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 4,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_LAST_NAME',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_LAST_NAM$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 5,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_MIDDLE_NAME',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_MIDDLE_N$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 6,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_PERSON_TITLE',0,80),=0A= SUBSTR("HZ_PARTIES_PERSON_TITLE$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 7,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ADDRESS1',0,80),=0A= SUBSTR("HZ_PARTIES_ADDRESS1$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 8,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ADDRESS2',0,80),=0A= SUBSTR("HZ_PARTIES_ADDRESS2$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 9,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_CITY',0,80),=0A= SUBSTR("HZ_PARTIES_CITY$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 10,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_STATE',0,80),=0A= SUBSTR("HZ_PARTIES_STATE$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 11,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_POSTAL_CODE',0,80),=0A= SUBSTR("HZ_PARTIES_POSTAL_CODE$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 12,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('EXPR_COMPANY_NAME',0,80),=0A= SUBSTR("EXPR_COMPANY_NAME$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 13,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('HZ_PARTIES_ATTRIBUTE3',0,80),=0A= SUBSTR("HZ_PARTIES_ATTRIBUTE3$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= WBPRTI.error_source(=0A= get_runtime_audit_id,=0A= get_rowkey + error_index - 1,=0A= 14,=0A= 1,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('EXPR_COMPANY_FLAG',0,80),=0A= SUBSTR("EXPR_COMPANY_FLAG$0"(error_index),0,2000),=0A= get_step_number=0A= );=0A= RETURN;=0A= END "HZ_PARTIES_ES$0";=0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Procedure "HZ_PARTIES_ER$0" registers error for one errorneous = row=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_ER$0"(p_statement IN VARCHAR2, p_column IN = VARCHAR2, p_col_value IN VARCHAR2, p_sqlcode IN NUMBER, p_sqlerrm IN = VARCHAR2, p_auditd_id IN NUMBER, p_error_index IN NUMBER) IS=0A= l_source_target_name VARCHAR2(80);=0A= BEGIN =0A= IF p_auditd_id IS NULL THEN =0A= l_source_target_name :=3D get_source_name;=0A= ELSE =0A= l_source_target_name :=3D get_target_name;=0A= END IF;=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN =0A= WBPRTI.error( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= p_auditd_id,=0A= get_rowkey + p_error_index - 1,=0A= l_source_target_name,=0A= p_column,=0A= p_col_value,=0A= p_statement,=0A= p_sqlcode,=0A= p_sqlerrm,=0A= NULL=0A= );=0A= END IF;=0A= =0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= WBPRTI.register_feedback( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= get_rowkey + p_error_index - 1,=0A= 'ERROR',=0A= l_source_target_name,=0A= 'T',=0A= SUBSTR(p_statement,0,30)=0A= );=0A= END IF;=0A= =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN =0A= "HZ_PARTIES_ES$0"(p_error_index);=0A= END IF;=0A= END "HZ_PARTIES_ER$0";=0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Procedure "HZ_PARTIES_SU$0" opens and initializes data source=0A= -- for map "HZ_PARTIES_t"=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_SU$0" IS=0A= BEGIN=0A= IF get_abort THEN=0A= RETURN;=0A= END IF;=0A= OPEN "HZ_PARTIES_c$0";=0A= get_is_cursor_open :=3D TRUE;=0A= END "HZ_PARTIES_SU$0";=0A= =0A= ------------------------------------------------------------------------= ---=0A= -- Procedure "HZ_PARTIES_RD$0" fetches a bulk of rows from=0A= -- the data source for map "HZ_PARTIES_t"=0A= ------------------------------------------------------------------------= ---=0A= PROCEDURE "HZ_PARTIES_RD$0" IS=0A= BEGIN =0A= IF get_abort THEN =0A= RETURN;=0A= END IF;=0A= =0A= BEGIN =0A= "HZ_PARTIES_PARTY_ID$0".DELETE;=0A= "HZ_PARTIES_PARTY_NUMBER$0".DELETE;=0A= "HZ_PARTIES_PERSON_FIRST_NA$0".DELETE;=0A= "HZ_PARTIES_PERSON_LAST_NAM$0".DELETE;=0A= "HZ_PARTIES_PERSON_MIDDLE_N$0".DELETE;=0A= "HZ_PARTIES_PERSON_TITLE$0".DELETE;=0A= "HZ_PARTIES_ADDRESS1$0".DELETE;=0A= "HZ_PARTIES_ADDRESS2$0".DELETE;=0A= "HZ_PARTIES_CITY$0".DELETE;=0A= "HZ_PARTIES_STATE$0".DELETE;=0A= "HZ_PARTIES_POSTAL_CODE$0".DELETE;=0A= "EXPR_COMPANY_NAME$0".DELETE;=0A= "HZ_PARTIES_ATTRIBUTE3$0".DELETE;=0A= "EXPR_COMPANY_FLAG$0".DELETE;=0A= =0A= FETCH =0A= "HZ_PARTIES_c$0"=0A= BULK COLLECT INTO =0A= "HZ_PARTIES_PARTY_ID$0",=0A= "HZ_PARTIES_PARTY_NUMBER$0",=0A= "HZ_PARTIES_PERSON_FIRST_NA$0",=0A= "HZ_PARTIES_PERSON_LAST_NAM$0",=0A= "HZ_PARTIES_PERSON_MIDDLE_N$0",=0A= "HZ_PARTIES_PERSON_TITLE$0",=0A= "HZ_PARTIES_ADDRESS1$0",=0A= "HZ_PARTIES_ADDRESS2$0",=0A= "HZ_PARTIES_CITY$0",=0A= "HZ_PARTIES_STATE$0",=0A= "HZ_PARTIES_POSTAL_CODE$0",=0A= "EXPR_COMPANY_NAME$0",=0A= "HZ_PARTIES_ATTRIBUTE3$0",=0A= "EXPR_COMPANY_FLAG$0"=0A= LIMIT get_bulk_size;=0A= =0A= IF "HZ_PARTIES_c$0"%NOTFOUND AND "HZ_PARTIES_PARTY_ID$0".COUNT =3D = 0 THEN =0A= RETURN;=0A= END IF;=0A= =0A= -- register feedback for successful reads=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN =0A= get_rowkey :=3D = WBPRTI.get_rowkey("HZ_PARTIES_PARTY_ID$0".COUNT);=0A= END IF;=0A= =0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= WBPRTI.register_feedback_bulk( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= get_rowkey,=0A= 'NEW',=0A= get_source_name,=0A= 'S',=0A= 'SELECT',=0A= "HZ_PARTIES_PARTY_ID$0".COUNT=0A= );=0A= END IF;=0A= get_map_selected :=3D get_map_selected + = "HZ_PARTIES_PARTY_ID$0".COUNT;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= -- register error=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level =3D = AUDIT_COMPLETE THEN=0A= one_rowkey :=3D WBPRTI.get_rowkey;=0A= WBPRTI.error(=0A= get_runtime_audit_id,=0A= get_step_number,=0A= NULL,=0A= one_rowkey,=0A= get_source_name,=0A= '*',=0A= NULL,=0A= 'SELECT',=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= =0A= -- register feedback for the erroneous row=0A= IF get_audit_level =3D AUDIT_COMPLETE THEN=0A= WBPRTI.register_feedback(=0A= get_runtime_audit_id,=0A= get_step_number,=0A= one_rowkey,=0A= 'ERROR',=0A= get_source_name,=0A= 'S',=0A= 'SELECT'=0A= );=0A= END IF;=0A= get_errors :=3D get_errors + get_bulk_size - = "HZ_PARTIES_PARTY_ID$0".COUNT;=0A= IF get_errors > get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= END "HZ_PARTIES_RD$0";=0A= =0A= =0A= ------------------------------------------------------------------------= ---=0A= -- "HZ_PARTIES_t" main=0A= ------------------------------------------------------------------------= ---=0A= =0A= BEGIN =0A= IF get_abort THEN =0A= RETURN;=0A= END IF;=0A= =0A= sql_stmt :=3D 'ALTER SESSION DISABLE PARALLEL DML';=0A= EXECUTE IMMEDIATE sql_stmt;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= =0A= IF get_audit_level !=3D AUDIT_NONE THEN =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= "C40_ADMIN_CUSTOMER_id" :=3D =0A= = WBPRTI.auditd_begin(get_runtime_audit_id,get_step_number,get_map_name,=0A= get_source_name,'"C40_ADMIN_CUSTOMER"',NULL,NULL);=0A= END IF;=0A= COMMIT;=0A= END IF;=0A= =0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= -- Truncate the target table=0A= BEGIN =0A= EXECUTE IMMEDIATE 'TRUNCATE TABLE "C40_ADMIN_CUSTOMER"';=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level = =3D AUDIT_COMPLETE THEN =0A= WBPRTI.error( =0A= get_runtime_audit_id,=0A= 1,=0A= "C40_ADMIN_CUSTOMER_id",=0A= 0,=0A= '"C40_ADMIN_CUSTOMER"',=0A= '*',=0A= NULL,=0A= 'TRUNCATE',=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= get_abort :=3D TRUE;=0A= END;=0A= END IF;=0A= =0A= "HZ_PARTIES_si$0" :=3D 0;=0A= "C40_ADMIN_CUSTOMER_i" :=3D 1;=0A= =0A= IF NOT get_abort THEN =0A= "HZ_PARTIES_SU$0";=0A= =0A= LOOP =0A= IF "HZ_PARTIES_si$0" =3D 0 THEN =0A= "HZ_PARTIES_RD$0"; -- Fetch data from source=0A= END IF;=0A= =0A= -- Processing:=0A= "HZ_PARTIES_i$0" :=3D "HZ_PARTIES_si$0";=0A= BEGIN =0A= LOOP =0A= EXIT WHEN "C40_ADMIN_CUSTOMER_i" > get_bulk_size OR = get_abort;=0A= =0A= "HZ_PARTIES_i$0" :=3D "HZ_PARTIES_i$0" + 1;=0A= "HZ_PARTIES_si$0" :=3D "HZ_PARTIES_i$0";=0A= =0A= IF "HZ_PARTIES_i$0" > "HZ_PARTIES_PARTY_ID$0".COUNT THEN = =0A= "HZ_PARTIES_si$0" :=3D 0;=0A= EXIT;=0A= END IF;=0A= =0A= get_target_name :=3D '"C40_ADMIN_CUSTOMER"';=0A= get_audit_detail_id :=3D "C40_ADMIN_CUSTOMER_id";=0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= BEGIN =0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_CUSTOMER_ID$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_ID$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_CUSTOMER_ID$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PARTY_ID$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_CUSTOMER_ID$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_ID$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_ACCOUNT_CODE$0"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_HP_CONSTANT"; ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_ACCOUNT_CODE$0"',0,80);=0A= error_value :=3D SUBSTR(C40_ADMIN_CUSTOMER_MAP."CONST_HP= _CONSTANT",0,2000);=0A= "C40_ADMI_ACCOUNT_CODE$0"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_HP_CONSTANT";=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_CUSTOMER_NUMBER$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PARTY_NUMBER$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_CUSTOMER_NUMBER$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PARTY_NUMBER$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_CUSTOMER_NUMBER$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PARTY_NUMBER$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_FIRST_N$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_FIRST_NA$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_FIRST_N$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_FIRST_NA$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_FIRST_N$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PERSON_FIRST_NA$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_LAST_NA$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_LAST_NAM$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_LAST_NA$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_LAST_NAM$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_LAST_NA$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PERSON_LAST_NAM$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_MIDDLE_NAME$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_MIDDLE_N$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_MIDDLE_NAME$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_MIDDLE_N$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_MIDDLE_NAME$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_MIDDLE_N$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_PREFIX$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_PERSON_TITLE$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_PREFIX$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_PERSON_TITLE$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_PREFIX$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_PERSON_TITLE$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ADDRESS1$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_ADDRESS1$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_ADDRESS$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_ADDRESS1$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS$1"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ADDRESS2$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_ADDRESS$1"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_ADDRESS2$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_ADDRESS$1"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_ADDRESS2$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_CITY$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_CITY$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_CITY$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_CITY$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_CITY$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_CITY$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_STATE$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_STATE$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMIN_CUSTOMER_STATE$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_STATE$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMIN_CUSTOMER_STATE$0"("C40_ADMIN_CUSTOMER_i") = :=3D "HZ_PARTIES_STATE$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_POSTAL_CODE$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_POSTAL_CODE$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_POSTAL_CODE$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_POSTAL_CODE$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_POSTAL_CODE$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_POSTAL_CODE$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_COMPANY_NAME$0"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_NAME$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_COMPANY_NAME$0"',0,80);=0A= error_value :=3D = SUBSTR("EXPR_COMPANY_NAME$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_COMPANY_NAME$0"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_NAME$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_COMPANY_TITLE$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ATTRIBUTE3$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_COMPANY_TITLE$0"',0,80);=0A= error_value :=3D = SUBSTR("HZ_PARTIES_ATTRIBUTE3$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_COMPANY_TITLE$0"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_ATTRIBUTE3$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_COMPANY_FLAG$0"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_FLAG$0"("HZ_PARTIES_i$0"); ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_COMPANY_FLAG$0"',0,80);=0A= error_value :=3D = SUBSTR("EXPR_COMPANY_FLAG$0"("HZ_PARTIES_i$0"),0,2000);=0A= "C40_ADMI_COMPANY_FLAG$0"("C40_ADMIN_CUSTOMER_i") :=3D = "EXPR_COMPANY_FLAG$0"("HZ_PARTIES_i$0");=0A= =0A= error_stmt :=3D = SUBSTR('"C40_ADMI_IMPORT_DATE$0"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE"; ',0,2000);=0A= error_column :=3D = SUBSTR('"C40_ADMI_IMPORT_DATE$0"',0,80);=0A= error_value :=3D = SUBSTR(C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE",0,2000);=0A= "C40_ADMI_IMPORT_DATE$0"("C40_ADMIN_CUSTOMER_i") :=3D = C40_ADMIN_CUSTOMER_MAP."CONST_SYS_DATE";=0A= =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR = get_audit_level =3D AUDIT_COMPLETE THEN =0A= "C40_ADMIN_CUSTOMER_srk"("C40_ADMIN_CUSTOMER_i") :=3D = "HZ_PARTIES_i$0";=0A= END IF;=0A= "C40_ADMIN_CUSTOMER_i" :=3D "C40_ADMIN_CUSTOMER_i" + = 1;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= "HZ_PARTIES_ER$0"(error_stmt, error_column, = error_value, SQLCODE, SQLERRM, get_audit_detail_id, = "HZ_PARTIES_i$0");=0A= "C40_ADMIN_CUSTOMER_err" :=3D = "C40_ADMIN_CUSTOMER_err" + 1;=0A= IF get_errors + "C40_ADMIN_CUSTOMER_err" > = get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= END IF;=0A= =0A= END LOOP;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= -- Insert DML for "C40_ADMIN_CUSTOMER"=0A= normal_action :=3D 'INSERT';=0A= error_action :=3D 'INSERT';=0A= get_target_name :=3D '"C40_ADMIN_CUSTOMER"';=0A= get_audit_detail_id :=3D "C40_ADMIN_CUSTOMER_id";=0A= "C40_ADMIN_CUSTOMER_si" :=3D 1;=0A= LOOP =0A= EXIT WHEN "C40_ADMIN_CUSTOMER_i" <=3D get_bulk_size AND = "HZ_PARTIES_c$0"%FOUND AND NOT get_abort;=0A= =0A= get_rowid.DELETE;=0A= =0A= BEGIN =0A= FORALL i IN = "C40_ADMIN_CUSTOMER_si".."C40_ADMIN_CUSTOMER_i" - 1 =0A= INSERT =0A= /*+ APPEND PARALLEL("C39_CALL_CUSTOMER", DEFAULT, = DEFAULT) */=0A= INTO =0A= "C40_ADMIN_CUSTOMER"=0A= ("CUSTOMER_ID",=0A= "ACCOUNT_CODE",=0A= "CUSTOMER_NUMBER",=0A= "FIRST_NAME",=0A= "LAST_NAME",=0A= "MIDDLE_NAME",=0A= "PREFIX",=0A= "ADDRESS_1",=0A= "ADDRESS_2",=0A= "CITY",=0A= "STATE",=0A= "POSTAL_CODE",=0A= "COMPANY_NAME",=0A= "COMPANY_TITLE",=0A= "COMPANY_FLAG",=0A= "IMPORT_DATE")=0A= VALUES =0A= ("C40_ADMI_CUSTOMER_ID$0"(i),=0A= "C40_ADMI_ACCOUNT_CODE$0"(i),=0A= "C40_ADMI_CUSTOMER_NUMBER$0"(i),=0A= "C40_ADMIN_CUSTOMER_FIRST_N$0"(i),=0A= "C40_ADMIN_CUSTOMER_LAST_NA$0"(i),=0A= "C40_ADMI_MIDDLE_NAME$0"(i),=0A= "C40_ADMIN_CUSTOMER_PREFIX$0"(i),=0A= "C40_ADMIN_CUSTOMER_ADDRESS$0"(i),=0A= "C40_ADMIN_CUSTOMER_ADDRESS$1"(i),=0A= "C40_ADMIN_CUSTOMER_CITY$0"(i),=0A= "C40_ADMIN_CUSTOMER_STATE$0"(i),=0A= "C40_ADMI_POSTAL_CODE$0"(i),=0A= "C40_ADMI_COMPANY_NAME$0"(i),=0A= "C40_ADMI_COMPANY_TITLE$0"(i),=0A= "C40_ADMI_COMPANY_FLAG$0"(i),=0A= "C40_ADMI_IMPORT_DATE$0"(i))=0A= RETURNING ROWID BULK COLLECT INTO get_rowid;=0A= =0A= error_index :=3D "C40_ADMIN_CUSTOMER_si" + = get_rowid.COUNT;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= error_index :=3D "C40_ADMIN_CUSTOMER_si" + = get_rowid.COUNT;=0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR = get_audit_level =3D AUDIT_COMPLETE THEN =0A= "HZ_PARTIES_ER$0"(error_action, '*', NULL, SQLCODE, = SQLERRM, get_audit_detail_id, = "C40_ADMIN_CUSTOMER_srk"(error_index));=0A= END IF;=0A= "C40_ADMIN_CUSTOMER_err" :=3D = "C40_ADMIN_CUSTOMER_err" + 1;=0A= IF get_errors + "C40_ADMIN_CUSTOMER_err" > = get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= =0A= -- feedback for a bulk of rows=0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= get_rowkey_bulk.DELETE;=0A= rowkey_bulk_index :=3D 1;=0A= FOR rowkey_index IN = "C40_ADMIN_CUSTOMER_si"..error_index - 1 LOOP =0A= get_rowkey_bulk(rowkey_bulk_index) :=3D get_rowkey + = "C40_ADMIN_CUSTOMER_srk"(rowkey_index) - 1;=0A= rowkey_bulk_index :=3D rowkey_bulk_index + 1;=0A= END LOOP;=0A= END IF;=0A= IF get_audit_level =3D AUDIT_COMPLETE THEN =0A= WBPRTI.register_feedback_bulk( =0A= get_runtime_audit_id,=0A= get_step_number,=0A= get_rowkey_bulk,=0A= 'NEW',=0A= get_target_name,=0A= 'T',=0A= normal_action,=0A= get_rowid=0A= );=0A= END IF;=0A= =0A= "C40_ADMIN_CUSTOMER_ins" :=3D "C40_ADMIN_CUSTOMER_ins" + = get_rowid.COUNT;=0A= "C40_ADMIN_CUSTOMER_si" :=3D error_index + 1;=0A= =0A= IF "C40_ADMIN_CUSTOMER_si" >=3D "C40_ADMIN_CUSTOMER_i" OR = get_abort THEN =0A= "C40_ADMIN_CUSTOMER_i" :=3D 1;=0A= EXIT;=0A= END IF;=0A= END LOOP;=0A= END IF;=0A= EXCEPTION =0A= WHEN OTHERS THEN =0A= "HZ_PARTIES_ER$0"(error_stmt, '*', NULL, SQLCODE, SQLERRM, = NULL, "HZ_PARTIES_i$0");=0A= get_errors :=3D get_errors + 1;=0A= IF get_errors > get_max_errors THEN =0A= get_abort :=3D TRUE;=0A= END IF;=0A= END;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St" THEN =0A= IF get_audit_level !=3D AUDIT_NONE THEN =0A= WBPRTI.auditd_progress( =0A= p_rtd=3D>"C40_ADMIN_CUSTOMER_id",=0A= p_sel=3D>get_map_selected,=0A= p_ins=3D>"C40_ADMIN_CUSTOMER_ins",=0A= p_upd=3D>"C40_ADMIN_CUSTOMER_upd",=0A= p_del=3D>"C40_ADMIN_CUSTOMER_del",=0A= p_err=3D>"C40_ADMIN_CUSTOMER_err",=0A= p_dis=3D>NULL=0A= );=0A= END IF;=0A= COMMIT;=0A= END IF;=0A= =0A= EXIT WHEN get_abort OR ("HZ_PARTIES_c$0"%NOTFOUND AND = "HZ_PARTIES_i$0" > "HZ_PARTIES_PARTY_ID$0".COUNT);=0A= END LOOP;=0A= END IF;=0A= COMMIT;=0A= BEGIN =0A= IF get_is_cursor_open THEN =0A= CLOSE "HZ_PARTIES_c$0";=0A= END IF;=0A= EXCEPTION WHEN OTHERS THEN =0A= NULL;=0A= END;=0A= =0A= IF NOT get_abort THEN =0A= -- resolve actual owner and name=0A= WBPRTIU.resolve_name(USER, 'C40_ADMIN_CUSTOMER', actual_owner, = actual_name);=0A= =0A= BEGIN =0A= IF actual_name IS NOT NULL THEN =0A= SELECT NVL(num_rows, 0) INTO get_map_num_rows=0A= FROM all_tables=0A= WHERE table_name =3D actual_name=0A= AND owner =3D actual_owner;=0A= IF ("C40_ADMIN_CUSTOMER_ins" + "C40_ADMIN_CUSTOMER_upd" + = "C40_ADMIN_CUSTOMER_del") >=3D get_map_num_rows THEN =0A= DBMS_STATS.gather_table_stats( =0A= ownname=3D>actual_owner,=0A= tabname=3D>actual_name,=0A= estimate_percent=3D>10,=0A= granularity=3D>'GLOBAL',=0A= degree=3D>null=0A= );=0A= END IF;=0A= END IF;=0A= EXCEPTION WHEN OTHERS THEN =0A= IF get_audit_level =3D AUDIT_ERROR_DETAILS OR get_audit_level = =3D AUDIT_COMPLETE THEN=0A= WBPRTI.error(=0A= get_runtime_audit_id,=0A= get_step_number,=0A= 0,=0A= 0,=0A= 'C40_ADMIN_CUSTOMER',=0A= '*',=0A= NULL,=0A= NULL,=0A= SQLCODE,=0A= SQLERRM,=0A= NULL=0A= );=0A= END IF;=0A= END;=0A= END IF;=0A= END IF;=0A= =0A= IF NOT "C40_ADMIN_CUSTOMER_St"=0A= AND get_audit_level !=3D AUDIT_NONE THEN =0A= WBPRTI.auditd_end( =0A= p_rtd=3D>"C40_ADMIN_CUSTOMER_id",=0A= p_sel=3D>get_map_selected,=0A= p_ins=3D>"C40_ADMIN_CUSTOMER_ins",=0A= p_upd=3D>"C40_ADMIN_CUSTOMER_upd",=0A= p_del=3D>"C40_ADMIN_CUSTOMER_del",=0A= p_err=3D>"C40_ADMIN_CUSTOMER_err",=0A= p_dis=3D>NULL=0A= );=0A= END IF;=0A= =0A= get_inserted :=3D get_inserted + "C40_ADMIN_CUSTOMER_ins";=0A= get_updated :=3D get_updated + "C40_ADMIN_CUSTOMER_upd";=0A= get_deleted :=3D get_deleted + "C40_ADMIN_CUSTOMER_del";=0A= get_errors :=3D get_errors + "C40_ADMIN_CUSTOMER_err";=0A= get_selected :=3D get_selected + get_map_selected;=0A= COMMIT;=0A= END "HZ_PARTIES_t";=0A= =0A= =0A= PROCEDURE Initialize(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, = p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER = DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN = NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL') IS=0A= BEGIN=0A= get_selected :=3D 0;=0A= get_inserted :=3D 0;=0A= get_updated :=3D 0;=0A= get_deleted :=3D 0;=0A= get_merged :=3D 0;=0A= get_errors :=3D 0;=0A= get_abort :=3D FALSE;=0A= IF p_bulk_size IS NOT NULL THEN=0A= get_bulk_size :=3D p_bulk_size;=0A= END IF;=0A= IF p_audit_level IS NOT NULL THEN=0A= get_audit_level :=3D p_audit_level;=0A= END IF;=0A= IF p_max_errors IS NOT NULL THEN=0A= get_max_errors :=3D p_max_errors;=0A= END IF;=0A= IF p_commit_frequency IS NOT NULL THEN=0A= get_commit_frequency :=3D p_commit_frequency;=0A= END IF;=0A= IF p_operating_mode IS NOT NULL THEN=0A= get_operating_mode :=3D p_operating_mode;=0A= END IF;=0A= IF get_audit_level !=3D AUDIT_NONE THEN=0A= get_runtime_audit_id :=3D WBPRTI.audit_begin(=0A= p_job,=0A= get_model_id,=0A= get_model_name,=0A= get_purge_group,=0A= NULL,=0A= SUBSTR('"HZ_PARTIES"@"HP_CRM_LNK"',0,80),=0A= SUBSTR('"C40_ADMIN_CUSTOMER"',0,80),=0A= NULL,=0A= get_cycle_date=0A= );=0A= END IF;=0A= =0A= COMMIT;=0A= END Initialize;=0A= =0A= =0A= PROCEDURE Finalize(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, = p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER = DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN = NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL') IS=0A= BEGIN=0A= =0A= IF p_bulk_size IS NOT NULL THEN=0A= get_bulk_size :=3D p_bulk_size;=0A= END IF;=0A= IF p_audit_level IS NOT NULL THEN=0A= get_audit_level :=3D p_audit_level;=0A= END IF;=0A= IF p_max_errors IS NOT NULL THEN=0A= get_max_errors :=3D p_max_errors;=0A= END IF;=0A= IF p_commit_frequency IS NOT NULL THEN=0A= get_commit_frequency :=3D p_commit_frequency;=0A= END IF;=0A= IF get_audit_level !=3D AUDIT_NONE THEN=0A= IF NOT get_abort THEN=0A= WBPRTI.audit_end(=0A= get_runtime_audit_id,=0A= get_selected,=0A= get_inserted,=0A= get_updated,=0A= get_deleted,=0A= get_errors,=0A= NULL,=0A= get_merged=0A= );=0A= ELSE=0A= WBPRTI.audit_fail(=0A= p_rta=3D>get_runtime_audit_id,=0A= p_sel=3D>get_selected,=0A= p_ins=3D>get_inserted,=0A= p_upd=3D>get_updated,=0A= p_del=3D>get_deleted,=0A= p_err=3D>get_errors,=0A= p_dis=3D>NULL,=0A= p_mer=3D>get_merged=0A= );=0A= raise_application_error(-20102, 'Fatal error or maximum number of = errors exceeded');=0A= END IF;=0A= END IF;=0A= COMMIT;=0A= END Finalize;=0A= =0A= =0A= PROCEDURE Main(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, = p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER = DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN = NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL') IS=0A= get_batch_status BOOLEAN :=3D TRUE;=0A= BEGIN=0A= IF WBPRTIU.supportsDesignClient(p_designVersion=3D>'9.0.3.35.1', = p_minRuntimeVersion=3D>'9.0.3.0.1') < 1 THEN =0A= raise_application_error(-20103, 'Incompatible runtime and design = client versions.');=0A= END IF;=0A= Initialize(p_operating_mode, p_audit_level, p_max_errors, = p_commit_frequency, p_bulk_size, p_job);=0A= -- Initialize all batch status variables=0A= "C40_ADMIN_CUSTOMER_St" :=3D FALSE;=0A= =0A= -- Processing for different operating modes=0A= IF get_operating_mode =3D MODE_SET THEN =0A= "C40_ADMIN_CUSTOMER_St" :=3D "C40_ADMIN_CUSTOMER_Bat";=0A= END IF;=0A= IF get_operating_mode =3D MODE_ROW THEN =0A= "HZ_PARTIES_p";=0A= END IF;=0A= IF get_operating_mode =3D MODE_ROW_TARGET THEN =0A= "HZ_PARTIES_t";=0A= END IF;=0A= IF get_operating_mode =3D MODE_SET_FAILOVER_ROW THEN =0A= IF get_batch_status THEN =0A= "C40_ADMIN_CUSTOMER_St" :=3D "C40_ADMIN_CUSTOMER_Bat";=0A= get_batch_status :=3D get_batch_status AND = "C40_ADMIN_CUSTOMER_St";=0A= END IF;=0A= "HZ_PARTIES_p";=0A= END IF;=0A= IF get_operating_mode =3D MODE_SET_FAILOVER_ROW_TARGET THEN =0A= IF get_batch_status THEN =0A= "C40_ADMIN_CUSTOMER_St" :=3D "C40_ADMIN_CUSTOMER_Bat";=0A= get_batch_status :=3D get_batch_status AND = "C40_ADMIN_CUSTOMER_St";=0A= END IF;=0A= "HZ_PARTIES_t";=0A= END IF;=0A= =0A= Finalize(p_operating_mode, p_audit_level, p_max_errors, = p_commit_frequency, p_bulk_size, p_job);=0A= END Main;=0A= =0A= END C40_ADMIN_CUSTOMER_MAP;=0A= =0A= /=0A= ------_=_NextPart_000_01C2A222.1D3697A0-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chris Stephens INET: ChrisStephens@affina.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).