Depot Repair Useful SQL queries

Query to fetch the repair order details, repair type and repair flow status for all the repair orders having repair flow status as ‘Rcvd at Repair Centre’ and repair type as 'Warranty - Repair and Return'

SELECT cr.repair_number, cr.serial_number, cr.repair_line_id,

                    crtv.NAME repair_type, crfs.flow_status_meaning,cr.*

  FROM apps.csd_repairs cr,

                      apps.csd_repair_types_vl crtv,

                      apps.csd_repair_flow_statuses_v crfs

           WHERE cr.repair_type_id = crtv.repair_type_id

               AND cr.flow_status_id = crfs.flow_status_id

               AND crfs.flow_status_meaning = 'Rcvd at Repair Centre'—Enter reqd flow status

               AND crtv.name = 'Warranty - Repair and Return';—Enter rqd repair type

 

Query to fetch the logistic line details for an RO consisting of the SHIP or RMA line details based on action_type passed for a particular repair order.

SELECT cr.repair_number, cr.repair_line_id, cpt.action_type, cpt.action_code,

                 cpt.prod_txn_status, cpt.sub_inventory, cpt.source_serial_number

  FROM apps.csd_repairs cr, apps.csd_product_transactions cpt

WHERE cr.repair_line_id = cpt.repair_line_id

   AND action_type = 'SHIP'    -- or RMA

   AND cpt.prod_txn_status <> 'CANCELLED'

   AND cr.repair_number = :p_repair_number

        AND cpt.product_transaction_id IN (

                                             SELECT MAX (cpt1.product_transaction_id)

                  FROM apps.csd_product_transactions cpt1

                WHERE action_type = 'SHIP'     --RMA   

       AND cpt.prod_txn_status <> 'CANCELLED'                                    

                                                                        AND cpt1.repair_line_id = cr.repair_line_id)

 

Query to fetch the SHIP and RMA line details for the handset in a single query for all the repair orders

 

SELECT cr.repair_number, crtv.NAME repair_type, cr.creation_date,

       cr.repair_line_id, cpt.action_type ship_action_type,

       cpt.action_code ship_action_code,

       cpt.prod_txn_status ship_prod_txn_status,

       cpt.source_serial_number ship_ser_num, a.action_type rma_action_type,

       a.action_code rma_action_code, a.prod_txn_status rma_prod_txn_status,

       a.source_serial_number rma_ser_num

  FROM apps.csd_repairs cr,

       apps.csd_product_transactions cpt,

       apps.csd_repair_types_vl crtv,

       (SELECT cpt.action_type, cpt.action_code, cr.repair_line_id,

               cpt.prod_txn_status, cpt.sub_inventory,

               cpt.source_serial_number

          FROM apps.csd_product_transactions cpt, apps.csd_repairs cr

         WHERE cr.repair_line_id = cpt.repair_line_id

           AND cpt.action_type = 'RMA'                               -- or RMA

           AND cpt.prod_txn_status <> 'CANCELLED'

           AND cpt.product_transaction_id IN (

                  SELECT MAX (cpt2.product_transaction_id)

                    FROM apps.csd_product_transactions cpt2

                   WHERE cpt2.action_type = 'RMA'                        --RMA

                     AND cpt2.prod_txn_status <> 'CANCELLED'

                     AND cpt2.repair_line_id = cr.repair_line_id)) a

WHERE cr.repair_line_id = cpt.repair_line_id

   AND cpt.action_type = 'SHIP'                                      -- or RMA

   AND cpt.prod_txn_status <> 'CANCELLED'

--   AND cr.repair_number = 'RR1-49243876655'

   AND TRUNC (cr.creation_date) BETWEEN '01-MAR-2013' AND '17-APR-2013'

   AND a.repair_line_id = cr.repair_line_id

   AND cpt.product_transaction_id IN (

          SELECT MAX (cpt1.product_transaction_id)

            FROM apps.csd_product_transactions cpt1

           WHERE action_type = 'SHIP'                                    --RMA

             AND cpt.prod_txn_status <> 'CANCELLED'

             AND cpt1.repair_line_id = cr.repair_line_id)

   AND cr.repair_type_id = crtv.repair_type_id

 

 

Query to fetch the estimate lines for an RO where original source code = ‘DR’, and passing the repair org and repair order number.

 

SELECT cr.repair_number, cr.repair_line_id, cre.repair_estimate_id,

               crel.repair_estimate_line_id, ced.estimate_detail_id,

                      ced.no_charge_flag, crel.override_charge_flag price_override,

                 msi.segment1 item, msi.description, msi.material_billable_flag,

                 ced.selling_price unit_price, ced.after_warranty_cost total_price

        FROM apps.csd_repairs cr,

                  apps.cs_estimate_details ced,

                  apps.csd_repair_estimate cre,

                  apps.csd_repair_estimate_lines crel,

                  apps.mtl_system_items_b msi

WHERE cr.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 = :p_org_id – Repair Centre Org Id

   AND cr.repair_number = :p_ro_number;

 

 

Query to fetch the wip job details for an RO by passing the repair org and repair order number having operation seq = 10

 

SELECT   cr.repair_number, cr.repair_line_id, crtv.NAME repair_type,

              crfs.flow_status_meaning, cr.creation_date,

              msib.segment1 component_item, msib.description component_description,

              msib.inventory_item_id component_item_id,

              NVL (cwtd.transaction_quantity,

              (wro.required_quantity - wro.quantity_issued

              )

             ) required_qty,

         DECODE (wro.quantity_issued,

                 0, TO_NUMBER (NULL),

                 wro.quantity_issued

                ) issued_qty,

         we.wip_entity_name wip_job, msib.material_billable_flag

    FROM apps.csd_repairs cr,

         apps.csd_repair_job_xref crjx,

         apps.wip_requirement_operations wro,

         apps.mtl_system_items_b msib,

         apps.wip_entities we,

         apps.csd_wip_transaction_details cwtd,

         apps.wip_discrete_jobs wdj,

         apps.wip_operations wo,

         apps.csd_repair_types_vl crtv,

         apps.csd_repair_flow_statuses_v crfs

   WHERE crjx.repair_line_id = cr.repair_line_id

     AND crjx.wip_entity_id = wro.wip_entity_id

     AND wro.inventory_item_id = msib.inventory_item_id

     AND wro.organization_id = msib.organization_id

     AND wro.wip_entity_id = we.wip_entity_id

     AND wro.wip_entity_id = wdj.wip_entity_id

     AND cwtd.inventory_item_id(+) = wro.inventory_item_id

     AND cwtd.wip_entity_id(+) = wro.wip_entity_id

     AND wro.wip_entity_id = wo.wip_entity_id

     AND wro.organization_id = wo.organization_id

     AND wro.operation_seq_num = wo.operation_seq_num

     AND wro.operation_seq_num = 10

     AND wdj.status_type <> 12

     AND wro.inventory_item_id NOT IN (

                                SELECT csd.inventory_item_id

                                  FROM apps.csd_repairs csd

                                 WHERE csd.repair_line_id =

                                                           crjx.repair_line_id)

     AND msib.organization_id = :p_org_id – Repair Centre Org Id

     AND cr.repair_type_id = crtv.repair_type_id

     AND cr.flow_status_id = crfs.flow_status_id

     AND cr.repair_number = :p_ro_number;

 

 

 

Query to fetch repair actual details and item details for an RO with material billable flag for the item as ‘M’.

 

SELECT   cr.repair_number, cr.repair_line_id, crtv.NAME repair_type,

                  crfs.flow_status_meaning, cr.creation_date, msi.segment1 item,

         msi.description item_description, msi.m  aterial_billable_flag,

              ced.selling_price unit_price, ced.after_warranty_cost total_price

    FROM apps.csd_repairs cr,

             apps.csd_repair_actuals cra,

             apps.csd_repair_actual_lines cral,

             apps.cs_estimate_details ced,

             apps.mtl_system_items msi,

             apps.csd_repair_types_vl crtv,

            apps.csd_repair_flow_statuses_v crfs

   WHERE cr.repair_line_id = cra.repair_line_id

       AND cra.repair_actual_id = cral.repair_actual_id

            AND cral.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 = :p_org_id – Repair Centre Org Id

       AND msi.material_billable_flag = 'M'

       AND cr.repair_type_id = crtv.repair_type_id

       AND cr.flow_status_id = crfs.flow_status_id

            AND cr.repair_number = :p_ro_number;

 

Query to fetch the active customers in EBS having the account status =’A’

 

SELECT hp.party_name customer_name, hca.account_number account_number,                              hps.party_site_id, hp.party_id party_id,hca.attribute1 Partner_Code,                 hca.status  Customer_Account_Status,

            hps.identifying_address_flag, hps.status party_site_status

  FROM apps.hz_cust_accounts hca, apps.hz_parties hp, apps.hz_party_sites hps

WHERE hca.party_id = hp.party_id

   AND hca.status = 'A'

   AND hp.party_id = hps.party_id

   order by hp.party_id

 

 

Query to fetch the address details for active EBS customer accounts defined with site use  as ‘BILL_TO’

 

SELECT hp.party_name customer_name, hca.account_number account_number,

            hps.party_site_id, hp.party_id party_id,hca.attribute1 Partner_Code,                        hca.status Customer_Account_Status,

            hps.identifying_address_flag, hps.status                                                                               party_site_status,hps.party_site_number,hl.address1||','||hl.address2||','||hl. address3||'           '||hl.city||' '||hl.postal_code||'      '||hl.state||','||decode(hl.country,'AU','Australia') address

FROM apps.hz_parties hp,

          apps.hz_cust_accounts hca,

          apps.hz_cust_acct_sites_all hzcas,

          apps.hz_party_site_uses hzpsu,

          apps.hz_party_sites hps,

          apps.hz_locations hl

WHERE hp.party_id = hca.party_id

   AND hp.status = 'A'         

   AND hca.status = 'A'

   AND hzcas.cust_account_id = hca.cust_account_id

   AND hzcas.status = 'A'

   AND hzcas.party_site_id = hzpsu.party_site_id

   AND hzpsu.site_use_type = 'BILL_TO'

   AND hzcas.party_site_id = hps.party_site_id

   AND hps.location_id = hl.location_id

   AND hzpsu.status = 'A'

   AND hzpsu.primary_per_type = 'Y'

 

Query to fetch the incoming IMEI and shipped IMEI for an RO

 

          SELECT cr.repair_number, cpt.prod_txn_status item_in_status,

                   msi.segment1 in_item,

           (SELECT msi1.segment1

          FROM apps.csd_product_transactions cpt1,

               apps.cs_estimate_details ced1,

               apps.mtl_system_items_b msi1

         WHERE cpt1.repair_line_id = cr.repair_line_id

           AND cpt1.action_type = 'SHIP'

           AND cpt1.prod_txn_status <> 'CANCELLED'

           AND cpt1.estimate_detail_id = ced1.estimate_detail_id

           AND ced1.inventory_item_id = msi1.inventory_item_id

           AND ced1.transaction_inventory_org = msi1.organization_id)

                                                                     out_item,

       (SELECT cpt1.prod_txn_status imei_out_status

          FROM apps.csd_product_transactions cpt1

         WHERE cpt1.repair_line_id = cr.repair_line_id

           AND cpt1.action_type = 'SHIP'

           AND cpt1.prod_txn_status <> 'CANCELLED') imei_out_status

  FROM apps.csd_repairs cr,

       apps.csd_product_transactions cpt,

       apps.cs_estimate_details ced,

       apps.mtl_system_items_b msi

WHERE cr.repair_line_id = cpt.repair_line_id

   AND cpt.action_type = 'RMA'

   AND cpt.prod_txn_status <> 'CANCELLED'

   AND cpt.estimate_detail_id = ced.estimate_detail_id

   AND ced.inventory_item_id = msi.inventory_item_id

   AND ced.transaction_inventory_org = msi.organization_id

   AND cr.repair_number = :p_ro_number;

 

Query to fetch RO created in one RC and received in another RC (RC-Repair Centre)with repair floe status as ‘Work In Progress ’ and ‘Rcvd at Repair Centre’

 

SELECT cr.repair_number, cr.creation_date,

                     mp1.organization_code ro_created_repair_centre,

               mp2.organization_code ro_rcvd_repair_centre

  FROM apps.csd_repairs cr,

                    apps.csd_product_transactions cpt,

              apps.cs_estimate_details ced,

                    apps.csd_repair_flow_statuses_v crfs,

              apps.mtl_parameters mp1,

              apps.mtl_parameters mp2

WHERE cr.repair_line_id = cpt.repair_line_id

   AND cpt.estimate_detail_id = ced.estimate_detail_id

   AND cr.flow_status_id = crfs.flow_status_id

   AND crfs.flow_status_id IN (1168, 1160)-- Work in Progress, Rcvd at Repair Centre

   AND cpt.action_type = 'RMA'

   AND cpt.prod_txn_status <> 'CANCELLED'

   AND cr.inventory_org_id <> ced.transaction_inventory_org

             AND cr.inventory_org_id = mp1.organization_id

             AND ced.transaction_inventory_org = mp2.organization_id

                AND cr.repair_type_id = 10060;-- Warranty - Repair and Return

 

 

Query to fetch the instance details for a serial numbers passing the inventory item id and serial number

select * from apps.csi_item_instances

where INVENTORY_ITEM_ID= :p_inv_item_id

and serial_number=:p_ser_num

 

 

Query to fetch the install base errors for items which are defined as Install Base.

 

select * from csi_txn_errors;

 

 

 

Query to fetch the records stuck in receiving interface tables for repair orders.

 

          SELECT cr.repair_number

                   , cpt.prod_txn_status rma_line_status

                   , crfs.flow_status_meaning ro_status

                   , oeh.order_number

            FROM apps.cs_estimate_details ced

                      ,apps.csd_product_transactions cpt

                      ,apps.csd_repairs cr

                      ,apps.csd_repair_flow_statuses_v crfs

                      ,apps.oe_order_headers_all oeh

              WHERE ced.estimate_detail_id = cpt.estimate_detail_id

                AND cpt.action_type = 'RMA'

                AND cpt.repair_line_id = cr.repair_line_id

                AND cr.flow_status_id = crfs.flow_status_id

                AND EXISTS (SELECT 1

                      FROM apps.rcv_transactions_interface rti

                     WHERE rti.oe_order_line_id = ced.order_line_id)

                AND ced.order_header_id = oeh.header_id';