[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE po_distributions_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
DELETE po_price_diff_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
DELETE po_line_locations_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
DELETE po_lines_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
DELETE po_headers_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
SELECT DISTINCT 'Package Installed'
INTO l_jl_installed
FROM user_objects
WHERE object_name = 'JG_GLOBE_UTIL_PKG'
AND object_type = 'PACKAGE BODY';
select prh.segment1||','||prl.line_num_display into l_requisition_number
from po_requisition_lines_all prl, po_requisition_headers_all prh
where prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
and prl.requisition_line_id = p_req_line_id;
PROCEDURE insert_row_in_uda_interface
(
p_transaction_id IN NUMBER,
p_row_identifier IN NUMBER,
p_pk1_value NUMBER ,
p_mod_draft_id NUMBER,
p_uda_template_id NUMBER,
p_attr_group_type VARCHAR2 ,
p_attr_group_name IN VARCHAR2 ,
p_attr_name IN VARCHAR2,
p_data_level IN VARCHAR2,
p_mod_value VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'insert_row_in_uda_interface';
SELECT attr_display_name , eagv.attr_group_id , eav.data_type_code
INTO l_attr_display_name , l_attr_group_id , l_data_type_code
FROM ego_attrs_v eav, ego_attr_groups_v eagv
WHERE eav.attr_group_name = eagv.attr_group_name
AND eav.attr_group_type = eagv.attr_group_type
AND eav.attr_group_name = p_attr_group_name
AND eav.attr_name = p_attr_name
AND eav.attr_group_type = p_attr_group_type;
INSERT INTO po_uda_interface
(transaction_id,
row_identifier,
attr_group_id,
attr_group_app_id,
attr_group_type,
attr_group_name,
data_level_1,
attr_name,
attr_value_str,
attr_value_num,
attr_value_date,
--attr_disp_value, : Need not be populated in this flow
pk1_value,
pk2_value,
uda_template_id,
process_status)
VALUES (p_transaction_id,
p_row_identifier,
l_attr_group_id,
l_application_id,
p_attr_group_type,
p_attr_group_name,
p_data_level,
p_attr_name,
l_attr_value_str,
l_attr_value_num,
l_attr_value_date,
--l_attr_display_name, : Need not be populated in this flow
p_pk1_value,
p_mod_draft_id,
p_uda_template_id,
l_process_status);
END insert_row_in_uda_interface;
SELECT DISTINCT eav.attr_group_type|| To_Char( eagv.attr_group_id)|| To_Char(eav.attr_id) ||'LOVDisp'
BULK collect
INTO p_attr_display_name
FROM (SELECT * FROM ego_attrs_v WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS') eav,
(SELECT * FROM ego_attr_groups_v WHERE attr_group_name = 'addresses') eagv ,
(SELECT * FROM po_entity_differences WHERE mod_doc_source_name = 'PO_ADDRESSES_DRAFT_DIFF_V') ped
WHERE eav.attr_group_name = eagv.attr_group_name
AND eav.attr_group_type = eagv.attr_group_type
AND SubStr(ped.column_name, InStr(ped.column_name, Chr(0))+1) = eav.attr_name
AND mod_doc_pk2_val = p_par_draft_id;
SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) add_type
BULK COLLECT INTO l_address_type_tbl
FROM po_entity_differences
WHERE mod_doc_pk2_val = p_par_draft_id
and mod_doc_source_name = p_mod_doc_source_name ;
SELECT SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
BULK COLLECT INTO
l_attr_name_tbl, l_mod_value_tbl
FROM po_entity_differences
WHERE mod_doc_pk2_val = p_par_draft_id
AND mod_doc_pk1_val = p_pk1_value
and mod_doc_source_name = p_mod_doc_source_name
AND column_name LIKE l_address_type_tbl(i) ||'%' ;
insert_row_in_uda_interface
(
p_transaction_id => p_transaction_id,
p_row_identifier => l_row_identifier_add,
p_pk1_value => p_pk1_value ,
p_mod_draft_id => p_mod_draft_id,
p_uda_template_id => p_uda_template_id,
p_attr_group_type => l_attr_group_type ,
p_attr_group_name => 'addresses',
p_attr_name => l_attr_name_tbl(j),
p_data_level => l_data_level,
p_mod_value => l_mod_value_tbl(j)
) ;
SELECT value_set_name
INTO l_value_set
FROM ego_attrs_v
WHERE attr_group_type = l_attr_group_type
AND attr_name =l_attr_name
AND ATTR_GROUP_NAME = 'addresses';
INSERT INTO po_uda_interface
(transaction_id,
row_identifier,
attr_group_id,
attr_group_app_id,
attr_group_type,
attr_group_name,
data_level_1,
attr_name,
attr_value_str,
attr_value_num,
attr_value_date,
--attr_disp_value, : Need not be populated in this flow
pk1_value,
pk2_value,
uda_template_id,
process_status)
VALUES (p_transaction_id,
l_row_identifier_add,
l_attr_group_id,
FND_GLOBAL.resp_appl_id,
l_attr_group_type,
l_attr_group_name,
l_data_level,
'HiddenLKPType',
l_value_set,
l_attr_value_num,
l_attr_value_date,
--l_attr_display_name, : Need not be populated in this flow
p_pk1_value,
p_mod_draft_id,
p_uda_template_id,
l_process_status);
SELECT attr_group_id
INTO l_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_name = 'addresses'
AND ATTR_GROUP_TYPE = l_attr_group_type;
INSERT INTO po_uda_interface
(transaction_id,
row_identifier,
attr_group_id,
attr_group_app_id,
attr_group_type,
attr_group_name,
data_level_1,
attr_name,
attr_value_str,
attr_value_num,
attr_value_date,
--attr_disp_value, : Need not be populated in this flow
pk1_value,
pk2_value,
uda_template_id,
process_status)
VALUES (p_transaction_id,
l_row_identifier_add,
l_attr_group_id,
FND_GLOBAL.resp_appl_id,
l_attr_group_type,
'addresses',
l_data_level,
'HiddenAddType',
l_address_type_tbl(i),
l_attr_value_num,
l_attr_value_date,
--l_attr_display_name, : Need not be populated in this flow
p_pk1_value,
p_mod_draft_id,
p_uda_template_id,
l_process_status);
INSERT INTO po_uda_interface
(transaction_id,
row_identifier,
attr_group_id,
attr_group_app_id,
attr_group_type,
attr_group_name,
data_level_1,
attr_name,
attr_value_str,
attr_value_num,
attr_value_date,
--attr_disp_value, : Need not be populated in this flow
pk1_value,
pk2_value,
uda_template_id,
process_status)
VALUES (p_transaction_id,
l_row_identifier_add,
l_attr_group_id,
FND_GLOBAL.resp_appl_id,
l_attr_group_type,
'addresses',
l_data_level,
'HiddenLKPType',
'PO_UDA_ADDRESS_TYPES',
l_attr_value_num,
l_attr_value_date,
--l_attr_display_name, : Need not be populated in this flow
p_pk1_value,
p_mod_draft_id,
p_uda_template_id,
l_process_status);
SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) attr_group_name,
SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
BULK COLLECT INTO
l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
FROM po_entity_differences
WHERE mod_doc_pk2_val = p_par_draft_id
AND mod_doc_pk1_val = p_pk1_value
AND mod_doc_source_name = p_mod_doc_source_name
ORDER BY attr_group_name,attr_name,mod_value ;
SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) attr_group_name,
SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
BULK COLLECT INTO
l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
FROM po_entity_differences
WHERE mod_doc_pk2_val = p_par_draft_id
AND mod_doc_pk3_val = p_pk1_value
AND mod_doc_source_name = p_mod_doc_source_name
ORDER BY attr_group_name,attr_name,mod_value ;
SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) attr_group_name,
SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
BULK COLLECT INTO
l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
FROM po_entity_differences
WHERE mod_doc_pk2_val = p_par_draft_id
AND mod_doc_pk4_val = p_pk1_value
AND mod_doc_source_name = p_mod_doc_source_name
ORDER BY attr_group_name,attr_name,mod_value ;
insert_row_in_uda_interface
(
p_transaction_id => p_transaction_id,
p_row_identifier => l_row_identifier,
p_pk1_value => p_pk1_value ,
p_mod_draft_id => p_mod_draft_id,
p_uda_template_id => p_uda_template_id,
p_attr_group_type => l_attr_group_type ,
p_attr_group_name => l_attr_grp_tbl(i) ,
p_attr_name => l_attr_name_tbl(i),
p_data_level => l_data_level,
p_mod_value => l_mod_value_tbl(i)
) ;
SELECT SubStr(col_name, 1,InStr(col_name,'_'||Chr(0))-1) attr_grp_name,
SubStr(col_name, InStr(col_name, Chr(0))+1) attr_name ,
col_value col_value
FROM po_headers_ext_draft_diff_v
WHERE col_value IS NOT NULL
) --Collecting the attribute grp name, aatribute name and their value in ag_data
SELECT ag_data.attr_grp_name, ag_data.attr_name, ag_data.col_value
BULK COLLECT INTO l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
FROM ag_data
--The attr group should be currently existing in po_uda_interface
--and the attribute should not exist in po_uda_interface
WHERE ag_data.attr_grp_name IN (SELECT DISTINCT attr_group_name
FROM po_uda_interface
WHERE transaction_id = p_transaction_id
AND data_level_1 = l_data_level
AND pk1_value = p_pk1_value
AND PK2_VALUE = p_mod_draft_id)
AND NOT EXISTS (SELECT 1 FROM po_uda_interface
WHERE pk2_value =p_mod_draft_id
AND transaction_id = p_transaction_id
AND ATTR_GROUP_NAME = ag_data.attr_grp_name
AND ATTR_NAME = ag_data.attr_name);
SELECT SubStr(col_name, 1,InStr(col_name,'_'||Chr(0))-1) attr_grp_name,
SubStr(col_name, InStr(col_name, Chr(0))+1) attr_name ,
col_value col_value
FROM po_lines_ext_draft_diff_v
WHERE col_value IS NOT NULL
) --Collecting the attribute grp name, aatribute name and their value in ag_data
SELECT ag_data.attr_grp_name, ag_data.attr_name, ag_data.col_value
BULK COLLECT INTO l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
FROM ag_data
--The attr group should be currently existing in po_uda_interface
--and the attribute should not exist in po_uda_interface
WHERE ag_data.attr_grp_name IN (SELECT DISTINCT attr_group_name
FROM po_uda_interface
WHERE transaction_id = p_transaction_id
AND data_level_1 = l_data_level
AND pk1_value = p_pk1_value
AND PK2_VALUE = p_mod_draft_id)
AND NOT EXISTS (SELECT 1 FROM po_uda_interface
WHERE pk2_value =p_mod_draft_id
AND transaction_id = p_transaction_id
AND ATTR_GROUP_NAME = ag_data.attr_grp_name
AND ATTR_NAME = ag_data.attr_name);
SELECT SubStr(col_name, 1,InStr(col_name,'_'||Chr(0))-1) attr_grp_name,
SubStr(col_name, InStr(col_name, Chr(0))+1) attr_name ,
col_value col_value
FROM po_line_locs_ext_draft_diff_v
WHERE col_value IS NOT NULL
) --Collecting the attribute grp name, aatribute name and their value in ag_data
SELECT ag_data.attr_grp_name, ag_data.attr_name, ag_data.col_value
BULK COLLECT INTO l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
FROM ag_data
--The attr group should be currently existing in po_uda_interface
--and the attribute should not exist in po_uda_interface
WHERE ag_data.attr_grp_name IN (SELECT DISTINCT attr_group_name
FROM po_uda_interface
WHERE transaction_id = p_transaction_id
AND data_level_1 = l_data_level
AND pk1_value = p_pk1_value
AND PK2_VALUE = p_mod_draft_id)
AND NOT EXISTS (SELECT 1 FROM po_uda_interface
WHERE pk2_value =p_mod_draft_id
AND transaction_id = p_transaction_id
AND ATTR_GROUP_NAME = ag_data.attr_grp_name
AND ATTR_NAME = ag_data.attr_name);
SELECT DISTINCT row_identifier
INTO l_row_identifier
FROM po_uda_interface
WHERE transaction_id = p_transaction_id
AND attr_group_name = l_attr_grp_tbl(k)
AND pk1_value = p_pk1_value;
insert_row_in_uda_interface
(
p_transaction_id => p_transaction_id,
p_row_identifier => l_row_identifier,
p_pk1_value => p_pk1_value ,
p_mod_draft_id => p_mod_draft_id,
p_uda_template_id => p_uda_template_id,
p_attr_group_type => l_attr_group_type ,
p_attr_group_name => l_attr_grp_tbl(k) ,
p_attr_name => l_attr_name_tbl(k),
p_data_level => l_data_level,
p_mod_value => l_mod_value_tbl(k)
) ;
UPDATE po_lines_draft_all
SET DRAFT_LINE_STATUS = 'MOD_CREATED',
mod_draft_id = p_mod_draft_id
WHERE draft_id = p_par_draft_id_tbl(i)
AND po_line_id = p_par_line_tbl(i);
UPDATE po_headers_draft_all
SET document_creation_method = 'PAR_AUTOCREATE'
WHERE draft_id = p_mod_draft_id;
UPDATE po_requisition_lines_all
SET po_line_id = ( SELECT mod_line_id
FROM po_lines_draft_all par, po_requisition_lines_all req
WHERE req.par_line_id = p_par_line_tbl(i)
AND req.par_line_id = par.po_line_id
AND par.draft_id = p_par_draft_id_tbl(i)
AND par.draft_id = req.par_draft_id
)
WHERE par_line_id = p_par_line_tbl(i)
AND par_draft_id = p_par_draft_id_tbl(i);
SELECT distribution_id, par_distribution_id
BULK COLLECT INTO l_distribution_id_tbl, l_par_distribution_id_tbl
FROM po_req_distributions_all prd, po_requisition_lines_all prl
WHERE prl.par_line_id = p_par_line_tbl(i)
AND prl.par_draft_id = p_par_draft_id_tbl(i)
AND prd.par_draft_id = prl.par_draft_id
AND prl.requisition_line_id = prd.requisition_line_id;
UPDATE po_distributions_draft_all
SET req_distribution_id = l_distribution_id_tbl(i)
WHERE draft_id = p_mod_draft_id
AND par_distribution_id = l_par_distribution_id_tbl(i);
UPDATE po_headers_draft_all
SET mod_draft_id = p_mod_draft_id
WHERE draft_id = p_par_draft_id_tbl(i);
SELECT 'N'
INTO l_return_type
FROM dual
WHERE EXISTS (SELECT 'the line in the Autocreated Mod is Locked'
FROM po_entity_locks
WHERE lock_by_draft_id = P_MOD_DRAFT_ID
AND entity_pk1 = P_PO_LINE_ID
AND entity_name = 'PO_LINE')
AND EXISTS (SELECT 'the structure is not same in MOD and PAR'
FROM po_lines_draft_all par, po_lines_draft_all mod
WHERE par.po_line_id = P_PO_LINE_ID
AND par.draft_id = P_PAR_DRAFT_ID
AND mod.po_line_id = par.po_line_id
AND mod.draft_id = P_MOD_DRAFT_ID
AND ( Nvl(par.CONTRACT_TYPE,'N') <> Nvl(mod.CONTRACT_TYPE,'N')
OR Nvl(par.CLM_IDC_TYPE,'N') <> Nvl(mod.CLM_IDC_TYPE,'N')));
PROCEDURE UPDATE_MOD_LINE_ID
(p_par_draft_id IN NUMBER,
p_par_line_id IN NUMBER,
p_mod_line_id IN NUMBER)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_mod_line_id';
UPDATE po_lines_draft_all
SET mod_line_id = p_mod_line_id
WHERE po_line_id = p_par_line_id
AND draft_id = p_par_draft_id;
po_log.stmt (d_module, 10, 'No of lines updated : ' || sql%rowcount );
END UPDATE_MOD_LINE_ID;
SELECT line_location_id
FROM po_line_locations_draft_all
WHERE po_line_id = p_line_id
AND draft_id = p_par_draft_id
AND change_status = p_change_status ;
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = p_mod_draft_id;
SELECT mod_line_id
INTO l_mod_line_id
FROM po_lines_draft_all
WHERE draft_id =p_par_draft_id_tbl(i)
AND po_line_id = p_par_line_id_tbl(i);
for shipments_rec in fetch_line_location_ids(p_par_line_id_tbl(i), p_par_draft_id_tbl(i), 'UPDATE') LOOP
l_attach_count := get_attachment_count(shipments_rec.line_location_id, p_par_draft_id_tbl(i), 'PO_SHIPMENTS');
SELECT line_location_id
INTO l_mod_line_location_id
FROM po_line_locations_draft_all
WHERE draft_id = p_mod_draft_id
AND po_line_id =Nvl((SELECT mod_line_id
FROM po_lines_draft_all
WHERE po_line_id = p_par_line_id_tbl(i)
AND draft_id =p_par_draft_id_tbl(i)), p_par_line_id_tbl(i) )
AND shipment_num = (SELECT shipment_num
FROM po_line_locations_draft_all
WHERE line_location_id = shipments_rec.line_location_id) ;
SELECT Count(*) INTO l_count
FROM FND_ATTACHED_DOCUMENTS
WHERE entity_name = p_entity_name
AND pk1_value = p_pk1_value||'-'|| p_po_draft_id ;