Advanced Collections – XML Request History

-- ##############################################################################
--      BASIC XML REQUESTS HISTORY
-- ##############################################################################

    SELECT ixrh.xml_request_id
         , ixrh.creation_date xml_cr_date
         , ixrh.xmldata
         , ixrh.method
         , ixrh.document_type
         , ixrh.destination
         , ixrh.failure_reason
      FROM iex.iex_xml_request_histories ixrh
     WHERE 1 = 1
       AND ixrh.creation_date > '01-JUN-2016'
--       AND ixrh.method = 'EMAIL'
       AND 1 = 1;

-- ##############################################################################
--      WITH LINK TO DELINQUENCIES
-- ##############################################################################

    SELECT ida.delinquency_id
         , ida.creation_date del_created
         , ida.last_update_date del_updated
         , ida.status del_status
         , hca.account_number
         , hca.account_name
         , rcta.trx_number
         , rcta.customer_trx_id
         , rcta.creation_date trx_created
         , rcta.last_update_date trx_updated
         , rcta.term_id
         , ixrh.xml_request_id
         , ixrh.creation_date xml_cr_date
         , ixrh.xmldata
         , ixrh.method
         , ixrh.document_type
         , ixrh.destination
         , ixrh.failure_reason
         , rtt.name term_trx
         , rtt.description
         , rbsa.name batch_source
      FROM iex.iex_delinquencies_all ida
      JOIN ar.hz_cust_accounts hca            ON ida.cust_account_id =           hca.cust_account_id
      JOIN ar.ra_customer_trx_all rcta        ON ida.transaction_id =            rcta.customer_trx_id
      JOIN iex.iex_xml_request_histories ixrh ON ixrh.party_id =                 hca.party_id
      JOIN ar.ra_terms_tl rtt                 ON rcta.term_id =                  rtt.term_id
      JOIN ar.ra_batch_sources_all rbsa       ON rbsa.batch_source_id =          rcta.batch_source_id
     WHERE 1 = 1
--       AND hca.account_name LIKE 'Bob%'
--       AND hca.account_number = 1234
       AND TRUNC(ixrh.creation_date) = '15-MAR-2016'
       AND ixrh.method = 'EMAIL'
--       AND ida.delinquency_id = 1568432
--       AND ixrh.creation_date > '01-MAR-2016'
--       AND dbms_lob.instr(ixrh.xmldata,'PAYMENT_HISTORY_ROW') = 0 -- blank letter
--       AND rcta.trx_number IN ('12345678')
--       AND ida.status = 'DELINQUENT'
--       AND hca.account_number = '1234'
       AND 1 = 1
  ORDER BY ida.creation_date DESC;

-- basic IEX_XML_REQUEST_HISTORIES

    select ixrh.xml_request_id
         , ixrh.query_temp_id
         , ixrh.status
         , ixrh.document
         , ixrh.xmldata
         , dbms_lob.instr(ixrh.xmldata,'PAYMENT_HISTORY_ROW') pay_hist
         , ixrh.method
         , ixrh.destination
         , ixrh.object_type
         , ixrh.object_id
         , ixrh.view_by
         , ixrh.resource_id
         , ixrh.party_id
         , ixrh.cust_account_id
         , ixrh.cust_site_use_id
         , ixrh.delinquency_id
         , ixrh.created_by
         , ixrh.creation_date
         , ixrh.last_update_date
         , ixrh.last_updated_by
         , ixrh.last_update_login
         , ixrh.object_version_number
         , ixrh.html_document
         , ixrh.subject
         , ixrh.request_id
         , ixrh.worker_id
         , ixrh.confirmation_mode
         , ixrh.conc_request_id
         , ixrh.language
         , ixrh.territory
         , ixrh.printer_uri
         , ixrh.job_id
         , ixrh.sjob_id
         , ixrh.job_uri
         , ixrh.failure_reason
         , ixrh.org_id
         , ixrh.document_type
         , ixrh.addt_query_temp_id
         , ixrh.addt_xmldata
         , ixrh.addt_status
         , ixrh.addt_html_document
         , ixrh.addt_document
      from iex.IEX_XML_REQUEST_HISTORIES ixrh 
     where 1 = 1
--       AND party_id IN (15012231, 14352799)
       AND conc_request_id = 20135188
       AND dbms_lob.instr(ixrh.xmldata,'PAYMENT_HISTORY_ROW') = 0;

-- stuff
SELECT * FROM iex.IEX_XML_REQUEST_HISTORIES ORDER BY creation_date DESC;
SELECT * FROM iex.IEX_XML_QUERIES;
select * from iex.IEX_XML_REQUEST_HISTORIES where party_id IN (1234, 1235);
select * from iex.IEX_XML_REQUEST_HISTORIES where party_id = 1234;
select * from ar.hz_parties hp where hp.party_name LIKE 'Bob%Hope%';
select * from ar.hz_parties hp where hp.party_id = 1234;
select * from iex.IEX_XML_REQUEST_HISTORIES where conc_request_id = 20135188 and destination like 'hope%';

Advanced Collections – Work Items and Strategies per Collector

-- ##############################################################################
--        WORK ITEMS AND STRATEGIES PER COLLECTOR
-- ##############################################################################

SELECT idus.party_name account
     , hca.account_number account
     , idus.location
     , idus.past_due_inv_value overdue
     , idus.last_payment_amount
     , idus.last_payment_date
     , acpcv.profile_class_name profile_class
     , acpcv.profile_class_description
     , acpcv.collector_name collector_on_profile
     , ac.name collector_on_work_item
     , stry_templates.strategy_name
     , lk1.meaning work_item_status
     , lk2.meaning work_type
     , lk3.meaning category_type
     , '############'
     , iswi.*
  FROM iex.iex_strategy_work_items iswi
  JOIN iex.iex_dln_uwq_summary idus                        ON iswi.strategy_id = idus.strategy_id
  JOIN ar.ar_collectors ac                                 ON iswi.resource_id = ac.resource_id
  JOIN iex.iex_strategies istrat                           ON iswi.strategy_id = istrat.strategy_id
  JOIN ar.hz_cust_accounts hca                             ON istrat.cust_account_id = hca.cust_account_id
  JOIN ar.hz_parties hp                                    ON hca.party_id = hp.party_id
  JOIN apps.ar_customer_profile_classes_v acpcv            ON hca.customer_class_code = acpcv.profile_class_name
  JOIN iex.iex_stry_temp_work_items_b stry_temp_wkitem_b   ON iswi.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
  JOIN iex.iex_stry_temp_work_items_tl stry_temp_wkitem_tl ON stry_temp_wkitem_b.work_item_temp_id = stry_temp_wkitem_tl.work_item_temp_id
  JOIN apps.iex_strategy_templates_vl stry_templates       ON stry_templates.strategy_temp_id = iswi.strategy_temp_id
  JOIN apps.iex_lookups_v lk1                              ON lk1.lookup_code = iswi.status_code AND lk1.lookup_type = 'IEX_STRATEGY_WORK_STATUS'
  JOIN apps.iex_lookups_v lk2                              ON lk2.lookup_code = stry_temp_wkitem_b.work_type AND lk2.lookup_type = 'IEX_STRATEGY_WORK_TYPE'
  JOIN apps.iex_lookups_v lk3                              ON lk3.lookup_code = stry_temp_wkitem_b.category_type AND lk3.lookup_type = 'IEX_STRATEGY_WORK_CATEGORY'
 WHERE 1 = 1
--   AND iswi.status_code = 'OPEN'
--   AND idus.past_due_inv_value IS NOT NULL
--   AND lk2.meaning != 'Automatic'
   AND hca.account_number = 1234
   AND iswi.creation_date > '22-JAN-2016'
   AND 1 = 1;

-- counting

  SELECT ac.name collector_on_work_item
       , COUNT(*) ct 
    FROM iex.iex_strategy_work_items iswi
    JOIN iex.iex_dln_uwq_summary idus                        ON iswi.strategy_id = idus.strategy_id
    JOIN ar.ar_collectors ac                                 ON iswi.resource_id = ac.resource_id
    JOIN iex.iex_strategies istrat                           ON iswi.strategy_id = istrat.strategy_id
    JOIN ar.hz_cust_accounts hca                             ON istrat.cust_account_id = hca.cust_account_id
    JOIN ar.hz_parties hp                                    ON hca.party_id = hp.party_id
    JOIN apps.ar_customer_profile_classes_v acpcv            ON hca.customer_class_code = acpcv.profile_class_name
    JOIN iex.iex_stry_temp_work_items_b stry_temp_wkitem_b   ON iswi.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
    JOIN iex.iex_stry_temp_work_items_tl stry_temp_wkitem_tl ON stry_temp_wkitem_b.work_item_temp_id = stry_temp_wkitem_tl.work_item_temp_id
    JOIN apps.iex_strategy_templates_vl stry_templates       ON stry_templates.strategy_temp_id = iswi.strategy_temp_id
    JOIN apps.iex_lookups_v lk1                              ON lk1.lookup_code = iswi.status_code AND lk1.lookup_type = 'IEX_STRATEGY_WORK_STATUS'
    JOIN apps.iex_lookups_v lk2                              ON lk2.lookup_code = stry_temp_wkitem_b.work_type AND lk2.lookup_type = 'IEX_STRATEGY_WORK_TYPE'
    JOIN apps.iex_lookups_v lk3                              ON lk3.lookup_code = stry_temp_wkitem_b.category_type AND lk3.lookup_type = 'IEX_STRATEGY_WORK_CATEGORY'
   WHERE iswi.status_code = 'OPEN'
     AND idus.past_due_inv_value IS NOT NULL
     AND lk2.meaning != 'Automatic'
     AND 1 = 1
GROUP BY ac.name;  

-- ##############################################################################
--        WORK ITEMS PER COLLECTOR
-- ##############################################################################
  SELECT ac.name
       , iswi.resource_id
       , iswi.status_code
       , count(*) ct
    FROM iex.iex_strategy_work_items iswi
    JOIN ar.ar_collectors ac ON iswi.resource_id = ac.resource_id
   WHERE status_code = 'OPEN'
     AND iswi.resource_id = 100000001
GROUP BY ac.name
       , iswi.resource_id
       , iswi.status_code
ORDER BY ac.name;

-- ##############################################################################
--        WORK ITEM DETAILS FOR A COLLECTOR
-- ##############################################################################
SELECT * 
  FROM iex.IEX_STRATEGY_WORK_ITEMS iswi 
 WHERE 1 = 1
--   AND resource_id = 100000001
--   AND iswi.strategy_id = 492151
   AND iswi.strategy_temp_id = 10038
   AND 1 = 1;

SELECT * 
  FROM iex.IEX_STRATEGY_WORK_ITEMS iswi 
 WHERE 1 = 1
--   AND resource_id = 100000001
--   AND iswi.strategy_id = 492151
   AND iswi.strategy_temp_id = 10038
   AND creation_date > '22-JAN-2016'
   AND 1 = 1;

Advanced Collections – Tasks

--  ##############################################################################
--       TASKS
--  ############################################################################*/

     SELECT tasks.task_number
          , tasks.task_name
          , SUBSTR(tasks.description, 0, 30) descr
          , tasks.creation_date
          , tasks.task_type
          , tasks.task_status
          , tasks.owner
          , tasks.cust_account_id
          , tasks.last_update_date
          , hca.account_number
          , hca.account_name
          , tasks.cust_account_id
          , fu.description assigned_by
          , ipd.creation_date
          , ipd.promise_date
          , ipd.promise_amount
          , ipd.amount_due_remaining
          , ipd.status
          , ipd.broken_on_date
          , ipd.state
          , ac.name collector
       FROM iex.iex_promise_details ipd
  RIGHT JOIN ar.ar_collectors ac         ON ipd.resource_id =         ac.resource_id
  RIGHT JOIN apps.iex_tasks_main_v tasks ON tasks.source_object_id =  ipd.promise_detail_id
  LEFT JOIN ar.hz_cust_accounts hca      ON tasks.cust_account_id =   hca.cust_account_id
  LEFT JOIN ar.hz_parties hp             ON hca.party_id =            hp.party_id
  LEFT JOIN applsys.fnd_user fu          ON tasks.assigned_by_name =  fu.user_name
      WHERE 1 = 1
    --    AND tasks.owner = 'Hope, Bob'
        AND tasks.task_status IN ('Open', 'Working')
--        AND hp.party_type = 'ORGANIZATION'
        AND 1 = 1;

     SELECT ac.name collector
          , COUNT(*) ct
       FROM iex.iex_promise_details ipd
  RIGHT JOIN ar.ar_collectors ac         ON ipd.resource_id =         ac.resource_id
  RIGHT JOIN apps.iex_tasks_main_v tasks ON tasks.source_object_id =  ipd.promise_detail_id
  LEFT JOIN ar.hz_cust_accounts hca      ON tasks.cust_account_id =   hca.cust_account_id
  LEFT JOIN applsys.fnd_user fu          ON tasks.assigned_by_name =  fu.user_name
      WHERE 1 = 1
        AND tasks.task_status IN ('Open', 'Working')
        AND 1 = 1
   GROUP BY ac.name;

--  ##############################################################################
--         TASKS LINKED TO AR TRANSACTION NUMBERS
--  ############################################################################*/

     SELECT tasks.task_number
          , tasks.task_name
          , SUBSTR(tasks.description, 0, 30) descr
          , tasks.task_type
          , tasks.task_status
          , tasks.owner
          , hca.account_number
          , hca.account_name
          , fu.description assigned_by
          , ipd.creation_date
          , ipd.promise_date
          , ipd.promise_amount
          , ipd.amount_due_remaining
          , ipd.status
          , ipd.broken_on_date
          , ipd.state
          , ac.name collector
          , psa.customer_trx_id customer_trx_id
          , psa.trx_number trx_number
          , psa.trx_date trx_date
          , psa.terms_sequence_number terms_sequence_number
          , psa.due_date due_date
       FROM iex.iex_promise_details ipd
       JOIN iex.iex_delinquencies_all ida   ON ipd.delinquency_id =      ida.delinquency_id
       JOIN ar.ar_collectors ac             ON ipd.resource_id =         ac.resource_id
       JOIN ar.ar_payment_schedules_all psa ON ida.payment_schedule_id = psa.payment_schedule_id
       JOIN apps.iex_tasks_main_v tasks     ON tasks.source_object_id =  ipd.promise_detail_id
       JOIN ar.hz_cust_accounts hca         ON tasks.cust_account_id =   hca.cust_account_id
  LEFT JOIN applsys.fnd_user fu             ON tasks.assigned_by_name =  fu.user_name
      WHERE 1 = 1
    --    AND tasks.owner = 'Hope, Bob'
--        AND tasks.task_status IN ('Open', 'Working')
--        AND psa.trx_number = '12345678'
        AND ipd.status = 'FULFILLED'
    --    AND tasks.task_number = 16116
        AND 1 = 1;

Advanced Collections – Strategies

 -- ##############################################################################
--        BASIC STRATEGY
-- ##############################################################################

SELECT * 
  FROM iex.iex_strategies istrat
 WHERE 1 = 1
--   AND strategy_id = 492151
--   AND strategy_template_id = 10038
   AND creation_date > '12-JUL-2016'
   AND 1 = 1
   AND 1 = 1;

  SELECT status_code
       , count(*) ct
    FROM iex.iex_strategies istrat
   WHERE istrat.strategy_template_id = 10038
GROUP BY status_code;

SELECT hca.account_number
     , hp.party_name
     , hp.party_type
     , istrat.creation_date
     , istrat.customer_site_use_id
     , str_temp.strategy_name
     , hp.party_name account
     , hca.created_by_module customer_creation_method
     , hca.creation_date customer_created
     , fu.description customer_created_by
     , (SELECT classes.name 
          FROM ar.hz_cust_profile_classes classes
             , ar.hz_customer_profiles profiles 
         WHERE profiles.profile_class_id = classes.profile_class_id
           AND profiles.cust_account_id = hca.cust_account_id 
           AND profiles.site_use_id = istrat.customer_site_use_id) site_profile_class   
     , acpcv.customer_profile_class_id
     , acpcv.profile_class_name account_profile
     , acpcv.collector_name account_collector
     , (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_id = hca.cust_account_id AND apsa.amount_due_remaining > 0) balance_due
--     , istrat.*
  FROM iex.iex_strategies istrat
  JOIN ar.hz_cust_accounts hca ON istrat.cust_account_id = hca.cust_account_id
  JOIN ar.hz_parties hp ON hca.party_id = hp.party_id
  JOIN apps.ar_customer_profile_classes_v acpcv ON acpcv.profile_class_name = hca.customer_class_code
  JOIN applsys.fnd_user fu ON hca.created_by = fu.user_id
  JOIN iex.iex_strategy_templates_tl str_temp ON istrat.strategy_template_id = str_temp.strategy_temp_id
  JOIN ar.hz_parties hp ON hca.party_id = hp.party_id
 WHERE 1 = 1
--   AND strategy_id = 492151
   AND istrat.strategy_template_id = 10038
--   AND istrat.customer_site_use_id = 1954608
--   AND istrat.creation_date > '22-JAN-2016'
   AND istrat.status_code = 'OPEN'
   AND (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_id = hca.cust_account_id AND apsa.amount_due_remaining > 0) > 0
--   AND hca.account_number IN ('1234')
   AND 1 = 1
   AND 1 = 1;

Advanced Collections – Promises

-- ##############################################################################
--        PROMISES
-- ##############################################################################

-- promises including transaction number

 SELECT ac.name collector_on_promise
      , acpcv.collector_name collector_profile
      , (SELECT DISTINCT ac.name collector_name FROM ar.hz_customer_profiles hcp JOIN ar.hz_cust_accounts hca1 ON hcp.cust_account_id = hca1.cust_account_id JOIN ar.hz_parties hp        ON hcp.party_id =        hp.party_id AND hp.party_id = hca1.party_id JOIN ar.ar_collectors ac     ON hcp.collector_id =    ac.collector_id WHERE hca1.cust_account_id = hca.cust_account_id) coll_on_cust
      , hp.party_name
      , acpcv.profile_class_name profile_class
      , hca.account_number account
      , ipd.promise_detail_id
      , ipd.creation_date
      , ipd.promise_date
      , ipd.promise_amount
      , ipd.amount_due_remaining
      , ipd.status
      , ipd.broken_on_date
      , ipd.state
      , ipd.resource_id
      , ida.transaction_id
      , fu.user_name promise_collector_username
      , fu.end_date promise_collector_end_date
      , rcta.trx_number
   FROM iex.iex_promise_details ipd
   JOIN iex.iex_delinquencies_all ida            ON ipd.delinquency_id =      ida.delinquency_id 
   JOIN ar.ar_collectors ac                      ON ipd.resource_id =         ac.resource_id
   JOIN ar.hz_cust_accounts hca                  ON ipd.cust_account_id =     hca.cust_account_id
   JOIN ar.hz_parties hp                         ON hp.party_id =             hca.party_id 
   JOIN applsys.fnd_user fu                      ON ac.employee_id =          fu.employee_id
   JOIN apps.ar_customer_profile_classes_v acpcv ON hca.customer_class_code = acpcv.profile_class_name
   JOIN ar.ra_customer_trx_all rcta              ON ida.transaction_id = rcta.customer_trx_id
  WHERE 1 = 1
--    AND fu.user_name LIKE 'M%'
--    AND length(hp.party_number) > 7
    AND hca.account_number = 1234
--    AND fu.end_date IS NOT NULL
--    AND ac.name = 'Bob Hope'
--    AND ipd.status = 'COLLECTABLE'
--    AND ipd.state = 'BROKEN_PROMISE'
    AND 1 = 1;

-- this is the view users see in UWQ
-- they can only see Promises against Customers which they have access to

  SELECT ac.name collector
       , acpcv.collector_name collector_profile
      , (SELECT DISTINCT ac.name collector_name FROM ar.hz_customer_profiles hcp JOIN ar.hz_cust_accounts hca1 ON hcp.cust_account_id = hca1.cust_account_id JOIN ar.hz_parties hp        ON hcp.party_id =        hp.party_id AND hp.party_id = hca1.party_id JOIN ar.ar_collectors ac     ON hcp.collector_id =    ac.collector_id WHERE hca1.cust_account_id = hca.cust_account_id) coll_on_cust
       , idus.party_name
       , hca.customer_class_code profile_class
       , idus.account_number account
       , idus.location bill_to_loc
       , idus.number_of_promises promise_count
       , idus.promise_amount
       , idus.broken_promise_amount
       , idus.last_payment_amount
       , idus.last_payment_date
    FROM iex.iex_dln_uwq_summary idus
    JOIN ar.ar_collectors ac                      ON idus.collector_resource_id = ac.resource_id 
    JOIN ar.hz_cust_accounts hca                  ON idus.cust_account_id =       hca.cust_account_id 
    JOIN apps.ar_customer_profile_classes_v acpcv ON acpcv.profile_class_name =   hca.customer_class_code
   WHERE 1 = 1
     AND idus.number_of_promises > 0
--     AND ac.name LIKE '%Pare%'
ORDER BY 3;

-- promises per collecor

  SELECT ac.name collector
       , COUNT(*) ct
    FROM iex.iex_dln_uwq_summary idus
    JOIN ar.ar_collectors ac ON idus.collector_resource_id = ac.resource_id 
   WHERE 1 = 1
     AND idus.number_of_promises > 0
GROUP BY ac.name
ORDER BY ac.name;

-- view from Collections Admin > Ownership > Promises

 SELECT ac.name collector_on_promise
      , acpcv.collector_name collector_profile
      , (SELECT DISTINCT ac.name collector_name FROM ar.hz_customer_profiles hcp JOIN ar.hz_cust_accounts hca1 ON hcp.cust_account_id = hca1.cust_account_id JOIN ar.hz_parties hp        ON hcp.party_id =        hp.party_id AND hp.party_id = hca1.party_id JOIN ar.ar_collectors ac     ON hcp.collector_id =    ac.collector_id WHERE hca1.cust_account_id = hca.cust_account_id) coll_on_cust
      , hp.party_name
      , acpcv.profile_class_name profile_class
      , hca.account_number account
      , ipd.promise_detail_id
      , ipd.creation_date
      , ipd.promise_date
      , ipd.promise_amount
      , ipd.amount_due_remaining
      , ipd.status
      , ipd.broken_on_date
      , ipd.state
      , ipd.resource_id
      , fu.user_name promise_collector_username
      , fu.end_date promise_collector_end_date
   FROM iex.iex_promise_details ipd
   JOIN iex.iex_delinquencies_all ida            ON ipd.delinquency_id =      ida.delinquency_id 
   JOIN ar.ar_collectors ac                      ON ipd.resource_id =         ac.resource_id
   JOIN ar.hz_cust_accounts hca                  ON ipd.cust_account_id =     hca.cust_account_id
   JOIN ar.hz_parties hp                         ON hp.party_id =             hca.party_id 
   JOIN applsys.fnd_user fu                      ON ac.employee_id =          fu.employee_id
   JOIN apps.ar_customer_profile_classes_v acpcv ON hca.customer_class_code = acpcv.profile_class_name 
  WHERE 1 = 1
--    AND fu.user_name LIKE 'M%'
    AND length(hp.party_number) > 7
--    AND fu.end_date IS NOT NULL
--    AND ac.name = 'Kalpesh Parekh'
--    AND ipd.status = 'COLLECTABLE'
--    AND hca.account_number = 4193
--    AND ipd.state = 'BROKEN_PROMISE'
    AND 1 = 1;

 WITH tbl_coll AS
  ( SELECT ac.name collector
       , CASE WHEN ipd.state = 'BROKEN_PROMISE' THEN 1 END promise_broken
       , CASE WHEN ipd.state = 'PROMISE' THEN 1 END promise
   FROM iex.iex_promise_details ipd
   JOIN iex.iex_delinquencies_all ida            ON ipd.delinquency_id =      ida.delinquency_id
   JOIN ar.ar_collectors ac                      ON ipd.resource_id =         ac.resource_id
   JOIN ar.hz_cust_accounts hca                  ON ipd.cust_account_id =     hca.cust_account_id
   JOIN ar.hz_parties hp                         ON hca.party_id =            hp.party_id
   JOIN applsys.fnd_user fu                      ON ac.employee_id =          fu.employee_id
   JOIN apps.ar_customer_profile_classes_v acpcv ON hca.customer_class_code = acpcv.profile_class_name
  WHERE 1 = 1
    AND ipd.status = 'COLLECTABLE'
--    AND fu.end_date IS NOT NULL
    AND fu.user_name LIKE 'M%')
  SELECT collector
       , SUM(promise) promise
       , SUM(promise_broken) broken_promise
    FROM tbl_coll
GROUP BY collector
ORDER BY collector;

Advanced Collections – Notes

-- Notes are held at different levels.
-- They can be added at Promise, Transaction, Party and Account levels
-- When in collections, if you view a transaction, right-click it and view notes, you can only see the Notes linked to that Transaction
-- But if you click the Notes tab in Collections (next to the Transactions tab) you see all Notes.
-- Same if you click in the Header in the Collections FORM you can click on the View Notes yellow icon in the toolbar to see that same thing as you see in the Notes tab  

-- v1

    select jnt.jtf_note_id
         , jnt.creation_date cr_date
         , substr(jnt.notes, 0, 30) notes
         , fu.description cr_by
      from jtf.JTF_NOTES_TL jnt 
      JOIN applsys.fnd_user fu ON jnt.created_by = fu.user_id
     WHERE NOTES like 'PROMISE%12345678%';

-- v2

    select jnt.jtf_note_id
         , jnb.source_object_id
         , jnb.source_object_code
         , jnb.note_status
         , jnb.note_type
         , jnt.creation_date cr_date
         , (replace(replace(NOTES,chr(10),' '),chr(13),' ')) notes
         , fu.description cr_by
         , CASE WHEN jnb.source_object_code = 'IEX_INVOICES' THEN (SELECT rcta.trx_number FROM ar.ra_customer_trx_all rcta JOIN ar.ar_payment_schedules_all apsa ON apsa.customer_trx_id = rcta.customer_trx_id AND apsa.payment_schedule_id = jnb.source_object_id) END trx_from_payment_schedule
         , CASE WHEN jnb.source_object_code = 'IEX_PROMISE' THEN (SELECT rcta.trx_number FROM iex.iex_promise_details ipd JOIN iex.iex_delinquencies_all ida ON ipd.delinquency_id = ida.delinquency_id JOIN ar.ra_customer_trx_all rcta ON ida.transaction_id = rcta.customer_trx_id AND ipd.promise_detail_id = jnb.source_object_id) END trx_from_promise 
      from jtf.JTF_NOTES_TL jnt 
      JOIN jtf.JTF_NOTES_B jnb ON jnt.jtf_note_id = jnb.jtf_note_id
      JOIN applsys.fnd_user fu ON jnt.created_by = fu.user_id
     WHERE 1 = 1
       AND (replace(replace(NOTES,chr(10),' '),chr(13),' ')) like 'COLLECTABLE 14021058%'
--       AND NOTES like '%12345678%'
       AND 1 = 1;

-- v3
     
    select jnt.jtf_note_id
         , jnb.source_object_id
         , jnb.source_object_code
         , jnb.note_status
         , jnb.note_type
         , jnt.creation_date cr_date
         , substr(jnt.notes, 0, 100) notes
         , fu.description cr_by
         , jnc.note_context_type
      from jtf.JTF_NOTES_TL jnt 
      JOIN jtf.JTF_NOTES_B jnb ON jnt.jtf_note_id = jnb.jtf_note_id
      JOIN jtf.JTF_NOTE_CONTEXTS jnc ON jnt.jtf_note_id = jnc.jtf_note_id
      JOIN applsys.fnd_user fu ON jnt.created_by = fu.user_id
     WHERE 1 = 1
--       AND NOTES like 'PROMISE%12345678%'
       AND jnt.JTF_NOTE_ID IN (849155,832159,835569,842558,849173,849167,842860)
       AND NOTES like '%12345678%'
--       AND jnb.source_object_id = 2818149
       AND 1 = 1;

-- v4

    select jnt.jtf_note_id
         , jnb.source_object_id
         , jnb.source_object_code
         , jnb.note_status
         , jnb.note_type
         , jnt.creation_date cr_date
         , jnt.notes
         , fu.description cr_by
         , CASE WHEN jnb.source_object_code = 'IEX_INVOICES' THEN (SELECT rcta.trx_number FROM ar.ra_customer_trx_all rcta JOIN ar.ar_payment_schedules_all apsa ON apsa.customer_trx_id = rcta.customer_trx_id AND apsa.payment_schedule_id = jnb.source_object_id) END trx_from_payment_schedule
         , CASE WHEN jnb.source_object_code = 'IEX_PROMISE' THEN (SELECT rcta.trx_number FROM iex.iex_promise_details ipd JOIN iex.iex_delinquencies_all ida ON ipd.delinquency_id = ida.delinquency_id JOIN ar.ra_customer_trx_all rcta ON ida.transaction_id = rcta.customer_trx_id AND ipd.promise_detail_id = jnb.source_object_id) END trx_from_promise
         , CASE WHEN jnb.source_object_code = 'PARTY' THEN (SELECT party_number || ': ' || party_name from ar.hz_parties where party_id = jnb.source_object_id) END party_number
         , CASE WHEN jnb.source_object_code = 'IEX_ACCOUNT' THEN (SELECT account_number || ': ' || account_name from ar.hz_cust_accounts where cust_account_id = jnb.source_object_id) END account_number
      from jtf.JTF_NOTES_TL jnt
      JOIN jtf.JTF_NOTES_B jnb ON jnt.jtf_note_id = jnb.jtf_note_id
      JOIN applsys.fnd_user fu ON jnt.created_by = fu.user_id
     WHERE 1 = 1
       AND (NOTES like '%12345678%')
       AND 1 = 1;

-- v5

    select jnt.jtf_note_id
         , jnb.source_object_id
         , jnb.source_object_code
         , jnb.note_status
         , jnb.note_type
         , jnt.creation_date cr_date
         , jnt.notes
         , fu.description cr_by
      from jtf.JTF_NOTES_TL jnt 
      JOIN jtf.JTF_NOTES_B jnb ON jnt.jtf_note_id = jnb.jtf_note_id
      JOIN applsys.fnd_user fu ON jnt.created_by = fu.user_id
     WHERE 1 = 1
       AND jnb.source_object_code = 'IEX_ACCOUNT'
       AND 1 = 1;

-- v6

    select jnt.jtf_note_id
         , jnb.source_object_id
         , jnb.source_object_code
         , jnb.note_status
         , jnb.note_type
         , jnt.creation_date cr_date
         , jnt.notes
         , fu.description cr_by
      from jtf.JTF_NOTES_TL jnt
      JOIN jtf.JTF_NOTES_B jnb ON jnt.jtf_note_id = jnb.jtf_note_id
      JOIN applsys.fnd_user fu ON jnt.created_by = fu.user_id
     WHERE 1 = 1
       AND 1 = 1;

Advanced Collections – Delinquencies

-- ##############################################################################
--        DELINQUENCY TABLE ONLY
-- ##############################################################################

    SELECT ida.*
      FROM iex.iex_delinquencies_all ida
     WHERE ida.transaction_id = 2733682;

-- ##############################################################################
--        BASIC DELINQUENCIES
-- ##############################################################################

-- version 1

    SELECT ida.delinquency_id 
         , ida.creation_date del_created 
         , ida.status del_status 
         , ida.payment_schedule_id 
         , rcta.trx_number 
         , rcta.customer_trx_id 
         , rcta.creation_date trx_created 
         , apsa.payment_schedule_id 
         , apsa.due_date 
         , apsa.amount_due_original 
         , apsa.amount_due_remaining 
         , apsa.amount_applied 
         , apsa.amount_credited 
         , apsa.customer_id 
         , (SELECT COUNT(*) FROM ar.ar_payment_schedules_all apsa where apsa.customer_trx_id = rcta.customer_trx_id) apsa_ct_all
         , (SELECT COUNT(*) FROM ar.ar_payment_schedules_all apsa where apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_applied IS NULL AND apsa.amount_credited IS NULL) apsa_ct_clean
      FROM iex.iex_delinquencies_all ida 
 LEFT JOIN ar.ra_customer_trx_all rcta ON ida.transaction_id = rcta.customer_trx_id 
 LEFT JOIN ar.ar_payment_schedules_all apsa ON ida.payment_schedule_id = apsa.payment_schedule_id 
     WHERE 1 = 1 
--       AND ida.status = 'DELINQUENT'
       AND rcta.trx_number = '12345678'
--       AND (apsa.amount_applied IS NULL AND apsa.amount_credited IS NULL)
--       AND (SELECT COUNT(*) FROM ar.ar_payment_schedules_all apsa where apsa.customer_trx_id = rcta.customer_trx_id) = (SELECT COUNT(*) FROM ar.ar_payment_schedules_all apsa where apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_applied IS NULL AND apsa.amount_credited IS NULL)
       AND 1 = 1 
  ORDER BY ida.creation_date DESC;

-- without xml history

    SELECT ida.delinquency_id
         , ida.creation_date del_created
         , ida.last_update_date del_updated
         , ida.status del_status
         , ida.payment_schedule_id
         , ida.status
         , ida.dunn_yn
         , hca.account_number
         , hca.account_name
         , rcta.trx_number
         , rcta.customer_trx_id
         , rcta.creation_date trx_created
         , rcta.last_update_date trx_updated
         , rcta.term_id
         , apsa.payment_schedule_id
         , apsa.due_date
         , apsa.amount_due_original
         , apsa.amount_due_remaining
         , apsa.status
         , (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_due_remaining > 0) balance_due
      FROM iex.iex_delinquencies_all ida
      JOIN ar.ra_customer_trx_all rcta      ON ida.transaction_id =      rcta.customer_trx_id
      JOIN ar.ar_payment_schedules_all apsa ON ida.payment_schedule_id = apsa.payment_schedule_id
      JOIN ar.hz_cust_accounts hca            ON ida.cust_account_id =           hca.cust_account_id
     WHERE 1 = 1
       AND rcta.trx_number IN ('12345678')
--       AND ida.status = 'CURRENT'
       AND 1 = 1
  ORDER BY ida.creation_date DESC;

-- link with xml history

    SELECT ida.delinquency_id
         , ida.creation_date del_created
         , ida.last_update_date del_updated
         , ida.status del_status
         , hca.account_number
         , hca.account_name
         , rcta.trx_number
         , rcta.customer_trx_id
         , rcta.creation_date trx_created
         , rcta.last_update_date trx_updated
         , rcta.term_id
         , ixrh.xml_request_id
         , ixrh.creation_date xml_cr_date
         , ixrh.xmldata
         , dbms_lob.instr(ixrh.xmldata,'PAYMENT_HISTORY_ROW') xml_pay_hist
         , rtt.name term_trx
         , rtt.description
         , xati.term_name term_iface
         , rbsa.name batch_source
      FROM iex.iex_delinquencies_all ida
      JOIN ar.hz_cust_accounts hca            ON ida.cust_account_id =           hca.cust_account_id
      JOIN ar.ra_customer_trx_all rcta        ON ida.transaction_id =            rcta.customer_trx_id
 LEFT JOIN iex.iex_xml_request_histories ixrh ON ixrh.party_id =                 hca.party_id
      JOIN ar.ra_terms_tl rtt                 ON rcta.term_id =                  rtt.term_id
      JOIN ar.ra_customer_trx_all rcta        ON ida.transaction_id =            rcta.customer_trx_id
      JOIN ar.ra_batch_sources_all rbsa       ON rbsa.batch_source_id =          rcta.batch_source_id
      JOIN ar.ar_payment_schedules_all apsa   ON ida.payment_schedule_id =       apsa.payment_schedule_id
     WHERE 1 = 1
--       AND hca.account_name LIKE 'Fil%'
--       AND ida.delinquency_id = 1568432
--       AND ixrh.creation_date > '01-JUN-2016'
--       AND dbms_lob.instr(ixrh.xmldata,'PAYMENT_HISTORY_ROW') = 0 -- blank letter
       AND rcta.trx_number IN ('12345678')
--       AND ida.status = 'DELINQUENT'
--       AND hca.account_number = '123456'
       AND 1 = 1
  ORDER BY ida.creation_date DESC;

-- ##############################################################################
-- STATUS COUNTS
-- ##############################################################################

    SELECT ida.status
         , count(*) ct
      FROM iex.iex_delinquencies_all ida
  GROUP BY ida.status; 

-- ##############################################################################
-- TRANSACTIONS WITH AMOUNT OUTSTANDING AND DELINQUENCY COUNT
-- ##############################################################################

    SELECT rcta.trx_number
         , rcta.customer_trx_id trx_id
         , (SELECT COUNT(*) FROM iex.iex_delinquencies_all ida WHERE ida.payment_schedule_id = apsa.payment_schedule_id) ida_count
         , (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_due_remaining > 0) balance_due
      FROM ar.ra_customer_trx_all rcta
      JOIN ar.ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
     WHERE 1 = 1
--       AND rcta.trx_number IN ('15022370','25160451')
       AND rcta.creation_date BETWEEN '01-JAN-2016' AND '01-FEB-2016'
       AND 1 = 1
  ORDER BY rcta.customer_trx_id;

    SELECT rcta.trx_number
         , rcta.customer_trx_id
         , rcta.creation_date trx_created
         , rcta.last_update_date trx_updated
         , rcta.term_id
         , rbsa.name source
         , apsa.payment_schedule_id
         , apsa.due_date
         , ROUND(SYSDATE - apsa.due_date, 0) days_late
         , apsa.amount_due_original
         , apsa.amount_due_remaining
         , apsa.status
         , apsa.program_application_id
         , CASE WHEN apsa.in_collection IS NULL THEN 'not_in_collection' ELSE 'in_collection' END collection_flag
         , (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_due_remaining > 0) balance_due
         , (SELECT COUNT(*) FROM iex.iex_delinquencies_all ida WHERE ida.transaction_id = rcta.customer_trx_id AND ida.payment_schedule_id = apsa.payment_schedule_id AND ida.status = 'DELINQUENT') del_ct_del
         , (SELECT COUNT(*) FROM iex.iex_delinquencies_all ida WHERE ida.transaction_id = rcta.customer_trx_id AND ida.payment_schedule_id = apsa.payment_schedule_id AND ida.status = 'CURRENT') del_ct_current
--         , '################'
--         , apsa.*
      FROM ar.ra_customer_trx_all rcta 
      JOIN ar.ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
 LEFT JOIN ar.ra_batch_sources_all rbsa              ON rcta.batch_source_id =          rbsa.batch_source_id AND rcta.org_id = rbsa.org_id
     WHERE 1 = 1
--       AND apsa.status = 'OP'
--       AND rcta.trx_number IN ('12345678')
       AND rcta.creation_date BETWEEN '01-JAN-2016' AND '01-FEB-2016' 
       AND (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_due_remaining > 0) > 0
  ORDER BY rcta.creation_date DESC;

Advanced Collections – Customers

-- ##############################################################################
--        CUSTOMERS
--        Collections Admin Customers only return Customers for Collectors linked to 
--          Promises (promise or broken promises) and / or Work Items
-- ############################################################################*/

-- SQL derived from
-- How to Setup Territories/Resources/Collectors to work with R12 Collections (Doc ID 1397139.1)

    SELECT DISTINCT
           hp.party_name
         , hca.account_number
         , ac.name collector_name
         , ac.description
         , hca.cust_account_id
      FROM ar.hz_customer_profiles hcp
      JOIN ar.hz_cust_accounts hca ON hcp.cust_account_id = hca.cust_account_id 
      JOIN ar.hz_parties hp        ON hcp.party_id =        hp.party_id AND hp.party_id = hca.party_id
      JOIN ar.ar_collectors ac     ON hcp.collector_id =    ac.collector_id
     WHERE 1 = 1
       AND hca.status = 'A'
--       AND ac.name = 'Bob Hope'
--       AND hca.account_number = 11000
  ORDER BY party_name;

-- count by collector
    SELECT ac.name collector_name
         , COUNT(DISTINCT hca.cust_account_id) ct
      FROM ar.hz_customer_profiles hcp
      JOIN ar.hz_cust_accounts hca ON hcp.cust_account_id = hca.cust_account_id 
      JOIN ar.hz_parties hp        ON hcp.party_id =        hp.party_id AND hp.party_id = hca.party_id
      JOIN ar.ar_collectors ac     ON hcp.collector_id =    ac.collector_id
     WHERE hca.status = 'A'
       AND ac.name = 'Bob Hope'
  GROUP BY ac.name
  ORDER BY ac.name; 

-- Customers linked to profile classes
-- ################################################################################

    SELECT ac.name
--         , arcpv.profile_class_name
         , COUNT(DISTINCT hca.cust_account_id) ct
      FROM apps.ar_customer_profile_classes_v arcpv
      JOIN ar.ar_collectors ac     ON arcpv.collector_id =      ac.collector_id
      JOIN ar.hz_cust_accounts hca ON hca.customer_class_code = arcpv.profile_class_name
     WHERE hca.status = 'A'
  GROUP BY ac.name
--         , arcpv.profile_class_name
  ORDER BY 1,2;

    SELECT hca.account_number
         , hca.account_name
         , ac.name
         , arcpv.profile_class_name
         , arcpv.profile_class_description
         , hca.customer_class_code
      FROM apps.ar_customer_profile_classes_v arcpv
      JOIN ar.ar_collectors ac     ON arcpv.collector_id =      ac.collector_id
      JOIN ar.hz_cust_accounts hca ON hca.customer_class_code = arcpv.profile_class_name
     WHERE hca.account_number = 1234;
       
-- Customers via Collections Admin view

        SELECT hca.account_number
             , hca.account_name
             , hca.status
             , acpcv.profile_class_name profile_class
             , acpcv.profile_class_description
             , acpcv.collector_name collector_on_cust_profile
             , ac.name collector_on_customer
             , iov.location bill_to_location
             , iov.amount_due_remaining
             , iov.promise_count
             , iov.work_item_count
             , ac.resource_id
          FROM apps.iex_ownerships_v iov
          JOIN ar.ar_collectors ac                      ON iov.resource_id =         ac.resource_id
          JOIN ar.hz_cust_accounts hca                  ON iov.cust_account_id =     hca.cust_account_id
          JOIN applsys.fnd_user fu                      ON ac.employee_id =          fu.employee_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcv ON hca.customer_class_code = acpcv.profile_class_name
         WHERE NVL(fu.end_date, SYSDATE + 1) > SYSDATE
--           AND ac.name = 'Bob Hope'
           AND 1 = 1
        ORDER BY ac.name
               , hca.account_name;

SELECT *
  FROM apps.iex_ownerships_v iov
  JOIN ar.ar_collectors ac ON iov.resource_id = ac.resource_id
 WHERE ac.name = 'Bob Hope'
ORDER BY ac.name;

SELECT collector
     , party_name
     , account_number
     , SUM(flag_prom) promises
     , SUM(flag_brok) broken_promises
     , SUM(amount_due_remaining) amount_due_remaining 
  FROM (SELECT p.party_name party_name
              , ac.name collector
              , h.account_number account_number
              , l.location location
              , a.resource_id resource_id
              , a.amount_due_remaining
              , p.party_id party_id
              , h.cust_account_id cust_account_id
              , l.site_use_id customer_site_use_id
              , CASE WHEN a.state = 'PROMISE' then 1 END flag_prom
              , CASE WHEN a.state = 'BROKEN_PROMISE' then 1 END flag_brok
           FROM iex.iex_promise_details a
      LEFT JOIN iex.iex_delinquencies_all d ON a.delinquency_id =       d.delinquency_id  
           JOIN ar.hz_cust_accounts h       ON a.cust_account_id =      h.cust_account_id  
           JOIN ar.hz_parties p             ON h.party_id =             p.party_id 
           JOIN ar.ar_collectors ac         ON a.resource_id =          ac.resource_id 
      LEFT JOIN ar.hz_cust_site_uses_all l  ON d.customer_site_use_id = l.site_use_id 
          WHERE a.status = 'COLLECTABLE')
   WHERE 1 = 1
--     AND collector LIKE 'Bob%' 
GROUP BY collector
       , party_name
       , account_number;

SELECT * FROM apps.iex_ownerships_v iov;

Advanced Collections – Collectors

-- ##############################################################################
--        SETUP OF COLLECTORS
-- ##############################################################################
    SELECT ac.name collector
         , ac.collector_id
         , ac.resource_id
         , ac.employee_id
         , ac.creation_date
         , fu1.description created_by
         , ac.last_update_date
         , fu2.description updated_by
--         , fu3.user_name user_name
--         , fu3.end_date
         , papf.full_name
      FROM ar.ar_collectors ac
      JOIN applsys.fnd_user fu1 ON ac.created_by =      fu1.user_id
      JOIN applsys.fnd_user fu2 ON ac.last_updated_by = fu2.user_id
--      JOIN applsys.fnd_user fu3 ON ac.employee_id =     fu3.employee_id
 LEFT JOIN hr.per_all_people_f papf ON ac.employee_id = papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
     WHERE 1 = 1
--       AND fu3.user_name LIKE 'M%'
       AND 1 = 1;

Advanced Collections – Admin

I used these SQLs when I was supporting Advanced Collections and wanted to work out how to extract info to mirror what you can see in the Collections Administrator responsibility screens.

-- ##############################################################################
--        TOP LEVEL SUMMARY, MIRRORS COLLECTIONS ADMIN
-- ##############################################################################

    SELECT joined_up.collector
         , joined_up.party_name
         , joined_up.account_number
         , joined_up.location
         , joined_up.user_name
         , joined_up.end_date
         , SUM(work_item_flag) work_items
         , SUM(flag_prom) promises
         , SUM(flag_brok) broken_promises
         , SUM(amount_due_remaining) amount_due_remaining
    FROM 
              (SELECT ac.name collector
             , hp.party_name
             , hca.account_number
             , hcsua.location
             , ipd.resource_id
             , hp.party_id
             , hca.cust_account_id
             , hcsua.site_use_id
             , 0 work_item_flag
             , CASE WHEN ipd.state = 'PROMISE' THEN 1 END flag_prom
             , CASE WHEN ipd.state = 'BROKEN_PROMISE' THEN 1 END flag_brok
             , ipd.amount_due_remaining amount_due_remaining
             , fu.user_name
             , fu.end_date 
          FROM ar.ar_collectors ac
          JOIN iex.iex_promise_details ipd    ON ac.resource_id =           ipd.resource_id
          JOIN iex.iex_delinquencies_all ida  ON ipd.delinquency_id =       ida.delinquency_id
          JOIN ar.hz_cust_accounts hca        ON ipd.cust_account_id =      hca.cust_account_id
          JOIN ar.hz_parties hp               ON hca.party_id =             hp.party_id
          JOIN ar.hz_cust_site_uses_all hcsua ON ida.customer_site_use_id = hcsua.site_use_id
          JOIN applsys.fnd_user fu              ON fu.employee_id              = ac.employee_id
         WHERE ipd.status = 'COLLECTABLE'
--           AND ac.name = 'Cheryl Royle'
           AND fu.user_name LIKE 'M%'
           AND fu.end_date IS NOT NULL
        UNION ALL
        SELECT ac.name collector
             , hp.party_name
             , hca.account_number
             , hcsua.location
             , iswi.resource_id
             , hp.party_id
             , hca.cust_account_id
             , hcsua.site_use_id
             , CASE WHEN iswi.work_item_id > 0 THEN 1 END work_item_flag
             , 0 flag_prom
             , 0 flag_brok
             , 0 amount_due_remaining
             , fu.user_name
             , fu.end_date 
          FROM ar.ar_collectors ac
          JOIN iex.iex_strategy_work_items iswi ON ac.resource_id =              iswi.resource_id
          JOIN iex.iex_strategies istrat        ON istrat.strategy_id =          iswi.strategy_id
          JOIN ar.hz_cust_accounts hca          ON istrat.cust_account_id =      hca.cust_account_id
          JOIN ar.hz_parties hp                 ON hca.party_id =                hp.party_id
          JOIN ar.hz_cust_site_uses_all hcsua   ON istrat.customer_site_use_id = hcsua.site_use_id
          JOIN applsys.fnd_user fu              ON fu.employee_id              = ac.employee_id
         WHERE iswi.status_code IN ('OPEN','PRE-WAIT')
           AND istrat.status_code IN ('OPEN', 'ONHOLD')
           AND TRUNC(iswi.schedule_start) = TRUNC(iswi.schedule_start)
--           AND ac.name = 'Bob Hope'
           AND fu.user_name LIKE 'M%'
           AND fu.end_date IS NOT NULL) joined_up
  GROUP BY joined_up.collector
         , joined_up.party_name
         , joined_up.account_number
         , joined_up.location
         , joined_up.user_name
         , joined_up.end_date
  ORDER BY joined_up.collector
         , joined_up.party_name;

-- counting

SELECT collector
     , user_name
     , end_date
     , count(distinct account_number) customers
     , sum(work_items) work_items
     , sum(promises) promises
     , sum(broken_promises) broken_promises
     , sum(amount_due_remaining) total_outstanding
 from (SELECT joined_up.collector
         , joined_up.party_name
         , joined_up.account_number
         , joined_up.location
         , joined_up.user_name
         , joined_up.end_date
         , SUM(work_item_flag) work_items
         , SUM(flag_prom) promises
         , SUM(flag_brok) broken_promises
         , SUM(amount_due_remaining) amount_due_remaining
    FROM 
              (SELECT ac.name collector
             , hp.party_name
             , hca.account_number
             , hcsua.location
             , ipd.resource_id
             , hp.party_id
             , hca.cust_account_id
             , hcsua.site_use_id
             , 0 work_item_flag
             , CASE WHEN ipd.state = 'PROMISE' THEN 1 END flag_prom
             , CASE WHEN ipd.state = 'BROKEN_PROMISE' THEN 1 END flag_brok
             , ipd.amount_due_remaining amount_due_remaining
             , fu.user_name
             , fu.end_date 
          FROM ar.ar_collectors ac
          JOIN iex.iex_promise_details ipd    ON ac.resource_id =           ipd.resource_id
          JOIN iex.iex_delinquencies_all ida  ON ipd.delinquency_id =       ida.delinquency_id
          JOIN ar.hz_cust_accounts hca        ON ipd.cust_account_id =      hca.cust_account_id
          JOIN ar.hz_parties hp               ON hca.party_id =             hp.party_id
          JOIN ar.hz_cust_site_uses_all hcsua ON ida.customer_site_use_id = hcsua.site_use_id
          JOIN applsys.fnd_user fu              ON fu.employee_id              = ac.employee_id
         WHERE ipd.status = 'COLLECTABLE'
--           AND ac.name = 'Bob Hope'
           AND fu.user_name LIKE 'M%'
--           AND fu.end_date IS NOT NULL
           AND 1 = 1
        UNION ALL
        SELECT ac.name collector
             , hp.party_name
             , hca.account_number
             , hcsua.location
             , iswi.resource_id
             , hp.party_id
             , hca.cust_account_id
             , hcsua.site_use_id
             , CASE WHEN iswi.work_item_id > 0 THEN 1 END work_item_flag
             , 0 flag_prom
             , 0 flag_brok
             , 0 amount_due_remaining
             , fu.user_name
             , fu.end_date 
          FROM ar.ar_collectors ac
          JOIN iex.iex_strategy_work_items iswi ON ac.resource_id =              iswi.resource_id
          JOIN iex.iex_strategies istrat        ON istrat.strategy_id =          iswi.strategy_id
          JOIN ar.hz_cust_accounts hca          ON istrat.cust_account_id =      hca.cust_account_id
          JOIN ar.hz_parties hp                 ON hca.party_id =                hp.party_id
          JOIN ar.hz_cust_site_uses_all hcsua   ON istrat.customer_site_use_id = hcsua.site_use_id
          JOIN applsys.fnd_user fu              ON fu.employee_id              = ac.employee_id
         WHERE iswi.status_code IN ('OPEN','PRE-WAIT')
           AND istrat.status_code IN ('OPEN', 'ONHOLD')
           AND TRUNC(iswi.schedule_start) = TRUNC(iswi.schedule_start)
--           AND ac.name = 'Bob Hope'
           AND fu.user_name LIKE 'M%'
--           AND fu.end_date IS NOT NULL
           AND 1 = 1) joined_up
  GROUP BY joined_up.collector
         , joined_up.party_name
         , joined_up.account_number
         , joined_up.location
         , joined_up.user_name
         , joined_up.end_date
    
    ) tbl
  group by collector, user_name, end_date
  order by collector;

-- customers, mirroring collections admin

-- iex_ownerships_v definition

/* Formatted on 25/06/2015 11:18:45 (QP5 v5.185.11230.41888) */
      SELECT ac.name collector
           , p.party_name party_name
           , h.account_number account_number
           , l.location location
           , a.resource_id resource_id
           , p.party_id party_id
           , h.cust_account_id cust_account_id
           , l.site_use_id customer_site_use_id
           , 0 work_item_count
           , COUNT (*) promise_count
           , SUM (a.amount_due_remaining) amount_due_remaining
        FROM iex.iex_promise_details a
        JOIN ar.hz_cust_accounts h       ON h.cust_account_id =      a.cust_account_id 
        JOIN ar.hz_parties p             ON h.party_id =             p.party_id
   LEFT JOIN iex.iex_delinquencies_all d ON a.delinquency_id =       d.delinquency_id
   LEFT JOIN ar.hz_cust_site_uses_all l  ON d.customer_site_use_id = l.site_use_id
        JOIN ar.ar_collectors ac         ON a.resource_id =          ac.resource_id
        JOIN applsys.fnd_user fu         ON ac.employee_id =         fu.employee_id
       WHERE a.status = 'COLLECTABLE'
         AND fu.user_name LIKE 'M%'
         AND fu.end_date IS NOT NULL
    GROUP BY ac.name
           , p.party_name
           , h.account_number
           , l.location
           , a.resource_id
           , p.party_id
           , h.cust_account_id
           , l.site_use_id
       UNION ALL
      SELECT ac.name collector
           , p.party_name party_name
           , h.account_number account_number
           , l.location location
           , a.resource_id resource_id
           , p.party_id party_id
           , h.cust_account_id cust_account_id
           , l.site_use_id customer_site_use_id
           , COUNT (*) work_item_count
           , 0 promise_count
           , 0 amount_due_remaining
        FROM iex.iex_strategy_work_items a
        JOIN iex.iex_strategies s       ON s.strategy_id = a.strategy_id
   LEFT JOIN ar.hz_cust_accounts h      ON s.cust_account_id = h.cust_account_id
   LEFT JOIN ar.hz_parties p            ON s.party_id = p.party_id
  RIGHT JOIN ar.hz_cust_site_uses_all l ON l.site_use_id = s.customer_site_use_id
        JOIN ar.ar_collectors ac        ON a.resource_id = ac.resource_id
        JOIN applsys.fnd_user fu        ON ac.employee_id = fu.employee_id
       WHERE a.status_code = 'OPEN'
          AND TRUNC (a.schedule_start) = TRUNC (a.schedule_start)
          AND fu.user_name LIKE 'M%'
          AND fu.end_date IS NOT NULL
     GROUP BY ac.name
            , p.party_name
            , h.account_number
            , l.location
            , a.resource_id
            , p.party_id
            , h.cust_account_id
            , l.site_use_id;