CSD Depot Repair XMLP Estimate
Description
Categories: BI Publisher
Application: Depot Repair
Source: Depot Repair XMLP Estimate Report
Short Name: CSDERT
DB package: CSD_Repair_Estimate_Report_Pvt
Source: Depot Repair XMLP Estimate Report
Short Name: CSDERT
DB package: CSD_Repair_Estimate_Report_Pvt
Run
CSD Depot Repair XMLP Estimate and other Oracle EBS reports with Blitz Report™ on our demo environment
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' |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Repair Line Id |
|
Number |