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' |