[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT s.currency_code
FROM cn_periods p
, cn_repositories r
, gl_sets_of_books s
WHERE r.current_period_id = p.period_id(+)
AND r.application_id = 283
AND r.set_of_books_id = s.set_of_books_id;
SELECT NVL(symbol, '') AS symbol
FROM fnd_currencies
WHERE currency_code = g_currency_code;
SELECT fu.user_id
, fu.user_name
, jrs.resource_id
, jrs.NAME
, jrs.salesrep_id
, jrs.org_id
FROM fnd_user fu
, jtf_rs_defresources_v jrd
, jtf_rs_salesreps jrs
WHERE fu.user_name = usr_name
AND jrd.user_id = fu.user_id
AND jrs.resource_id = jrd.resource_id
AND jrs.org_id = p_org_id;
SELECT MIN(start_date) AS min_date
, MAX(end_date) AS max_date
FROM cn_periods
WHERE closing_status = 'O';
SELECT TO_CHAR(cl.processed_date, 'MM/DD') AS processed_date
, ch.customer_id AS customer_id
, NVL(hp.party_name, 'CUSTOMER NOT PRESENT') AS customer_name
, NVL(ch.attribute1, inventory_item_id) AS product_name
, NVL(ch.transaction_amount, 0) AS transaction_amount
, NVL(cl.perf_achieved, 0) AS credit
, NVL(cl.commission_amount, 0) AS earnings
FROM cn_commission_headers ch
, cn_commission_lines cl
, cn_salesreps rep
, hz_parties hp
, hz_cust_accounts hca
WHERE cl.org_id = ch.org_id
AND rep.org_id = ch.org_id
AND cl.status = 'CALC'
AND cl.commission_header_id = ch.commission_header_id
AND ch.direct_salesrep_id = rep.salesrep_id
AND cl.credited_salesrep_id = ch.direct_salesrep_id
AND rep.salesrep_id = p_salesrep_id
AND cl.processed_date BETWEEN p_from_date AND p_to_date
AND hca.cust_account_id(+) = ch.customer_id
AND hp.party_id = hca.party_id
AND ch.customer_id IS NOT NULL
ORDER BY cl.processed_date DESC;
SELECT fu.user_id
, fu.user_name
, jrs.resource_id
, jrs.NAME
, jrs.salesrep_id
, jrs.org_id
FROM fnd_user fu
, jtf_rs_defresources_v jrd
, jtf_rs_salesreps jrs
WHERE fu.user_name = usr_name
AND jrd.user_id = fu.user_id
AND jrs.resource_id = jrd.resource_id
AND jrs.org_id = p_org_id;
SELECT MIN(start_date) AS min_date
, MAX(end_date) AS max_date
FROM cn_periods
WHERE closing_status = 'O';
SELECT customer_name
, earnings
, (comm_rate * 100) comm_rate
, credit
, transaction_amount
FROM (SELECT NVL (hp.party_name, 'CUSTOMER NOT PRESENT') AS customer_name
, hca.cust_account_id customer_id
, MAX (NVL (cl.commission_amount, 0)) AS earnings
, MAX (NVL (cl.commission_rate, 0)) AS comm_rate
, MAX (NVL (cl.perf_achieved, 0)) AS credit
, MAX (NVL (ch.transaction_amount, 0)) AS transaction_amount
, RANK () OVER (PARTITION BY hca.cust_account_id ORDER BY NVL
(cl.commission_amount,
0
) DESC NULLS LAST) AS comp_rank
FROM cn_commission_headers ch
, cn_commission_lines cl
, cn_salesreps rep
, hz_parties hp
, hz_cust_accounts hca
WHERE cl.org_id = ch.org_id
AND rep.org_id = ch.org_id
AND cl.status = 'CALC'
AND cl.commission_header_id = ch.commission_header_id
AND cl.credited_salesrep_id = ch.direct_salesrep_id
AND ch.direct_salesrep_id = rep.salesrep_id
AND rep.salesrep_id = p_salesrep_id
AND cl.processed_date BETWEEN p_from_date AND p_to_date
AND ch.customer_id IS NOT NULL
AND hca.cust_account_id(+) = ch.customer_id
AND hp.party_id = hca.party_id
GROUP BY hca.cust_account_id
, party_name
, NVL (cl.commission_amount, 0))
WHERE comp_rank = 1
ORDER BY earnings DESC;
SELECT fu.user_id
, fu.user_name
, jrs.resource_id
, jrs.NAME
, jrs.salesrep_id
, jrs.org_id
FROM fnd_user fu
, jtf_rs_defresources_v jrd
, jtf_rs_salesreps jrs
WHERE fu.user_name = usr_name
AND jrd.user_id = fu.user_id
AND jrs.resource_id = jrd.resource_id
AND jrs.org_id = p_org_id;
SELECT MIN(start_date) AS min_date
, MAX(end_date) AS max_date
FROM cn_periods
WHERE closing_status = 'O';
SELECT product_name
, earnings
, (comm_rate * 100) comm_rate
, credit
, transaction_amount
FROM (SELECT NVL(ch.attribute1, inventory_item_id) AS product_name,
MAX(NVL(cl.commission_amount, 0)) AS earnings,
MAX(NVL(cl.commission_rate, 0)) AS comm_rate,
MAX(NVL(cl.perf_achieved, 0)) AS credit,
MAX(NVL(ch.transaction_amount, 0)) AS transaction_amount
FROM cn_commission_headers ch,
cn_commission_lines cl,
cn_salesreps rep
WHERE cl.org_id = ch.org_id
AND rep.org_id = ch.org_id
AND cl.status = 'CALC'
AND cl.commission_header_id = ch.commission_header_id
AND cl.credited_salesrep_id = ch.direct_salesrep_id
AND ch.direct_salesrep_id = rep.salesrep_id
AND rep.salesrep_id = p_salesrep_id
AND cl.processed_date BETWEEN p_from_date AND p_to_date
AND ch.customer_id IS NOT NULL
GROUP BY NVL(ch.attribute1, inventory_item_id))
ORDER BY earnings DESC;
SELECT task_number
, task_id
, assignment_status
, task_name
, customer_name
, priority_name
, resource_name
, resource_id
, resource_type_code
, planned_start_date
, scheduled_start_date
, sla_esc
, calc_date
FROM (SELECT b.task_number, b.task_id, s.NAME assignment_status, b.task_name,
pi.party_name customer_name, jtp.NAME priority_name,
csf_resource_pub.get_resource_name(resource_id,
resource_type_code
) resource_name,
resource_id, resource_type_code, b.planned_start_date,
b.scheduled_start_date, 'ESC' sla_esc, NULL calc_date
FROM jtf_tasks_b esc_t,
jtf_task_statuses_vl s,
jtf_tasks_vl b,
jtf_task_references_b r,
hz_party_sites ps,
hz_locations hl,
hz_parties pi,
jtf_task_assignments jtb,
jtf_task_priorities_vl jtp
WHERE esc_t.task_id = r.task_id
AND esc_t.task_type_id = 22
AND s.task_status_id = jtb.assignment_status_id
AND r.reference_code = 'ESC'
AND r.object_type_code = 'TASK'
AND r.object_id = b.task_id
AND ps.party_site_id(+) = b.address_id
AND hl.location_id(+) = ps.location_id
AND pi.party_id(+) = b.customer_id
AND NVL(esc_t.deleted_flag, 'N') <> 'Y'
AND NVL(s.cancelled_flag, 'N') <> 'Y'
AND jtb.task_id = b.task_id
AND jtp.task_priority_id = b.task_priority_id
UNION
SELECT b.task_number, b.task_id, s.NAME assignment_status, b.task_name,
pi.party_name customer_name, jtp.NAME priority_name,
csf_resource_pub.get_resource_name(resource_id,
resource_type_code
) resource_name,
resource_id, resource_type_code, b.planned_start_date,
b.scheduled_start_date, 'SLA' sla_esc,
planned_start_date - scheduled_start_date calc_date
FROM jtf_task_statuses_vl s,
jtf_tasks_vl b,
hz_party_sites ps,
hz_locations hl,
hz_parties pi,
jtf_task_assignments jtb,
jtf_task_priorities_vl jtp
WHERE jtb.task_id = b.task_id
AND s.task_status_id = jtb.assignment_status_id
AND ps.party_site_id(+) = b.address_id
AND hl.location_id(+) = ps.location_id
AND pi.party_id(+) = b.customer_id
AND NVL(s.closed_flag, 'N') <> 'Y'
AND jtp.task_priority_id = b.task_priority_id
AND NVL(s.cancelled_flag, 'N') <> 'Y'
AND planned_start_date - scheduled_start_date > 0)
ORDER BY calc_date ASC;