SELECT DISTINCT 'PCS' AS TPACode, CASE WHEN LEN(deal.sdealer_number)>10
      AND LEFT(deal.sdealer_number,3)='DTD' THEN 'DTDG' + CONVERT(VARCHAR(6),RIGHT(LTRIM(RTRIM(deal.sdealer_number)),6))
ELSE deal.sdealer_number END AS DealerNumber, con.scontract_no AS AgreementNumber, NULL AS AgreementSuffix,
clm.sclaim_number AS ClaimNumber, clmdet.sjob_no AS ClaimSequenceNumber, 
ISNULL(CONVERT(VARCHAR(10),tcm.UniComponentCode),'UNMAPPED') AS ComponentCode, 
CASE clm.sclaim_status WHEN 'P' THEN 'C' WHEN 'O' THEN 'P' WHEN 'V' THEN 'N' WHEN 'D' THEN 'D' ELSE NULL END AS ClaimStatus, 
sjccc.scomplaint AS Complaint, CONVERT(CHAR(10),MIN(clmhist.dtcycle_date),101) AS BusinessDate, 
   /* Per BU, use the minimum dtcycle_date since there are lots of dates in the history table*/
CONVERT(CHAR(10),clm.dtro_received,101) AS ReportedDate, CONVERT(CHAR(10),ISNULL(clm.dtdate_loss_occurred,clm.dtro_received),101) AS FailedDate, 
clm.iodometer_at_time_of_loss AS FailedMileage, ssc.sservice_name AS RepairFacilityName, NULL AS FailedComponentFlag,
clmdet.ntax_percent AS TaxRate, cp.cauth_unit_cost AS PartCost, CASE cp.sdetail_Type WHEN'P' THEN cp.nauth_qty ELSE 0.00 END AS PartQuantity, 
CASE cp.sdetail_Type WHEN'L' THEN cp.nauth_qty ELSE 0.00 END AS LaborHours,
clmdet.clabor_rate AS LaborRate, CASE clmdet.sdetail_Type WHEN'P' THEN clmdet.ctax_amt_1 ELSE 0.00 END AS PartTotalTax, 
CASE clmdet.sdetail_Type WHEN'L' THEN clmdet.ctax_amt_1 ELSE 0.00 END AS LaborTotalTax,
  clmdet.cauth_total AS ComponentTotalCost 
,
CASE cst.sclaim_det_status_code WHEN 'PD' THEN 'C' ELSE 'N' END AS PaymentStatus,
--This column has 2 character values PD (paid), AP (approved to pay), AU (authorized), DE (denied), and RE (requested).
-- All other values mean "not paid."
CONVERT(CHAR(10),(CASE WHEN cp.dtpaid IS NULL THEN clmdet.dtpaid ELSE cp.dtpaid END),101) AS CheckDate, 
CASE WHEN cp.scheck_number IS NULL AND clmdet.scheck_number IS NOT NULL THEN clmdet.scheck_number 
    WHEN cp.scheck_number IS NOT NULL AND clmdet.scheck_number IS NULL THEN cp.scheck_number ELSE NULL END AS CheckNumber, 
 ISNULL(SUM(clmdet.cauth_total),0.00) AS CheckAmount 
, NULL AS CCAuthorization, NULL AS TransientFlag, 'M' AS DateFormatType,
scr.scancel_reason_code AS DeniedReasonCode, con.cdeductible_amount AS DeductibleAmount 
FROM ASA_Auto.dbo.scs_claims clm
INNER JOIN ASA_Auto.dbo.scs_contracts con
ON clm.icontract_id = con.icontract_id
INNER JOIN ASA_Auto.dbo.scs_dealers deal
ON con.sdealer_number = deal.sdealer_number
INNER JOIN ASA_Auto.dbo.scs_claim_jobs cj
ON clm.iclaim_ID = cj.iclaim_ID
INNER JOIN ASA_Auto.dbo.scs_claim_details clmdet
ON clm.iclaim_ID = clmdet.iclaim_ID
AND cj.sjob_no = clmdet.sjob_no
AND clmdet.sjob_no = cj.sjob_no
INNER JOIN ASA_Auto.dbo.scs_claim_detail_status cst  
ON clmdet.iclaim_det_status_id = cst.iclaim_det_status_id
LEFT OUTER JOIN (SELECT UniComponentCode, ComponentOriginator, OriginatorComponentCode
                FROM Auto_DB.dbo.tblComponentMapping
                WHERE ComponentOriginator = 'Test CREEK') tcm
ON LTRIM(RTRIM(clmdet.sloss_code)) = LTRIM(RTRIM(tcm.OriginatorComponentCode))
LEFT OUTER JOIN ASA_Auto.dbo.scs_claim_parts cp
ON clm.iclaim_ID = cp.iclaim_ID
AND clmdet.sjob_no = cp.sjob_no
AND clmdet.sdetail_Type = cp.sdetail_Type
LEFT OUTER JOIN ASA_Auto.dbo.scs_claim_jobs_complaint_cause_correction sjccc
ON cj.ijob_id = sjccc.ijob_id
LEFT OUTER JOIN ASA_Auto.dbo.scs_service_centers ssc
ON clm.iservice_center_ID = ssc.iservice_ID
LEFT OUTER JOIN ASA_Auto.dbo.scs_cancel_reason scr
ON con.icancel_reason_id = scr.icancel_reason_ID
LEFT OUTER JOIN ASA_Auto.dbo.scs_claim_details_history clmhist
ON clm.iclaim_id = clmhist.iclaim_id
WHERE con.icontract_type = 1
AND con.scontract_no='VSE00146887'
AND clm.sclaim_number='C000045326'
GROUP BY deal.sdealer_number, con.scontract_no, clm.sclaim_number, clmdet.sjob_no, tcm.UniComponentCode, clm.sclaim_status, 
sjccc.scomplaint, clm.dtro_received, clm.dtdate_loss_occurred, clm.iodometer_at_time_of_loss, ssc.sservice_name, 
clmdet.ntax_percent, cp.cauth_unit_cost, cp.nauth_qty, clmdet.nreq_qty, clmdet.clabor_rate, cp.ctax_amt_1, clmdet.ctax_amt_1, 
clmdet.cauth_total,cst.sclaim_det_status_code, cp.dtpaid, clmdet.dtpaid, cp.scheck_number, clmdet.scheck_number, clmdet.camt_paid, 
scr.scancel_reason_code, clmdet.sdetail_Type, con.cdeductible_amount, cp.sdetail_Type;




GO