Here is the query
SELECT PTI.PARTY_ID AS "CUSTID",
PTI.CMA_CUSTOMER_NUMBER AS "CUSTNUM",
PTI.SOURCE_ID AS "CUSTSOURCE",
PTI.PARTY_CLASS_CD AS "CLASS",
(SELECT CMAPSV.STATUS_VALUE
FROM type3 CMAPS,
type4 CMAPSV
WHERE PTI.PARTY_TYPE_INSTANCE_ID = CMAPS.PARTY_TYPE_INSTANCE_ID
AND CMAPS.PARTY_STATUS_TYPE_ID = 1
AND CMAPS.PARTY_STATUS_VALUE_ID = CMAPSV.PARTY_STATUS_VALUE_ID
AND (NVL(CMAPS.PEND_LIST_STATUS,
'N') = 'N')
AND (TRD.CURR_DT >= CMAPS.FROM_DT
AND TRD.CURR_DT <= CMAPS.TO_DT)
AND ROWNUM = 1) AS "CUSTSTATUS",
PTI.TYPE_NAME AS "CUSTTYPENAME",
PTI.MASTER_PARTY_TYPE_ID AS "CUST_SUPER_TYPE_ID",
PTI.PARTY_TYPE_ID AS "CUSTTYPE",
(SELECT AU3.LINE_1 || '~' || AU3.CITY || '~' || AU3.STATE || '~' ||
AU3.POSTAL_CODE
FROM MV_type4 AU3
WHERE AU3.PARTY_ID = PTI.PARTY_ID
AND (NVL(AU3.PEND_LIST_STATUS,
'N') = 'N')
AND ((TRD.CURR_DT >= AU3.ADDRUSG_FROM_DT
AND TRD.CURR_DT <= AU3.ADDRUSG_TO_DT))
AND AU3.ADDR_USAGE_TYPE_ID = '83'
AND ((TRD.CURR_DT >= AU3.ADDR_FROM_DT
AND TRD.CURR_DT <= AU3.ADDR_TO_DT))
AND ROWNUM = 1) AS ADDRESS,
(SELECT FIRST_NAME || '~' || MIDDLE_NAME || '~' || LAST_NAME
FROM PERSON
WHERE PERSON.PARTY_ID = PTI.PARTY_ID) AS "NAME",
(SELECT CC.CLASSIF_CODE_NAME
FROM type5 PC,
type6 CC
WHERE PTI.PARTY_ID = PC.PARTY_ID
AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
AND (TRD.CURR_DT >= PC.FROM_DT
AND TRD.CURR_DT <= PC.TO_DT)
AND (NVL(PC.PEND_LIST_STATUS,
'N') = 'N')
AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
AND CC.CLASSIF_TYPE_ID = '7'
AND ROWNUM = 1) AS "Primary Specialty",
(SELECT CC.CLASSIF_CODE_NAME
FROM type5 PC,
type6 CC
WHERE PTI.PARTY_ID = PC.PARTY_ID
AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
AND (TRD.CURR_DT >= PC.FROM_DT
AND TRD.CURR_DT <= PC.TO_DT)
AND (NVL(PC.PEND_LIST_STATUS, 'N') = 'N')
AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
AND CC.CLASSIF_TYPE_ID = '132'
AND ROWNUM = 1) AS "CAM_FLAG",
(SELECT CC.CLASSIF_CODE_NAME
FROM type5 PC,
type6 CC
WHERE PTI.PARTY_ID = PC.PARTY_ID
AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
AND (TRD.CURR_DT >= PC.FROM_DT
AND TRD.CURR_DT <= PC.TO_DT)
AND (NVL(PC.PEND_LIST_STATUS,
'N') = 'N')
AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
AND CC.CLASSIF_TYPE_ID = '12'
AND ROWNUM = 1) AS "MODEL",
(SELECT CC.CLASSIF_CODE_NAME
FROM type5 PC,
type6 CC
WHERE PTI.PARTY_ID = PC.PARTY_ID
AND PTI.PARTY_TYPE_ID = PC.PARTY_TYPE_ID
AND (TRD.CURR_DT >= PC.FROM_DT
AND TRD.CURR_DT <= PC.TO_DT)
AND (NVL(PC.PEND_LIST_STATUS,
'N') = 'N')
AND PC.CLASSIF_CODE_ID = CC.CLASSIF_CODE_ID
AND CC.CLASSIF_TYPE_ID = '12'
AND ROWNUM = 1) AS "PROF_CLASS",
(SELECT PR.REFERENCE_VALUE
FROM PARTY_REFERENCE PR
WHERE PTI.PARTY_ID = PR.PARTY_ID
AND PR.PARTY_TYPE_ID = PTI.PARTY_TYPE_ID
AND (TRD.CURR_DT >= PR.FROM_DT
AND TRD.CURR_DT <= PR.TO_DT)
AND (NVL(PR.PEND_LIST_STATUS,
'N') = 'N')
AND PR.REF_TYPE_ID = '108'
AND ROWNUM = 1) AS "IMS ID"
FROM MV_TYPE1 PTI,
TAB__RUN_TYPE2 TRD
WHERE (TRD.CURR_DT >= PTI.FROM_DT
AND TRD.CURR_DT <= PTI.TO_DT)
AND (NVL(PTI.PEND_LIST_STATUS,
'N') = 'N')
AND PTI.PARTY_TYPE_GROUP_ID IN ('1',
'2')
AND (PTI.CMA_CUSTOMER_NUMBER IS NULL
OR NOT PTI.CMA_CUSTOMER_NUMBER = 1)
AND PTI.PARTY_TYPE_ID = ('14')
AND EXISTS (SELECT 1
FROM PARTY_CLASSIF PC
WHERE PC.PARTY_ID = PTI.PARTY_ID
AND PC.PARTY_TYPE_ID = PTI.PARTY_TYPE_ID
AND (NVL(PC.PEND_LIST_STATUS,
'N') = 'N')
AND NOT (PC.FROM_DT >= '31-DEC-4000'
OR PC.TO_DT <= '2-FEB-2006')
AND (PC.CLASSIF_CODE_ID = ('1951')))
ORDER BY TO_NUMBER("CUSTNUM") ASC
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
