We have job titles, and job titles have approval limit attached to them.
Approval limits work against document types - so that for example, job title 'Extended Services Strategy Manager::CA:CB:46' might be able to approve:
1. Standard Purchase Orders
2. Purchase Requisitions
3. Internal Requisitions
This is a sample SQL to show how tables are joined:
The following SQL details how the tables are joined
SELECT pj.NAME
2 , pcf.control_function_name
3 , pcga.control_group_name control_group
4 , pcr.amount_limit
5 FROM po.po_position_controls_all ppca
6 , po.po_control_groups_all pcga
7 , po.po_control_functions pcf
8 , po.po_control_rules pcr
9 , hr.per_jobs pj
10 WHERE ppca.job_id = pj.job_id
11 AND pcga.control_group_id = ppca.control_group_id
12 AND pcf.control_function_id = ppca.control_function_id
13 AND pcr.control_group_id = ppca.control_group_id
14 AND pcr.object_code = 'DOCUMENT_TOTAL'
15 AND pj.NAME IN
16 ('6th Form Admin and Cover Officer::AA:AE:5031'
17 , 'Acc Asst/Auditor:G:KB::')
18 ORDER BY pj.NAME;
NAME CONTROL_FUNCTION_NAME CONTROL_GROUP AMOUNT_LIMIT
------------------------------------------------------------------ --------------------------------- --------------- ------------
6th Form Admin and Cover Officer::AA:AE:5031 Approve Purchase Requisitions GG £5000 5000
6th Form Admin and Cover Officer::AA:AE:5031 Approve Standard Purchase Orders GG £5000 5000
6th Form Admin and Cover Officer::AA:AE:5031 Approve Purchase Requisitions GH £5000 5000
6th Form Admin and Cover Officer::AA:AE:5031 Approve Standard Purchase Orders GH £5000 5000
6th Form Admin and Cover Officer::AA:AE:5031 Approve Purchase Requisitions GJ £5000 5000
6th Form Admin and Cover Officer::AA:AE:5031 Approve Standard Purchase Orders GJ £5000 5000
Acc Asst/Auditor:G:KB:: Approve Purchase Requisitions AC £10000 10000
Acc Asst/Auditor:G:KB:: Approve Standard Purchase Orders AC £10000 10000
Acc Asst/Auditor:G:KB:: Approve Internal Requisitions AC £10000 10000
9 rows selected.
SQL>
Each of the 'CONTROL_GROUPS', stored in the po.po_control_groups_all table hold the name of a Control Group - e.g. GG £5000.
Each CONTROL_GROUP has a rule attached to it (stored in the po.po_control_rules table).
The rule has an approval value attached to it (abount_limit).
The 'CONTROL_GROUP' is also attached to a control_function_name, which is just a document type. So we can say, for example, that against a job title, a user can approve Standard Purchase Orders, and Purchase Requisitions, for the GG Service Segment, for a value of £5000.
Our policy states that the abount_limit against a job title should always be the same. As you can see above - the amount_limit for the 6th Form Admin and Cover Officer::AA:AE:5031 is only ever set to £5000.
However, there are job titles where the amount_limit value contains 2 or more different values against a job title.
I need a way to identify these job titles, but I cannot work out how to do it.
I could do a count of CONTROL_GROUPS against a job title, but that doesn't help a lot:
SELECT pj.NAME
2 , COUNT(*) ct
3 FROM po.po_position_controls_all ppca
4 , po.po_control_rules pcr
5 , hr.per_jobs pj
6 WHERE ppca.job_id = pj.job_id
7 AND pcr.control_group_id = ppca.control_group_id
8 AND SYSDATE BETWEEN ppca.start_date AND NVL(ppca.end_date, SYSDATE + 1)
9 AND pcr.object_code = 'DOCUMENT_TOTAL'
10 AND pj.NAME IN
11 ('6th Form Admin and Cover Officer::AA:AE:5031'
12 , 'Acc Asst/Auditor:G:KB::')
13 GROUP BY pj.NAME
14 ORDER BY 2 DESC;
NAME
------------------------------------------------------------------------------------- -----
6th Form Admin and Cover Officer::AA:AE:5031 6
Acc Asst/Auditor:G:KB:: 3
SQL>
Because I can see now that there are 6 rules attached to the first job title. Ideally, what would be great would be to find a way to modify the Counbt SQL statement above, to include a 3rd column which did a distinct count of how many different 'amount_limits' were also attached to the job title.
That is where I am very stuck.
Any help much appreciated.
Sorry for writing so much. Seems easier to write too much, than not enough. I can provide desc outputs for the tables as well, but haven't done right away, because it'd make this post even longer...