[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT structure_id
INTO x_auth_check_ids.item_cat_struct_id
FROM mtl_default_sets_view mfsv
WHERE mfsv.functional_area_id = 2;
SELECT glsob.chart_of_accounts_id
, NVL(fsp.use_positions_flag, 'N')
, fsp.inventory_organization_id
INTO x_auth_check_ids.coa_id
, l_using_pos_str
, x_auth_check_ids.fsp_org_id
FROM financials_system_parameters fsp,
gl_sets_of_books glsob
WHERE fsp.set_of_books_id = glsob.set_of_books_id;
SELECT nvl(paf.position_id, 0)
INTO x_auth_check_ids.position_id
FROM PER_ALL_ASSIGNMENTS_F paf --
WHERE paf.person_id = l_emp_id
AND paf.assignment_type IN ('E','C') --R12 CWK enhancement
AND paf.primary_flag = 'Y'
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT nvl(paf.job_id, 0)
INTO x_auth_check_ids.job_id
FROM PER_ALL_ASSIGNMENTS_F paf --
WHERE paf.person_id = l_emp_id
AND paf.assignment_type IN ('E','C') --R12 CWK enhancement
AND paf.primary_flag = 'Y'
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT pocf.control_function_id
INTO x_auth_check_ids.ctl_function_id
FROM po_control_functions pocf
WHERE pocf.document_type_code = p_document_type
AND pocf.document_subtype = p_document_subtype
AND pocf.action_type_code = p_action_to_verify
AND pocf.enabled_flag = 'Y';
SELECT sign(min(nvl(POCR.amount_limit, l_amt_limit_nvl) - sum(pgt.num1)))
INTO l_result
FROM po_control_rules pocr
, po_control_groups pocg
, po_position_controls popc
, po_session_gt pgt
WHERE pgt.key = p_session_gt_key
AND pgt.num1 IS NOT NULL
AND pgt.num2 IS NULL -- Bug 4610058
--
AND ((p_auth_check_ids.position_id IS NULL) OR
(popc.position_id = p_auth_check_ids.position_id))
AND ((p_auth_check_ids.job_id IS NULL) OR
(popc.job_id = p_auth_check_ids.job_id))
--
AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)
AND popc.control_function_id = p_auth_check_ids.ctl_function_id
AND pocg.enabled_flag = 'Y'
AND pocg.control_group_id = popc.control_group_id
AND pocr.control_group_id = pocg.control_group_id
AND pocr.object_code = 'DOCUMENT_TOTAL'
AND NVL(pocr.inactive_date, sysdate + 1) > sysdate
GROUP BY pocr.control_rule_id, pocr.amount_limit;
SELECT sign(min(nvl(POCR.amount_limit, -1) - sum(pgt.num1)))
INTO l_result
FROM po_control_rules pocr
, po_control_groups pocg
, po_position_controls popc
, po_session_gt pgt
WHERE pgt.key = p_session_gt_key
AND pgt.num1 IS NOT NULL
AND pgt.num2 IS NULL -- Bug 4610058
AND pgt.char1 = 'N'
AND pgt.char2 <> 'FINALLY CLOSED'
--
AND ((p_auth_check_ids.position_id IS NULL) OR
(popc.position_id = p_auth_check_ids.position_id))
AND ((p_auth_check_ids.job_id IS NULL) OR
(popc.job_id = p_auth_check_ids.job_id))
--
AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)
AND popc.control_function_id = p_auth_check_ids.ctl_function_id
AND pocg.enabled_flag = 'Y'
AND pocg.control_group_id = popc.control_group_id
AND pocr.control_group_id = pocg.control_group_id
AND pocr.object_code = 'LOCATION' --Bug#4901549
AND NVL(pocr.inactive_date, sysdate + 1) > sysdate
AND pocr.location_id = pgt.num4
GROUP BY pocr.control_rule_id, pocr.amount_limit;
x_range_check_sql := 'SELECT sign(min(nvl(POCR.amount_limit, -1) '
|| ' - sum(' || l_sum_col || ')))'
|| ' FROM po_session_gt pgt, po_control_rules pocr'
|| ' , po_control_groups pocg, po_position_controls popc'
|| l_flex_table
|| ' WHERE pgt.key = :1 AND ' || l_sum_col || ' IS NOT NULL'
|| ' AND pgt.char1 = ''N'' '
|| ' AND pgt.char2 <> ''FINALLY CLOSED'' '
|| l_flex_join
--
|| ' AND ((:2 IS NULL) OR (popc.position_id = :3))'
|| ' AND ((:4 IS NULL) OR (popc.job_id = :5))'
--
|| ' AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)'
|| ' AND popc.control_function_id = :6'
|| ' AND pocg.enabled_flag = ''Y'' '
|| ' AND pocg.control_group_id = popc.control_group_id'
|| ' AND pocr.control_group_id = pocg.control_group_id'
|| ' AND pocr.object_code = :7 '
|| ' AND NVL(pocr.inactive_date, sysdate + 1) > sysdate '
|| l_flex_segment_where
|| ' GROUP BY pocr.control_rule_id, pocr.amount_limit';
x_range_check_sql := 'SELECT nvl(min(-1),0) '
|| ' FROM po_session_gt pgt '
|| l_flex_table
|| ' WHERE pgt.key = :1 '
|| l_flex_join
|| ' AND NOT EXISTS ( '
|| ' SELECT ''account is in range'' '
|| ' FROM po_control_rules pocr, po_control_groups pocg'
|| ' , po_position_controls popc '
--
|| ' WHERE ((:2 IS NULL) OR (popc.position_id = :3))'
|| ' AND ((:4 IS NULL) OR (popc.job_id = :5))'
--
|| ' AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)'
|| ' AND popc.control_function_id = :6'
|| ' AND pocg.enabled_flag = ''Y'' '
|| ' AND pocg.control_group_id = popc.control_group_id'
|| ' AND pocr.control_group_id = pocg.control_group_id'
|| ' AND pocr.object_code = :7 '
|| ' AND NVL(pocr.inactive_date, sysdate + 1) > sysdate '
|| l_flex_segment_where
|| ' ) ';
SELECT PO_SESSION_GT_S.nextval INTO x_session_gt_key FROM dual;
INSERT INTO PO_SESSION_GT(
key
, num1
, num2
, num3
, num4
, num5
, num6
, char1
, char2
)
SELECT
x_session_gt_key
, (DECODE(pod.amount_ordered,
NULL,(pod.quantity_ordered - NVL(pod.quantity_cancelled,0)) * poll.price_override,
pod.amount_ordered - NVL(pod.amount_cancelled,0))
+ po_tax_sv.get_tax('PO',pod.po_distribution_id))
* nvl(pod.rate,1)
, NULL
, pod.code_combination_id
, poll.ship_to_location_id + 0
, pol.item_id
, pol.category_id
, DECODE(nvl(pol.cancel_flag, 'N'), 'N', NVL(poll.cancel_flag, 'N'), pol.cancel_flag)
, DECODE(nvl(pol.closed_code, 'OPEN'), 'OPEN', NVL(poll.closed_code, 'OPEN'), pol.closed_code)
FROM po_headers poh
, po_lines pol
, po_line_locations poll
, po_distributions pod
WHERE poh.po_header_id = p_document_id
AND pol.po_header_id = poh.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.shipment_type <> 'PREPAYMENT' --
AND pod.line_location_id = poll.line_location_id
AND ((poh.type_lookup_code <> 'PLANNED') OR
((poh.type_lookup_code = 'PLANNED') AND (poll.shipment_type = 'PLANNED')))
;
INSERT INTO PO_SESSION_GT(
key
, num1
, num2
, num3
, num4
, num5
, num6
, char1
, char2
)
SELECT
x_session_gt_key
, po_calculatereqtotal_pvt.get_req_distribution_total(
porl.requisition_header_id,porl.requisition_line_id,pord.distribution_id)
, NULL
, pord.code_combination_id
, porl.deliver_to_location_id
, porl.item_id
, porl.category_id
, 'N'
, 'OPEN' -- Bug 4610058
FROM po_req_distributions pord
, po_requisition_lines porl
WHERE porl.requisition_header_id = p_document_id
AND porl.requisition_line_id = pord.requisition_line_id
AND NVL(porl.cancel_flag, 'N') = 'N'
AND NVL(porl.modified_by_agent_flag, 'N') = 'N';
INSERT INTO PO_SESSION_GT(
key
, num1
, num2
, num3
, num4
, num5
, num6
, char1
, char2
)
SELECT
x_session_gt_key
, (DECODE(pod.amount_ordered,
NULL, (pod.quantity_ordered - NVL(pod.quantity_cancelled,0)) * poll.price_override,
pod.amount_ordered - NVL(pod.amount_cancelled,0))
+ po_tax_sv.get_tax('RELEASE',pod.po_distribution_id))
* NVL(pod.rate,1)
, NULL
, pod.code_combination_id
, poll.ship_to_location_id
, pol.item_id
, pol.category_id
, DECODE(nvl(pol.cancel_flag, 'N'), 'N', NVL(poll.cancel_flag, 'N'), pol.cancel_flag)
, DECODE(nvl(pol.closed_code, 'OPEN'), 'OPEN', NVL(poll.closed_code, 'OPEN'), pol.closed_code)
FROM po_distributions pod
, po_line_locations poll
, po_lines pol
WHERE poll.po_release_id = p_document_id
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id;
INSERT INTO PO_SESSION_GT(
key
, num1
, num2
, num3
, num4
, num5
, num6
, char1
, char2
)
SELECT
x_session_gt_key
, nvl(poh.blanket_total_amount,0) * nvl(poh.rate,1)
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'OPEN' -- Bug 4610058
FROM po_headers poh
WHERE poh.po_header_id = p_document_id;
INSERT INTO PO_SESSION_GT(
key
, num1
, num2
, num3
, num4
, num5
, num6
, char1
, char2
)
SELECT
x_session_gt_key
, NULL
-- Bug 4610058 Start : Should not sum up lines here; that will be done