[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT classification_code
FROM EGO_OBJ_AG_ASSOCS_B
WHERE object_id = x_object_id
AND data_level = x_data_level_name
AND attr_group_id = x_attr_group_id;
SELECT 'BC:'||lookup_code AS Code
FROM pos_bus_class_attr
WHERE party_id = x_party_id
AND start_date_active <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND status = 'A';
SELECT DISTINCT 'AC:'||hzl.country AS Code
FROM hz_locations hzl, hz_party_sites hzps
WHERE hzps.party_id = x_party_id
AND hzl.location_id = hzps.location_id;
SELECT 'AP:' || site_use_type AS Code
FROM hz_party_sites hzps, hz_party_site_uses hzpsu
WHERE hzps.party_id = x_party_id
AND hzpsu.party_site_id = hzps.party_site_id
AND hzpsu.status = 'A'
UNION
SELECT 'AP:PURCHASING' as code
FROM ap_supplier_sites_all
WHERE vendor_id = x_vendor_id
AND purchasing_site_flag = 'Y'
UNION
SELECT 'AP:PAY'
FROM ap_supplier_sites_all
WHERE vendor_id = x_vendor_id
AND pay_site_flag = 'Y'
UNION
SELECT 'AP:PRIMARY_PAY'
FROM ap_supplier_sites_all
WHERE vendor_id = x_vendor_id
AND primary_pay_site_flag = 'Y'
UNION
SELECT 'AP:RFQ'
FROM ap_supplier_sites_all
WHERE vendor_id = x_vendor_id
AND rfq_only_site_flag = 'Y'
UNION
SELECT 'AP:PCARD'
FROM ap_supplier_sites_all
WHERE vendor_id = x_vendor_id
AND pcard_site_flag = 'Y';
SELECT 'PS:' || pos_product_service_utl_pkg.get_concat_code(classification_id) as code
FROM pos_sup_products_services
WHERE vendor_id = x_vendor_id;
SELECT 'HZ:'|| hccr.class_category || ':' || hccr.class_code AS code
FROM hz_class_code_relations hccr,
( SELECT class_category, class_code, owner_table_id
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_party_id
AND start_date_active <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND status = 'A' ) v
WHERE hccr.class_category = v.class_category
START WITH hccr.class_code = v.class_code
CONNECT BY PRIOR hccr.class_code = hccr.sub_class_code
UNION
SELECT 'HZ:'|| fnd.lookup_type || ':' || fnd.lookup_code
FROM fnd_lookup_values_vl fnd,
( SELECT class_category, class_code, owner_table_id
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_party_id
AND start_date_active <= SYSDATE
AND nvl(end_date_active, SYSDATE) >= SYSDATE
AND status = 'A' ) v
WHERE fnd.lookup_type = v.class_category
AND fnd.lookup_code = v.class_code;
SELECT 'ST:'||vendor_type_lookup_code
INTO st_code
FROM AP_SUPPLIERS
WHERE party_id = p_party_id;
SELECT ITEM_CATALOG_GROUP_ID
INTO l_item_catalog_group_id
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_item_id
AND ROWNUM = 1;
SELECT DISTINCT
ATTR_GROUP_ID
, DATA_LEVEL_ID
, LINE_NUMBER
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_header_id
AND MAPPING_TYPE IN ('DOC_HEADER','DOC_REQ', 'CAT_LINE', 'DOC_SEC_SCORE'); --Bug13471195 Header Mapping Issue
SELECT SECTION_ID
, SEQUENCE_NUMBER
, MAPPING_TYPE
, RESPONSE
, ATTR_INT_NAME
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_header_id
AND ATTR_GROUP_ID = p_attr_group_id
AND DATA_LEVEL_ID = p_data_level_id
AND ( ( LINE_NUMBER = p_line_number AND mapping_type IN ('DOC_REQ', 'CAT_LINE', 'DOC_SEC_SCORE') )
OR ( LINE_NUMBER = -1 AND mapping_type = 'DOC_HEADER') );
SELECT DISTINCT
ATTR_GROUP_ID
, DATA_LEVEL_ID
, LINE_NUMBER
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_header_id
AND MAPPING_TYPE IN ('ITEM_LINE','ITEM_HEADER'); ----Bug13471195 Header Mapping Issue
SELECT SEQUENCE_NUMBER
, MAPPING_TYPE
, RESPONSE
, ATTR_INT_NAME
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_header_id
AND ATTR_GROUP_ID = p_attr_group_id
AND DATA_LEVEL_ID = p_data_level_id
AND ( ( LINE_NUMBER = p_line_number AND mapping_type = 'ITEM_LINE' )
OR ( LINE_NUMBER = -1 AND mapping_type = 'ITEM_HEADER') );
SELECT AUCTION_TITLE
, DOCUMENT_NUMBER
, INTGR_HDR_ATTR_FLAG
, INTGR_CAT_LINE_ATTR_FLAG
, INTGR_ITEM_LINE_ATTR_FLAG
, HDR_ATTR_ENABLE_WEIGHTS
INTO l_auction_title
, l_document_number
, l_intgr_hdr_flag
, l_intgr_cat_line_flag
, l_intgr_item_line_flag
, l_hdr_enable_weights_flag
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id;
SELECT HZ1.PARTY_NAME
, HZ2.PARTY_NAME
, PON.PUBLISH_DATE
, PON.TRADING_PARTNER_ID
, PON.VENDOR_ID
, PON.VENDOR_SITE_ID
, AP.VENDOR_NAME
, PON.EVALUATION_FLAG
INTO l_evaluator_name
, l_supp_contact_name
, l_publish_date
, l_party_id
, l_vendor_id
, l_vendor_site_id
, l_vendor_name
, l_evaluation_flag
FROM PON_BID_HEADERS PON
, HZ_PARTIES HZ1
, HZ_PARTIES HZ2
, AP_SUPPLIERS AP
WHERE PON.AUCTION_HEADER_ID = p_auction_header_id
AND PON.BID_NUMBER = p_bid_number
AND HZ1.PARTY_ID (+) = PON.EVALUATOR_ID
AND HZ2.PARTY_ID (+) = PON.TRADING_PARTNER_CONTACT_ID
AND AP.VENDOR_ID = PON.VENDOR_ID;
l_row_attrs_table.DELETE;
SELECT ATTR_GROUP_NAME
, ATTR_GROUP_DISP_NAME
INTO l_attr_group_name
, l_attr_group_disp_name
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_ID = supp_ag_dl_rec.attr_group_id;
SELECT DATA_LEVEL_NAME
, USER_DATA_LEVEL_NAME
INTO l_data_level_name
, l_user_data_level_name
FROM EGO_DATA_LEVEL_VL
WHERE DATA_LEVEL_ID = supp_ag_dl_rec.data_level_id;
SELECT 'NUM'
, NULL
, DECODE(l_hdr_enable_weights_flag, 'Y', SUM(WEIGHTED_SCORE), 'N', SUM(SCORE), NULL)
INTO l_datatype
, l_value
, l_score
FROM PON_BID_ATTRIBUTE_VALUES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND BID_NUMBER = p_bid_number
AND AUCTION_LINE_NUMBER = supp_ag_dl_rec.line_number;
SELECT 'NUM'
, NULL
, DECODE(l_hdr_enable_weights_flag, 'Y', SUM(WEIGHTED_SCORE), 'N', SUM(SCORE), NULL)
INTO l_datatype
, l_value
, l_score
FROM PON_BID_ATTRIBUTE_VALUES BID
, PON_AUCTION_ATTRIBUTES ATTR
, PON_AUCTION_SECTIONS SEC
WHERE BID.AUCTION_HEADER_ID = p_auction_header_id
AND BID.BID_NUMBER = p_bid_number
AND BID.AUCTION_LINE_NUMBER = supp_ag_dl_rec.line_number
AND BID.AUCTION_HEADER_ID = ATTR.AUCTION_HEADER_ID
AND BID.AUCTION_HEADER_ID = SEC.AUCTION_HEADER_ID
AND BID.LINE_NUMBER = ATTR.LINE_NUMBER
AND BID.LINE_NUMBER = SEC.LINE_NUMBER
AND ATTR.SECTION_NAME = SEC.SECTION_NAME
AND SEC.SECTION_ID = supp_mapping_setup_rec.section_id
AND BID.SEQUENCE_NUMBER = ATTR.SEQUENCE_NUMBER;
SELECT DATATYPE
, VALUE
, DECODE(l_hdr_enable_weights_flag, 'Y', WEIGHTED_SCORE, 'N', SCORE, NULL)
INTO l_datatype
, l_value
, l_score
FROM PON_BID_ATTRIBUTE_VALUES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND BID_NUMBER = p_bid_number
AND AUCTION_LINE_NUMBER = supp_ag_dl_rec.line_number
AND SEQUENCE_NUMBER = supp_mapping_setup_rec.sequence_number;
SELECT VALUE_SET_ID
INTO l_value_set_id
FROM EGO_ATTRS_V
WHERE ATTR_GROUP_NAME = l_attr_group_name
AND ATTR_NAME = supp_mapping_setup_rec.attr_int_name;
l_row_attrs_table.DELETE;
SELECT ATTR_GROUP_NAME
, ATTR_GROUP_DISP_NAME
INTO l_attr_group_name
, l_attr_group_disp_name
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_ID = item_ag_dl_rec.attr_group_id;
SELECT DATA_LEVEL_NAME
, USER_DATA_LEVEL_NAME
INTO l_data_level_name
, l_user_data_level_name
FROM EGO_DATA_LEVEL_VL
WHERE DATA_LEVEL_ID = item_ag_dl_rec.data_level_id;
SELECT ITEM_ID
, ITEM_NUMBER
, F.INVENTORY_ORGANIZATION_ID
INTO l_item_id
, l_item_number
, l_org_id
FROM PON_AUCTION_ITEM_PRICES_ALL P
, FINANCIALS_SYSTEM_PARAMS_ALL F
WHERE P.AUCTION_HEADER_ID = p_auction_header_id
AND P.LINE_NUMBER = item_ag_dl_rec.line_number
AND P.ORG_ID = F.ORG_ID;
SELECT SHIP_TO_LOCATION_ID
INTO l_ship_to_org_id
FROM PON_BID_ITEM_PRICES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND BID_NUMBER = p_bid_number
AND LINE_NUMBER = item_ag_dl_rec.line_number;
SELECT DATATYPE
, VALUE
, DECODE(l_hdr_enable_weights_flag, 'Y', WEIGHTED_SCORE, 'N', SCORE, NULL)
INTO l_datatype
, l_value
, l_score
FROM PON_BID_ATTRIBUTE_VALUES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND BID_NUMBER = p_bid_number
AND AUCTION_LINE_NUMBER = item_ag_dl_rec.line_number
AND SEQUENCE_NUMBER = item_mapping_setup_rec.sequence_number;
SELECT VALUE_SET_ID
INTO l_value_set_id
FROM EGO_ATTRS_V
WHERE ATTR_GROUP_NAME = l_attr_group_name
AND ATTR_NAME = item_mapping_setup_rec.attr_int_name;
SELECT item.bid_number
, item.line_number
, bid.vendor_id
, bid.vendor_site_id
, item.ship_to_location_id
, item.category_id
, item.approval_status
, f.inventory_organization_id
, NVL(hl.inventory_organization_id, -1) ship_to_org_id
FROM pon_bid_headers bid
, pon_bid_item_prices item
, pon_auction_item_prices_all price
, financials_system_params_all f
, hr_locations_all hl
WHERE bid.bid_number = item.bid_number
AND item.line_number = price.line_number
AND bid.auction_header_id = price.auction_header_id
AND price.item_id IS NULL
AND bid.auction_header_id = p_header_id
AND item.approval_status = 'APPROVED'
AND f.org_id = price.org_id
AND hl.location_id(+) = item.ship_to_location_id;
SELECT INTGR_CAT_LINE_ASL_FLAG
INTO l_intgr_cat_line_asl
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id;
PO_ASL_THS.insert_row(
l_asl_row_id,
l_asl_id,
cate_lin_rec.ship_to_org_id, -- ship to org
cate_lin_rec.inventory_organization_id, --x_owning_organization_id
'DIRECT', --x_vendor_business_type, -- direct, manufacture, or distributor, if distributor, need to provide manufacturer_id
2, --x_asl_status_id, 1 for new, 2 for approved
sysdate, --x_last_update_date,
fnd_global.user_id, --x_last_updated_by,
sysdate, --x_creation_date,
fnd_global.user_id, --x_created_by,
NULL, --x_manufacturer_id,
cate_lin_rec.vendor_id, --x_vendor_id,
NULL, --x_item_id,
cate_lin_rec.category_id, --x_category_id,
cate_lin_rec.vendor_site_id, --x_vendor_site_id,
NULL, --x_primary_vendor_item,
NULL, --x_manufacturer_asl_id,
NULL, --x_comments,
NULL, --x_review_by_date,
NULL, --x_attribute_category,
NULL, --x_attribute1,
NULL, --x_attribute2,
NULL, --x_attribute3,
NULL, --x_attribute4,
NULL, --x_attribute5,
NULL, --x_attribute6,
NULL, --x_attribute7,
NULL, --x_attribute8,
NULL, --x_attribute9,
NULL, --x_attribute10,
NULL, --x_attribute11,
NULL, --x_attribute12,
NULL, --x_attribute13,
NULL, --x_attribute14,
NULL, --x_attribute15,
fnd_global.user_id, --x_last_update_login,
NULL --x_disable_flag
);
PO_ASL_ATTRIBUTES_THS.insert_row(
l_attr_row_id,
l_asl_id,
cate_lin_rec.ship_to_org_id, --x_using_organization_id NUMBER,
sysdate, --x_last_update_date DATE,
fnd_global.user_id, --x_last_updated_by NUMBER,
sysdate, --x_creation_date DATE,
fnd_global.user_id, --x_created_by NUMBER,
'ASL', --x_document_sourcing_method VARCHAR2,
NULL, --x_release_generation_method VARCHAR2,
NULL, --x_purchasing_unit_of_measure VARCHAR2,
'N', --x_enable_plan_schedule_flag VARCHAR2,
'N', --x_enable_ship_schedule_flag VARCHAR2,
NULL, --x_plan_schedule_type VARCHAR2,
NULL, --x_ship_schedule_type VARCHAR2,
NULL, --x_plan_bucket_pattern_id NUMBER,
NULL, --x_ship_bucket_pattern_id NUMBER,
'N', --x_enable_autoschedule_flag VARCHAR2,
NULL, --x_scheduler_id NUMBER,
'N', --x_enable_authorizations_flag VARCHAR2,
cate_lin_rec.vendor_id, --x_vendor_id NUMBER,
cate_lin_rec.vendor_site_id, --x_site_id NUMBER,
NULL, --x_item_id NUMBER,
cate_lin_rec.category_id, --x_category_id NUMBER,
NULL, --x_attribute_category VARCHAR2,
NULL, --x_attribute1 VARCHAR2,
NULL, --x_attribute2 VARCHAR2,
NULL, --x_attribute3 VARCHAR2,
NULL, --x_attribute4 VARCHAR2,
NULL, --x_attribute5 VARCHAR2,
NULL, --x_attribute6 VARCHAR2,
NULL, --x_attribute7 VARCHAR2,
NULL, --x_attribute8 VARCHAR2,
NULL, --x_attribute9 VARCHAR2,
NULL, --x_attribute10 VARCHAR2,
NULL, --x_attribute11 VARCHAR2,
NULL, --x_attribute12 VARCHAR2,
NULL, --x_attribute13 VARCHAR2,
NULL, --x_attribute14 VARCHAR2,
NULL, --x_attribute15 VARCHAR2,
fnd_global.user_id, --x_last_update_login NUMBER,
NULL, --x_price_update_tolerance NUMBER,
NULL, --x_processing_lead_time NUMBER,
NULL, --x_delivery_calendar VARCHAR2,
NULL, --x_min_order_qty NUMBER,
NULL, --x_fixed_lot_multiple NUMBER,
NULL, --x_country_of_origin_code VARCHAR2,
NULL, --x_enable_vmi_flag VARCHAR2,
NULL, --x_vmi_min_qty NUMBER,
NULL, --x_vmi_max_qty NUMBER,
NULL, --x_enable_vmi_auto_repl_flag VARCHAR2,
NULL, --x_vmi_replenishment_approval VARCHAR2,
NULL, --x_consigned_from_supplier_flag VARCHAR2,
NULL, --x_consigned_billing_cycle NUMBER ,
NULL, --x_last_billing_date DATE,
NULL, --x_replenishment_method NUMBER,
NULL, --x_vmi_min_days NUMBER,
NULL, --x_vmi_max_days NUMBER,
NULL, --x_fixed_order_quantity NUMBER,
NULL, --x_forecast_horizon NUMBER,
NULL, --x_consume_on_aging_flag VARCHAR2,
NULL --x_aging_period NUMBER
);
SELECT BID_NUMBER
FROM PON_BID_HEADERS
WHERE AUCTION_HEADER_ID = p_header_id
AND BID_STATUS = 'ACTIVE'
AND VENDOR_ID <> -1
ORDER BY VENDOR_ID,
EVALUATION_FLAG DESC,
BID_NUMBER;
SELECT BID.AUCTION_HEADER_ID
, BID.BID_NUMBER
FROM PON_BID_HEADERS BID
, PON_AUCTION_HEADERS_ALL AUCTION
WHERE BID.VENDOR_ID = p_v_id
AND BID.AUCTION_HEADER_ID = AUCTION.AUCTION_HEADER_ID
AND AUCTION.AUCTION_STATUS = 'AUCTION_CLOSED'
ORDER BY AUCTION_HEADER_ID,
EVALUATION_FLAG DESC,
BID_NUMBER;