|
[x]
The Solution Rating System
|
|
| With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating. - The Grade of the Solution
- The Zone Rank of the Expert Providing the Solution
- The Number of Author and Expert Comments
- The Number of Experts Contributing
- The Feedback of the Community
Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site. If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support. Thank you! |
|
|
|
|
Asked by uTab in Oracle 3rd Party Tools
I have two tables
dbcontacts
Name Null? Type
----------------------------------------- -------- ----------------
CONTACTID NOT NULL NUMBER
TAXID NOT NULL VARCHAR2(11)
INACTIVEFLAG NOT NULL CHAR(1)
PENDINGFLAG NOT NULL CHAR(1)
COMPANYNAME VARCHAR2(50)
EMAILADDRESS VARCHAR2(50)
WEBPAGE VARCHAR2(50)
DENOMPREFID NUMBER
INTERESTTYPEPREF CHAR(1)
CITIZEN CHAR(1)
ISSUEPREFID NUMBER
LASTINQUIRY NUMBER
DATELASTINQ DATE
AVAILTIMESTART DATE
AVAILTIMEEND DATE
PERMGRANTED CHAR(1)
DONTCALL CHAR(1)
COMMENTFIELD VARCHAR2(2000)
and dbcontactperson
Name Null? Type
----------------------------------------- -------- --------------
CONTACTID NOT NULL NUMBER
CONTACTTYPE NOT NULL CHAR(1)
TAXID NOT NULL VARCHAR2(11)
PREFIX VARCHAR2(5)
FIRSTNAME NOT NULL VARCHAR2(20)
MIDDLENAME VARCHAR2(20)
LASTNAME NOT NULL VARCHAR2(20)
SUFFIX VARCHAR2(20)
ADDRESS1 NOT NULL VARCHAR2(50)
ADDRESS2 VARCHAR2(50)
CITY NOT NULL VARCHAR2(30)
STATE NOT NULL CHAR(2)
ZIPCODE NOT NULL VARCHAR2(10)
WORKPHONE VARCHAR2(14)
HOMEPHONE VARCHAR2(14)
FAXNUMBER VARCHAR2(14)
BIRTHDATE DATE
MARITALSTATUS CHAR(1)
DRIVERLICENSENO VARCHAR2(20)
DRIVERSTATE CHAR(2)
DENOMID NUMBER
a contacttype can be a primary contactperson, a secondary contact person or tertiary contactperson.
The datasource pkg is as follows but does not work. Can someone help me make this pkg work?
CREATE OR REPLACE PACKAGE BODY CalBaptist_DataSource AS
--------------------------- REF CURSOR to populate form ------------------------------
PROCEDURE CONTACTS_QUERY_REFCUR (P_Block_Data IN OUT CALBAPTIST_DATASOURCE.contacts_cur,
P_Prospects IN NUMBER,
P_Marketing IN NUMBER,
P_Trust IN NUMBER,
P_Retail IN NUMBER,
P_Origination IN NUMBER,
P_Vendor IN NUMBER,
P_Church IN NUMBER,
P_Pastor IN NUMBER,
P_ContactOnly IN NUMBER,
P_Employee IN NUMBER,
P_CapitalGifts IN NUMBER,
P_ChurchLoans IN NUMBER,
P_GroupFilter IN NUMBER)
IS
BEGIN
OPEN P_Block_Data FOR
Select C.CONTACTID,
C.INACTIVEFLAG,
C.TAXID,
C.COMPANYNAME,
C.EMAILADDRESS,
C.WEBPAGE,
C.DENOMPREFID,
C.INTERESTTYPEPREF,
C.CITIZEN,
C.ISSUEPREFID,
C.LASTINQUIRY,
C.DATELASTINQ,
C.COMMENTFIELD,
C.AVAILTIMESTART,
C.AVAILTIMEEND,
C.PERMGRANTED,
C.DONTCALL,
C.PENDINGFLAG,
P.CONTACTID "PCONTACTID",
P.CONTACTTYPE "PCONTACTTYPE",
P.TAXID "PTAXID",
P.PREFIX "PPREFIX",
P.FIRSTNAME "PFIRSTNAME",
P.MIDDLENAME "PMIDDLENAME",
P.LASTNAME "PLASTNAME",
P.SUFFIX "PSUFFIX",
P.ADDRESS1 "PADDRESS1",
P.ADDRESS2 "PADDRESS2",
P.CITY "PCITY",
P.STATE "PSTATE",
P.ZIPCODE "PZIPCODE",
P.WORKPHONE "PWORKPHONE",
P.HOMEPHONE "PHOMEPHONE",
P.FAXNUMBER "PFAXNUMBER",
P.BIRTHDATE "PBIRTHDATE",
P.MARITALSTATUS "PMARITALSTATUS",
P.DRIVERLICENSENO "PDRIVERLICENSENO",
P.DRIVERSTATE "PDRIVERSTATE",
P.DENOMID "PDENOMID",
S.CONTACTID "SCONTACTID",
S.CONTACTTYPE "SCONTACTTYPE",
S.TAXID "STAXID",
S.PREFIX "SPREFIX",
S.FIRSTNAME "SFIRSTNAME",
S.MIDDLENAME "SMIDDLENAME",
S.LASTNAME "SLASTNAME",
S.SUFFIX "SSUFFIX",
S.ADDRESS1 "SADDRESS1",
S.ADDRESS2 "SADDRESS2",
S.CITY "SCITY",
S.STATE "SSTATE",
S.ZIPCODE "SZIPCODE",
S.WORKPHONE "SWORKPHONE",
S.HOMEPHONE "SHOMEPHONE",
S.FAXNUMBER "SFAXNUMBER",
S.BIRTHDATE "SBIRTHDATE",
S.MARITALSTATUS "SMARITALSTATUS",
S.DRIVERLICENSENO "SDRIVERLICENSENO",
S.DRIVERSTATE "SDRIVERSTATE",
S.DENOMID "SDENOMID",
T.CONTACTID "TCONTACTID",
T.CONTACTTYPE "TCONTACTTYPE",
T.TAXID "TTAXID",
T.PREFIX "TPREFIX",
T.FIRSTNAME "TFIRSTNAME",
T.MIDDLENAME "TMIDDLENAME",
T.LASTNAME "TLASTNAME",
T.SUFFIX "TSUFFIX",
T.ADDRESS1 "TADDRESS1",
T.ADDRESS2 "TADDRESS2",
T.CITY "TCITY",
T.STATE "TSTATE",
T.ZIPCODE "TZIPCODE",
T.WORKPHONE "TWORKPHONE",
T.HOMEPHONE "THOMEPHONE",
T.FAXNUMBER "TFAXNUMBER",
T.BIRTHDATE "TBIRTHDATE",
T.MARITALSTATUS "TMARITALSTATUS",
T.DRIVERLICENSENO "TDRIVERLICENSENO",
T.DRIVERSTATE "TDRIVERSTATE",
T.DENOMID "TDENOMID"
FROM
GWTSDEV.dbContacts C,
GWTSDEV.dbContactPerson P,
GWTSDEV.dbContactPerson S,
GWTSDEV.dbContactPerson T
WHERE (C.ContactID = P.ContactID(+) AND P.ContactType(+) = 'P')
AND (C.ContactID = S.ContactID(+) AND S.ContactType(+) = 'S')
AND (C.ContactID = T.ContactID(+) AND T.ContactType(+) = 'T')
AND C.ContactID in ( select distinct dbConcats.ContactID
from dbConcats
where dbConcats.CategoryID in (P_Prospects,
P_Marketing,
P_Trust,
P_Retail,
P_Origination,
P_Vendor,
P_Church,
P_Pastor,
P_ContactOnly,
P_Employee,
P_CapitalGifts,
P_ChurchLoans,
P_GroupFilter ));
END Contacts_Query_RefCur;
----------------------- DML and Locking Procedures ---------------------------------
----------------------- Using PL/SQL Tables ---------------------------------
----------------------- Work Around for FRM-10766 ---------------------------------
PROCEDURE dbContacts_Query(P_Tab_Data IN OUT Contacts_Tab, P_ContactID IN NUMBER) IS
i NUMBER;
CURSOR dbContacts_Cur IS
SELECT
C.CONTACTID,
C.INACTIVEFLAG,
C.TAXID,
C.COMPANYNAME,
C.EMAILADDRESS,
C.WEBPAGE,
C.DENOMPREFID,
C.INTERESTTYPEPREF,
C.CITIZEN,
C.ISSUEPREFID,
C.LASTINQUIRY,
C.DATELASTINQ,
C.COMMENTFIELD,
C.AVAILTIMESTART,
C.AVAILTIMEEND,
C.PERMGRANTED,
C.DONTCALL,
C.PENDINGFLAG,
P.CONTACTID "PCONTACTID",
P.CONTACTTYPE "PCONTACTTYPE",
P.TAXID "PTAXID",
P.PREFIX "PPREFIX",
P.FIRSTNAME "PFIRSTNAME",
P.MIDDLENAME "PMIDDLENAME",
P.LASTNAME "PLASTNAME",
P.SUFFIX "PSUFFIX",
P.ADDRESS1 "PADDRESS1",
P.ADDRESS2 "PADDRESS2",
P.CITY "PCITY",
P.STATE "PSTATE",
P.ZIPCODE "PZIPCODE",
P.WORKPHONE "PWORKPHONE",
P.HOMEPHONE "PHOMEPHONE",
P.FAXNUMBER "PFAXNUMBER",
P.BIRTHDATE "PBIRTHDATE",
P.MARITALSTATUS "PMARITALSTATUS",
P.DRIVERLICENSENO "PDRIVERLICENSENO",
P.DRIVERSTATE "PDRIVERSTATE",
P.DENOMID "PDENOMID",
S.CONTACTID "SCONTACTID",
S.CONTACTTYPE "SCONTACTTYPE",
S.TAXID "STAXID",
S.PREFIX "SPREFIX",
S.FIRSTNAME "SFIRSTNAME",
S.MIDDLENAME "SMIDDLENAME",
S.LASTNAME "SLASTNAME",
S.SUFFIX "SSUFFIX",
S.ADDRESS1 "SADDRESS1",
S.ADDRESS2 "SADDRESS2",
S.CITY "SCITY",
S.STATE "SSTATE",
S.ZIPCODE "SZIPCODE",
S.WORKPHONE "SWORKPHONE",
S.HOMEPHONE "SHOMEPHONE",
S.FAXNUMBER "SFAXNUMBER",
S.BIRTHDATE "SBIRTHDATE",
S.MARITALSTATUS "SMARITALSTATUS",
S.DRIVERLICENSENO "SDRIVERLICENSENO",
S.DRIVERSTATE "SDRIVERSTATE",
S.DENOMID "SDENOMID",
T.CONTACTID "TCONTACTID",
T.CONTACTTYPE "TCONTACTTYPE",
T.TAXID "TTAXID",
T.PREFIX "TPREFIX",
T.FIRSTNAME "TFIRSTNAME",
T.MIDDLENAME "TMIDDLENAME",
T.LASTNAME "TLASTNAME",
T.SUFFIX "TSUFFIX",
T.ADDRESS1 "TADDRESS1",
T.ADDRESS2 "TADDRESS2",
T.CITY "TCITY",
T.STATE "TSTATE",
T.ZIPCODE "TZIPCODE",
T.WORKPHONE "TWORKPHONE",
T.HOMEPHONE "THOMEPHONE",
T.FAXNUMBER "TFAXNUMBER",
T.BIRTHDATE "TBIRTHDATE",
T.MARITALSTATUS "TMARITALSTATUS",
T.DRIVERLICENSENO "TDRIVERLICENSENO",
T.DRIVERSTATE "TDRIVERSTATE",
T.DENOMID "TDENOMID"
FROM
GWTSDEV.dbContacts C,
GWTSDEV.dbContactPerson P,
GWTSDEV.dbContactPerson S,
GWTSDEV.dbContactPerson T
WHERE (C.ContactID = P.ContactID(+) AND P.ContactType(+) = 'P')
AND (C.ContactID = S.ContactID(+) AND S.ContactType(+) = 'S')
AND (C.ContactID = T.ContactID(+) AND T.ContactType(+) = 'T')
ORDER BY P.LastName;
BEGIN
OPEN dbContacts_Cur;
i := 1;
LOOP
FETCH dbContacts_Cur
INTO
P_Tab_Data(i).CONTACTID,
P_Tab_Data(i).TAXID,
P_Tab_Data(i).INACTIVEFLAG,
P_Tab_Data(i).PENDINGFLAG,
P_Tab_Data(i).COMPANYNAME,
P_Tab_Data(i).EMAILADDRESS,
P_Tab_Data(i).WEBPAGE,
P_Tab_Data(i).DENOMPREFID,
P_Tab_Data(i).INTERESTTYPEPREF,
P_Tab_Data(i).CITIZEN,
P_Tab_Data(i).ISSUEPREFID,
P_Tab_Data(i).LASTINQUIRY,
P_Tab_Data(i).DATELASTINQ,
-- P_Tab_Data(i).PRIMARYREPID,
P_Tab_Data(i).AVAILTIMESTART,
P_Tab_Data(i).AVAILTIMEEND,
P_Tab_Data(i).PERMGRANTED,
P_Tab_Data(i).DONTCALL,
P_Tab_Data(i).COMMENTFIELD,
P_Tab_Data(i).P_CONTACTID,
P_Tab_Data(i).P_CONTACTTYPE,
P_Tab_Data(i).P_TAXID,
P_Tab_Data(i).P_PREFIX,
P_Tab_Data(i).P_FIRSTNAME,
P_Tab_Data(i).P_MIDDLENAME,
P_Tab_Data(i).P_LASTNAME,
P_Tab_Data(i).P_SUFFIX,
P_Tab_Data(i).P_ADDRESS1,
P_Tab_Data(i).P_ADDRESS2,
P_Tab_Data(i).P_CITY,
P_Tab_Data(i).P_STATE,
P_Tab_Data(i).P_ZIPCODE,
P_Tab_Data(i).P_WORKPHONE,
P_Tab_Data(i).P_HOMEPHONE,
P_Tab_Data(i).P_FAXNUMBER,
P_Tab_Data(i).P_BIRTHDATE,
P_Tab_Data(i).P_MARITALSTATUS,
P_Tab_Data(i).P_DRIVERLICENSENO,
P_Tab_Data(i).P_DRIVERSTATE,
P_Tab_Data(i).P_DENOMID,
P_Tab_Data(i).S_CONTACTID,
P_Tab_Data(i).S_CONTACTTYPE,
P_Tab_Data(i).S_TAXID,
P_Tab_Data(i).S_PREFIX,
P_Tab_Data(i).S_FIRSTNAME,
P_Tab_Data(i).S_MIDDLENAME,
P_Tab_Data(i).S_LASTNAME,
P_Tab_Data(i).S_SUFFIX,
P_Tab_Data(i).S_ADDRESS1,
P_Tab_Data(i).S_ADDRESS2,
P_Tab_Data(i).S_CITY,
P_Tab_Data(i).S_STATE,
P_Tab_Data(i).S_ZIPCODE,
P_Tab_Data(i).S_WORKPHONE,
P_Tab_Data(i).S_HOMEPHONE,
P_Tab_Data(i).S_FAXNUMBER,
P_Tab_Data(i).S_BIRTHDATE,
P_Tab_Data(i).S_MARITALSTATUS,
P_Tab_Data(i).S_DRIVERLICENSENO,
P_Tab_Data(i).S_DRIVERSTATE,
P_Tab_Data(i).S_DENOMID,
P_Tab_Data(i).T_CONTACTID,
P_Tab_Data(i).T_CONTACTTYPE,
P_Tab_Data(i).T_TAXID,
P_Tab_Data(i).T_PREFIX,
P_Tab_Data(i).T_FIRSTNAME,
P_Tab_Data(i).T_MIDDLENAME,
P_Tab_Data(i).T_LASTNAME,
P_Tab_Data(i).T_SUFFIX,
P_Tab_Data(i).T_ADDRESS1,
P_Tab_Data(i).T_ADDRESS2,
P_Tab_Data(i).T_CITY,
P_Tab_Data(i).T_STATE,
P_Tab_Data(i).T_ZIPCODE,
P_Tab_Data(i).T_WORKPHONE,
P_Tab_Data(i).T_HOMEPHONE,
P_Tab_Data(i).T_FAXNUMBER,
P_Tab_Data(i).T_BIRTHDATE,
P_Tab_Data(i).T_MARITALSTATUS,
P_Tab_Data(i).T_DRIVERLICENSENO,
P_Tab_Data(i).T_DRIVERSTATE,
P_Tab_Data(i).T_DENOMID;
EXIT WHEN dbContacts_Cur%notfound;
i := i + 1;
END LOOP;
CLOSE dbContacts_Cur;
END dbContacts_Query;
----------------------------- dbCONTACTS_INSERT_TAB --------------------------------
PROCEDURE dbContacts_Insert_Tab(P_Tab_Data IN Contacts_tab)
IS
cnt NUMBER;
BEGIN
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
INSERT INTO dbcontacts
(CONTACTID,
TAXID,
COMPANYNAME,
EMAILADDRESS,
WEBPAGE,
DENOMPREFID,
INTERESTTYPEPREF,
CITIZEN,
ISSUEPREFID,
LASTINQUIRY,
DATELASTINQ,
PRIMARYREPID,
COMMENTFIELD,
AVAILTIMESTART,
AVAILTIMEEND)
values
(P_Tab_Data(i).ContactID,
P_Tab_Data(i).Taxid,
P_Tab_Data(i).CompanyName,
P_Tab_Data(i).EmailAddress,
P_Tab_Data(i).WebPage,
P_Tab_Data(i).DenomPrefID,
P_Tab_Data(i).InteresttypePref,
P_Tab_Data(i).Citizen,
P_Tab_Data(i).IssuePrefID,
P_Tab_Data(i).LastInquiry,
P_Tab_Data(i).DateLastInq,
P_Tab_Data(i).PrimaryRepID,
P_Tab_Data(i).CommentField,
P_Tab_Data(i).AvailTimeStart,
P_Tab_Data(i).AvailTimeEnd)
END LOOP;
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
INSERT INTO DBCONTACTPERSON
(CONTACTID,
CONTACTTYPE,
TAXID,
PREFIX,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
SUFFIX,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIPCODE,
WORKPHONE,
HOMEPHONE,
FAXNUMBER,
BIRTHDATE,
MARITALSTATUS,
DRIVERLICENSENO,
DRIVERSTATE,
DENOMID)
VALUES
(P_Tab_Data(i).P_CONTACTID,
P_Tab_Data(i).P_CONTACTTYPE,
P_Tab_Data(i).P_TAXID,
P_Tab_Data(i).P_PREFIX,
P_Tab_Data(i).P_FIRSTNAME,
P_Tab_Data(i).P_MIDDLENAME,
P_Tab_Data(i).P_LASTNAME,
P_Tab_Data(i).P_SUFFIX,
P_Tab_Data(i).P_ADDRESS1,
P_Tab_Data(i).P_ADDRESS2,
P_Tab_Data(i).P_CITY,
P_Tab_Data(i).P_STATE,
P_Tab_Data(i).P_ZIPCODE,
P_Tab_Data(i).P_WORKPHONE,
P_Tab_Data(i).P_HOMEPHONE,
P_Tab_Data(i).P_FAXNUMBER,
P_Tab_Data(i).P_BIRTHDATE,
P_Tab_Data(i).P_MARITALSTATUS,
P_Tab_Data(i).P_DRIVERLICENSENO,
P_Tab_Data(i).P_DRIVERSTATE,
P_Tab_Data(i).P_DENOMID)
END LOOP;
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
INSERT INTO DBCONTACTPERSON
(CONTACTID,
CONTACTTYPE,
TAXID,
PREFIX,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
SUFFIX,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIPCODE,
WORKPHONE,
HOMEPHONE,
FAXNUMBER,
BIRTHDATE,
MARITALSTATUS,
DRIVERLICENSENO,
DRIVERSTATE,
DENOMID)
VALUES
(P_Tab_Data(i).S_CONTACTID,
P_Tab_Data(i).S_CONTACTTYPE,
P_Tab_Data(i).S_TAXID,
P_Tab_Data(i).S_PREFIX,
P_Tab_Data(i).S_FIRSTNAME,
P_Tab_Data(i).S_MIDDLENAME,
P_Tab_Data(i).S_LASTNAME,
P_Tab_Data(i).S_SUFFIX,
P_Tab_Data(i).S_ADDRESS1,
P_Tab_Data(i).S_ADDRESS2,
P_Tab_Data(i).S_CITY,
P_Tab_Data(i).S_STATE,
P_Tab_Data(i).S_ZIPCODE,
P_Tab_Data(i).S_WORKPHONE,
P_Tab_Data(i).S_HOMEPHONE,
P_Tab_Data(i).S_FAXNUMBER,
P_Tab_Data(i).S_BIRTHDATE,
P_Tab_Data(i).S_MARITALSTATUS,
P_Tab_Data(i).S_DRIVERLICENSENO,
P_Tab_Data(i).S_DRIVERSTATE,
P_Tab_Data(i).S_DENOMID)
END LOOP;
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
INSERT INTO DBCONTACTPERSON
(CONTACTID,
CONTACTTYPE,
TAXID,
PREFIX,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
SUFFIX,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIPCODE,
WORKPHONE,
HOMEPHONE,
FAXNUMBER,
BIRTHDATE,
MARITALSTATUS,
DRIVERLICENSENO,
DRIVERSTATE,
DENOMID)
VALUES
(P_Tab_Data(i).T_CONTACTID,
P_Tab_Data(i).T_CONTACTTYPE,
P_Tab_Data(i).T_TAXID,
P_Tab_Data(i).T_PREFIX,
P_Tab_Data(i).T_FIRSTNAME,
P_Tab_Data(i).T_MIDDLENAME,
P_Tab_Data(i).T_LASTNAME,
P_Tab_Data(i).T_SUFFIX,
P_Tab_Data(i).T_ADDRESS1,
P_Tab_Data(i).T_ADDRESS2,
P_Tab_Data(i).T_CITY,
P_Tab_Data(i).T_STATE,
P_Tab_Data(i).T_ZIPCODE,
P_Tab_Data(i).T_WORKPHONE,
P_Tab_Data(i).T_HOMEPHONE,
P_Tab_Data(i).T_FAXNUMBER,
P_Tab_Data(i).T_BIRTHDATE,
P_Tab_Data(i).T_MARITALSTATUS,
P_Tab_Data(i).T_DRIVERLICENSENO,
P_Tab_Data(i).T_DRIVERSTATE,
P_Tab_Data(i).T_DENOMID)
END LOOP;
END dbContacts_Insert_Tab;
---------------------------dbCONTACTS_UPDATE_TAB ------------------------------------
PROCEDURE dbContacts_Update_Tab(P_Tab_Data IN Contacts_tab) IS
i NUMBER;
cnt NUMBER;
BEGIN
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
UPDATE dbContacts
SET
CONTACTID = P_Tab_Data(i).ContactID,
TAXID = P_Tab_Data(i).Taxid,
COMPANYNAME = P_Tab_Data(i).CompanyName,
EMAILADDRESS = P_Tab_Data(i).EmailAddress,
WEBPAGE = P_Tab_Data(i).WebPage,
DENOMPREFID = P_Tab_Data(i).DenomPrefID,
INTERESTTYPEPREF = P_Tab_Data(i).InteresttypePref,
CITIZEN = P_Tab_Data(i).Citizen,
ISSUEPREFID = P_Tab_Data(i).IssuePrefID,
LASTINQUIRY = P_Tab_Data(i).LastInquiry,
DATELASTINQ = P_Tab_Data(i).DateLastInq,
PRIMARYREPID = P_Tab_Data(i).PrimaryRepID,
COMMENTFIELD = P_Tab_Data(i).CommentField,
AVAILTIMESTART = P_Tab_Data(i).AvailTimeStart,
AVAILTIMEEND = P_Tab_Data(i).AvailTimeEnd
WHERE C.ContactID = P_Tab_Data(i).ContactID;
END LOOP;
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
UPDATE dbContactperson
SET
CONTACTID = P_Tab_Data(i).P_CONTACTID,
CONTACTTYPE = P_Tab_Data(i).P_CONTACTTYPE,
TAXID = P_Tab_Data(i).P_TAXID,
PREFIX = P_Tab_Data(i).P_PREFIX,
FIRSTNAME = P_Tab_Data(i).P_FIRSTNAME,
MIDDLENAME = P_Tab_Data(i).P_MIDDLENAME,
LASTNAME = P_Tab_Data(i).P_LASTNAME,
SUFFIX = P_Tab_Data(i).P_SUFFIX,
ADDRESS1 = P_Tab_Data(i).P_ADDRESS1,
ADDRESS2 = P_Tab_Data(i).P_ADDRESS2,
CITY = P_Tab_Data(i).P_CITY,
STATE = P_Tab_Data(i).P_STATE,
ZIPCODE = P_Tab_Data(i).P_ZIPCODE,
WORKPHONE = P_Tab_Data(i).P_WORKPHONE,
HOMEPHONE = P_Tab_Data(i).P_HOMEPHONE,
FAXNUMBER = P_Tab_Data(i).P_FAXNUMBER,
BIRTHDATE = P_Tab_Data(i).P_BIRTHDATE,
MARITALSTATUS = P_Tab_Data(i).P_MARITALSTATUS,
DRIVERLICENSENO = P_Tab_Data(i).P_DRIVERLICENSENO,
DRIVERSTATE = P_Tab_Data(i).P_DRIVERSTATE,
DENOMID = P_Tab_Data(i).P_DENOMID
WHERE P.ContactID = P_Tab_Data(i).ContactID;
END LOOP;
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
UPDATE dbContactperson
SET
CONTACTID = P_Tab_Data(i).S_CONTACTID,
CONTACTTYPE = P_Tab_Data(i).S_CONTACTTYPE,
TAXID = P_Tab_Data(i).S_TAXID,
PREFIX = P_Tab_Data(i).S_PREFIX,
FIRSTNAME = P_Tab_Data(i).S_FIRSTNAME,
MIDDLENAME = P_Tab_Data(i).S_MIDDLENAME,
LASTNAME = P_Tab_Data(i).S_LASTNAME,
SUFFIX = P_Tab_Data(i).S_SUFFIX,
ADDRESS1 = P_Tab_Data(i).S_ADDRESS1,
ADDRESS2 = P_Tab_Data(i).S_ADDRESS2,
CITY = P_Tab_Data(i).S_CITY,
STATE = P_Tab_Data(i).S_STATE,
ZIPCODE = P_Tab_Data(i).S_ZIPCODE,
WORKPHONE = P_Tab_Data(i).S_WORKPHONE,
HOMEPHONE = P_Tab_Data(i).S_HOMEPHONE,
FAXNUMBER = P_Tab_Data(i).S_FAXNUMBER,
BIRTHDATE = P_Tab_Data(i).S_BIRTHDATE,
MARITALSTATUS = P_Tab_Data(i).S_MARITALSTATUS,
DRIVERLICENSENO = P_Tab_Data(i).S_DRIVERLICENSENO,
DRIVERSTATE = P_Tab_Data(i).S_DRIVERSTATE,
DENOMID = P_Tab_Data(i).S_DENOMID
WHERE S.ContactID = P_Tab_Data(i).ContactID;
END LOOP;
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
UPDATE dbContactperson
SET
CONTACTID = P_Tab_Data(i).T_CONTACTID,
CONTACTTYPE = P_Tab_Data(i).T_CONTACTTYPE,
TAXID = P_Tab_Data(i).T_TAXID,
PREFIX = P_Tab_Data(i).T_PREFIX,
FIRSTNAME = P_Tab_Data(i).T_FIRSTNAME,
MIDDLENAME = P_Tab_Data(i).T_MIDDLENAME,
LASTNAME = P_Tab_Data(i).T_LASTNAME,
SUFFIX = P_Tab_Data(i).T_SUFFIX,
ADDRESS1 = P_Tab_Data(i).T_ADDRESS1,
ADDRESS2 = P_Tab_Data(i).T_ADDRESS2,
CITY = P_Tab_Data(i).T_CITY,
STATE = P_Tab_Data(i).T_STATE,
ZIPCODE = P_Tab_Data(i).T_ZIPCODE,
WORKPHONE = P_Tab_Data(i).T_WORKPHONE,
HOMEPHONE = P_Tab_Data(i).T_HOMEPHONE,
FAXNUMBER = P_Tab_Data(i).T_FAXNUMBER,
BIRTHDATE = P_Tab_Data(i).T_BIRTHDATE,
MARITALSTATUS = P_Tab_Data(i).T_MARITALSTATUS,
DRIVERLICENSENO = P_Tab_Data(i).T_DRIVERLICENSENO,
DRIVERSTATE = P_Tab_Data(i).T_DRIVERSTATE,
DENOMID = P_Tab_Data(i).T_DENOMID
WHERE T.ContactID = P_Tab_Data(i).ContactID;
END LOOP;
END dbContacts_Update_Tab;
------------------------------ dbCONTACTS_DELETE_TAB -------------------------------
PROCEDURE dbContacts_Delete_Tab(P_Tab_data IN ContactsID_Tab) IS
cnt NUMBER;
BEGIN
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
DELETE FROM dbContacts
WHERE C.ContactID = P_Tab_Data(i).ContactID;
END LOOP;
END dbContacts_Delete_Tab;
----------------------------- dbCONTACTS_LOC_TAB ------------------------------------
PROCEDURE dbContacts_Lock_Tab(P_Tab_Data IN ContactsID_tab) IS
i NUMBER;
cnt NUMBER;
block_rec Contacts_Rec;
BEGIN
cnt := P_Tab_Data.count;
FOR i IN 1..cnt LOOP
Select C.CONTACTID,
C.INACTIVEFLAG,
C.TAXID,
C.COMPANYNAME,
C.EMAILADDRESS,
C.WEBPAGE,
C.DENOMPREFID,
C.INTERESTTYPEPREF,
C.CITIZEN,
C.ISSUEPREFID,
C.LASTINQUIRY,
C.DATELASTINQ,
C.COMMENTFIELD,
C.AVAILTIMESTART,
C.AVAILTIMEEND,
C.PERMGRANTED,
C.DONTCALL,
C.PENDINGFLAG,
P.CONTACTID "PCONTACTID",
P.CONTACTTYPE "PCONTACTTYPE",
P.TAXID "PTAXID",
P.PREFIX "PPREFIX",
P.FIRSTNAME "PFIRSTNAME",
P.MIDDLENAME "PMIDDLENAME",
P.LASTNAME "PLASTNAME",
P.SUFFIX "PSUFFIX",
P.ADDRESS1 "PADDRESS1",
P.ADDRESS2 "PADDRESS2",
P.CITY "PCITY",
P.STATE "PSTATE",
P.ZIPCODE "PZIPCODE",
P.WORKPHONE "PWORKPHONE",
P.HOMEPHONE "PHOMEPHONE",
P.FAXNUMBER "PFAXNUMBER",
P.BIRTHDATE "PBIRTHDATE",
P.MARITALSTATUS "PMARITALSTATUS",
P.DRIVERLICENSENO "PDRIVERLICENSENO",
P.DRIVERSTATE "PDRIVERSTATE",
P.DENOMID "PDENOMID",
S.CONTACTID "SCONTACTID",
S.CONTACTTYPE "SCONTACTTYPE",
S.TAXID "STAXID",
S.PREFIX "SPREFIX",
S.FIRSTNAME "SFIRSTNAME",
S.MIDDLENAME "SMIDDLENAME",
S.LASTNAME "SLASTNAME",
S.SUFFIX "SSUFFIX",
S.ADDRESS1 "SADDRESS1",
S.ADDRESS2 "SADDRESS2",
S.CITY "SCITY",
S.STATE "SSTATE",
S.ZIPCODE "SZIPCODE",
S.WORKPHONE "SWORKPHONE",
S.HOMEPHONE "SHOMEPHONE",
S.FAXNUMBER "SFAXNUMBER",
S.BIRTHDATE "SBIRTHDATE",
S.MARITALSTATUS "SMARITALSTATUS",
S.DRIVERLICENSENO "SDRIVERLICENSENO",
S.DRIVERSTATE "SDRIVERSTATE",
S.DENOMID "SDENOMID",
T.CONTACTID "TCONTACTID",
T.CONTACTTYPE "TCONTACTTYPE",
T.TAXID "TTAXID",
T.PREFIX "TPREFIX",
T.FIRSTNAME "TFIRSTNAME",
T.MIDDLENAME "TMIDDLENAME",
T.LASTNAME "TLASTNAME",
T.SUFFIX "TSUFFIX",
T.ADDRESS1 "TADDRESS1",
T.ADDRESS2 "TADDRESS2",
T.CITY "TCITY",
T.STATE "TSTATE",
T.ZIPCODE "TZIPCODE",
T.WORKPHONE "TWORKPHONE",
T.HOMEPHONE "THOMEPHONE",
T.FAXNUMBER "TFAXNUMBER",
T.BIRTHDATE "TBIRTHDATE",
T.MARITALSTATUS "TMARITALSTATUS",
T.DRIVERLICENSENO "TDRIVERLICENSENO",
T.DRIVERSTATE "TDRIVERSTATE",
T.DENOMID "TDENOMID"
INTO block_rec
FROM
GWTSDEV.dbContacts C,
GWTSDEV.dbContactPerson P,
GWTSDEV.dbContactPerson S,
GWTSDEV.dbContactPerson T
WHERE C.ContactID = P_TAB_Data(i).ContactID
OR P.ContactID = P_TAB_Data(i).P_ContactID
OR S.ContactID = P_TAB_Data(i).S_ContactID
OR T.ContactID = P_TAB_Data(i).T_ContactID
FOR UPDATE NOWAIT;
END LOOP;
END dbContacts_Lock_Tab;
-------------------------- COUNT FUNCTION -------------------------------------
FUNCTION Count_Query_Func RETURN NUMBER IS
r NUMBER;
BEGIN
SELECT count(*)
INTO r
FROM dbcontacts;
RETURN r;
END Count_Query_Func;
/********************** DML and Locking Procedures ********************************
----------------------- based on Doc ID 66887.1
----------------------- Using Record data types ---------------------------------
PROCEDURE dbContacts_Insert(P_Record_Data IN contacts_rec) IS
BEGIN
INSERT INTO dbcontacts
(CONTACTID,
TAXID,
COMPANYNAME,
EMAILADDRESS,
WEBPAGE,
DENOMPREFID,
INTERESTTYPEPREF,
CITIZEN,
ISSUEPREFID,
LASTINQUIRY,
DATELASTINQ,
PRIMARYREPID,
COMMENTFIELD,
AVAILTIMESTART,
AVAILTIMEEND)
values
(P_Record_Data.ContactID,
P_Record_Data.Taxid,
P_Record_Data.CompanyName,
P_Record_Data.EmailAddress,
P_Record_Data.WebPage,
P_Record_Data.DenomPrefID,
P_Record_Data.InteresttypePref,
P_Record_Data.Citizen,
P_Record_Data.IssuePrefID,
P_Record_Data.LastInquiry,
P_Record_Data.DateLastInq,
P_Record_Data.PrimaryRepID,
P_Record_Data.CommentField,
P_Record_Data.AvailTimeStart,
P_Record_Data.AvailTimeEnd);
INSERT INTO DBCONTACTPERSON
(CONTACTID,
CONTACTTYPE,
TAXID,
PREFIX,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
SUFFIX, &n