***Moderator action (Timo): link to external page removed.***
Hi, The requirement is like if we have quantity +ve and -ve in shipped_qty in lnd_shipment table . we are going to comapre the shipped_qty in spp_shipment table . IN spp_shipment table 2 values can sit i.e one positive and one negative or one positive value . There wont abe any negative value in spp_shipment . The business logic is like : always we need to check the lnd_shipment table with spp_shipment table and the range should be 0 to 99 allowed otherwise we will make the records fail .
The test scenarios are written in the link shared in above .
Please help me on getting rid of same code written in multiple places .
CREATE TABLE LH_LAND.LND_SHIPMENT
(
LAND_RAW_ID NUMBER NOT NULL,
SRC_PROVIDER_ID VARCHAR2(30 BYTE) NOT NULL,
SPP_PAT_ID VARCHAR2(4000 BYTE),
NDC_NO VARCHAR2(4000 BYTE),
SHIPMENT_DT VARCHAR2(4000 BYTE),
PRODUCT_ID VARCHAR2(15 BYTE) NOT NULL,
SHIPPED_QTY VARCHAR2(4000 BYTE),
SRC_FILENAME VARCHAR2(250 BYTE) NOT NULL,
FILE_REC_SEQ_NO NUMBER NOT NULL,
QC_STAT_FLG VARCHAR2(1 BYTE),
ERROR_REC_ID NUMBER
);
Insert into LH_LAND.LND_SHIPMENT
(LAND_RAW_ID, SRC_PROVIDER_ID, SPP_PAT_ID, NDC_NO, SHIPMENT_DT, PRODUCT_ID, SHIPPED_QTY, SRC_FILENAME, FILE_REC_SEQ_NO, QC_STAT_FLG)
Values
(4083552, '18', '113836', '00944270007', '10/02/2018', '300', '-100', 'SHIRE_HEM_HEALIX_DISPENSE_20181030.TXT', 1, 'S');
Insert into LH_LAND.LND_SHIPMENT
(LAND_RAW_ID, SRC_PROVIDER_ID, SPP_PAT_ID, NDC_NO, SHIPMENT_DT, PRODUCT_ID, SHIPPED_QTY, SRC_FILENAME, FILE_REC_SEQ_NO, QC_STAT_FLG)
Values
(4083554, '18', '113836', '00944270007', '10/02/2018', '300', '99', 'SHIRE_HEM_HEALIX_DISPENSE_20181030.TXT', 1, 'S');
COMMIT;
CREATE TABLE LH_LAND.SPP_SHIPMENT
(
SPP_SHIPMENT_KEY NUMBER NOT NULL,
SRC_KEY VARCHAR2(30 BYTE) NOT NULL,
PRESC_KEY NUMBER,
PAT_KEY NUMBER,
SPP_LOC_KEY NUMBER,
PRMRY_PYR_KEY NUMBER,
SCNDRY_PYR_KEY NUMBER,
PAT_DIAGNOSIS_DT DATE,
PAT_DIAGNOSIS_CD_1 VARCHAR2(15 BYTE),
PAT_DIAGNOSIS_CD_2 VARCHAR2(15 BYTE),
PAT_DIAGNOSIS_CD_3 VARCHAR2(15 BYTE),
PAT_DIAGNOSIS_CD_4 VARCHAR2(15 BYTE),
PAT_NEW_TO_DRUG VARCHAR2(3 BYTE),
PAT_PRIOR_THRPY_1 VARCHAR2(50 BYTE),
PAT_PRIOR_THRPY_2 VARCHAR2(50 BYTE),
PAT_PRIOR_THRPY_3 VARCHAR2(50 BYTE),
PAT_PRIOR_THRPY_4 VARCHAR2(50 BYTE),
PAT_PRIOR_THRPY_5 VARCHAR2(50 BYTE),
PAT_PRIOR_REGIMEN VARCHAR2(50 BYTE),
DUR_ON_PRIOR_THRPY VARCHAR2(50 BYTE),
DUR_ON_PRIOR_THRPY_1 VARCHAR2(50 BYTE),
DUR_ON_PRIOR_THRPY_2 VARCHAR2(50 BYTE),
DUR_ON_PRIOR_THRPY_3 VARCHAR2(50 BYTE),
DUR_ON_PRIOR_THRPY_4 VARCHAR2(50 BYTE),
DISCONT_REAS_ON_PRIOR_THRPY VARCHAR2(50 BYTE),
PAT_PREV_SITE_CARE VARCHAR2(50 BYTE),
PAT_CURR_THRPY_1 VARCHAR2(50 BYTE),
PAT_CURR_THRPY_2 VARCHAR2(50 BYTE),
PAT_CURR_THRPY_3 VARCHAR2(50 BYTE),
PAT_CURR_THRPY_4 VARCHAR2(50 BYTE),
PAT_CURR_THRPY_5 VARCHAR2(50 BYTE),
DUR_ON_NEW_THRPY VARCHAR2(50 BYTE),
PAT_CURR_REGIMEN VARCHAR2(50 BYTE),
PAT_INSUR_STAT VARCHAR2(50 BYTE),
PAT_STUDY_ID VARCHAR2(30 BYTE),
PAT_SERVICES_DT DATE,
NDC_NO VARCHAR2(13 BYTE),
CLIENT_PROD_CD VARCHAR2(30 BYTE),
RFRL_NO VARCHAR2(50 BYTE),
RX_NO VARCHAR2(200 BYTE),
RX_TYP VARCHAR2(35 BYTE),
AUTHORIZED_REFILLS NUMBER,
RFRL_STRT_DT DATE,
RFRL_DT_TO_HUB DATE,
ROUTE_OF_ADMINSTRN VARCHAR2(50 BYTE),
UNIQUE_DOSE NUMBER,
RX_WRITTEN_DT DATE,
DRUG_AUTH_ID VARCHAR2(30 BYTE),
CASE_ID VARCHAR2(30 BYTE),
ORDER_TRAN_NO VARCHAR2(50 BYTE),
ORDER_TRAN_TYP VARCHAR2(30 BYTE),
TRAN_TYP VARCHAR2(35 BYTE),
ORDER_DT DATE,
SHIPMENT_ID VARCHAR2(30 BYTE),
INVOICE_COST VARCHAR2(30 BYTE),
INVOICE_NO VARCHAR2(30 BYTE),
SHIPMENT_DT DATE,
LOT_NO VARCHAR2(30 BYTE),
LOT_EXPRY_DT DATE,
ACTUAL_POTENCY NUMBER,
ORDER_LINE_NO NUMBER,
TOT_ORDER_LINES NUMBER,
SHIPPED_QTY_UOM VARCHAR2(30 BYTE),
SHIPPED_QTY NUMBER,
SHIPPED_VIAL_SIZE VARCHAR2(30 BYTE),
UNIT_PRICE NUMBER,
TOT_SHIP_PRICE NUMBER,
DAYS_SUPPLY NUMBER,
FILL_NO NUMBER,
PAT_FIRST_DSPNSD_DT DATE,
LAST_FILL_DT DATE,
NEXT_FILL_DT DATE,
REFILL_REMAINING NUMBER,
ADDTNL_SHIPMENT_INFO VARCHAR2(100 BYTE),
DOSE_FREQ VARCHAR2(200 BYTE),
SELF_ADMINSTRD VARCHAR2(30 BYTE),
SHIP_CARRIER VARCHAR2(100 BYTE),
SHIP_TRACK_NO VARCHAR2(50 BYTE),
SHIP_TO_ADDR_TYP VARCHAR2(30 BYTE),
SHIP_FIRST_NM VARCHAR2(50 BYTE),
SHIP_LAST_NM VARCHAR2(50 BYTE),
SHIP_ADDR_LINE1 VARCHAR2(100 BYTE),
SHIP_ADDR_LINE2 VARCHAR2(100 BYTE),
SHIP_ADDR_LINE3 VARCHAR2(100 BYTE),
SHIP_CITY VARCHAR2(100 BYTE),
SHIP_STATE VARCHAR2(2 BYTE),
SHIP_PSTL_ID VARCHAR2(15 BYTE),
SHIP_PHONE VARCHAR2(20 BYTE),
SHIP_FAX VARCHAR2(20 BYTE),
RX_DIRCTN VARCHAR2(2000 BYTE),
LEAVE_PHONE_MSG_IND VARCHAR2(3 BYTE),
MEDICAL_GUIDE_PROVIDED VARCHAR2(3 BYTE),
PRMRY_COPAY_TYP VARCHAR2(30 BYTE),
PRMRY_COPAY_AMT NUMBER,
PRMRY_DEDUCTIBLE NUMBER,
PRMRY_COINSUR_RT NUMBER,
PRMRY_COINSUR_AMT NUMBER,
PRMRY_OUT_OF_PCKT_AMT NUMBER,
PRMRY_MAX_OUT_OF_PCKT_AMT NUMBER,
PRMRY_PLN_ANNUAL_MAX_AMT NUMBER,
PRMRY_PLN_LIFETIME_MAX_AMT NUMBER,
PRMRY_INSUR_COVERED VARCHAR2(3 BYTE),
PRMRY_INSUR_EFCTV_DT DATE,
PRMRY_DEDUCTIBLE_MET VARCHAR2(10 BYTE),
PRMRY_DEDUCTIBLE_AMT_REMAIN NUMBER,
PRMRY_PA_RQRD VARCHAR2(3 BYTE),
PRMRY_PA_ID VARCHAR2(20 BYTE),
PRMRY_PA_EXP_DT DATE,
PRMRY_PA_OUTCOME VARCHAR2(30 BYTE),
PRMRY_PA_OUTCOME_DT DATE,
PRMRY_DENIAL_REAS VARCHAR2(30 BYTE),
PRMRY_THRPY_REQ VARCHAR2(3 BYTE),
PRMRY_APPEAL_BEGIN_DT DATE,
PRMRY_APPEAL_OUTCOME VARCHAR2(30 BYTE),
SCNDRY_COPAY_TYP VARCHAR2(30 BYTE),
SCNDRY_COPAY_AMT NUMBER,
SCNDRY_DEDUCTIBLE NUMBER,
SCNDRY_COINSUR_RATE NUMBER,
SCNDRY_COINSUR_AMT NUMBER,
SCNDRY_OUT_OF_PCKT_AMT NUMBER,
SCNDRY_MAX_OUT_OF_PCKT_AMT NUMBER,
SCNDRY_PLN_ANNUAL_MAX_AMT NUMBER,
SCNDRY_PLN_LIFETIME_MAX_AMT NUMBER,
SCNDRY_INSUR_COVERED VARCHAR2(3 BYTE),
SCNDRY_INSUR_EFFCT_DT DATE,
SCNDRY_DEDUCTIBLE_MET VARCHAR2(3 BYTE),
SCNDRY_DEDUCTIBLE_AMT_REMAIN NUMBER,
SCNDRY_PA_RQRD VARCHAR2(3 BYTE),
SCNDRY_PA_ID VARCHAR2(30 BYTE),
SCNDRY_PA_EXP_DT DATE,
SCNDRY_PA_OUTCOME VARCHAR2(30 BYTE),
SCNDRY_PA_OUTCOME_DT DATE,
SCNDRY_DEIAL_REAS VARCHAR2(30 BYTE),
SCNDRY_THRPY_REQ VARCHAR2(3 BYTE),
SCNDRY_APPEAL_BEGIN_DT DATE,
SCNDRY_APPEAL_OUTCOME VARCHAR2(30 BYTE),
RECORD_CD VARCHAR2(30 BYTE),
SRC_REC_UNQ_ID VARCHAR2(30 BYTE),
PAT_HIPAA_CONSENT_VRB_CONF_FLG VARCHAR2(15 BYTE),
RFRL_SOURCE VARCHAR2(100 BYTE),
OTHER_FLEX_COL_1 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_2 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_3 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_4 VARCHAR2(500 BYTE),
OTHER_FLEX_COL_5 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_6 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_7 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_8 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_9 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_10 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_11 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_12 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_13 NUMBER,
OTHER_FLEX_COL_14 NUMBER,
OTHER_FLEX_COL_15 NUMBER,
OTHER_FLEX_COL_16 NUMBER,
OTHER_FLEX_COL_17 DATE,
OTHER_FLEX_COL_18 DATE,
OTHER_FLEX_COL_19 DATE,
OTHER_FLEX_COL_20 DATE,
SHIP_TO_SITE_ID VARCHAR2(100 BYTE),
SHIP_TO_SITE_NM VARCHAR2(100 BYTE),
STD_QTY_UOM VARCHAR2(30 BYTE),
STD_SHIPPED_QTY NUMBER,
FILE_TYP_ID NUMBER,
CLIENT_ID VARCHAR2(15 BYTE) NOT NULL,
PRODUCT_ID VARCHAR2(15 BYTE) NOT NULL,
INSERT_LAND_RAW_ID NUMBER,
INSERT_SRC_FILENAME VARCHAR2(250 BYTE) NOT NULL,
INSERT_TIMESTAMP DATE NOT NULL,
INSERT_PROCESS_NM VARCHAR2(250 BYTE) NOT NULL,
UPDATE_LAND_RAW_ID NUMBER,
UPDATE_SRC_FILENAME VARCHAR2(250 BYTE),
UPDATE_TIMESTAMP DATE,
UPDATE_PROCESS_NM VARCHAR2(250 BYTE),
CONCURRENT_PRODUCTS VARCHAR2(100 BYTE),
PROD_STRENGTH VARCHAR2(50 BYTE),
SIGN VARCHAR2(1 BYTE),
OTHER_FLEX_COL_23 VARCHAR2(200 BYTE),
HUB_PAT_ID VARCHAR2(200 BYTE),
MANUFCTR VARCHAR2(200 BYTE),
OTHER_FLEX_COL_21 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_26 VARCHAR2(200 BYTE),
PAT_CURR_THRPY VARCHAR2(100 BYTE),
PAT_THRPY_END_DT DATE,
PAT_THRPY_STRT_DT DATE,
PAT_WEIGHT NUMBER,
PAT_YOB VARCHAR2(4 BYTE),
PA_UNITS_ALLWD NUMBER,
PHRCMY_LOC_ADDR_LINE1 VARCHAR2(200 BYTE),
PHRCMY_LOC_ADDR_LINE2 VARCHAR2(200 BYTE),
PHRCMY_LOC_CITY VARCHAR2(200 BYTE),
PHRCMY_LOC_NM VARCHAR2(200 BYTE),
PHRCMY_LOC_PSTL_ID VARCHAR2(15 BYTE),
PHRCMY_LOC_STATE VARCHAR2(200 BYTE),
PRESC_ADDR_LINE1 VARCHAR2(200 BYTE),
PRESC_ADDR_LINE2 VARCHAR2(200 BYTE),
PRESC_ADDR_LINE3 VARCHAR2(200 BYTE),
PRESC_CITY VARCHAR2(200 BYTE),
PRESC_DEA_NO VARCHAR2(9 BYTE),
PRESC_FST_NM VARCHAR2(50 BYTE),
PRESC_LST_NM VARCHAR2(50 BYTE),
PRESC_MID_NM VARCHAR2(50 BYTE),
PRESC_NPI_NO NUMBER(10),
PRESC_PSTL_ID VARCHAR2(15 BYTE),
PRESC_SPECIALTY VARCHAR2(100 BYTE),
PRESC_STATE VARCHAR2(2 BYTE),
PRMRY_PYR_NM VARCHAR2(100 BYTE),
PRMRY_PYR_TYP VARCHAR2(30 BYTE),
PROD_DESC VARCHAR2(100 BYTE),
PROD_NM VARCHAR2(50 BYTE),
RX_QTY NUMBER,
SHIPMENT_AMT NUMBER,
SRC_LOC_ID VARCHAR2(50 BYTE),
SRC_PROD_CD VARCHAR2(50 BYTE),
TOT_QTY NUMBER,
HCPO_340B_ELGBLE_FLG VARCHAR2(200 BYTE),
ALT_PHRMCY_ID VARCHAR2(200 BYTE),
BILL_TO_CITY VARCHAR2(200 BYTE),
BILL_TO_CUST_NM VARCHAR2(200 BYTE),
BILL_TO_CUST_NO VARCHAR2(200 BYTE),
BILL_TO_PSTL_ID VARCHAR2(200 BYTE),
BILL_TO_STATE VARCHAR2(200 BYTE),
DRUG_AUTH_TYP VARCHAR2(200 BYTE),
DRUG_NM VARCHAR2(200 BYTE),
INSUR_TYP VARCHAR2(200 BYTE),
MNFCTR_SOC_ID VARCHAR2(200 BYTE),
MNFCTR_SRC_ID VARCHAR2(200 BYTE),
ORDER_ID VARCHAR2(200 BYTE),
OTHER_FLEX_COL_31 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_32 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_33 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_34 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_35 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_36 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_37 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_38 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_39 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_40 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_41 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_42 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_45 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_46 DATE,
OTHER_FLEX_COL_47 DATE,
OTHER_FLEX_COL_48 DATE,
OTHER_FLEX_COL_49 DATE,
OTHER_FLEX_COL_50 DATE,
PA_EFFCT_STRT_DT DATE,
PHRMCY_HUB_PYR_ID VARCHAR2(200 BYTE),
PRMRY_ADDR_LINE_1 VARCHAR2(200 BYTE),
PRMRY_ADDR_LINE_2 VARCHAR2(200 BYTE),
PRMRY_CITY VARCHAR2(200 BYTE),
PRMRY_COST_SHRE_TYP VARCHAR2(200 BYTE),
PRMRY_INSUR_END_DT DATE,
PRMRY_PA_STRT_DT DATE,
PRMRY_PBM_BIN VARCHAR2(200 BYTE),
PRMRY_PBM_GRP VARCHAR2(200 BYTE),
PRMRY_PBM_PCN VARCHAR2(200 BYTE),
PRMRY_PBM_PLCY_NMBR VARCHAR2(200 BYTE),
PRMRY_PBM_PLN_TYP VARCHAR2(200 BYTE),
PRMRY_PBM_TYP VARCHAR2(200 BYTE),
PRMRY_PLCY_NMBR VARCHAR2(200 BYTE),
PRMRY_PLCY_NMBR_GRP VARCHAR2(200 BYTE),
PRMRY_PSTL_ID VARCHAR2(200 BYTE),
PRMRY_PYR_STATE VARCHAR2(200 BYTE),
RECORD_TYPE VARCHAR2(200 BYTE),
SCNDRY_ADDR_LINE_1 VARCHAR2(200 BYTE),
SCNDRY_ADDR_LINE_2 VARCHAR2(200 BYTE),
SCNDRY_CITY VARCHAR2(200 BYTE),
SCNDRY_COST_SHRE_TYP VARCHAR2(200 BYTE),
SCNDRY_HUB_PYR_ID VARCHAR2(200 BYTE),
SCNDRY_INSUR_END_DT DATE,
SCNDRY_INSUR_TYP VARCHAR2(200 BYTE),
SCNDRY_PBM_BIN VARCHAR2(200 BYTE),
SCNDRY_PBM_GRP VARCHAR2(200 BYTE),
SCNDRY_PBM_PCN VARCHAR2(200 BYTE),
SCNDRY_PBM_PLCY_NMBR VARCHAR2(200 BYTE),
SCNDRY_PBM_PLN_TYP VARCHAR2(200 BYTE),
SCNDRY_PBM_TYP VARCHAR2(200 BYTE),
SCNDRY_PLCY_NMBR VARCHAR2(200 BYTE),
SCNDRY_PLCY_NMBR_GRP VARCHAR2(200 BYTE),
SCNDRY_PSTL_ID VARCHAR2(200 BYTE),
SCNDRY_STATE VARCHAR2(200 BYTE),
SHIP_TO_CUST_NM VARCHAR2(200 BYTE),
SHIP_TO_CUST_NO VARCHAR2(200 BYTE),
SHIP_TO_FAX VARCHAR2(200 BYTE),
SHIP_TO_NPI_NO VARCHAR2(200 BYTE),
SHIP_TO_PHONE VARCHAR2(200 BYTE),
SHIP_TO_PSTL_ID VARCHAR2(200 BYTE),
SHIP_TO_STATE VARCHAR2(200 BYTE),
SITE_OF_SERVICE VARCHAR2(200 BYTE),
SOC_ADDR_LINE_1 VARCHAR2(200 BYTE),
SOC_ADDR_LINE_2 VARCHAR2(200 BYTE),
SOC_CITY VARCHAR2(200 BYTE),
SOC_NM VARCHAR2(200 BYTE),
SOC_PSTL_ID VARCHAR2(200 BYTE),
SOC_STATE VARCHAR2(200 BYTE),
SP_SOC_ID VARCHAR2(200 BYTE),
TERR_ID VARCHAR2(200 BYTE),
TRTRY_COST_SHRE_TYP VARCHAR2(200 BYTE),
TRTRY_DEDUCTIBLE VARCHAR2(200 BYTE),
TRTRY_DEDUCTIBLE_MET VARCHAR2(200 BYTE),
TRTRY_INSUR_EFFCT_DT DATE,
TRTRY_INSUR_END_DT DATE,
TRTRY_INSUR_TYP VARCHAR2(200 BYTE),
TRTRY_MAX_OUT_OF_PCKT_AMT NUMBER,
TRTRY_OUT_OF_PCKT_AMT NUMBER,
TRTRY_PBM_BIN VARCHAR2(200 BYTE),
TRTRY_PBM_GRP VARCHAR2(200 BYTE),
TRTRY_PBM_NM VARCHAR2(200 BYTE),
TRTRY_PBM_PCN VARCHAR2(200 BYTE),
TRTRY_PBM_PHONE VARCHAR2(200 BYTE),
TRTRY_PBM_PLCY_NMBR VARCHAR2(200 BYTE),
TRTRY_PBM_PLN_TYP VARCHAR2(200 BYTE),
TRTRY_PBM_TYP VARCHAR2(200 BYTE),
TRTRY_PLCY_NMBR VARCHAR2(200 BYTE),
TRTRY_PLN_ANNUAL_MAX_AMT NUMBER,
TRTRY_PLN_ANNUAL_MAX_MET VARCHAR2(200 BYTE),
TRTRY_PLN_TYP VARCHAR2(200 BYTE),
TRTRY_PYR_GRP_ID VARCHAR2(200 BYTE),
TRTRY_PYR_NM VARCHAR2(200 BYTE),
TRTRY_PYR_PHONE VARCHAR2(200 BYTE),
TRTRY_PYR_TYP VARCHAR2(200 BYTE),
TRTRY_STATE VARCHAR2(200 BYTE),
OTHER_SHIPPED_QTY1 VARCHAR2(200 BYTE),
OTHER_SHIPPED_QTY2 VARCHAR2(200 BYTE),
OTHER_SHIPPED_QTY3 VARCHAR2(200 BYTE),
OTHER_SHIPPED_QTY4 VARCHAR2(200 BYTE),
VOID_DATE VARCHAR2(200 BYTE),
PHRCMY_LOC_NPI_NO VARCHAR2(10 BYTE),
PHRCMY_NM VARCHAR2(100 BYTE),
PRESC_SUFFIX VARCHAR2(20 BYTE),
PRESC_PHONE VARCHAR2(20 BYTE),
DOSAGE_UNIT NUMBER,
DRUG_CLASS_CD VARCHAR2(50 BYTE),
INVOICE_DT DATE,
PAT_DIAGNOSIS_CD_DESC_2 VARCHAR2(15 BYTE),
PAT_THRPY_STRT_REAS VARCHAR2(50 BYTE),
PHRMCY_LOC_ADDR_LINE1 VARCHAR2(100 BYTE),
PHRMCY_LOC_PSTL_ID VARCHAR2(15 BYTE),
SUPPLIER_CD VARCHAR2(50 BYTE),
FILE_REC_SEQ_NO NUMBER,
PAT_AGE NUMBER,
SHIP_FROM_PSTL_ID VARCHAR2(200 BYTE),
BRAND_NM VARCHAR2(100 BYTE),
OTHER_FLEX_COL_1000 DATE,
OTHER_FLEX_COL_90 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_51 NUMBER,
OTHER_FLEX_COL_52 NUMBER,
OTHER_FLEX_COL_53 NUMBER,
OTHER_FLEX_COL_54 NUMBER,
OTHER_FLEX_COL_55 NUMBER,
OTHER_FLEX_COL_56 NUMBER,
OTHER_FLEX_COL_57 NUMBER,
OTHER_FLEX_COL_58 NUMBER,
OUTLET_COT VARCHAR2(200 BYTE),
PAT_THRPY_END_REAS VARCHAR2(200 BYTE),
PRESC_ADDR_FLG VARCHAR2(5 BYTE),
PRESC_NM_FLG VARCHAR2(5 BYTE),
PAT_PSTL_ID VARCHAR2(15 BYTE),
SHIP_TO_ADDR_LINE1 VARCHAR2(200 BYTE),
SHIP_TO_CITY VARCHAR2(200 BYTE),
OTHER_FLEX_COL_22 VARCHAR2(200 BYTE),
PRMRY_STATE VARCHAR2(200 BYTE),
RECORD_TIMESTAMP DATE,
PRMRY_PLN_ANNUAL_MAX_MET VARCHAR2(200 BYTE),
OTHER_FLEX_COL_24 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_25 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_27 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_28 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_29 VARCHAR2(200 BYTE),
OTHER_FLEX_COL_30 VARCHAR2(200 BYTE),
SHIP_TO_ADDR_LINE2 VARCHAR2(200 BYTE),
TRTRY_PYR_KEY NUMBER,
RESTATEMENT_FLAG VARCHAR2(1 BYTE),
SCNDRY_PLN_ANNUAL_MAX_MET VARCHAR2(200 BYTE),
SHIP_TO_OTHR_CUST_NO VARCHAR2(8 BYTE),
PAT_OUT_OF_PCKT_MAX NUMBER,
VOUCHER_NO VARCHAR2(11 BYTE),
ORG_KEY NUMBER,
HUB_SPECIALTY_PHRCMY_ID VARCHAR2(30 BYTE),
ADDR_KEY NUMBER,
HUB_PRESC_ID VARCHAR2(30 BYTE),
SPP_PAT_ID VARCHAR2(50 BYTE)
) ;
Insert into SPP_SHIPMENT
(SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, PAT_DIAGNOSIS_CD_1, PAT_DIAGNOSIS_CD_2, NDC_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, OTHER_FLEX_COL_2, OTHER_FLEX_COL_3, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PRESC_ADDR_LINE1, PRESC_CITY, PRESC_FST_NM, PRESC_LST_NM, PRESC_NPI_NO, PRESC_PSTL_ID, PRESC_STATE, PROD_DESC, TOT_QTY, PHRCMY_NM, FILE_REC_SEQ_NO, PAT_AGE, BRAND_NM, SPP_PAT_ID)
Values
(3462100, '18', 110906, 256189, '279.00', 'D80.1', '00944270007', TO_DATE('10/02/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'GR', 100, 'UNSPECIFIED HYPOGAMMAGLOBULINEMIA', 'NONFAMILIAL HYPOGAMMAGLOBULINEMIA', 795, '100', '300', 4082771, 'SHIRE_HEM_HEALIX_DISPENSE_20181031.TXT', TO_DATE('02/18/2019 05:24:49', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SHIRE_SPP.M_LH_ODS_SPP_SHIPMENT_SHIRE_SPP', '275 COLLIER RD SUITE 450', 'ATLANTA', 'RICHARD', 'HENGEL', 1972605665, '30309', 'GA', 'GAMMAGARD LIQUID 30 GM / 300 ML', 25, 'HEALIX', 235, 79, 'GAMMAGARD LIQUID', '113836');
Insert into LH_LAND.SPP_SHIPMENT
(SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, PAT_DIAGNOSIS_CD_1, PAT_DIAGNOSIS_CD_2, NDC_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, OTHER_FLEX_COL_2, OTHER_FLEX_COL_3, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PRESC_ADDR_LINE1, PRESC_CITY, PRESC_FST_NM, PRESC_LST_NM, PRESC_NPI_NO, PRESC_PSTL_ID, PRESC_STATE, PROD_DESC, TOT_QTY, PHRCMY_NM, FILE_REC_SEQ_NO, PAT_AGE, BRAND_NM, SPP_PAT_ID)
Values
(3462099, '18', 110906, 256189, '279.00', 'D80.1', '00944270007', TO_DATE('10/02/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'GR', -1, 'UNSPECIFIED HYPOGAMMAGLOBULINEMIA', 'NONFAMILIAL HYPOGAMMAGLOBULINEMIA', 795, '100', '300', 4082771, 'SHIRE_HEM_HEALIX_DISPENSE_20181031.TXT', TO_DATE('02/18/2019 05:24:49', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SHIRE_SPP.M_LH_ODS_SPP_SHIPMENT_SHIRE_SPP', '275 COLLIER RD SUITE 450', 'ATLANTA', 'RICHARD', 'HENGEL', 1972605665, '30309', 'GA', 'GAMMAGARD LIQUID 30 GM / 300 ML', 25, 'HEALIX', 235, 79, 'GAMMAGARD LIQUID', '113836');
COMMIT;
CREATE OR REPLACE PROCEDURE LH_LAND.NEGATIVE_SHP_QTY_UPD_4test_1
-- This change is for tottal_qty range check on group level for Healix and Coram ( 18,25) 0 and 99
-- Buminate , Flexbumin , cuvitru ,Gammagard IGA , Gammagrd Liquid Gammagard SD , Hyqvia ( 1,6,2,5)
-- Change made by Harishankar kar on 28th Feb 2019 6:20 PM
-- v1 change : 5th march
IS
v_ods_count NUMBER;
v_hist_count NUMBER;
v_pte_land_count NUMBER;
v_nte_land_count NUMBER;
v_lnd_raw_id_pte NUMBER;
v_lnd_raw_id_nte NUMBER;
v_lnd_over_all_shp_qty VARCHAR2(10);
v_ods_over_all_shp_qty NUMBER;
v_pte_src_id VARCHAR2(10);
v_nte_src_id VARCHAR2(10);
v_pte_stat_flg VARCHAR2(10);
v_nte_stat_flg VARCHAR2(10);
v_err_suc_rec_id NUMBER;
v_ods_pos_shp_qty NUMBER;
v_ods_neg_shp_qty NUMBER;
v_lnd_pos_shp_qty NUMBER;
v_lnd_neg_shp_qty NUMBER;
BEGIN
dbms_output.put_line('Process started : ');
FOR h IN (
SELECT land_raw_id
,src_provider_id
,spp_pat_id
,ndc_no
,shipment_dt
,product_id
,shipped_qty
,src_filename
,file_rec_seq_no
,qc_stat_flg
,error_rec_id
FROM lnd_shipment --lh_land.lnd_spp_shipment_vw
WHERE src_provider_id IN (18, 25)
AND qc_stat_flg IN ('S', 'E')
--AND qc_prod_grp_id in (1,6,2,5)
)
LOOP
dbms_output.put_line('Loop iteration ');
v_pte_land_count:=0;
v_nte_land_count:=0;
v_lnd_raw_id_pte:=0;
v_lnd_raw_id_nte:=0;
BEGIN
SELECT COUNT(*), src_provider_id, max(land_raw_id)
INTO v_pte_land_count, v_pte_src_id, v_lnd_raw_id_pte
FROM --lh_land.lnd_spp_shipment_vw
lnd_shipment
a
WHERE TO_NUMBER(shipped_qty) > 0
AND qc_stat_flg IN ('S','E','I')
--AND QC_PROD_GRP_ID in (1,6,2,5)
AND a.src_provider_id = h.src_provider_id AND a.spp_pat_id = h.spp_pat_id
AND a.ndc_no = h.ndc_no
AND a.shipment_dt = h.shipment_dt
AND a.product_id = h.product_id
GROUP BY src_provider_id;
SELECT qc_stat_flg,shipped_qty into v_pte_stat_flg,v_lnd_pos_shp_qty
FROM lnd_shipment--lh_land.lnd_spp_shipment_vw
WHERE LAND_RAW_ID = v_lnd_raw_id_pte;
dbms_output.put_line('v_lnd_pos_shp_qty is :'||v_lnd_pos_shp_qty);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_pte_land_count := 0 ; --new changes
DBMS_OUTPUT.PUT_LINE('Unable to find +ve records in landing in ' || h.src_filename);
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
BEGIN
SELECT COUNT(*), src_provider_id,max(land_raw_id)
INTO v_nte_land_count, v_nte_src_id, v_lnd_raw_id_nte
FROM lnd_shipment a--lh_land.lnd_spp_shipment_vw a
WHERE TO_NUMBER(shipped_qty) < 0
AND qc_stat_flg IN ('S','E','I')
-- AND QC_PROD_GRP_ID in (1,6,2,5)
AND a.src_provider_id = h.src_provider_id AND a.spp_pat_id = h.spp_pat_id
AND a.ndc_no = h.ndc_no AND a.shipment_dt = h.shipment_dt
AND a.product_id = h.product_id
GROUP BY src_provider_id;
SELECT qc_stat_flg
,shipped_qty
INTO v_nte_stat_flg
,v_lnd_neg_shp_qty
FROM lnd_shipment --lh_land.lnd_spp_shipment_vw
WHERE land_raw_id = v_lnd_raw_id_nte;
dbms_output.put_line('v_lnd_neg_shp_qty is :'||v_lnd_neg_shp_qty);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_nte_land_count := 0 ;
DBMS_OUTPUT.PUT_LINE('Unable to find -ve records in landing in ' || h.src_filename);
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
-- TO FAIL THOSE RECORDS WHICH ARE NOT MAX LAND RAW ID
IF ( v_nte_land_count > 0 AND v_pte_land_count =0 ) THEN
UPDATE lnd_shipment a SET qc_stat_flg = 'F'
WHERE TO_NUMBER(shipped_qty) < 0
AND qc_stat_flg IN ('S','E','I')
-- AND QC_PROD_GRP_ID in (1,6,2,5)
AND a.src_provider_id = h.src_provider_id AND a.spp_pat_id = h.spp_pat_id
AND a.ndc_no = h.ndc_no AND a.shipment_dt = h.shipment_dt
AND a.product_id = h.product_id
AND a.LAND_RAW_ID <> v_lnd_raw_id_nte ;
END IF ;
IF ( v_pte_land_count > 0 AND v_nte_land_count = 0 ) THEN
UPDATE lnd_shipment a SET qc_stat_flg = 'F'
WHERE TO_NUMBER(shipped_qty) > 0
AND qc_stat_flg IN ('S','E','I')
-- AND QC_PROD_GRP_ID in (1,6,2,5)
AND a.src_provider_id = h.src_provider_id AND a.spp_pat_id = h.spp_pat_id
AND a.ndc_no = h.ndc_no AND a.shipment_dt = h.shipment_dt
AND a.product_id = h.product_id
AND a.LAND_RAW_ID <> v_lnd_raw_id_pte ;
END IF ;
IF ( v_pte_land_count > 0 AND v_nte_land_count > 0 ) THEN
UPDATE lnd_shipment a SET qc_stat_flg = 'F'
WHERE TO_NUMBER(shipped_qty) > 0
AND qc_stat_flg IN ('S','E','I')
-- AND QC_PROD_GRP_ID in (1,6,2,5)
AND a.src_provider_id = h.src_provider_id AND a.spp_pat_id = h.spp_pat_id
AND a.ndc_no = h.ndc_no AND a.shipment_dt = h.shipment_dt
AND a.product_id = h.product_id
AND a.LAND_RAW_ID <> v_lnd_raw_id_pte
AND a.LAND_RAW_ID <> v_lnd_raw_id_nte ;
END IF ;
-- END OF CODE
BEGIN
SELECT COUNT(*)
INTO v_ods_count
FROM --lh_ods.ods_spp_shipment a
spp_shipment a
,lh_ods.ods_raw_patient b
WHERE a.pat_key = b.pat_key
AND a.src_key=b.src_key
AND a.src_key = h.src_provider_id
AND b.spp_pat_id = h.spp_pat_id
AND a.ndc_no = h.ndc_no
AND a.shipment_dt=TO_DATE(h.shipment_dt,'MM/DD/YYYY')
AND a.product_id = h.product_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_ods_count:=0;
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
BEGIN
SELECT SUM(TO_NUMBER(shipped_qty))
INTO v_lnd_over_all_shp_qty
FROM --lh_land.lnd_spp_shipment_vw
lnd_shipment
lnd_shp
WHERE src_provider_id IN (18, 25)
AND qc_stat_flg IN ('S','E','I')
AND (lnd_shp.land_raw_id=v_lnd_raw_id_pte OR lnd_shp.land_raw_id=v_lnd_raw_id_nte);
END;
/* followinf code will check if this landrawid is max landraw id or not otherwise it will be fail */
dbms_output.put_line('v ods count is : '|| v_ods_count );
IF v_ods_count = 0
THEN
IF ( v_lnd_over_all_shp_qty < 0 OR v_lnd_over_all_shp_qty > 99 ) AND h.qc_stat_flg in ('S','E') ---AND TO_NUMBER(h.shipped_qty) < 0
THEN
SELECT CASE WHEN NVL(h.error_rec_id,0)=0
THEN lh_common.lh_error_log_record_id_seq.NEXTVAL
ELSE h.error_rec_id
END
INTO v_err_suc_rec_id
FROM dual;
UPDATE --lh_land.lnd_spp_referral_shipment
lnd_shipment
lnd SET qc_stat_flg = 'I', error_rec_id=v_err_suc_rec_id
WHERE lnd.land_raw_id= h.land_raw_id;
COMMIT ;
INSERT INTO lh_qc_file_record_log2 VALUES(v_err_suc_rec_id,808,h.src_filename,h.file_rec_seq_no,sysdate,h.src_provider_id,null);
dbms_output.put_line('HERE ');
END IF;
-- INSERT INTO lh_logging.lh_qc_error_log VALUES(v_err_suc_rec_id,808,H.shipped_qty,NULL,sysdate,H.src_provider_id ,NULL,'F',NULL);
-- COMMIT;
ELSE -- v_ods_count > 0
BEGIN
SELECT SUM(oss.shipped_qty)
INTO v_ods_pos_shp_qty
FROM --lh_ods.ods_spp_shipment oss,
spp_shipment oss,
lh_ods.ods_raw_patient orp--,
--(select * from lh_land.lnd_spp_shipment_vw where src_provider_id in (18,25) and qc_stat_flg in ('S','E')) lnd_shp
WHERE oss.pat_key = orp.pat_key
AND oss.src_key = orp.src_key
AND oss.src_key = h.src_provider_id
AND orp.SPP_PAT_ID = h.SPP_PAT_ID
AND oss.ndc_no = h.ndc_no
AND oss.shipment_dt = TO_DATE(h.shipment_dt,'MM/DD/YYYY')
AND oss.product_id = h.product_id
AND oss.shipped_qty>0
AND NOT EXISTS
(SELECT A.SRC_KEY,B.SPP_PAT_ID,A.NDC_NO,A.SHIPMENT_DT
FROM spp_shipment A
,lh_ods.ods_raw_patient B
WHERE A.SRC_KEY=B.SRC_KEY AND A.PAT_KEY=B.PAT_KEY
AND A.SRC_KEY=OSS.SRC_KEY
AND B.SPP_PAT_ID=ORP.SPP_PAT_ID
AND A.NDC_NO=OSS.NDC_NO
AND A.SHIPMENT_DT=OSS.SHIPMENT_DT
AND A.product_id = h.product_id
AND A.SIGN=CASE WHEN TO_NUMBER(h.shipped_qty)<0 THEN '-' ELSE '+' END
AND A.shipped_qty>0
);
EXCEPTION WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Unable to Find matching +ve pair in LH_ODS' || h.src_filename);
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
BEGIN
SELECT SUM(oss.shipped_qty)
INTO v_ods_neg_shp_qty
FROM -- lh_ods.ods_spp_shipment oss,
spp_shipment oss,
lh_ods.ods_raw_patient orp--,
WHERE oss.pat_key = orp.pat_key
AND oss.src_key = orp.src_key
AND oss.src_key = h.src_provider_id
AND orp.SPP_PAT_ID = h.SPP_PAT_ID
AND oss.ndc_no = h.ndc_no
AND oss.shipment_dt = TO_DATE(h.shipment_dt,'MM/DD/YYYY')
AND oss.product_id = h.product_id
AND oss.shipped_qty<0
AND NOT EXISTS
(SELECT A.SRC_KEY,B.SPP_PAT_ID,A.NDC_NO,A.SHIPMENT_DT
---,A.ORDER_TRAN_NO,A.CLIENT_PROD_CD,A.PROD_STRENGTH
FROM spp_shipment A,lh_ods.ods_raw_patient B
WHERE A.SRC_KEY=B.SRC_KEY AND A.PAT_KEY=B.PAT_KEY
AND A.SRC_KEY=OSS.SRC_KEY
AND B.SPP_PAT_ID=ORP.SPP_PAT_ID
AND A.NDC_NO=OSS.NDC_NO
AND A.SHIPMENT_DT=OSS.SHIPMENT_DT
AND A.product_id=OSS.PRODUCT_ID
AND A.SIGN=OSS.SIGN
AND A.SRC_KEY=h.src_provider_id
AND B.SPP_PAT_ID=h.SPP_PAT_ID
AND A.NDC_NO=h.ndc_no
AND A.SHIPMENT_DT=TO_DATE(h.shipment_dt,'MM/DD/YYYY')
AND A.product_id = h.product_id
AND A.SIGN=CASE WHEN TO_NUMBER(h.shipped_qty)<0 THEN '-' ELSE '+' END
AND A.shipped_qty<0
);
EXCEPTION WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Unable to Find matching -ve pair in LH_ODS' || h.src_filename);
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
IF ( v_lnd_over_all_shp_qty <0 )
THEN
IF (v_nte_land_count> 0 AND v_pte_land_count > 0 )
THEN
UPDATE --lh_land.ln d_spp_referral_shipment lnd
lnd_shipment lnd
SET qc_stat_flg = 'I'
WHERE lnd.land_raw_id= h.land_raw_id;
SELECT CASE WHEN NVL(h.error_rec_id,0)=0
THEN lh_common.lh_error_log_record_id_seq.NEXTVAL
ELSE h.error_rec_id
END
INTO v_err_suc_rec_id
FROM dual;
INSERT INTO LH_QC_ERROR_LOG
VALUES ( v_err_suc_rec_id,808,h.shipped_qty,NULL,SYSDATE,h.src_provider_id ,NULL,'F',NULL );
COMMIT;
ELSE
IF ( v_ods_pos_shp_qty + v_lnd_neg_shp_qty < 0)
THEN
UPDATE --lh_land.lnd_spp_referral_shipment lnd
lnd_shipment lnd
SET qc_stat_flg = 'I'
WHERE lnd.land_raw_id= h.land_raw_id;
/* Formatted on 2019/03/05 08:23 (Formatter Plus v4.8.8) */
SELECT CASE
WHEN NVL (h.error_rec_id, 0) = 0
THEN lh_common.lh_error_log_record_id_seq.NEXTVAL
ELSE h.error_rec_id
END
INTO v_err_suc_rec_id
FROM DUAL;
INSERT INTO LH_QC_ERROR_LOG
VALUES ( v_err_suc_rec_id,808,h.shipped_qty,NULL,SYSDATE,h.src_provider_id ,NULL,'F',NULL );
COMMIT;
END IF;
END IF;
ELSE
IF ( (v_nte_land_count> 0) AND (v_pte_land_count > 0)) THEN
IF (v_lnd_over_all_shp_qty BETWEEN 0 AND 99 )
THEN NULL;
ELSE
dbms_output.put_line('COMING HERE ');
UPDATE --lh_land.lnd_spp_referral_shipment lnd
lnd_shipment lnd
SET qc_stat_flg = 'I'
WHERE lnd.land_raw_id= h.land_raw_id;
COMMIT;
END IF;
END IF ;
IF ( (v_ods_neg_shp_qty + v_lnd_pos_shp_qty) NOT BETWEEN 0 AND 99 )
THEN
UPDATE --lh_land.lnd_spp_referral_shipment lnd
lnd_shipment lnd
SET qc_stat_flg = 'I'
WHERE lnd.land_raw_id= h.land_raw_id;
SELECT CASE
WHEN NVL (h.error_rec_id, 0) = 0
THEN lh_common.lh_error_log_record_id_seq.NEXTVAL
ELSE h.error_rec_id
END
INTO v_err_suc_rec_id
FROM DUAL;
INSERT INTO LH_QC_ERROR_LOG
VALUES ( v_err_suc_rec_id,808,h.shipped_qty,NULL,SYSDATE,h.src_provider_id ,NULL,'F',NULL );
COMMIT;
END IF;
END IF ;
END IF ;
--
-- IF ( h.land_raw_id <> v_lnd_raw_id_pte AND h.land_raw_id <> v_lnd_raw_id_nte)
-- THEN
-- UPDATE --lh_land.lnd_spp_referral_shipment lnd
-- lnd_shipment lnd
-- SET qc_stat_flg = 'F'
-- WHERE lnd.land_raw_id= h.land_raw_id;
-- SELECT CASE
-- WHEN NVL (h.error_rec_id, 0) = 0
-- THEN lh_common.lh_error_log_record_id_seq.NEXTVAL
-- ELSE h.error_rec_id
-- END
-- INTO v_err_suc_rec_id
-- FROM DUAL;
--
-- INSERT INTO lh_qc_error_log
-- VALUES ( v_err_suc_rec_id,808,h.shipped_qty,NULL,SYSDATE,h.src_provider_id ,NULL,'F',NULL );
-- COMMIT;
--
-- END IF ;
END LOOP;
UPDATE
lnd_shipment
SET qc_stat_flg = 'F'
WHERE qc_stat_flg = 'I';
COMMIT;
END;
/