CSD Depot Repair XMLP Estimate

SELECT
DRA.REPAIR_NUMBER,CRE.ESTIMATE_STATUS QUOTE_STATUS ,
DRA.PROMISE_DATE,
PARTY.PARTY_NAME CUSTOMER,
bill_to_party.party_name Bill_To_Customer,
 decode (SR_CONT.contact_type, 'EMPLOYEE' ,FND2.FIRST_NAME||' '||FND2.LAST_NAME,
        'PARTY_RELATIONSHIP',SUB_PARTY.PERSON_FIRST_NAME || ' ' || SUB_PARTY.PERSON_LAST_NAME ) CONTACT,
                ACCOUNT.ACCOUNT_NUMBER ACC_NO,
                HL.ADDRESS1,
                HL.ADDRESS2,
                HL.ADDRESS3,
                HL.ADDRESS4,
                HL.CITY||', '||HL.STATE||'-'||HL.POSTAL_CODE CITYSTCODE,
        (SELECT HCP.RAW_PHONE_NUMBER 
         FROM HZ_CONTACT_POINTS HCP
     WHERE HCP.OWNER_TABLE_ID = HPS.PARTY_ID
     AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND HCP.PRIMARY_FLAG = 'Y'
     AND HCP.CONTACT_POINT_TYPE = 'PHONE') TEL,
        INC.INCIDENT_NUMBER SR_NO,
        INC.CONTRACT_NUMBER,
        ITEMS.CONCATENATED_SEGMENTS PRODUCT_NUM,
        ITEMS.DESCRIPTION ITEM_DESC , 
        DECODE(DRA.CUSTOMER_PRODUCT_ID,  '',  DRA.SERIAL_NUMBER,  CP.SERIAL_NUMBER) SERIAL_NUMBER,
        STT.NAME TRANSACTION_TYPE,
        MSI.CONCATENATED_SEGMENTS PRODUCT,
        MSI.DESCRIPTION, 
        CED.UNIT_OF_MEASURE_CODE UOM,
        CED.QUANTITY_REQUIRED QTY,
        CED.SELLING_PRICE,
        CED.AFTER_WARRANTY_COST  CHARGE,
CREL.repair_estimate_line_id,
                cre.repair_estimate_id, 
        CSD_Repair_Estimate_Report_Pvt.cf_cust_poformula() CF_CUST_PO, 
                INC.INCIDENT_NUMBER SR_NO,INC.CONTRACT_NUMBER,
                ITEMS.CONCATENATED_SEGMENTS PRODUCT_NUM,
                ITEMS.DESCRIPTION ITEM_DESC , 
                DECODE(DRA.CUSTOMER_PRODUCT_ID,  '',  DRA.SERIAL_NUMBER,  CP.SERIAL_NUMBER) SERIAL_NUMBER,
STT.NAME TRANSACTION_TYPE,
MSI.CONCATENATED_SEGMENTS PRODUCT,
MSI.DESCRIPTION, 
CED.UNIT_OF_MEASURE_CODE UOM,
CED.QUANTITY_REQUIRED QTY,
CED.SELLING_PRICE,
CED.AFTER_WARRANTY_COST  CHARGE,
CREL.repair_estimate_line_id,
cre.repair_estimate_id 
FROM
CSI_ITEM_INSTANCES CP,
MTL_SYSTEM_ITEMS_VL ITEMS,
CSD_REPAIRS DRA,
csd_repair_estimate cre,
cs_estimate_details ced,
cs_transaction_types_vl stt,
csd_repair_estimate_lines crel,
cs_txn_billing_types sbt ,
mtl_system_items_vl msi,
CS_INCIDENTS_ALL_B         INC,
JTF_PARTIES_ALL_V          PARTY,
JTF_PARTIES_ALL_V  bill_to_party,
JTF_CUST_ACCOUNTS_ALL_V    ACCOUNT,
CS_HZ_SR_CONTACT_POINTS    SR_CONT,
HZ_PARTIES CONT_PARTY,
HZ_RELATIONSHIPS PARTY_REL,
HZ_PARTIES SUB_PARTY,
PER_ALL_PEOPLE_F FND2,
HZ_PARTY_SITES HPS ,
HZ_PARTY_SITE_USES HPSU ,
HZ_LOCATIONS HL
WHERE
    DRA.REPAIR_LINE_ID = :P_REPAIR_LINE_ID
    AND DRA.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID 
    AND ITEMS.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
    AND DRA.CUSTOMER_PRODUCT_ID = CP.INSTANCE_ID(+)
    AND DRA.repair_line_id = cre.repair_line_id 
    AND cre.repair_estimate_id = crel.repair_estimate_id 
    AND crel.estimate_detail_id = ced.estimate_detail_id 
    AND ced.original_source_code = 'DR' 
    AND ced.inventory_item_id = msi.inventory_item_id 
    AND msi.organization_id = cs_std.get_item_valdn_orgzn_id 
    AND sbt.txn_billing_type_id = ced.txn_billing_type_id 
    AND sbt.transaction_type_id = stt.transaction_type_id
     AND INC.CUSTOMER_ID = PARTY.PARTY_ID 
     AND INC.ACCOUNT_ID  = ACCOUNT.CUST_ACCOUNT_ID  
     AND INC.INCIDENT_ID = SR_CONT.INCIDENT_ID (+)  
     AND SR_CONT.PARTY_ID = CONT_PARTY.PARTY_ID (+) 
     AND SR_CONT.PRIMARY_FLAG (+) = 'Y' 
     AND DECODE(SR_CONT.contact_type, 'EMPLOYEE', SR_CONT.party_id, NULL) = FND2.PERSON_ID(+)
     AND TRUNC(SYSDATE) BETWEEN NVL(FND2.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) 
     AND NVL(FND2.EFFECTIVE_END_DATE, TRUNC(SYSDATE+1)) 
     AND CONT_PARTY.PARTY_ID = PARTY_REL.PARTY_ID(+) 
     AND PARTY_REL.SUBJECT_ID = SUB_PARTY.PARTY_ID(+)  
    AND INC.BILL_TO_SITE_USE_ID = HPSU.PARTY_SITE_USE_ID
    AND HPSU.PARTY_SITE_ID =HPS.PARTY_SITE_ID
        AND HPS.LOCATION_ID = HL.LOCATION_ID
    and hps.party_id = bill_to_party.party_id
    and DRA.INCIDENT_ID = INC.INCIDENT_ID
    AND ced.interface_to_oe_flag <> 'Y' 
    AND ced.order_header_id IS NULL 
    AND ced.order_line_id IS NULL
    AND PARTY_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND PARTY_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND PARTY_REL.DIRECTIONAL_FLAG (+) = 'F'