The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT UDA_TEMPLATE_ID
into l_uda_template
FROM PO_REQUISITION_LINES_ALL
WHERE REQUISITION_LINE_ID=REQ_LINE_ID;
SELECT AGS.ATTR_GROUP_NAME
INTO L_ATTR_GROUP_NAME
FROM PO_REQ_LINES_EXT_B prleb,
po_uda_ag_template_usages usages,
ego_attr_groups_v ags,
po_requisition_lines_all prla
WHERE prleb.requisition_line_id = p_req_line_id
AND prleb.attr_group_id = ags.attr_group_id
AND prleb.attr_group_id = usages.attribute_group_id
AND usages.attribute_category = 'PRICING'
AND prla.requisition_line_id = p_req_line_id
AND usages.template_id = prla.uda_template_id
AND usages.attribute1 ='IDC_NA'
AND usages.attribute2= prla.contract_type;
* 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_1.REQUISITION_LINE_ID,
prl_1.ORG_ID,
prl_1.REQUISITION_HEADER_ID,
prl_1.GROUP_LINE_ID,--add
prl_1.CLM_INFO_FLAG,--add
PRHA.FEDERAL_FLAG,
prl_2.LINE_NUM_DISPLAY
FROM po_requisition_lines_all prl_1,
po_requisition_lines_all prl_2,
Po_Requisition_Headers_All prha
WHERE prl_1.requisition_line_id = p_req_line_id
AND prha.Requisition_Header_Id = prl_1.Requisition_Header_Id
AND prl_2.Requisition_Line_Id = NVL(prl_1.GROUP_LINE_ID,p_req_line_id)
FOR UPDATE OF prl_1.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);
SELECT COUNT(*) INTO l_number_of_slins
FROM po_requisition_lines_all
WHERE Group_Line_Id=L_Requisition_Line_Id AND Requisition_Header_Id=L_Temp_Req_Header_Id;
SELECT Requisition_Line_Id BULK COLLECT
INTO l_slin_array_TBL
FROM po_requisition_lines_all
WHERE Group_Line_Id=L_Requisition_Line_Id AND requisition_header_id=l_temp_req_header_id;
PO_LOG.stmt(d_module_base,d_progress,'Inserting a new row');
SELECT po_requisition_lines_s.NEXTVAL into l_new_requisition_line_Id FROM dual;
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,
item_source_id, --Added for bug 9092341
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,
LINE_NUM_DISPLAY,--start addition for split_clm
CONFORMED_LINE_ID,
AMENDMENT_TYPE,
AMENDMENT_STATUS,
GROUP_LINE_ID,
CLM_INFO_FLAG,
CLM_OPTION_INDICATOR,
CLM_BASE_LINE_NUM,
CLM_OPTION_NUM,
CLM_OPTION_FROM_DATE,
CLM_OPTION_TO_DATE,
CLM_FUNDED_FLAG,
UDA_TEMPLATE_ID,
CONTRACT_TYPE,
COST_CONSTRAINT,
AMENDMENT_RESPONSE_REASON,
CLM_PERIOD_PERF_START_DATE,
CLM_PERIOD_PERF_END_DATE,
CLM_EXTENDED_ITEM_DESCRIPTION,
CLM_OPTION_EXERCISED,
ASSIGNMENT_NUMBER,
PO_DRAFT_ID--end addition
)
(SELECT l_new_requisition_line_Id,
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,
l_quantity,--clm_split
prl.deliver_to_location_id,
prl.to_person_id,
SYSDATE, -- Modified for bug 9092341
FND_GLOBAL.USER_ID,
prl.source_type_code,
prl.last_update_login,
SYSDATE, -- Modified for bug 9092341
FND_GLOBAL.USER_ID,
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.item_source_id, -- Added for bug 9092341
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,
l_display_number,-- added for clm_split
PRL.CONFORMED_LINE_ID,
PRL.AMENDMENT_TYPE,
PRL.AMENDMENT_STATUS,
l_grp_id,-- clm_split
PRL.CLM_INFO_FLAG,
PRL.CLM_OPTION_INDICATOR,
PRL.CLM_BASE_LINE_NUM,
PRL.CLM_OPTION_NUM,
PRL.CLM_OPTION_FROM_DATE,
PRL.CLM_OPTION_TO_DATE,
PRL.CLM_FUNDED_FLAG,
PRL.UDA_TEMPLATE_ID,
PRL.CONTRACT_TYPE,
PRL.COST_CONSTRAINT,
PRL.AMENDMENT_RESPONSE_REASON,
PRL.CLM_PERIOD_PERF_START_DATE,
PRL.CLM_PERIOD_PERF_END_DATE,
PRL.CLM_EXTENDED_ITEM_DESCRIPTION,
PRL.CLM_OPTION_EXERCISED,
PRL.ASSIGNMENT_NUMBER,
PRL.PO_DRAFT_ID -- end of addition for clm_split
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = l_req_line_id_temp);
INSERT INTO por_item_attribute_values(
item_type,
requisition_header_id,
requisition_line_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT piav.item_type,
piav.requisition_header_id,
l_new_requisition_line_Id ,
piav.attribute1,
piav.attribute2,
piav.attribute3,
piav.attribute4,
piav.attribute5,
piav.attribute6,
piav.attribute7,
piav.attribute8,
piav.attribute9,
piav.attribute10,
piav.attribute11,
piav.attribute12,
piav.attribute13,
piav.attribute14,
piav.attribute15,
piav.org_id,
piav.created_by,
piav.creation_date,
piav.last_updated_by,
piav.last_update_date,
piav.last_update_login
FROM por_item_attribute_values piav
WHERE piav.requisition_line_id = l_req_line_id_temp;
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 = l_slin_array(l_slin_num_index);--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,
REQUISITION_HEADER_ID /* <> */
INTO l_req_encumbered_flag,
l_Req_Header_Id /* <> */
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);