[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Modifies: Inserts new req lines and their distributions, For parent .
* req lines, update requisition_lines table to modified_by_agent_flag = 'Y' .
* Also sets prevent encumbrace flag to 'Y' in the po_req_distributions table.
* Effects: This api split the requisition lines, into two lines with specified quantity.
* This api uses a global temp.table to process the input given by autocreate(HTML) and
* inserts records into po_requisition_lines_all and po_req_distributions_all table.
* This api also handles the encumbrace effect of splitting requisition lines. This
* api would be called from Autocreate HTML.
*
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if action succeeds
* FND_API.G_RET_STS_ERROR if action fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
* x_msg_count returns count of messages in the stack.
* x_msg_data returns message only if 1 message.
* Algorithm:
* 1. Get the requisition line id of the req line that needs
* to be split
* 2. Retrieve the quantity on the given line and split using
* split function.
* 3. Calculate the maximum line number of the lines that
* belong to the given requisition.
* 4. Using a for loop insert two records into the po_requisition_lines_all
* table and provide the correct line number by incrementing
* max line number by one in each iteration.
* 5. Update the split req line and set the modified flag
* and purchasing agent flag.
* 6. Copy the attachments from the parent line on to the
* new lines.
* 7. Handle tax adjustments for the new lines
* 8. Handle encumbrance funds results for the new and old
* lines.
*
*/
PROCEDURE split_requisition_lines(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_req_line_id IN NUMBER,
p_num_of_new_lines IN NUMBER,
p_quantity_tbl IN PO_TBL_NUMBER,
p_agent_id IN NUMBER,
p_calling_program IN VARCHAR2,
p_handle_tax_diff_if_enc IN VARCHAR2,
x_new_line_ids_tbl OUT NOCOPY PO_TBL_NUMBER,
x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000
) IS
l_module CONSTANT VARCHAR2(100) := 'split_requisition_lines';
SELECT prl.requisition_line_id,
prl.org_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = p_req_line_id
FOR UPDATE OF prl.quantity NOWAIT;
SELECT MAX(prl.line_num)
INTO l_max_line_num
FROM po_requisition_lines_all prl
WHERE prl.requisition_header_id =
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_Line_id);
PO_LOG.stmt(d_module_base,d_progress,'Inserting a new row');
INSERT INTO po_requisition_lines_all
(requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
deliver_to_location_id,
to_person_id,
last_update_date,
last_updated_by,
source_type_code,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
quantity_delivered,
suggested_buyer_id,
encumbered_flag,
rfq_required_flag,
need_by_date,
line_location_id,
modified_by_agent_flag,
parent_req_line_id,
justification,
note_to_agent,
note_to_receiver,
purchasing_agent_id,
document_type_code,
blanket_po_header_id,
blanket_po_line_num,
currency_code,
rate_type,
rate_date,
rate,
currency_unit_price,
suggested_vendor_name,
suggested_vendor_location,
suggested_vendor_contact,
suggested_vendor_phone,
suggested_vendor_product_code,
un_number_id,
hazard_class_id,
must_use_sugg_vendor_flag,
reference_num,
on_rfq_flag,
urgent_flag,
cancel_flag,
source_organization_id,
source_subinventory,
destination_type_code,
destination_organization_id,
destination_subinventory,
quantity_cancelled,
cancel_date,
cancel_reason,
closed_code,
agent_return_note,
changed_after_research_flag,
vendor_id,
vendor_site_id,
vendor_contact_id,
research_agent_id,
on_line_flag,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
attribute_category,
destination_context,
inventory_source_context,
vendor_source_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bom_resource_id,
ussgl_transaction_code,
government_context,
closed_reason,
closed_date,
transaction_reason_code,
quantity_received,
tax_code_id,
tax_user_override_flag,
oke_contract_header_id,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_cancelled,
auction_header_id,
auction_display_number,
auction_line_number,
reqs_in_pool_flag,
vmi_flag,
bid_number,
bid_line_number,
order_type_lookup_code,
purchase_basis,
matching_basis,
org_id,
catalog_type,
catalog_source,
manufacturer_id,
manufacturer_name,
manufacturer_part_number,
requester_email,
requester_fax,
requester_phone,
unspsc_code,
other_category_code,
supplier_duns,
tax_status_indicator,
pcard_flag,
new_supplier_flag,
auto_receive_flag,
tax_attribute_update_code)
(SELECT po_requisition_lines_s.NEXTVAL,
prl.requisition_header_id,
(l_serial_num + l_max_line_num),
prl.line_type_id,
prl.category_id,
prl.item_description,
prl.unit_meas_lookup_code,
prl.unit_price,
p_quantity_tbl(l_line_num_index),
prl.deliver_to_location_id,
prl.to_person_id,
prl.last_update_date,
prl.last_updated_by,
prl.source_type_code,
prl.last_update_login,
prl.creation_date,
prl.created_by,
prl.item_id,
prl.item_revision,
prl.quantity_delivered,
prl.suggested_buyer_id,
prl.encumbered_flag,
prl.rfq_required_flag,
prl.need_by_date,
prl.line_location_id,
NULL,
p_req_line_id,
prl.justification,
prl.note_to_agent,
prl.note_to_receiver,
prl.purchasing_agent_id,
prl.document_type_code,
prl.blanket_po_header_id,
prl.blanket_po_line_num,
prl.currency_code,
prl.rate_type,
prl.rate_date,
prl.rate,
prl.currency_unit_price,
prl.suggested_vendor_name,
prl.suggested_vendor_location,
prl.suggested_vendor_contact,
prl.suggested_vendor_phone,
prl.suggested_vendor_product_code,
decode(p_calling_program,g_CALLING_PROGRAM_SPLIT,
prl.un_number_id,null),
prl.hazard_class_id,
prl.must_use_sugg_vendor_flag,
prl.reference_num,
prl.on_rfq_flag,
prl.urgent_flag,
prl.cancel_flag,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_type_code,
prl.destination_organization_id,
prl.destination_subinventory,
prl.quantity_cancelled,
prl.cancel_date,
prl.cancel_reason,
prl.closed_code,
prl.agent_return_note,
prl.changed_after_research_flag,
prl.vendor_id,
prl.vendor_site_id,
prl.vendor_contact_id,
prl.research_agent_id,
prl.on_line_flag,
prl.wip_entity_id,
prl.wip_line_id,
prl.wip_repetitive_schedule_id,
prl.wip_operation_seq_num,
prl.wip_resource_seq_num,
prl.attribute_category,
prl.destination_context,
prl.inventory_source_context,
prl.vendor_source_context,
prl.attribute1,
prl.attribute2,
prl.attribute3,
prl.attribute4,
prl.attribute5,
prl.attribute6,
prl.attribute7,
prl.attribute8,
prl.attribute9,
prl.attribute10,
prl.attribute11,
prl.attribute12,
prl.attribute13,
prl.attribute14,
prl.attribute15,
prl.bom_resource_id,
prl.ussgl_transaction_code,
prl.government_context,
prl.closed_reason,
prl.closed_date,
prl.transaction_reason_code,
prl.quantity_received,
prl.tax_code_id,
prl.tax_user_override_flag,
prl.oke_contract_header_id,
prl.oke_contract_version_id,
decode(p_calling_program,g_CALLING_PROGRAM_SPLIT,
prl.secondary_unit_of_measure,null),
prl.secondary_quantity,
prl.preferred_grade,
prl.secondary_quantity_received,
prl.secondary_quantity_cancelled,
prl.auction_header_id,
prl.auction_display_number,
prl.auction_line_number,
'Y', --new reqs are placed back in pool after splitting
prl.vmi_flag,
prl.bid_number,
prl.bid_line_number,
prl.order_type_lookup_code,
prl.purchase_basis,
prl.matching_basis,
prl.org_id,
prl.catalog_type,
prl.catalog_source,
prl.manufacturer_id,
prl.manufacturer_name,
prl.manufacturer_part_number,
prl.requester_email,
prl.requester_fax,
prl.requester_phone,
prl.unspsc_code,
prl.other_category_code,
prl.supplier_duns,
prl.tax_status_indicator,
prl.pcard_flag,
prl.new_supplier_flag,
prl.auto_receive_flag,
g_TAX_ATTRIBUTE_CREATE
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = p_req_line_id);
UPDATE po_requisition_lines_all
SET modified_by_agent_flag = 'Y',
purchasing_agent_id = p_agent_id,
reqs_in_pool_flag = NULL, --
on_rfq_flag = decode(p_calling_program,
g_CALLING_PROGRAM_CATALOG,
null,
on_rfq_flag),
bid_number = decode(p_calling_program,
g_calling_program_catalog,
null,
bid_number),
bid_line_number = decode(p_calling_program,
g_calling_program_catalog,
null,
bid_line_number),
auction_header_id = decode(p_calling_program,
g_calling_program_catalog,
null,
auction_header_id),
auction_display_number = decode(p_calling_program,
g_calling_program_catalog,
null,
auction_display_number),
auction_line_number = decode(p_calling_program,
g_calling_program_catalog,
null,
auction_line_number),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_line_id = p_req_line_id;
SELECT PRL1.requisition_line_id
BULK COLLECT
INTO new_req_line_id_rslt_tbl
FROM PO_REQUISITION_LINES_ALL PRL1, PO_REQUISITION_LINES_ALL PRL2
WHERE PRL1.requisition_header_id = PRL2.requisition_header_id
AND PRL1.parent_req_line_id = p_req_line_id
AND PRL2.requisition_line_id = p_req_line_id;
SELECT PRL1.quantity
BULK COLLECT
INTO l_quantity_table
FROM po_requisition_lines_all PRL1, po_requisition_lines_all PRL2
WHERE PRL1.requisition_header_id = PRL2.requisition_header_id
AND PRL1.parent_req_line_id = p_req_line_id
AND PRL2.requisition_line_id = p_req_line_id;
X_last_update_login => NULL,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => NULL);
select requisition_header_id
into l_requisition_header_id
from po_requisition_lines_all
where requisition_line_id = p_req_line_id;
SELECT nvl(req_encumbrance_flag, 'N')
INTO l_req_encumbrance_flag
FROM financials_system_parameters;
SELECT ENCUMBERED_FLAG
INTO l_req_encumbered_flag
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = p_req_line_id;
PO_LOG.stmt(d_module_base,d_progress,'Inserting values into po_req_split_lines_gt');
INSERT INTO po_req_split_lines_gt
(requisition_header_id,
requisition_line_id,
allocated_qty,
new_req_line_id,
record_status)
SELECT prl.requisition_header_id,
p_req_line_id,
prl.quantity,
DECODE(prl.requisition_line_id,
p_req_line_id,
NULL,
prl.requisition_line_id),
DECODE(prl.requisition_line_id, p_req_line_id, 'S', 'N')
FROM po_requisition_lines prl
WHERE (prl.requisition_line_id = p_req_line_id OR
prl.parent_req_line_id = p_req_line_id)
AND EXISTS(
SELECT requisition_header_id
FROM po_requisition_lines_all PRL1
WHERE PRL1.requisition_header_id = prl.requisition_header_id
AND PRL1.requisition_line_id = p_req_line_id);
PO_LOG.stmt(d_module_base,d_progress,'No rows inserted into PO_REQ_SPLIT_LINES_GT');
po_message_s.sql_error('No rows inserted into PO_REQ_SPLIT_LINES_GT', d_progress, sqlcode);