Query Issue
Date: Tue, 12 Feb 2019 17:34:46 -0500
Message-ID: <CAB6JwgiTAZvf2O33Va1j+TCL8VK4dCH-nkKUtOx7tyzdkt0=5g_at_mail.gmail.com>
Hi,
The below query returns records but no data is being inserted to table. Could you please suggest?
INSERT INTO PMP_PRESCRIPTION_TRANSACTION(
PMP_TXN_SEQ_NBR,
PRESCRIPTION_ID,
PRESCRIPTION_FILL_ID,
FACILITY_NUM,
RX_NUMBER,
FILL_NUMBER,
PARTIAL_FILL_SEQUENCE_NUMBER,
FILL_DATE,
IS_COMPOUND,
REPORTABLE_STATE_CD,
REPORTABLE_NRL_STATE_CD,
CREATED_DATE,
CREATED_BY,
LAST_UPDATED_DATE,
LAST_UPDATED_BY,
FID_VALUE,
FACILITY_DEA_NUMBER,
LEGAL_NAME,
FACILITY_ADDRESS1,
FACILITY_CITY,
FACILITY_STATE_CD,
FACILITY_ZIPCODE,
FACILITY_PHONE_NBR,
FACILITY_ID,
PAT_LAST_NAME,
PAT_FIRST_NAME,
PAT_ADDRESS_LINE1,
PAT_CITY,
PAT_STATE_CD,
PAT_ZIPCODE,
PAT_HOME_PHONE_NUM,
PAT_DATE_OF_BIRTH,
PAT_GENDER,
PAT_SPECIES_CODE,
PAT_PET_OWNER_LAST_NAME,
PAT_PET_OWNER_FIRST_NAME,
PAT_PET_OWNER_DOB,
PAT_PET_OWNER_GENDER,
PRESCRIPTION_DATE_WRITTEN,
PRESCRIBED_NUMBER_OF_REFILLS,
NDC_DISPENSED_DRUG,
DISPENSED_QUANTITY,
DAYS_SUPPLY,
PAYMENT_TYPE,
DIAGNOSIS_CODE,
PRE_NPI,
PRE_DEA_NUMBER,
PRE_STATE_LICENSE_ID,
PRE_LAST_NAME,
PRE_FIRST_NAME,
PRE_OFFICE_PHONE_NUM_1,
SENT_STATUS,
REPORTING_STATUS,
PACKAGE_UNITS,
SOURCE_CD,
NRL_SENT_STATUS,
RUN_ID_NBR,
RF_LAST_UPDATED_DATE,
FILL_STATE,
FILL_STATUS
)
SELECT PMP_TXN_SEQ_NBR.NEXTVAL,
main.*FROM (
SELECT DISTINCT
PRESCRIPTION_ID,
PRESCRIPTION_FILL_ID,
FACILITY_NUM,
RX_NUMBER,
FILL_NUMBER,
PARTIAL_FILL_SEQUENCE_NUMBER,
FILL_DATE,
IS_COMPOUND,
CASE
WHEN MAIN.IS_COMPOUND='N' AND (MAIN.PRO_SCHEDULE IN
('2','3','4','5') OR MAIN.GCN IN (SELECT NSD.CATEGORY_CODE
FROM PMP_STATE_DRUG NSD
WHERE NSD.STATE_CD = MAIN.FACILITY_STATE_CD
AND NSD.CATEGORY_TYPE = 'GCN')) THEN
FACILITY_STATE_CD
WHEN
MAIN.IS_COMPOUND='Y' AND
P_PMP_MANAGEMENT.SW_GET_PRODUCT_STATE(MAIN.PRODUCT_NUM,
MAIN.FACILITY_STATE_CD, MAIN.IS_COMPOUND, NULL, NULL,
MAIN.NDC_LOCALLY_ADDED) = 1
THEN
FACILITY_STATE_CD
END STATE_ID,
CASE WHEN MAIN.FACILITY_STATE_CD<>MAIN.PAT_STATE_CD THEN
CASE WHEN
P_PMP_MANAGEMENT.SW_GET_PRODUCT_STATE(MAIN.PRODUCT_NUM, MAIN.PAT_STATE_CD, MAIN.IS_COMPOUND, MAIN.FACILITY_ID, MAIN.FACILITY_STATE_CD, MAIN.NDC_LOCALLY_ADDED) = 2
THEN PAT_STATE_CD
END END NRL_STATE,
SYSDATE CREATED_DT,
USER CREATED_BY,
SYSDATE LAST_UPDATED_DATE,
USER LAST_UPDATED_BY,
FID_VALUE,
FACILITY_DEA_NUMBER,
LEGAL_NAME,
FACILITY_ADDRESS1,
FACILITY_CITY,
FACILITY_STATE_CD,
FACILITY_ZIPCODE,
FACILITY_PHONE_NBR,
FACILITY_ID,
PAT_LAST_NAME,
PAT_FIRST_NAME,
PAT_ADDRESS_LINE1,
PAT_CITY,
PAT_STATE_CD,
PAT_ZIPCODE,
PAT_HOME_PHONE_NUM,
PAT_DATE_OF_BIRTH,
PAT_GENDER,
PAT_SPECIES_CODE,
PAT_PET_OWNER_LAST_NAME,
PAT_PET_OWNER_FIRST_NAME,
PAT_PET_OWNER_DOB,
PAT_PET_OWNER_GENDER,
PRESCRIPTION_DATE_WRITTEN,
PRESCRIBED_NUMBER_OF_REFILLS,
NDC_DISPENSED_DRUG,
DISPENSED_QUANTITY,
DAYS_SUPPLY,
PAYMENT_TYPE,
DIAGNOSIS_CODE,
PRE_NPI,
PRE_DEA_NUMBER,
PRESCRIBE_STATE_LICENSE_ID,
PRE_LAST_NAME,
PRE_FIRST_NAME,
PRE_OFFICE_PHONE_NUM_1,
SENT_STATUS,
REPORTING_STATUS,
PACKAGE_UNITS,
'RXC' SOURCE_CD,
NRL_SENT_STATUS,
1 RUN_ID_NBR,
RF_LAST_UPDATED_DATE,
FILL_STATE,
FILL_STATUS
FROM (select
MAIN.*,
( SELECT PRODUCT_SCHEDULE
FROM (SELECT NVL( --Priority 1,2,3
(SELECT DEA FROM (SELECT DEA FROM
PRODUCT_DEA_OVERRIDE
WHERE
PRODUCT_NUM = MAIN.PRODUCT_NUM
AND ((TYPE IN (1, 2) AND STATE_CODE =
MAIN.FACILITY_STATE_CD) OR (TYPE = 3))
AND SYSDATE BETWEEN
RXC_START_DATE AND
RXC_END_DATE
ORDER BY TYPE) WHERE ROWNUM=1),
-- Priority 4 For PRODUCT
(SELECT PRD.PRODUCT_SCHEDULE
FROM PRODUCT PRD
WHERE PRD.PRODUCT_NUM =
MAIN.PRODUCT_NUM)
) PRODUCT_SCHEDULE
FROM DUAL))PRO_SCHEDULE,
( SELECT DX.GCN
FROM DRUG_XREF DX, PRODUCT P
WHERE DX.DRUG_NUM = P.DRUG_NUM
AND P.PRODUCT_NUM = MAIN.PRODUCT_NUM
AND ROWNUM<=1 )GCN
from(SELECT /*+PARALLEL(NFT 16)FULL(NFT)LEADING(NFT)USE_NL(RX
PA)USE_NL(RX DA)USE_NL(RX DR) USE_NL(RF F) USE_NL(RF P)*/
RF.PRESCRIPTION_ID,
RF.PRESCRIPTION_FILL_ID,
RF.FILL_NUMBER,
F.FACILITY_NUM,
(SELECT FID.VALUE
FROM FACILITY_ID FID
WHERE FID.FACILITY_NUM = F.FACILITY_NUM
AND FID.TYPE = 'F08') FID_VALUE,
F.LEGAL_NAME LEGAL_NAME,
(SELECT ADS.ADDRESS1
FROM ADDRESS ADS, ADDRESS_LINK ADL
WHERE ADL.ADDRESS_NUM = ADS.ADDRESS_NUM
AND ADL.OWNER_TYPE_CODE = 'C'
AND ADL.OWNER = F.FACILITY_NUM
AND ADL.USAGE = 8
AND ROWNUM < 2) FACILITY_ADDRESS1,
(SELECT ZIP.CITY
FROM ADDRESS ADS, ADDRESS_LINK ADL, ZIPCODE ZIP
WHERE ADL.ADDRESS_NUM = ADS.ADDRESS_NUM
AND ADL.OWNER_TYPE_CODE = 'C'
AND ADS.CSZ_NUM = ZIP.CSZ_NUM
AND ADL.OWNER = F.FACILITY_NUM
AND ADL.USAGE = 8
AND ROWNUM < 2) FACILITY_CITY,
(SELECT ZIP.STATE_CODE
FROM ADDRESS ADS, ADDRESS_LINK ADL, ZIPCODE ZIP
WHERE ADL.ADDRESS_NUM = ADS.ADDRESS_NUM
AND ADL.OWNER_TYPE_CODE = 'C'
AND ADS.CSZ_NUM = ZIP.CSZ_NUM
AND ADL.OWNER = F.FACILITY_NUM
AND ADL.USAGE = 8
AND ROWNUM < 2) FACILITY_STATE_CD,
(SELECT ZIP.ZIPCODE
FROM ADDRESS ADS, ADDRESS_LINK ADL, ZIPCODE ZIP
WHERE ADL.ADDRESS_NUM = ADS.ADDRESS_NUM
AND ADL.OWNER_TYPE_CODE = 'C'
AND ADS.CSZ_NUM = ZIP.CSZ_NUM
AND ADL.OWNER = F.FACILITY_NUM
AND ADL.USAGE = 8
AND ROWNUM < 2) FACILITY_ZIPCODE,
(SELECT PH.AREA_CODE || PH.PHONE_NUMBER
FROM PHONE PH, PHONE_LINK PL
WHERE PH.PHONE_NUM = PL.PHONE_NUM
AND PL.OWNER = F.FACILITY_NUM
AND PL.USAGE = 1
AND PL.PHONE_USAGE = 128
AND PL.OWNER_TYPE_CODE = 'C'
AND ROWNUM < 2) FACILITY_PHONE_NBR,
F.FACILITY_ID,
F.DEA_NUMBER FACILITY_DEA_NUMBER,
P.LAST_NAME PAT_LAST_NAME,
P.FIRST_NAME PAT_FIRST_NAME,
PA.ADDRESS_LINE1 PAT_ADDRESS_LINE1,
PA.CITY PAT_CITY,
PA.STATE PAT_STATE_CD,
PA.ZIPCODE PAT_ZIPCODE,
--PA.HOME_PHONE_NUM PAT_HOME_PHONE_NUM,
Decode(p.preferred_contact,2,p.office_phone_num,3, p.mobile_num,1,pa.home_phone_num) PAT_HOME_PHONE_NUM,
P.DATE_OF_BIRTH PAT_DATE_OF_BIRTH,
P.GENDER PAT_GENDER,
P.SPECIES_CODE PAT_SPECIES_CODE,
P.PET_OWNER_LAST_NAME PAT_PET_OWNER_LAST_NAME,
P.PET_OWNER_FIRST_NAME PAT_PET_OWNER_FIRST_NAME,
P.PET_OWNER_DOB PAT_PET_OWNER_DOB,
P.PET_OWNER_GENDER PAT_PET_OWNER_GENDER,
RF.RX_NUMBER,
RX.PRESCRIPTION_DATE_WRITTEN,
RX.PRESCRIBED_NUMBER_OF_REFILLS,
RF.FILL_DATE,
RX.IS_COMPOUND,
RF.NDC_DISPENSED_DRUG,
RF.DISPENSED_QUANTITY,
RF.DAYS_SUPPLY,
DECODE(RF.NDC_LOCALLY_ADDED,
'Y',
(SELECT LP.PACKAGE_UNIT
FROM RXP_LOCAL_PRODUCT LP
WHERE LP.PRESCRIPTION_FILL_ID =
RF.PRESCRIPTION_FILL_ID
AND LP.IS_DELETE = 'N'),
'N',
(SELECT DX.PACKAGE_UNITS FROM PRODUCT P,DRUG_XREF DX
WHERE P.PRODUCT_NUM=RF.PRODUCT_NUM AND
P.DRUG_NUM=DX.DRUG_NUM))PACKAGE_UNITS,
RF.PARTIAL_FILL_SEQUENCE_NUMBER,
RF.PAYMENT_TYPE,
RF.PRODUCT_NUM,
NVL((SELECT SAV.DIAGNOSIS_CODE FROM
SRD_ATTRIBUTE_VALUE SAV WHERE
SAV.PRESCRIPTION_FILL_ID=RF.PRESCRIPTION_FILL_ID and rownum<2),RX.DIAGNOSIS_CODE)DIAGNOSIS_CODE,
DR.NPI PRE_NPI,
DA.DEA_NUMBER PRE_DEA_NUMBER,
DA.PRESCRIBER_STATE_LICENSE_ID
PRESCRIBE_STATE_LICENSE_ID,
DR.LAST_NAME PRE_LAST_NAME,
DR.FIRST_NAME PRE_FIRST_NAME,
DA.OFFICE_PHONE_NUM_1 PRE_OFFICE_PHONE_NUM_1,
'N' SENT_STATUS,
'N' NRL_SENT_STATUS,
SYSDATE AUDIT_INS_DATE,
SYSDATE AUDIT_UPDATE_DATE,
'00' REPORTING_STATUS,
RF.LAST_UPDATED_DATE,
USER CREATED_BY,
USER UPDATED_BY,
RF.DRUG_SCHEDULE,
RF.NDC_LOCALLY_ADDED,
NFT.RF_LAST_UPDATED_DATE RF_LAST_UPDATED_DATE,
NFT.FILL_STATE,
NFT.FILL_STATUS
FROM RXP_PRESCRIPTION_FILL RF,
FACILITY F,
RXP_PRESCRIPTION RX,
RXP_PATIENT P,
RXP_PATIENT_ADDRESS PA,
RXP_PRESCRIBER DR,
RXP_PRESCRIBER_ADDRESS DA,
PMP_FINAL_TRANSACTION NFT
WHERE
RF.PRESCRIPTION_FILL_ID=NFT.PRESCRIPTION_FILL_ID
AND (NFT.STATUS ='X' or
nft.PRESCRIPTION_FILL_ID=707891875533)
- AND ((RF.fill_status = 7 AND RF.FILL_STATE!=3) or
- (RF.fill_status = 9 and RF.POS_STATUS IS NOT NULL)) and
--RF.rx_number is not null and RF.IS_DIRTY='N'
AND NFT.CREATED_DATE >= '17JAN2019' AND NFT.CREATED_DATE
<o_date('18jan2019') - (13 / 24)
AND RF.LAST_UPDATED_DATE < SYSDATE
AND RF.FACILITY_ID = F.FACILITY_ID
AND RF.PRESCRIPTION_ID = RX.PRESCRIPTION_ID
AND RF.PATIENT_ID = P.PATIENT_ID
AND RX.PATIENT_ADDRESS_ID = PA.PATIENT_ADDRESS_ID
AND RX.PRESCRIBER_ID = DR.PRESCRIBER_ID
AND RX.PRESCRIBER_ADDRESS_ID = DA.PRESCRIBER_ADDRESS_ID
) MAIN
WHERE
EXISTS (SELECT 1 FROM pmp_state nS WHERE
(NS.STATE_CODE=MAIN.FACILITY_STATE_CD OR NS.STATE_CODE=MAIN.PAT_STATE_CD )
AND MAIN.RF_LAST_UPDATED_DATE>=NS.REC_IDEN_DT))main
- where
/* NOT EXISTS
(SELECT 1 FROM PMP_PRESCRIPTION_TRANSACTION NPT WHERE
NPT.PRESCRIPTION_FILL_ID = MAIN.PRESCRIPTION_FILL_ID)
AND*/ --(
--
P_PMP_MANAGEMENT.SW_GET_PRODUCT_STATE(MAIN.PRODUCT_NUM, MAIN.FACILITY_STATE_CD, MAIN.IS_COMPOUND, NULL, NULL, MAIN.NDC_LOCALLY_ADDED) = 1 --OR CASE WHEN MAIN.FACILITY_STATE_CD<>MAIN.PAT_STATE_CD THEN P_PMP_MANAGEMENT.SW_GET_PRODUCT_STATE(MAIN.PRODUCT_NUM, MAIN.PAT_STATE_CD, MAIN.IS_COMPOUND, MAIN.FACILITY_ID, MAIN.FACILITY_STATE_CD, MAIN.NDC_LOCALLY_ADDED)END = 2)
)MAIN
- WHERE MAIN.STATE_ID IN (SELECT NS.STATE_CODE FROM pmp_state ns) OR MAIN.NRL_STATE IN (SELECT NS.STATE_CODE FROM pmp_state ns)
WHERE EXISTS (SELECT 1 FROM pmp_state nS WHERE (NS.STATE_CODE=MAIN.STATE_ID OR NS.STATE_CODE=MAIN.NRL_STATE ) AND MAIN.RF_LAST_UPDATED_DATE>=NS.REC_IDEN_DT) AND( STATE_ID IS NOT NULL OR NRL_STATE IS NOT NULL) If we replace MAIN.GCN with the below segment, the its working.
OR (SELECT DX.GCN FROM DRUG_XREF DX, PRODUCT P WHERE DX.DRUG_NUM = P.DRUG_NUM AND P.PRODUCT_NUM = MAIN.PRODUCT_NUM AND ROWNUM<=1 )
working
CASE
WHEN MAIN.IS_COMPOUND='N' AND (MAIN.PRO_SCHEDULE IN
('2','3','4','5') OR (SELECT DX.GCN
FROM DRUG_XREF DX, PRODUCT P
WHERE DX.DRUG_NUM = P.DRUG_NUM
AND P.PRODUCT_NUM = MAIN.PRODUCT_NUM
AND ROWNUM<=1 ) in (SELECT NSD.CATEGORY_CODE
FROM PMP_STATE_DRUG NSD
WHERE NSD.STATE_CD = MAIN.FACILITY_STATE_CD
--and nsd.CATEGORY_CODE=main.gcn
AND NSD.CATEGORY_TYPE = 'GCN'))
Not working
WHEN MAIN.IS_COMPOUND='N' AND (MAIN.PRO_SCHEDULE IN ('2','3','4','5') OR MAIN.GCN IN (SELECT NSD.CATEGORY_CODE
FROM PMP_STATE_DRUG NSD
WHERE NSD.STATE_CD = MAIN.FACILITY_STATE_CD
AND NSD.CATEGORY_TYPE = 'GCN')) THEN
Thanks,
pv
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 12 2019 - 23:34:46 CET