[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type ='OZF_SD_REQUEST_STATUS'
AND enabled_flag ='Y'
AND default_flag ='Y'
AND system_status_code = p_system_status_code;
SELECT system_status_code
FROM ams_user_statuses_b
WHERE system_status_type ='OZF_SD_REQUEST_STATUS'
AND enabled_flag ='Y'
AND user_status_id = p_user_status_id ;
SELECT COUNT(1)
FROM ozf_sd_status_transitions
WHERE enabled_flag ='Y'
AND from_status = p_from_status
AND to_status = p_to_status
AND NVL(owner_flag,'N') = p_cur_owner_flag
AND NVL(product_manager_flag,'N') = p_cur_pm_flag
AND external_flag = p_external_flag
AND system_flag IS NULL;
SELECT COUNT(1)
FROM ozf_sd_status_transitions
WHERE enabled_flag ='Y'
AND from_status = p_from_status
AND to_status = p_to_status
AND NVL(owner_flag,'N') = p_cur_owner_flag
AND NVL(product_manager_flag,'N') = p_cur_pm_flag
AND internal_flag = p_internal_flag
AND system_flag IS NULL;
SELECT user_id
FROM fnd_user
WHERE user_id =p_user_id;
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE MO_GLOBAL.check_access(ou.organization_id) = 'Y'
AND ou.organization_id =p_org_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE start_date_active <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
AND resource_id > 0
AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
AND user_id = p_user_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE start_date_active <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
AND resource_id > 0
AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
AND resource_id = p_requestor_id;
SELECT vendor_id
FROM ap_suppliers
WHERE vendor_id = p_supplier_id;
SELECT vendor_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_supplier_site_id
AND vendor_id = p_supplier_id
AND org_id = p_org_id;
SELECT apc.vendor_contact_id
,apc.area_code||apc.phone phone_number
,apc.email_address
,decode(pvc.last_name,null,null,'','',pvc.last_name || ', ') || nvl(pvc.middle_name, '')|| ' '|| pvc.first_name AS Sup_contact_full_name
FROM ap_supplier_contacts apc,po_vendor_contacts pvc
WHERE apc.vendor_site_id = pvc.vendor_site_id
AND apc.vendor_contact_id = pvc.vendor_contact_id
AND NVL(pvc.inactive_date, SYSDATE +1) > SYSDATE
AND apc.vendor_site_id = p_supplier_site_id
AND apc.vendor_contact_id = p_vendor_contact_id;
SELECT currency_code
FROM fnd_currencies
WHERE currency_code = p_currency_code
AND enabled_flag='Y';
SELECT cust_account_id
FROM hz_cust_accounts
WHERE status ='A'
AND customer_type ='I'
AND cust_account_id =p_cust_account_id;
SELECT language_code
FROM fnd_languages
WHERE language_code =p_language_code;
SELECT NVL(authorization_period,-1)
FROM ozf_supp_trd_prfls_all
WHERE supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND org_id = p_org_id;
SELECT COUNT(1)
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = p_request_header_id;
SELECT COUNT(1)
FROM ozf_sd_request_headers_all_b
WHERE request_number =p_request_number;
SELECT system_status_code
FROM ams_user_statuses_b
WHERE system_status_type ='OZF_SD_REQUEST_STATUS'
AND user_status_id = p_user_status_id;
SELECT order_number
FROM oe_order_headers_all
WHERE order_number = p_internal_order_number
AND org_id = p_org_id;
SELECT master_organization_id
FROM oe_system_parameters
WHERE org_id = p_header_org_id;
SELECT request_line_id
FROM ozf_sd_request_lines_all
WHERE request_line_id = p_request_line_id
AND request_header_id = p_request_header_id;
SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT category_id
FROM mtl_categories_v
WHERE category_id = p_category_id;
SELECT b.category_set_id
FROM mtl_default_category_sets a ,
mtl_category_sets_b b ,
mtl_categories_v c
WHERE a.functional_area_id in (7,11)
AND a.category_set_id = b.category_set_id
AND b.structure_id = c.structure_id
AND c.category_id = p_category_id
AND a.category_set_id = p_category_set_id;
SELECT list_price_per_unit
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT a.uom_code
FROM mtl_units_of_measure a,
mtl_system_items c
WHERE c.primary_unit_of_measure = a.unit_of_measure
AND c.organization_id = p_org_id
AND c.inventory_item_id = p_inventory_item_id
AND uom_code = p_item_uom;
SELECT claim_computation_basis
FROM ozf_supp_trd_prfls_all otrpf
WHERE supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND org_id = p_org_id;
SELECT NVL(qty_increase_tolerance,0)
FROM ozf_supp_trd_prfls_all
WHERE supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND org_id = p_org_id;
SELECT object_version_number
FROM ozf_sd_request_lines_all
WHERE request_line_id = p_request_line_id;
SELECT external_code
FROM ozf_supp_code_conversions_all code,
ozf_supp_trd_prfls_all trd_profile
WHERE code.code_conversion_type = 'OZF_PRODUCT_CODES'
AND code.supp_trade_profile_id = trd_profile.supp_trade_profile_id
AND trd_profile.supplier_id = p_supplier_id
AND trd_profile.supplier_site_id = p_supplier_site_id
AND trd_profile.org_id = p_org_id
AND internal_code = p_inventory_item_id;
SELECT external_code, --Vendor Item Code
internal_code --Inventory Item ID
FROM ozf_supp_code_conversions_all code,
ozf_supp_trd_prfls_all trd_profile
WHERE code.code_conversion_type = 'OZF_PRODUCT_CODES'
AND code.supp_trade_profile_id = trd_profile.supp_trade_profile_id
AND trd_profile.supplier_id = p_supplier_id
AND trd_profile.supplier_site_id = p_supplier_site_id
AND trd_profile.org_id = p_org_id;
SELECT object_version_number,
request_header_id,
product_context,
inventory_item_id,
prod_catg_id,
product_cat_set_id,
product_cost,
item_uom,
requested_discount_type,
requested_discount_value,
cost_basis,
max_qty,
limit_qty,
design_win,
end_customer_price,
requested_line_amount,
approved_discount_type,
approved_discount_value,
approved_max_qty,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
vendor_approved_flag,
vendor_item_code,
start_date,
end_date,
end_customer_price_type,
end_customer_tolerance_type,
end_customer_tolerance_value,
org_id,
rejection_code,
requested_discount_currency,
product_cost_currency,
end_customer_currency,
approved_discount_currency
FROM ozf_sd_request_lines_all
WHERE request_line_id = p_request_line_id;
IF p_mode ='UPDATE' THEN
IF p_SDR_lines_tbl(i).request_line_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).request_line_id IS NOT NULL THEN
--Updating existing lines
OPEN c_request_line_id(p_SDR_lines_tbl(i).request_line_id,p_SDR_hdr_rec.request_header_id);
SELECT request_customer_id
FROM ozf_sd_customer_details
WHERE request_customer_id = p_request_customer_id
AND request_header_id = p_request_header_id ;
SELECT party_id
FROM hz_parties
WHERE party_id = p_party_id;
SELECT cust_acct.cust_account_id
FROM hz_parties party,
hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
AND cust_acct.status = 'A'
AND cust_acct.cust_account_id = p_cust_account_id;
SELECT sites.site_use_id
FROM hz_cust_site_uses sites,
hz_cust_acct_sites acct_sites,
hz_party_sites party_sites
WHERE sites.cust_acct_site_id = acct_sites.cust_acct_site_id
AND acct_sites.party_site_id = party_sites.party_site_id
AND acct_sites.cust_account_id = p_cust_account_id
AND party_sites.party_id = p_party_id
AND sites.site_use_id = p_site_use_id
AND sites.site_use_code = p_site_use_code;
SELECT object_version_number,
request_header_id,
cust_account_id,
party_id,
site_use_id,
cust_usage_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
end_customer_flag
FROM ozf_sd_customer_details
WHERE request_customer_id = p_request_customer_id;
IF p_mode ='UPDATE' THEN
IF p_SDR_cust_tbl(j).request_customer_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).request_customer_id IS NOT NULL THEN
OPEN c_request_customer_id (p_SDR_cust_tbl(j).request_customer_id,G_REQUEST_HEADER_ID);
PROCEDURE insert_header_record(
p_SDR_hdr_rec IN SDR_Hdr_rec_type
,p_request_source IN VARCHAR2
,x_request_header_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_SDR_hdr_rec OZF_SD_REQUEST_PUB.SDR_Hdr_rec_type := p_SDR_hdr_rec;
SELECT OZF_SD_REQUEST_HEADERS_ALL_B_S.nextval
FROM dual;
SELECT source_code_suffix
FROM ams_custom_setups_vl
WHERE custom_setup_id = p_request_type_setup_id;
OZF_UTILITY_PVT.debug_message('Inside INSERT HEADER RECORD Procedure ');
OZF_SD_REQUEST_HEADER_PKG.Insert_Row(
p_request_header_id =>l_req_hdr_seq
,p_object_version_number =>1
,p_last_update_date =>SYSDATE
,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
,p_creation_date =>SYSDATE
,p_created_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
,p_program_update_date =>SYSDATE
,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
,p_created_from =>NULL
,p_request_number =>l_SDR_hdr_rec.request_number
,p_request_class =>l_request_class
,p_offer_type =>NULL
,p_offer_id =>NULL
,p_root_request_header_id =>l_root_request_header_id
,p_linked_request_header_id =>NULL
,p_request_start_date =>l_SDR_hdr_rec.request_start_date
,p_request_end_date =>l_SDR_hdr_rec.request_end_date
,p_user_status_id =>l_SDR_hdr_rec.user_status_id
,p_request_outcome =>l_SDR_hdr_rec.request_outcome
,p_decline_reason_code =>NULL
,p_return_reason_code =>NULL
,p_request_currency_code =>l_SDR_hdr_rec.request_currency_code
,p_authorization_number =>l_SDR_hdr_rec.authorization_number
,p_sd_requested_budget_amount =>NULL
,p_sd_approved_budget_amount =>NULL
,p_attribute_category =>l_SDR_hdr_rec.attribute_category
,p_attribute1 =>l_SDR_hdr_rec.attribute1
,p_attribute2 =>l_SDR_hdr_rec.attribute2
,p_attribute3 =>l_SDR_hdr_rec.attribute3
,p_attribute4 =>l_SDR_hdr_rec.attribute4
,p_attribute5 =>l_SDR_hdr_rec.attribute5
,p_attribute6 =>l_SDR_hdr_rec.attribute6
,p_attribute7 =>l_SDR_hdr_rec.attribute7
,p_attribute8 =>l_SDR_hdr_rec.attribute8
,p_attribute9 =>l_SDR_hdr_rec.attribute9
,p_attribute10 =>l_SDR_hdr_rec.attribute10
,p_attribute11 =>l_SDR_hdr_rec.attribute11
,p_attribute12 =>l_SDR_hdr_rec.attribute12
,p_attribute13 =>l_SDR_hdr_rec.attribute13
,p_attribute14 =>l_SDR_hdr_rec.attribute14
,p_attribute15 =>l_SDR_hdr_rec.attribute15
,p_supplier_id =>l_SDR_hdr_rec.supplier_id
,p_supplier_site_id =>l_SDR_hdr_rec.supplier_site_id
,p_supplier_contact_id =>l_SDR_hdr_rec.supplier_contact_id
,p_internal_submission_date =>l_SDR_hdr_rec.internal_submission_date
,p_assignee_response_by_date =>l_SDR_hdr_rec.assignee_response_by_date
,p_assignee_response_date =>l_SDR_hdr_rec.assignee_response_date
,p_submtd_by_for_supp_appr =>l_SDR_hdr_rec.submtd_by_for_supp_approval
,p_supplier_response_by_date =>l_SDR_hdr_rec.supplier_response_by_date
,p_supplier_response_date =>l_SDR_hdr_rec.supplier_response_date
,p_supplier_submission_date =>l_SDR_hdr_rec.supplier_submission_date
,p_requestor_id =>l_SDR_hdr_rec.requestor_id
,p_supplier_quote_number =>l_SDR_hdr_rec.supplier_quote_number
,p_internal_order_number =>l_SDR_hdr_rec.internal_order_number
,p_sales_order_currency =>l_SDR_hdr_rec.sales_order_currency
,p_request_source => p_request_source
,p_assignee_resource_id =>l_SDR_hdr_rec.assignee_resource_id
,p_org_id =>l_SDR_hdr_rec.org_id
,p_security_group_id =>NULL
,p_accrual_type =>l_SDR_hdr_rec.accrual_type
,p_cust_account_id =>l_SDR_hdr_rec.cust_account_id
,p_supplier_email =>l_SDR_hdr_rec.supplier_contact_email_address
,p_supplier_phone =>l_SDR_hdr_rec.supplier_contact_phone_number
,p_request_type_setup_id =>l_SDR_hdr_rec.request_type_setup_id
,p_request_basis =>l_SDR_hdr_rec.request_basis
,p_supplier_contact_name =>l_SDR_hdr_rec.supplier_contact_name); --//Bugfix : 7822442
End insert_header_record;
INSERT INTO ozf_sd_request_headers_all_tl
(request_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_description,
language,
source_lang,
request_id,
program_application_id,
program_update_date,
program_id,
created_from,
security_group_id,
org_id)
SELECT
p_request_header_id,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.conc_login_id,-1),
p_description,
l.language_code,
USERENV('LANG'),
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE,
FND_GLOBAL.CONC_PROGRAM_ID,
NULL,
NULL,
p_org_id
FROM fnd_languages l
WHERE l.installed_flag IN('I', 'B')
AND NOT EXISTS(SELECT NULL
FROM ozf_sd_request_headers_all_tl t
WHERE t.request_header_id = p_request_header_id
AND t.language = l.language_code);
ELSIF p_mode ='UPDATE' THEN
UPDATE ozf_sd_request_headers_all_tl
SET request_description = p_description
,org_id = p_org_id
WHERE request_header_id =p_request_header_id;
SELECT ozf_sd_request_lines_all_s.nextval
FROM dual;
OZF_SD_REQUEST_LINES_PKG.Insert_Row(
p_request_line_id =>l_req_line_seq
,p_object_version_number =>1
,p_last_update_date =>SYSDATE
,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
,p_creation_date =>SYSDATE
,p_created_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
,p_program_update_date =>SYSDATE
,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
,p_create_from =>NULL
,p_request_header_id =>p_request_header_id
,p_product_context =>p_SDR_lines_tbl(p).product_context
,p_inventory_item_id =>p_SDR_lines_tbl(p).inventory_item_id
,p_prod_catg_id =>p_SDR_lines_tbl(p).prod_catg_id
,p_product_cat_set_id =>p_SDR_lines_tbl(p).product_cat_set_id
,p_product_cost =>p_SDR_lines_tbl(p).product_cost
,p_item_uom =>p_SDR_lines_tbl(p).item_uom
,p_requested_discount_type =>p_SDR_lines_tbl(p).requested_discount_type
,p_requested_discount_value =>p_SDR_lines_tbl(p).requested_discount_value
,p_cost_basis =>p_SDR_lines_tbl(p).cost_basis
,p_max_qty =>p_SDR_lines_tbl(p).max_qty
,p_limit_qty =>p_SDR_lines_tbl(p).limit_qty
,p_design_win =>p_SDR_lines_tbl(p).design_win
,p_end_customer_price =>p_SDR_lines_tbl(p).end_customer_price
,p_requested_line_amount =>p_SDR_lines_tbl(p).requested_line_amount
,p_approved_discount_type =>p_SDR_lines_tbl(p).approved_discount_type
,p_approved_discount_value =>p_SDR_lines_tbl(p).approved_discount_value
,p_approved_amount =>NULL
,p_total_requested_amount =>NULL
,p_total_approved_amount =>NULL
,p_approved_max_qty =>p_SDR_lines_tbl(p).approved_max_qty
,p_attribute_category =>p_SDR_lines_tbl(p).attribute_category
,p_attribute1 =>p_SDR_lines_tbl(p).attribute1
,p_attribute2 =>p_SDR_lines_tbl(p).attribute2
,p_attribute3 =>p_SDR_lines_tbl(p).attribute3
,p_attribute4 =>p_SDR_lines_tbl(p).attribute4
,p_attribute5 =>p_SDR_lines_tbl(p).attribute5
,p_attribute6 =>p_SDR_lines_tbl(p).attribute6
,p_attribute7 =>p_SDR_lines_tbl(p).attribute7
,p_attribute8 =>p_SDR_lines_tbl(p).attribute8
,p_attribute9 =>p_SDR_lines_tbl(p).attribute9
,p_attribute10 =>p_SDR_lines_tbl(p).attribute10
,p_attribute11 =>p_SDR_lines_tbl(p).attribute11
,p_attribute12 =>p_SDR_lines_tbl(p).attribute12
,p_attribute13 =>p_SDR_lines_tbl(p).attribute13
,p_attribute14 =>p_SDR_lines_tbl(p).attribute14
,p_attribute15 =>p_SDR_lines_tbl(p).attribute15
,p_vendor_approved_flag =>p_SDR_lines_tbl(p).vendor_approved_flag
,p_vendor_item_code =>p_SDR_lines_tbl(p).vendor_item_code
,p_start_date =>p_SDR_lines_tbl(p).start_date
,p_end_date =>p_SDR_lines_tbl(p).end_date
,p_end_customer_price_type =>p_SDR_lines_tbl(p).end_customer_price_type
,p_end_customer_tolerance_type =>p_SDR_lines_tbl(p).end_customer_tolerance_type
,p_end_customer_tolerance_value =>p_SDR_lines_tbl(p).end_customer_tolerance_value
,p_security_group_id =>NULL
,p_org_id =>G_ITEM_ORG_ID
,p_rejection_code =>p_SDR_lines_tbl(p).rejection_code
,p_discount_currency =>p_SDR_lines_tbl(p).requested_discount_currency
,p_product_cost_currency =>p_SDR_lines_tbl(p).product_cost_currency
,p_end_customer_currency =>p_SDR_lines_tbl(p).end_customer_currency
,p_approved_discount_currency =>p_SDR_lines_tbl(p).approved_discount_currency);
ELSE --UPDATE MODE
OZF_SD_REQUEST_LINES_PKG.Update_Row(
p_request_line_id =>p_SDR_lines_tbl(p).request_line_id
,p_object_version_number =>p_SDR_lines_tbl(p).object_version_number + 1
,p_last_update_date =>SYSDATE
,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
,p_program_update_date =>SYSDATE
,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
,p_create_from =>NULL
,p_request_header_id =>p_request_header_id
,p_product_context =>p_SDR_lines_tbl(p).product_context
,p_inventory_item_id =>p_SDR_lines_tbl(p).inventory_item_id
,p_prod_catg_id =>p_SDR_lines_tbl(p).prod_catg_id
,p_product_cat_set_id =>p_SDR_lines_tbl(p).product_cat_set_id
,p_product_cost =>p_SDR_lines_tbl(p).product_cost
,p_item_uom =>p_SDR_lines_tbl(p).item_uom
,p_requested_discount_type =>p_SDR_lines_tbl(p).requested_discount_type
,p_requested_discount_value =>p_SDR_lines_tbl(p).requested_discount_value
,p_cost_basis =>p_SDR_lines_tbl(p).cost_basis
,p_max_qty =>p_SDR_lines_tbl(p).max_qty
,p_limit_qty =>p_SDR_lines_tbl(p).limit_qty
,p_design_win =>p_SDR_lines_tbl(p).design_win
,p_end_customer_price =>p_SDR_lines_tbl(p).end_customer_price
,p_requested_line_amount =>p_SDR_lines_tbl(p).requested_line_amount
,p_approved_discount_type =>p_SDR_lines_tbl(p).approved_discount_type
,p_approved_discount_value =>p_SDR_lines_tbl(p).approved_discount_value
,p_approved_amount =>NULL
,p_total_requested_amount =>NULL
,p_total_approved_amount =>NULL
,p_approved_max_qty =>p_SDR_lines_tbl(p).approved_max_qty
,p_attribute_category =>p_SDR_lines_tbl(p).attribute_category
,p_attribute1 =>p_SDR_lines_tbl(p).attribute1
,p_attribute2 =>p_SDR_lines_tbl(p).attribute2
,p_attribute3 =>p_SDR_lines_tbl(p).attribute3
,p_attribute4 =>p_SDR_lines_tbl(p).attribute4
,p_attribute5 =>p_SDR_lines_tbl(p).attribute5
,p_attribute6 =>p_SDR_lines_tbl(p).attribute6
,p_attribute7 =>p_SDR_lines_tbl(p).attribute7
,p_attribute8 =>p_SDR_lines_tbl(p).attribute8
,p_attribute9 =>p_SDR_lines_tbl(p).attribute9
,p_attribute10 =>p_SDR_lines_tbl(p).attribute10
,p_attribute11 =>p_SDR_lines_tbl(p).attribute11
,p_attribute12 =>p_SDR_lines_tbl(p).attribute12
,p_attribute13 =>p_SDR_lines_tbl(p).attribute13
,p_attribute14 =>p_SDR_lines_tbl(p).attribute14
,p_attribute15 =>p_SDR_lines_tbl(p).attribute15
,p_vendor_approved_flag =>p_SDR_lines_tbl(p).vendor_approved_flag
,p_vendor_item_code =>p_SDR_lines_tbl(p).vendor_item_code
,p_start_date =>p_SDR_lines_tbl(p).start_date
,p_end_date =>p_SDR_lines_tbl(p).end_date
,p_end_customer_price_type =>p_SDR_lines_tbl(p).end_customer_price_type
,p_end_customer_tolerance_type =>p_SDR_lines_tbl(p).end_customer_tolerance_type
,p_end_customer_tolerance_value =>p_SDR_lines_tbl(p).end_customer_tolerance_value
,p_security_group_id =>NULL--p_SDR_lines_tbl(p).security_group_id
,p_org_id =>G_ITEM_ORG_ID
,p_rejection_code =>p_SDR_lines_tbl(p).rejection_code
,p_discount_currency =>p_SDR_lines_tbl(p).requested_discount_currency
,p_product_cost_currency =>p_SDR_lines_tbl(p).product_cost_currency
,p_end_customer_currency =>p_SDR_lines_tbl(p).end_customer_currency
,p_approved_discount_currency =>p_SDR_lines_tbl(p).approved_discount_currency);
SELECT OZF_SD_CUSTOMER_DETAILS_S.nextval
FROM dual;
SELECT COUNT(1)
FROM ozf_sd_customer_details
WHERE request_header_id = p_request_header_id
AND cust_usage_code = p_cust_usage_code
AND party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND end_customer_flag = p_end_customer_flag;
OZF_SD_CUSTOMER_PKG.Insert_Row(
p_request_customer_id =>l_request_cust_seq
,p_request_header_id =>p_request_header_id
,p_cust_account_id =>p_SDR_cust_tbl(c).cust_account_id
,p_party_id =>p_SDR_cust_tbl(c).party_id
,p_site_use_id =>p_SDR_cust_tbl(c).site_use_id
,p_cust_usage_code =>p_SDR_cust_tbl(c).cust_usage_code
,p_security_group_id =>NULL
,p_creation_date =>SYSDATE
,p_created_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_date =>SYSDATE
,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
,p_object_version_number =>1
,p_attribute_category =>p_SDR_cust_tbl(c).attribute_category
,p_attribute1 =>p_SDR_cust_tbl(c).attribute1
,p_attribute2 =>p_SDR_cust_tbl(c).attribute2
,p_attribute3 =>p_SDR_cust_tbl(c).attribute3
,p_attribute4 =>p_SDR_cust_tbl(c).attribute4
,p_attribute5 =>p_SDR_cust_tbl(c).attribute5
,p_attribute6 =>p_SDR_cust_tbl(c).attribute6
,p_attribute7 =>p_SDR_cust_tbl(c).attribute7
,p_attribute8 =>p_SDR_cust_tbl(c).attribute8
,p_attribute9 =>p_SDR_cust_tbl(c).attribute9
,p_attribute10 =>p_SDR_cust_tbl(c).attribute10
,p_attribute11 =>p_SDR_cust_tbl(c).attribute11
,p_attribute12 =>p_SDR_cust_tbl(c).attribute12
,p_attribute13 =>p_SDR_cust_tbl(c).attribute13
,p_attribute14 =>p_SDR_cust_tbl(c).attribute14
,p_attribute15 =>p_SDR_cust_tbl(c).attribute15
,p_end_customer_flag =>p_SDR_cust_tbl(c).end_customer_flag);
OZF_SD_CUSTOMER_PKG.Update_Row(
p_request_customer_id =>p_SDR_cust_tbl(c).request_customer_id
,p_request_header_id =>p_request_header_id
,p_cust_account_id =>p_SDR_cust_tbl(c).cust_account_id
,p_party_id =>p_SDR_cust_tbl(c).party_id
,p_site_use_id =>p_SDR_cust_tbl(c).site_use_id
,p_cust_usage_code =>p_SDR_cust_tbl(c).cust_usage_code
,p_security_group_id =>NULL
,p_last_update_date =>SYSDATE
,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
,p_object_version_number =>p_SDR_cust_tbl(c).object_version_number + 1
,p_attribute_category =>p_SDR_cust_tbl(c).attribute_category
,p_attribute1 =>p_SDR_cust_tbl(c).attribute1
,p_attribute2 =>p_SDR_cust_tbl(c).attribute2
,p_attribute3 =>p_SDR_cust_tbl(c).attribute3
,p_attribute4 =>p_SDR_cust_tbl(c).attribute4
,p_attribute5 =>p_SDR_cust_tbl(c).attribute5
,p_attribute6 =>p_SDR_cust_tbl(c).attribute6
,p_attribute7 =>p_SDR_cust_tbl(c).attribute7
,p_attribute8 =>p_SDR_cust_tbl(c).attribute8
,p_attribute9 =>p_SDR_cust_tbl(c).attribute9
,p_attribute10 =>p_SDR_cust_tbl(c).attribute10
,p_attribute11 =>p_SDR_cust_tbl(c).attribute11
,p_attribute12 =>p_SDR_cust_tbl(c).attribute12
,p_attribute13 =>p_SDR_cust_tbl(c).attribute13
,p_attribute14 =>p_SDR_cust_tbl(c).attribute14
,p_attribute15 =>p_SDR_cust_tbl(c).attribute15
,p_end_customer_flag =>p_SDR_cust_tbl(c).end_customer_flag);
PROCEDURE update_header_record(
p_SDR_hdr_rec IN SDR_Hdr_rec_type
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_SDR';
SELECT offer_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id =p_request_header_id;
SELECT status_code
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
OZF_UTILITY_PVT.debug_message('Invokes Update Row');
OZF_SD_REQUEST_HEADER_PKG.Update_Row(
p_request_header_id =>p_SDR_hdr_rec.request_header_id
,p_object_version_number =>p_SDR_hdr_rec.object_version_number + 1
,p_last_update_date =>SYSDATE
,p_last_updated_by =>NVL(FND_GLOBAL.user_id,-1)
,p_last_update_login =>NVL(FND_GLOBAL.conc_login_id,-1)
,p_request_id =>FND_GLOBAL.CONC_REQUEST_ID
,p_program_application_id =>FND_GLOBAL.PROG_APPL_ID
,p_program_update_date =>SYSDATE
,p_program_id =>FND_GLOBAL.CONC_PROGRAM_ID
,p_created_from =>NULL
,p_request_number =>p_SDR_hdr_rec.request_number
,p_request_class =>'SD_REQUEST'
,p_offer_type =>l_offer_type
,p_offer_id =>l_qp_list_header_id
,p_root_request_header_id =>NULL
,p_linked_request_header_id =>NULL
,p_request_start_date =>p_SDR_hdr_rec.request_start_date
,p_request_end_date =>p_SDR_hdr_rec.request_end_date
,p_user_status_id =>p_SDR_hdr_rec.user_status_id
,p_request_outcome =>p_SDR_hdr_rec.request_outcome
,p_decline_reason_code =>NULL
,p_return_reason_code =>NULL
,p_request_currency_code =>p_SDR_hdr_rec.request_currency_code
,p_authorization_number =>p_SDR_hdr_rec.authorization_number
,p_sd_requested_budget_amount =>NULL
,p_sd_approved_budget_amount =>NULL
,p_attribute_category =>p_SDR_hdr_rec.attribute_category
,p_attribute1 =>p_SDR_hdr_rec.attribute1
,p_attribute2 =>p_SDR_hdr_rec.attribute2
,p_attribute3 =>p_SDR_hdr_rec.attribute3
,p_attribute4 =>p_SDR_hdr_rec.attribute4
,p_attribute5 =>p_SDR_hdr_rec.attribute5
,p_attribute6 =>p_SDR_hdr_rec.attribute6
,p_attribute7 =>p_SDR_hdr_rec.attribute7
,p_attribute8 =>p_SDR_hdr_rec.attribute8
,p_attribute9 =>p_SDR_hdr_rec.attribute9
,p_attribute10 =>p_SDR_hdr_rec.attribute10
,p_attribute11 =>p_SDR_hdr_rec.attribute11
,p_attribute12 =>p_SDR_hdr_rec.attribute12
,p_attribute13 =>p_SDR_hdr_rec.attribute13
,p_attribute14 =>p_SDR_hdr_rec.attribute14
,p_attribute15 =>p_SDR_hdr_rec.attribute15
,p_supplier_id =>p_SDR_hdr_rec.supplier_id
,p_supplier_site_id =>p_SDR_hdr_rec.supplier_site_id
,p_supplier_contact_id =>p_SDR_hdr_rec.supplier_contact_id
,p_internal_submission_date =>p_SDR_hdr_rec.internal_submission_date
,p_assignee_response_by_date =>p_SDR_hdr_rec.assignee_response_by_date
,p_assignee_response_date =>p_SDR_hdr_rec.assignee_response_date
,p_submtd_by_for_supp_appr =>p_SDR_hdr_rec.submtd_by_for_supp_approval
,p_supplier_response_by_date =>p_SDR_hdr_rec.supplier_response_by_date
,p_supplier_response_date =>p_SDR_hdr_rec.supplier_response_date
,p_supplier_submission_date =>p_SDR_hdr_rec.supplier_submission_date
,p_requestor_id =>p_SDR_hdr_rec.requestor_id
,p_supplier_quote_number =>p_SDR_hdr_rec.supplier_quote_number
,p_internal_order_number =>p_SDR_hdr_rec.internal_order_number
,p_sales_order_currency =>p_SDR_hdr_rec.sales_order_currency
,p_request_source =>'API'
,p_assignee_resource_id =>p_SDR_hdr_rec.assignee_resource_id
,p_org_id =>p_SDR_hdr_rec.org_id
,p_security_group_id =>NULL
,p_accrual_type =>p_SDR_hdr_rec.accrual_type
,p_cust_account_id =>p_SDR_hdr_rec.cust_account_id
,p_supplier_email =>p_SDR_hdr_rec.supplier_contact_email_address
,p_supplier_phone =>p_SDR_hdr_rec.supplier_contact_phone_number
,p_request_type_setup_id =>p_SDR_hdr_rec.request_type_setup_id
,p_request_basis =>p_SDR_hdr_rec.request_basis
,p_supplier_contact_name =>p_SDR_hdr_rec.supplier_contact_name); --//Bugfix : 7822442
OZF_UTILITY_PVT.debug_message('End update_header_record');
END update_header_record;
SELECT custom_setup_id,
activity_type_code
FROM ams_custom_setups_vl
WHERE object_type = 'SDREQUEST'
AND enabled_flag = 'Y'
AND custom_setup_id = p_request_type_setup_id;
SELECT request_number
FROM ozf_sd_request_headers_all_b
WHERE request_number =p_request_number;
OZF_UTILITY_PVT.debug_message('Inserting data into SD Request Header table');
Insert_header_record(
p_SDR_hdr_rec => l_SDR_rec
,p_request_source => 'API'
,x_request_header_id => x_request_header_id
,x_return_status => x_return_status);
PROCEDURE update_sd_request(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_SDR_hdr_rec IN SDR_Hdr_rec_type,
p_SDR_lines_tbl IN SDR_lines_tbl_type,
p_SDR_cust_tbl IN SDR_cust_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_sd_request';
l_line_update_flag VARCHAR2(1) := 'N';
l_cust_update_flag VARCHAR2(1) := 'N';
SELECT
object_version_number,
request_header_id,
request_number,
request_start_date,
request_end_date,
user_status_id,
request_outcome,
request_currency_code,
authorization_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
supplier_id,
supplier_site_id,
supplier_contact_id,
internal_submission_date,
asignee_response_by_date,
asignee_response_date,
submtd_by_for_supp_approval,
supplier_response_by_date,
supplier_response_date,
supplier_submission_date,
requestor_id,
supplier_quote_number,
internal_order_number,
sales_order_currency ,
asignee_resource_id,
org_id,
accrual_type,
cust_account_id,
supplier_contact_email_address,
supplier_contact_phone_number,
request_type_setup_id,
request_basis,
supplier_contact_name --//Bugfix : 7822442
FROM ozf_sd_Request_headers_all_b
WHERE request_header_id =p_request_header_id;
SELECT request_description
FROM ozf_sd_request_headers_all_tl
WHERE request_header_id = p_request_header_id;
SELECT jrgm.group_member_id
FROM jtf_rs_group_members jrgm,
fnd_profile_options fpo,
fnd_profile_option_values fpov
WHERE jrgm.resource_id = p_resource_id
AND fpo. profile_option_name = 'AMS_ADMIN_GROUP'
AND fpo.profile_option_id = fpov.profile_option_id
AND jrgm.group_id = fpov.profile_option_value;
SELECT owner_flag,
approver_flag
FROM ozf_sd_request_access
WHERE enabled_flag ='Y'
AND request_header_id =p_request_header_id
AND user_id =p_user_id;
SELECT user_id
FROM fnd_user
WHERE user_id =p_user_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE start_date_active <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
AND resource_id > 0
AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
AND user_id = p_user_id;
SELECT request_header_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = p_request_header_id;
SELECT request_number
FROM ozf_sd_request_headers_all_b
WHERE request_number =p_request_number;
SELECT name
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id;
SELECT request_communication
FROM ozf_supp_trd_prfls_all
WHERE supplier_id =p_supplier_id
AND supplier_site_id =p_supplier_site_id;
SELECT offer_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id =p_request_header_id;
SAVEPOINT UPDATE_SDR_PUB;
,p_mode =>'UPDATE'
,x_return_status => x_return_status);
OZF_UTILITY_PVT.debug_message('Owner can update Product Lines & Customer Details');
l_line_update_flag := 'Y';
l_cust_update_flag := 'Y';
OZF_UTILITY_PVT.debug_message('PM Can update Product Lines & Customer Details');
l_line_update_flag := 'Y';
l_cust_update_flag := 'Y';
IF l_line_update_flag ='Y' THEN
IF G_DEBUG THEN
OZF_UTILITY_PVT.debug_message('Validate Product Line Records');
,p_mode => 'UPDATE'
,x_return_status => x_return_status);
IF l_cust_update_flag = 'Y' THEN
IF G_DEBUG THEN
OZF_UTILITY_PVT.debug_message('Validate Customer Records');
,p_mode => 'UPDATE'
,x_return_status => x_return_status);
OZF_UTILITY_PVT.debug_message('update_header_record');
update_header_record(
p_SDR_hdr_rec =>l_old_sdr_hdr_rec
,x_return_status =>x_return_status);
,p_mode =>'UPDATE'
,x_return_status => x_return_status);
OZF_UTILITY_PVT.debug_message('Update Lines Record');
OZF_UTILITY_PVT.debug_message('Request Header id from update_header_record :'||p_SDR_hdr_rec.request_header_id);
UPDATE ozf_sd_request_headers_all_b
SET offer_id = l_qp_list_header_id,
offer_type = 'ACCRUAL'
WHERE request_header_id = p_SDR_hdr_rec.request_header_id;
ROLLBACK TO UPDATE_SDR_PUB;
ROLLBACK TO UPDATE_SDR_PUB;
ROLLBACK TO UPDATE_SDR_PUB;
END update_sd_request;
SELECT request_header_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = p_request_header_id;
SELECT request_number
FROM ozf_sd_request_headers_all_b
WHERE request_number = p_request_number;
SELECT cust_account_id
FROM hz_cust_accounts
WHERE status ='A'
AND customer_type ='I'
AND cust_account_id =p_cust_account_id;
SELECT
request_outcome,
request_currency_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
supplier_id,
supplier_site_id,
supplier_contact_id,
--requestor_id,
sales_order_currency,
org_id,
accrual_type,
cust_account_id,
supplier_contact_email_address,
supplier_contact_phone_number,
request_type_setup_id,
request_basis,
supplier_contact_name --//Bugfix : 7822442
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = p_request_header_id;
SELECT request_description
FROM ozf_sd_request_headers_all_tl
WHERE request_header_id = p_request_header_id;
SELECT COUNT(1)
FROM ozf_sd_request_lines_all
WHERE request_header_id = p_request_header_id;
SELECT
product_context,
inventory_item_id,
prod_catg_id,
product_cat_set_id,
product_cost,
item_uom,
requested_discount_type,
requested_discount_value,
cost_basis,
max_qty,
limit_qty,
design_win,
end_customer_price,
requested_line_amount,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
vendor_approved_flag,
vendor_item_code,
end_customer_price_type,
end_customer_tolerance_type,
end_customer_tolerance_value,
org_id,
rejection_code,
requested_discount_currency,
product_cost_currency,
end_customer_currency,
approved_discount_currency
FROM ozf_sd_request_lines_all
WHERE request_header_id = p_request_header_id;
SELECT COUNT(1)
FROM ozf_sd_customer_details
WHERE request_header_id = p_request_header_id
AND end_customer_flag = p_end_customer_flag;
SELECT
cust_account_id,
party_id,
site_use_id,
cust_usage_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
end_customer_flag
FROM ozf_sd_customer_details
WHERE request_header_id = p_request_header_id
AND end_customer_flag = p_end_customer_flag;
SELECT NVL(authorization_period,-1)
FROM ozf_supp_trd_prfls_all
WHERE supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND org_id = p_org_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE start_date_active <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
AND resource_id > 0
AND (category = 'EMPLOYEE' OR category = 'PARTNER' OR category = 'PARTY')
AND user_id = p_user_id;
Insert_header_record(
p_SDR_hdr_rec => l_sdr_source_rec
,p_request_source => p_request_source
,x_request_header_id => x_request_header_id
,x_return_status => x_return_status);
l_sdr_source_cust_tbl.DELETE;
l_sdr_source_cust_tbl.DELETE;