The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_no_rows_updated EXCEPTION;
l_update_failure EXCEPTION;
IS SELECT NVL(t.conversion_rate,0),
t.uom_class,
NVL(f.conversion_rate,0),
f.uom_class
FROM mtl_uom_conversions t,
mtl_uom_conversions f
WHERE t.inventory_item_id IN (x_item_id,0)
AND t.unit_of_measure = x_unit_of_purchase
AND f.inventory_item_id IN (x_item_id,0)
AND f.unit_of_measure = x_unit_of_measure
ORDER BY t.inventory_item_id desc, f.inventory_item_id desc ;
SELECT round(x_unit_price,2)
INTO x_unit_price
FROM dual ;
SELECT nvl(conversion_rate,0)
INTO x_class_rate
FROM mtl_uom_class_conversions
WHERE inventory_item_id = x_item_id
AND to_uom_class = x_item_uom_class
AND from_uom_class = x_from_class ;
SELECT ceil(l_quantity)
INTO l_quantity
FROM dual ;
IS SELECT poll.price_override
FROM po_line_locations poll
WHERE price_override is not null
AND sysdate between nvl(start_date,sysdate-1)
and nvl(end_date,sysdate+1)
AND (poll.quantity is null
OR
poll.quantity <= x_quantity)
AND unit_meas_lookup_code = x_unit_of_purchase
AND po_line_id = x_po_line_id
-- Bug : 4236157
AND poll.shipment_type <> 'PREPAYMENT'
ORDER BY price_override asc ;
SELECT pad.document_header_id,
pol.line_num,
pol.po_line_id,
pol.vendor_product_num,
poh.vendor_id,
poh.vendor_site_id ,
poh.vendor_contact_id,
pov.vendor_name,
povs.vendor_site_code,
nvl(pol.unit_price,0),
pol.unit_meas_lookup_code
FROM po_autosource_documents pad,
po_headers poh,
po_lines pol,
po_vendors pov,
po_vendor_sites povs,
po_autosource_vendors ven,
po_autosource_rules rul
WHERE pad.autosource_rule_id = rul.autosource_rule_id
AND pad.vendor_id = ven.vendor_id
AND ven.autosource_rule_id = rul.autosource_rule_id
AND ven.autosource_rule_id = pad.autosource_rule_id
AND pad.document_header_id = poh.po_header_id
AND pad.document_line_id = pol.po_line_id
AND (( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND nvl(poh.frozen_flag,'N') = 'N'
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N')
OR
( poh.type_lookup_code = 'QUOTATION'
AND poh.status_lookup_code = 'A'))
AND poh.vendor_id = pov.vendor_id
AND poh.vendor_site_id = povs.vendor_site_id(+)
AND poh.vendor_id = povs.vendor_id(+)
AND SYSDATE between nvl(poh.start_date, SYSDATE)
AND nvl(poh.end_date, SYSDATE+1)
AND rul.item_id = x_item_id
AND sysdate between nvl(rul.start_date, sysdate)
AND nvl(rul.end_date, sysdate+1)
ORDER BY vendor_rank asc, sequence_num ;
SELECT paa.purchasing_unit_of_measure,
pl.UNIT_MEAS_LOOKUP_CODE,
pl.unit_price,
ph.po_header_id,
pl.line_num,
pl.po_line_id,
pl.unit_price
FROM po_approved_supplier_list pasl,
po_vendors pv,
po_vendor_sites_all pvs,
po_asl_attributes paa,
po_asl_documents pad,
po_headers_all ph,
po_lines_all pl,
mrp_sr_source_org msso
WHERE pvs.vendor_site_id = pasl.vendor_site_id
AND pv.vendor_id = pasl.vendor_id
AND pasl.item_id = x_item_id
AND pasl.asl_id = paa.asl_id
AND pasl.asl_id = pad.asl_id
AND ph.po_header_id = pl.po_header_id
AND (( ph.type_lookup_code = 'BLANKET'
AND ph.approved_flag = 'Y'
AND nvl(ph.frozen_flag,'N') = 'N'
AND nvl(ph.cancel_flag,'N') = 'N'
AND nvl(pl.cancel_flag,'N') = 'N')
OR
( ph.type_lookup_code = 'QUOTATION'
AND ph.status_lookup_code = 'A'))
AND ph.po_header_id = pad.document_header_id
AND SYSDATE between nvl(ph.start_date, SYSDATE) AND nvl(ph.end_date, SYSDATE+1)
AND pl.item_id = x_item_id
AND msso.vendor_id = pv.vendor_id
AND (pasl.disable_flag IS NULL OR pasl.disable_flag = 'N')
ORDER BY msso.allocation_percent desc, msso.rank, pad.sequence_num; -- Added allocation_percent
SELECT paa.purchasing_unit_of_measure,
pl.UNIT_MEAS_LOOKUP_CODE,
pl.unit_price,
ph.po_header_id,
pl.line_num,
pl.po_line_id,
pl.unit_price
FROM po_approved_supplier_list pasl,
po_vendors pv,
po_vendor_sites_all pvs,
po_asl_attributes paa,
po_asl_documents pad,
po_headers_all ph,
po_lines_all pl,
mrp_sr_source_org msso
WHERE pvs.vendor_site_id = pasl.vendor_site_id
AND pv.vendor_id = pasl.vendor_id
AND pasl.item_id = x_item_id
AND pasl.asl_id = paa.asl_id
AND pasl.asl_id = pad.asl_id
AND ph.po_header_id = pl.po_header_id
AND (( ph.type_lookup_code = 'BLANKET'
AND ph.approved_flag = 'Y'
AND nvl(ph.frozen_flag,'N') = 'N'
AND nvl(ph.cancel_flag,'N') = 'N'
AND nvl(pl.cancel_flag,'N') = 'N')
OR
( ph.type_lookup_code = 'QUOTATION'
AND ph.status_lookup_code = 'A'))
AND ph.po_header_id = pad.document_header_id
AND SYSDATE between nvl(ph.start_date, SYSDATE) AND nvl(ph.end_date, SYSDATE+1)
AND pl.item_id = x_item_id
AND msso.vendor_id = pv.vendor_id
AND (pasl.disable_flag IS NULL OR pasl.disable_flag = 'N')
AND pv.vendor_id = x_suggested_vendor_id
AND pvs.vendor_site_id = x_suggested_vendor_site_id
ORDER BY msso.allocation_percent desc, msso.rank, pad.sequence_num; -- Added allocation_percent
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = x_uom_code ;
SELECT count(*) INTO g_count FROM PO_APPROVED_SUPPLIER_LIST;
select unit_meas_lookup_code
into l_uom_direct
from po_lines_all
where po_header_id = x_autosource_doc_header_id
and line_num = x_autosource_doc_line_num;
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_unit_of_purchase ;
SELECT list_price_per_unit
INTO l_unit_price
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = x_organization_id ;
UPDATE po_requisitions_interface
SET unit_price = l_unit_price
WHERE rowid = x_rowid ;
UPDATE po_requisitions_interface
SET quantity = x_quantity
-- uom_code = l_uom_code,
-- unit_of_measure = l_unit_of_purchase
WHERE rowid = x_rowid;
UPDATE po_requisitions_interface
SET unit_of_measure = l_unit_of_purchase,
uom_code = l_uom_code,
unit_price = l_unit_price,
quantity = x_quantity,
autosource_doc_header_id = l_document_header_id,
autosource_doc_line_num = l_document_line_num,
suggested_vendor_name = l_vendor_name,
suggested_vendor_id = l_vendor_id,
suggested_vendor_site = l_vendor_site_code,
suggested_vendor_site_id = l_vendor_site_id,
suggested_vendor_contact_id = l_vendor_contact_id,
suggested_vendor_contact = null,
suggested_vendor_phone = null,
suggested_vendor_item_num = l_vendor_product_number
WHERE rowid = x_rowid ;
SELECT nvl(unit_of_issue,primary_unit_of_measure)
INTO l_unit_of_issue
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = x_source_organization_id ;
SELECT uom_code
INTO l_uoi_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_unit_of_issue ;
UPDATE po_requisitions_interface
SET unit_of_measure = l_unit_of_issue,
uom_code = l_uoi_code,
quantity = l_quantity,
unit_price = l_unit_price
WHERE rowid = x_rowid ;
PROCEDURE JA_AU_UPDATE_ERRORS
(x_rowid IN
varchar2,
x_transaction_id IN
po_requisitions_interface.transaction_id%TYPE)
IS
BEGIN
UPDATE po_requisitions_interface
SET charge_account_id = 0,
request_id = NULL,
process_flag = 'ERROR'
WHERE rowid = x_rowid;
RAISE l_update_failure;
INSERT INTO po_interface_errors
(
interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
'REQIMPORT',
x_transaction_id,
l_error_msg,
sysdate,
sysdate,
-1,
sysdate,
-1
);
WHEN l_update_failure THEN
-- DBMS_OUTPUT.NEW_LINE;
END JA_AU_UPDATE_ERRORS;
SELECT chart_of_accounts_id, set_of_books_id
INTO x_chart_of_accts_id, x_set_of_books_id
FROM org_organization_definitions
WHERE organization_id = x_org_id
AND nvl(disable_date, sysdate+1) > sysdate ;
JA_AU_update_errors(x_rowid,x_transaction_id);
SELECT nvl(expense_account, -1)
INTO x_subinv_ccid
FROM mtl_secondary_inventories
WHERE organization_id = x_org_id
AND secondary_inventory_name = x_subinv ;
SELECT nvl(expense_account, -1)
INTO x_item_ccid
FROM mtl_system_items
WHERE organization_id = x_org_id
AND inventory_item_id = x_item_id ;
JA_AU_update_errors(x_rowid,x_transaction_id);
JA_AU_update_errors(x_rowid,x_transaction_id);
SELECT nvl(segment30,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment29,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment28,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment27,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment26,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment25,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment24,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment23,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment22,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment21,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment20,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment19,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment18,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment17,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment16,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment15,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment14,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment13,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment12,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment11,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment10,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment9,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment8,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment7,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment6,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment5,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment4,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment3,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment2,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
SELECT nvl(segment1,'!@')
INTO l_value
FROM gl_code_combinations
WHERE code_combination_id = x_ccid ;
JA_AU_update_errors(x_rowid,x_transaction_id);
JA_AU_update_errors(x_rowid,x_transaction_id);
JA_AU_update_errors(x_rowid,x_transaction_id);
JA_AU_update_errors(x_rowid,x_transaction_id);
PROCEDURE JA_AU_UPDATE_REQINTERFACE
(x_rowid IN
varchar2,
x_transaction_id IN
po_requisitions_interface.transaction_id%TYPE,
x_ccid IN
gl_code_combinations.code_combination_id%TYPE)
IS
BEGIN
UPDATE po_requisitions_interface
SET charge_account_id = x_ccid
WHERE rowid = x_rowid;
RAISE l_no_rows_updated;
WHEN l_no_rows_updated THEN
-- DBMS_OUTPUT.NEW_LINE;
l_error_msg := 'AUTOGL ERROR - Update of charge_account_id in po_requisitions_interface failed.';
JA_AU_update_errors(x_rowid,x_transaction_id);
END JA_AU_UPDATE_REQINTERFACE;
SELECT nvl(upper(s.table_name), '!~') TABLE_NAME,
nvl(s.constant, '!~') CONSTANT,
s.segment
FROM JA_AU_ACCT_DEFAULT_SEGS s, ja_au_account_defaults d
WHERE s.gl_default_id = d.gl_default_id
AND d.set_of_books_id = l_set_of_books_id
ORDER BY d.type,s.segment_num ;
JA_AU_update_errors(x_rowid,l_transaction_id);
JA_AU_update_reqinterface(x_rowid,
l_transaction_id,
l_ccid);
IS SELECT pri.source_type_code,
pri.requisition_header_id,
pri.requisition_line_id,
pri.req_distribution_id,
pri.requisition_type,
pri.unit_price,
pri.autosource_flag,
pri.item_id,
pri.charge_account_id,
pri.unit_of_measure,
pri.uom_code,
pri.source_organization_id,
pri.destination_organization_id,
pri.source_subinventory,
pri.destination_organization_id,
pri.destination_subinventory,
pri.destination_type_code,
pri.deliver_to_location_id,
pri.quantity,
pri.transaction_id,
pri.rowid
,pri.autosource_doc_header_id
,pri.autosource_doc_line_num
,pri.suggested_vendor_id
,pri.suggested_vendor_site_id
FROM po_requisitions_interface pri
WHERE request_id = x_request_id ;