[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT document_level
BULK COLLECT INTO x_doc_level_tbl
FROM po_uda_ag_templates
WHERE functional_area = p_func_area
AND ( p_doc_type IS NULL
OR document_type = p_doc_type
)
AND ( p_doc_style IS NULL
OR document_style_id = p_doc_style
)
AND document_level <> 'DISTRIBUTION'; -- bug 12611018
SELECT DISTINCT ag.attr_group_name
BULK COLLECT INTO x_attr_group_tbl
FROM po_uda_ag_template_usages tu, ego_attr_groups_v ag
WHERE ag.attr_group_id = tu.attribute_group_id
AND tu.template_id = p_uda_template_id
AND tu.attribute_category NOT IN ('PRICING','DOCUMENT_NUMBERING'); --12611018 Document numbering added
SELECT priority1,
priority2,
priority3,
priority4,
priority5
INTO l_priorities_tbl(1),
l_priorities_tbl(2),
l_priorities_tbl(3),
l_priorities_tbl(4),
l_priorities_tbl(5)
FROM po_autocreate_uda_rules
WHERE source = p_src_doc_type
AND target = p_tar_doc_type
AND target_doc_level = p_doc_level
AND group_name = p_attr_group
AND nvl ( group_property,'a') = nvl(p_group_property,'a');
SELECT Decode (p_src_doc_type,'REQ', 'PR'
,'OFFER','OFR')
INTO x_priorities_tbl(1)
FROM dual;*/
SELECT source_doc_level,group_type
INTO x_source_doc_level,x_group_type
FROM po_autocreate_uda_rules
WHERE source = p_src_doc_type
AND target = p_tar_doc_type
AND target_doc_level = p_doc_level
AND group_name = p_attr_group
AND (p_group_property IS NULL
OR group_property = p_group_property);
SELECT group_property
BULK COLLECT INTO x_group_property_tbl
FROM po_autocreate_uda_rules
WHERE source = p_src_doc_type
AND target = p_tar_doc_type
AND target_doc_level = p_doc_level
AND group_name = p_attr_group;
SELECT NULL
BULK COLLECT INTO x_group_property_tbl
FROM dual;
SELECT entity_code
INTO l_ag_type
FROM po_uda_ag_templates
WHERE 1=1
AND functional_area = p_func_area
AND Nvl(document_type,'@@@') = Nvl(p_doc_type,'@@@')
AND Nvl(document_style_id,-99) = Nvl(p_doc_style,-99)
AND document_level = p_doc_level
AND SYSDATE BETWEEN effective_from AND Nvl(effective_to,SYSDATE+1)
AND compiled_flag = 'Y';
SELECT ext_table_name
INTO l_ext_table_name
FROM ego_attr_group_types_v
WHERE 1=1
AND attr_group_type = p_ag_type;
l_query := 'SELECT uda_template_id FROM ' || p_table_name
||' WHERE '|| p_pkey1_col_name || ' = ' || p_pkey1_val;
SELECT type_lookup_code
INTO l_doc_type
FROM po_headers_all
WHERE po_header_id = po_autocreate_params.g_clm_source_document_id;
SELECT bid_number
INTO x_pk1_value
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND ROWNUM=1;
SELECT bid_line_number INTO x_pk2_value
FROM po_lines_interface
WHERE interface_header_id = po_autocreate_params.g_interface_header_id
AND interface_line_id = p_intf_line_id;
SELECT AUCTION_HEADER_ID
INTO x_pk1_value
FROM pon_bid_headers
WHERE BID_NUMBER=
(SELECT bid_number
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND ROWNUM=1);
SELECT bid.auction_line_number INTO x_pk2_value
FROM po_lines_interface PLI, pon_bid_item_prices bid
WHERE 1=1
AND PLI.interface_line_id = p_intf_line_id
AND bid.bid_number = PLI.bid_number
AND bid.line_number = PLI.bid_line_number;
SELECT requisition_header_id
INTO x_pk1_value
FROM po_requisition_lines_all
WHERE requisition_line_id =(
SELECT requisition_line_id
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND requisition_line_id IS NOT NULL
AND ROWNUM=1);
SELECT requisition_line_id
INTO x_pk1_value
FROM PO_LINES_INTERFACE
WHERE interface_line_id= p_intf_line_id;
SELECT IDV_HEADER_ID , -1
INTO x_pk1_value , x_pk2_value
FROM pon_bid_headers
WHERE BID_NUMBER=
(SELECT bid_number
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND ROWNUM=1);
SELECT style_id INTO l_doc_style
FROM po_headers_all
WHERE po_header_id = x_pk1_value;
SELECT bid.idv_line_id, -1 INTO x_pk1_value, x_pk2_value
FROM po_lines_interface PLI, pon_bid_item_prices bid
WHERE 1=1
AND PLI.interface_line_id = p_intf_line_id
AND bid.bid_number = PLI.bid_number
AND bid.line_number = PLI.bid_line_number;
SELECT requisition_header_id
INTO x_pk1_value
FROM po_requisition_lines_all
WHERE requisition_line_id =( SELECT requisition_line_id
FROM PO_LINES_INTERFACE
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND ROWNUM=1);
SELECT requisition_line_id INTO x_pk1_value
FROM PO_LINES_INTERFACE
WHERE interface_line_id= p_intf_line_id;
SELECT Nvl(from_header_id,contract_id), -1 INTO x_pk1_value, x_pk2_value
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND (from_header_id is not NULL OR contract_id IS NOT NULL)
AND ROWNUM=1;
SELECT style_id INTO l_doc_style
FROM po_headers_all
WHERE po_header_id = x_pk1_value;
SELECT from_line_id,-1 INTO x_pk1_value, x_pk2_value
FROM po_lines_interface
WHERE interface_line_id= p_intf_line_id;
SELECT from_header_id INTO x_pk1_value
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND from_header_id is not null
AND ROWNUM=1;
SELECT bid_number
INTO x_pk1_value
FROM pon_bid_headers
WHERE po_header_id = x_pk1_value;
SELECT from_line_id INTO x_pk2_value
FROM po_lines_interface
WHERE interface_line_id= p_intf_line_id;
SELECT line_number
INTO x_pk2_value
FROM pon_bid_item_prices
WHERE line_number = x_pk2_value
AND bid_number = x_pk1_value;
SELECT from_header_id INTO x_pk1_value
FROM po_lines_interface
WHERE interface_header_id=po_autocreate_params.g_interface_header_id
AND ROWNUM=1;
SELECT auction_header_id
INTO x_pk1_value
FROM pon_bid_headers
WHERE po_header_id = x_pk1_value;
SELECT from_line_id INTO x_pk2_value
FROM po_lines_interface
WHERE interface_line_id= p_intf_line_id;
SELECT auction_line_number
INTO x_pk2_value
FROM pon_bid_item_prices
WHERE line_number = x_pk2_value
AND bid_number = x_pk1_value;
SELECT attr_group_id
INTO l_attr_group_id
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE = p_ag_type
AND ATTR_GROUP_NAME = p_attr_grp;
l_query := 'SELECT extension_id FROM '|| p_ext_table_name
||' WHERE ATTR_GROUP_ID = ' ||p_ag_id
||' AND '|| p_pk1_col_name || ' = ' || p_pk1_value
||' AND '|| p_where_col_name ||' = '|| ''''||p_address_type||''''
-- : Pickup only records which have not null location id/code & contact
||' AND (n_ext_attr1 IS NOT NULL '
||' OR n_ext_attr2 IS NOT NULL '
||' OR n_ext_attr3 IS NOT NULL ) ';
l_query := 'select count(1) from '|| p_ext_table_name
||' WHERE ATTR_GROUP_ID = ' || p_ag_id
||' AND '|| p_pk1_col_name || ' = PO_UDA_AUTOCREATE_PKG.get_pk1_value()';
SELECT p_attr_group
BULK COLLECT INTO l_attr_group_tbl
FROM dual;
SELECT interface_line_id, po_line_id
FROM (SELECT interface_line_id , po_line_id,
Rank() over(partition BY PLI.line_num order by PLI.line_num,
pli.shipment_num,
pli.unit_price,
PLI.interface_line_id) rn
FROM po_lines_interface pli
WHERE pli.interface_header_id = po_autocreate_params.g_interface_header_id
/*Bug : 13561015
Introducing an NVL condition which take NULL value(coming from SOURCING) in action
as NEW action.
*/
AND Nvl(pli.action,'NEW') = 'NEW'
)
WHERE rn =1;
SELECT interface_line_id,
line_location_id
FROM po_line_locations_draft_all poll,
(SELECT interface_line_id interface_line_id,
po_line_id po_line_id,
shipment_num shipment_num
FROM (SELECT interface_line_id,
PLI.po_line_id,
PLI.shipment_num,
Rank() over(PARTITION BY pli.line_num, pli.shipment_num
ORDER BY pli.line_num,
pli.shipment_num,
pli.unit_price,
pli.interface_line_id) rn
FROM po_lines_interface PLI
WHERE pli.interface_header_id = po_autocreate_params.g_interface_header_id
/*Bug : 13561015
Introducing an NVL condition which take NULL value(coming from SOURCING) in action
as NEW action.
*/
AND Nvl(pli.action,'NEW') = 'NEW')
WHERE rn = 1) intf_lines
WHERE poll.po_line_id = intf_lines.po_line_id
AND poll.shipment_num = intf_lines.shipment_num
AND poll.po_header_id = po_autocreate_params.g_po_header_id
AND draft_id = po_autocreate_params.g_draft_id;
SELECT PLI.interface_line_id,PLI.requisition_line_id,PLI.bid_number,PLI.bid_line_number,PLI.line_num
FROM po_line_types plt, po_lines_interface pli
WHERE plt.line_type_id = pli.line_type_id
AND plt.ORDER_TYPE_LOOKUP_CODE IN ('AMOUNT','FIXED PRICE')--Bug 9945827
AND PLI.interface_header_id = po_autocreate_params.g_interface_header_id
--CLM Phase 2 Autocreate Changes start
--Bug 13612198 : Comlpex pricing attributes were not copied.
/*Bug : 13561015
Introducing an NVL condition which take NULL value(coming from SOURCING) in action
as NEW action.
*/
and Nvl(pli.action,'NEW') = 'NEW'
AND line_num = (SELECT line_num
FROM po_lines_interface
WHERE interface_line_id = PLI.interface_line_id
GROUP BY line_num
HAVING Count(line_num) = 1 ) ;
SELECT pla.po_line_id
FROM po_lines_interface PLI,po_lines_draft_all pla
WHERE PLI.interface_header_id = po_autocreate_params.g_interface_header_id
AND PLI.interface_line_id = p_intf_line_id
AND PLI.line_num = pla.line_num
AND pla.draft_id = po_autocreate_params.g_draft_id;
SELECT ag.attr_group_name
BULK COLLECT INTO l_attr_group_tbl
FROM pon_bid_item_prices_ext_b bid
,ego_attr_groups_v ag
,po_uda_ag_template_usages tu
WHERE bid.bid_number = l_src_rec.bid_number
AND bid.line_number = l_src_rec.bid_line_number
AND bid.attr_group_id = ag.attr_group_id
AND ag.attr_group_id = tu.attribute_group_id
AND tu.template_id = l_src_template_id
AND tu.attribute_category = 'PRICING';
SELECT ag.attr_group_name
BULK COLLECT INTO l_attr_group_tbl
FROM po_req_lines_ext_b req
,ego_attr_groups_v ag
,po_uda_ag_template_usages tu
WHERE req.requisition_line_id = l_src_rec.requisition_line_id
AND req.attr_group_id = ag.attr_group_id
AND ag.attr_group_id = tu.attribute_group_id
AND tu.template_id = l_src_template_id
AND tu.attribute_category = 'PRICING';
UPDATE po_lines_draft_all
SET clm_uda_pricing_total = Decode(matching_basis, 'QUANTITY', quantity, amount)
WHERE po_line_id = l_to_pk1_value
AND draft_id = po_autocreate_params.g_draft_id
AND order_type_lookup_code IN ('AMOUNT','FIXED PRICE');
SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
SELECT DISTINCT LOOKUP_CODE LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = l_address_lookup_type
AND LOOKUP_CODE = p_address_type;
SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
INTO l_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'addresses';
SELECT data_level_name
INTO l_src_data_level
FROM ego_data_level_b
WHERE attr_group_type = p_ag_type
AND data_level_name NOT LIKE '%ARCHIVE%';
SELECT Value(a)
BULK COLLECT INTO l_process_attr_row_tbl
FROM TABLE(x_attributes_row_table) a
WHERE a.row_identifier = p_extension_id;
SELECT Value(b)
BULK COLLECT INTO l_process_attr_data_tbl
FROM TABLE(x_attributes_data_table) b
WHERE b.ROW_IDENTIFIER =p_extension_id;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_new_extension_id
FROM dual;
SELECT distinct pudat.attribute_group_id
BULK COLLECT INTO x_attr_group_id_tbl
FROM po_headers_draft_all poh,
po_uda_ag_template_usages pudat,
EGO_ACTION_DISPLAYS_B ead,
EGO_ACTIONS_B ea
WHERE ea.action_id = ead.action_id
AND ea.attr_group_id = pudat.attribute_group_id
AND ea.classification_code = To_Char(pudat.template_id )
AND ead.EXECUTION_METHOD = 'XPD'
AND poh.po_header_id = po_autocreate_params.g_po_header_id
AND poh.uda_template_id = pudat.template_id;
SELECT distinct pudat.attribute_group_id
BULK COLLECT INTO x_attr_group_id_tbl
FROM po_headers_draft_all poh,
po_uda_ag_template_usages pudat,
EGO_ACTION_DISPLAYS_B ead,
EGO_ACTIONS_B ea
WHERE ea.action_id = ead.action_id
AND ea.attr_group_id = pudat.attribute_group_id
AND ea.classification_code = To_Char(pudat.template_id )
AND ead.EXECUTION_METHOD = 'XPD'
AND poh.po_header_id = po_autocreate_params.g_po_header_id
AND pudat.template_id = (SELECT uda_template_id
FROM po_lines_draft_all
WHERE po_header_id = poh.po_header_id
AND ROWNUM < 2) ;
SELECT distinct pudat.attribute_group_id
BULK COLLECT INTO x_attr_group_id_tbl
FROM po_headers_draft_all poh,
po_uda_ag_template_usages pudat,
EGO_ACTION_DISPLAYS_B ead,
EGO_ACTIONS_B ea
WHERE ea.action_id = ead.action_id
AND ea.attr_group_id = pudat.attribute_group_id
AND ea.classification_code = To_Char(pudat.template_id )
AND ead.EXECUTION_METHOD = 'XPD'
AND poh.po_header_id = po_autocreate_params.g_po_header_id
AND pudat.template_id = (SELECT uda_template_id
FROM po_line_locations_draft_all
WHERE po_header_id = poh.po_header_id
AND ROWNUM < 2) ;
SELECT distinct pudat.attribute_group_id
BULK COLLECT INTO x_attr_group_id_tbl
FROM po_headers_all poh,
po_uda_ag_template_usages pudat,
EGO_ACTION_DISPLAYS_B ead,
EGO_ACTIONS_B ea
WHERE ea.action_id = ead.action_id
AND ea.attr_group_id = pudat.attribute_group_id
AND ea.classification_code = To_Char(pudat.template_id )
AND ead.EXECUTION_METHOD = 'XPD'
AND poh.po_header_id = po_autocreate_params.g_po_header_id
AND poh.uda_template_id = pudat.template_id;
SELECT distinct pudat.attribute_group_id
BULK COLLECT INTO x_attr_group_id_tbl
FROM po_headers_all poh,
po_uda_ag_template_usages pudat,
EGO_ACTION_DISPLAYS_B ead,
EGO_ACTIONS_B ea
WHERE ea.action_id = ead.action_id
AND ea.attr_group_id = pudat.attribute_group_id
AND ea.classification_code = To_Char(pudat.template_id )
AND ead.EXECUTION_METHOD = 'XPD'
AND poh.po_header_id = po_autocreate_params.g_po_header_id
AND pudat.template_id = (SELECT uda_template_id
FROM po_lines_all
WHERE po_header_id = poh.po_header_id
AND ROWNUM < 2) ;
SELECT distinct pudat.attribute_group_id
BULK COLLECT INTO x_attr_group_id_tbl
FROM po_headers_all poh,
po_uda_ag_template_usages pudat,
EGO_ACTION_DISPLAYS_B ead,
EGO_ACTIONS_B ea
WHERE ea.action_id = ead.action_id
AND ea.attr_group_id = pudat.attribute_group_id
AND ea.classification_code = To_Char(pudat.template_id )
AND ead.EXECUTION_METHOD = 'XPD'
AND poh.po_header_id = po_autocreate_params.g_po_header_id
AND pudat.template_id = (SELECT uda_template_id
FROM po_line_locations_all
WHERE po_header_id = poh.po_header_id
AND ROWNUM < 2) ;
SELECT pld.po_line_id
FROM po_lines_draft_all pld, po_lines_interface PLI
WHERE
--bug 13640946 commenting out the condition as template_id has been stamped in the drafts table before making thisa uda call.
--pld.uda_template_id IS NULL AND
PLI.interface_header_id = po_autocreate_params.g_interface_header_id
AND PLD.po_header_id = po_autocreate_params.g_po_header_id
AND pld. line_num =PLI.line_num
AND pld. draft_id = po_autocreate_params.g_draft_id
;
SELECT pll.line_location_id
FROM po_lines_draft_all pld, po_lines_interface PLI, po_line_locations_draft_all pll
WHERE
--bug 13640946 commenting out the condition as template_id has been stamped in the drafts table before making thisa uda call.
--pll.uda_template_id IS NULL AND
PLI.interface_header_id = po_autocreate_params.g_interface_header_id
AND PLD.po_header_id = po_autocreate_params.g_po_header_id
AND pld. line_num =PLI.line_num
AND pld.po_line_id = pll.po_line_id
AND pll.shipment_num=PLI.shipment_num
AND pld. draft_id = po_autocreate_params.g_draft_id
AND pll.draft_id = po_autocreate_params.g_draft_id
;
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_name;
SELECT entity_code
INTO l_entity_code
FROM po_uda_ag_templates
WHERE template_id = p_template_id;
SELECT data_level_name
INTO l_data_level
FROM ego_data_level_b
WHERE attr_group_type = l_entity_code
AND data_level_name NOT LIKE '%ARCHIVE%';
SELECT ATTR_GROUP_NAME
INTO l_attr_group_name
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE = l_entity_code
AND attr_group_id = p_attr_group_id_tbl(i);
SELECT interface_source_code, document_type_code, document_subtype, style_id
INTO l_interface_source_code, l_document_type, l_document_subtype, l_document_style_id
FROM po_headers_interface
WHERE interface_header_id = po_autocreate_params.g_interface_header_id;
select authorization_status
INTO l_authorization_status
FROM po_headers_all
WHERE po_header_id = PO_AUTOCREATE_PARAMS.g_po_header_id;
SELECT ENTITY_CODE
INTO l_attr_group_Type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = x_src_template_id;
SELECT object_id
INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = l_object_dff_cr.l_object_name;
SELECT data_level_id
INTO l_main_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_group_Type
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';