SELECT AGR_SEQ, AGR_AGREEMENT_NO, AGR_TMBAND_STATUS, AGR_CAR_CODE, AGR_CAR_CARRIER_TYPE, AGR_TGP_SLOT_DURATION, AGL_SEQ, AGL_DIR_TRAN, AGL_LOC_PREFIX, AGL_LOC_CON_CODE, AGL_MAJ_CODE, AGL_CUR_CODE, AGL_BILL_ADMIN, ARA_SEQ, DECODE(:B1 , 'S', ARA_OUT_PAY_RATE, 'R', ARA_IN_PAY_RATE) ARA_RATE, ARA_TBT_CODE, ARA_REFILE_PERCENT, ARA_FOR_CODE FROM IS_M_AGR_ROUTE_RATE, IS_R_ROUTE_DTL, IS_M_AGREEMENT_LINE, IS_M_AGREEMENT WHERE DECODE(AGR_TMBAND_STATUS, 'N', '%', 'Y', ARA_TBT_CODE) = DECODE(AGR_TMBAND_STATUS, 'N', '%', 'Y', PK_IS_PRICING.FN_GET_TBT_INSIDE_SQL(:B3 , AGL_MAJ_CODE)) AND (ARA_VALID_STOP IS NULL OR ARA_VALID_STOP >= TRUNC(:B3 )) AND ARA_VALID_START <= TRUNC(:B3 ) AND ARA_FAC_CODE = :B7 AND ARA_AGL_SEQ = AGL_SEQ AND AGL_LOC_PREFIX = SUBSTR(:B6 , :B5 , LENGTH(AGL_LOC_PREFIX)) AND AGL_RGP_CODE = ROU_RGP_CODE AND ROU_CODE = :B4 AND (AGL_VALID_STOP IS NULL OR AGL_VALID_STOP >= TRUNC(:B3 )) AND AGL_VALID_START <= TRUNC(:B3 ) AND AGL_AGR_SEQ = AGR_SEQ AND (AGR_VALID_STOP IS NULL OR AGR_VALID_STOP >= TRUNC(:B3 )) AND AGR_VALID_START <= TRUNC(:B3 ) AND AGR_STY_CODE = :B2 AND AGR_IN_OUT_FLAG = :B1 AND LENGTH(AGL_LOC_PREFIX) = (SELECT MAX(LENGTH(AGL_LOC_PREFIX)) FROM IS_M_AGR_ROUTE_RATE, IS_R_ROUTE_DTL, IS_M_AGREEMENT_LINE, IS_M_AGREEMENT WHERE DECODE(AGR_TMBAND_STATUS, 'N', '%', 'Y', ARA_TBT_CODE) = DECODE(AGR_TMBAND_STATUS, 'N', '%', 'Y', PK_IS_PRICING.FN_GET_TBT_INSIDE_SQL(:B3 , AGL_MAJ_CODE)) AND (ARA_VALID_STOP IS NULL OR ARA_VALID_STOP >= TRUNC(:B3 )) AND ARA_VALID_START <= TRUNC(:B3 ) AND ARA_FAC_CODE = :B7 AND ARA_AGL_SEQ = AGL_SEQ AND AGL_LOC_PREFIX = SUBSTR(:B6 , :B5 , LENGTH(AGL_LOC_PREFIX)) AND AGL_RGP_CODE = ROU_RGP_CODE AND ROU_CODE = :B4 AND (AGL_VALID_STOP IS NULL OR AGL_VALID_STOP >= TRUNC(:B3 )) AND AGL_VALID_START <= TRUNC(:B3 ) AND AGL_AGR_SEQ = AGR_SEQ AND (AGR_VALID_STOP IS NULL OR AGR_VALID_STOP >= TRUNC(:B3 )) AND AGR_VALID_START <= TRUNC(:B3 ) AND AGR_STY_CODE = :B2 AND AGR_IN_OUT_FLAG = :B1 )