[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1) FROM GL_CODE_COMBINATIONS_KFV
WHERE code_combination_id = p_combination_id ;
SELECT COUNT(1) INTO l_org_count
FROM hr_operating_units
WHERE organization_id=p_supp_trade_profile_rec.org_id ;
SELECT COUNT(*) INTO l_comn_chan_count
FROM ozf_lookups
WHERE lookup_type='OZF_CLAIM_COMMUNICATION'
AND lookup_code = p_supp_trade_profile_rec.approval_communication ;
FND_MESSAGE.set_name('OZF', 'Invalid Approval Communication channel'); -- Select Valid Approval Communication Channel
SELECT COUNT(*) INTO l_comn_chan_count
FROM ozf_lookups
WHERE lookup_type='OZF_REQ_COMMUNICATION'
AND lookup_code = p_supp_trade_profile_rec.request_communication ;
FND_MESSAGE.set_name('OZF', 'OZF_INVALID_REQ_COMM_CHAN'); -- Select Valed Request Communication Channel
SELECT COUNT(*) INTO l_comn_chan_count
FROM ozf_lookups
WHERE lookup_type='OZF_CLAIM_COMMUNICATION'
AND lookup_code = p_supp_trade_profile_rec.claim_communication ;
FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CLM_COMM_CHAN'); -- Select Valid Claim Communication channel
SELECT COUNT(*) INTO l_sett_inc_count
FROM ozf_claim_sttlmnt_methods_all cs,
ozf_lookups ol
WHERE cs.settlement_method = ol.lookup_code
AND ol.lookup_type = 'OZF_PAYMENT_METHOD'
AND cs.source_object_class='PPINCVENDOR'
AND cs.org_id = p_supp_trade_profile_rec.org_id
AND ol.lookup_code = p_supp_trade_profile_rec.settlement_method_supplier_inc ;
FND_MESSAGE.set_name('OZF', 'OZF_DEF_SET_METH_INC'); -- Select valid value for Default Settlement for Supplier Price Increase Claims
SELECT COUNT(*) INTO l_sett_dec_count
FROM ozf_claim_sttlmnt_methods_all cs,
ozf_lookups ol
WHERE cs.settlement_method = ol.lookup_code
AND ol.lookup_type = 'OZF_PAYMENT_METHOD'
AND cs.source_object_class='PPVENDOR'
AND cs.org_id = p_supp_trade_profile_rec.org_id
AND ol.lookup_code = p_supp_trade_profile_rec.settlement_method_supplier_dec ;
FND_MESSAGE.set_name('OZF', 'OZF_DEF_SET_METH_DEC'); --Select valid value for Default Settlement for Supplier Price Decrease Claims
SELECT COUNT(*) INTO l_cust_sett_count
FROM ozf_claim_sttlmnt_methods_all cs,
ozf_lookups ol
WHERE cs.settlement_method = ol.lookup_code
AND ol.lookup_type = 'OZF_PAYMENT_METHOD'
AND cs.source_object_class='PPCUSTOMER'
AND cs.org_id = p_supp_trade_profile_rec.org_id
AND ol.lookup_code = p_supp_trade_profile_rec.settlement_method_customer ;
FND_MESSAGE.set_name('OZF', 'OZF_DEF_SET_METH_CUST'); -- Select Valid value for Default Settlement for Customer Claims
SELECT COUNT(*) INTO l_freq_unit_count
FROM ozf_lookups
WHERE lookup_type='OZF_AUTOPAY_PERIOD_TYPE'
AND lookup_code = p_supp_trade_profile_rec.claim_frequency_unit ;
SELECT COUNT(*) INTO l_comp_baisis_count
FROM qp_price_formulas_vl formula
WHERE trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate))
AND nvl(end_date_active, trunc(sysdate))
AND formula.price_formula_id = p_supp_trade_profile_rec.claim_computation_basis ;
select code_conversion_id from ozf_supp_code_conversions_all where external_code = cv_external_code
and code_conversion_id <> cv_conv_id
and supp_trade_profile_id = cv_supp_trade_profile_id
and ( to_date(cv_start_date_active,'dd-mm-yyyy') between
to_date(start_date_active,'dd-mm-yyyy') and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')))
union
select code_conversion_id from ozf_supp_code_conversions_all where internal_code = cv_internal_code
and code_conversion_id <> cv_conv_id
and supp_trade_profile_id = cv_supp_trade_profile_id
and ( to_date(cv_start_date_active,'dd-mm-yyyy') between to_date(start_date_active,'dd-mm-yyyy')
and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')));
SELECT inventory_item_id
FROM mtl_system_items_vl
WHERE (organization_id = FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID'))
AND ENABLED_FLAG = 'Y'
AND fnd_date.canonical_to_date(sysdate) BETWEEN fnd_date.canonical_to_date(NVL(START_DATE_ACTIVE,sysdate))
AND fnd_date.canonical_to_date(NVL(END_DATE_ACTIVE,sysdate))
AND concatenated_segments = cv_item_number ;
SELECT code_conversion_id,
object_version_number,
SYSDATE,
DECODE(p_code_con_rec.last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_code_con_rec.last_updated_by),
DECODE(p_code_con_rec.last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.CONC_LOGIN_ID,p_code_con_rec.last_update_login),
ORG_ID,
supp_trade_profile_id,
DECODE( p_code_con_rec.code_conversion_type, FND_API.g_miss_char, code_conversion_type, p_code_con_rec.code_conversion_type),
external_code,
internal_code,
DECODE( p_code_con_rec.description, FND_API.g_miss_char, description, p_code_con_rec.description),
DECODE( p_code_con_rec.start_date_active, FND_API.G_MISS_DATE,start_date_active , p_code_con_rec.start_date_active),
DECODE( p_code_con_rec.end_date_active, FND_API.G_MISS_DATE,end_date_active , p_code_con_rec.end_date_active),
DECODE( p_code_con_rec.attribute_category, FND_API.g_miss_char, attribute_category,p_code_con_rec.attribute_category),
DECODE( p_code_con_rec.attribute1, FND_API.g_miss_char, attribute1, p_code_con_rec.attribute1),
DECODE( p_code_con_rec.attribute2, FND_API.g_miss_char, attribute2, p_code_con_rec.attribute2),
DECODE( p_code_con_rec.attribute3, FND_API.g_miss_char, attribute3, p_code_con_rec.attribute3),
DECODE( p_code_con_rec.attribute4, FND_API.g_miss_char, attribute4, p_code_con_rec.attribute4),
DECODE( p_code_con_rec.attribute5, FND_API.g_miss_char, attribute5, p_code_con_rec.attribute5),
DECODE( p_code_con_rec.attribute6, FND_API.g_miss_char, attribute6, p_code_con_rec.attribute6),
DECODE( p_code_con_rec.attribute7, FND_API.g_miss_char, attribute7, p_code_con_rec.attribute7),
DECODE( p_code_con_rec.attribute8, FND_API.g_miss_char, attribute8, p_code_con_rec.attribute8),
DECODE( p_code_con_rec.attribute9, FND_API.g_miss_char, attribute9, p_code_con_rec.attribute9),
DECODE( p_code_con_rec.attribute10, FND_API.g_miss_char, attribute10, p_code_con_rec.attribute10),
DECODE( p_code_con_rec.attribute11, FND_API.g_miss_char, attribute11, p_code_con_rec.attribute11),
DECODE( p_code_con_rec.attribute12, FND_API.g_miss_char, attribute12, p_code_con_rec.attribute12),
DECODE( p_code_con_rec.attribute13, FND_API.g_miss_char, attribute13, p_code_con_rec.attribute13),
DECODE( p_code_con_rec.attribute14, FND_API.g_miss_char, attribute14, p_code_con_rec.attribute14),
DECODE( p_code_con_rec.attribute15, FND_API.g_miss_char, attribute15, p_code_con_rec.attribute15)
FROM ozf_supp_code_conversions_all
WHERE supp_trade_profile_id = p_code_con_rec.supp_trade_profile_id
AND code_conversion_id = p_code_con_rec.code_conversion_id;
p_code_con_rec.last_update_date,
p_code_con_rec.last_updated_by,
p_code_con_rec.last_update_login,
p_code_con_rec.ORG_ID,
p_code_con_rec.supp_trade_profile_id,
p_code_con_rec.code_conversion_type,
p_code_con_rec.external_code,
p_code_con_rec.internal_code,
p_code_con_rec.description,
p_code_con_rec.start_date_active,
p_code_con_rec.end_date_active,
p_code_con_rec.attribute_category,
p_code_con_rec.attribute1,
p_code_con_rec.attribute2,
p_code_con_rec.attribute3,
p_code_con_rec.attribute4,
p_code_con_rec.attribute5,
p_code_con_rec.attribute6,
p_code_con_rec.attribute7,
p_code_con_rec.attribute8,
p_code_con_rec.attribute9,
p_code_con_rec.attribute10,
p_code_con_rec.attribute11,
p_code_con_rec.attribute12,
p_code_con_rec.attribute13,
p_code_con_rec.attribute14,
p_code_con_rec.attribute15 ;
l_supp_trade_profile_rec.last_update_date := FND_API.G_MISS_DATE ;
l_supp_trade_profile_rec.last_updated_by := FND_API.g_miss_num ;
l_supp_trade_profile_rec.last_update_login := FND_API.g_miss_num ;
l_supp_trade_profile_rec.program_update_date := FND_API.G_MISS_DATE ;
IF (p_supp_trade_profile_rec.last_update_date <> FND_API.G_MISS_DATE) THEN
l_supp_trade_profile_rec.last_update_date := p_supp_trade_profile_rec.last_update_date ;
IF (p_supp_trade_profile_rec.last_updated_by <> FND_API.g_miss_num) THEN
l_supp_trade_profile_rec.last_updated_by := p_supp_trade_profile_rec.last_updated_by ;
IF (p_supp_trade_profile_rec.last_update_login <> FND_API.g_miss_num ) THEN
l_supp_trade_profile_rec.last_update_login := p_supp_trade_profile_rec.last_update_login ;
IF (p_supp_trade_profile_rec.program_update_date <> FND_API.G_MISS_DATE) THEN
l_supp_trade_profile_rec.program_update_date := p_supp_trade_profile_rec.program_update_date ;
SELECT DECODE(p_supp_trade_profile_rec.object_version_number,FND_API.g_miss_num,object_version_number,p_supp_trade_profile_rec.object_version_number) object_version_number,
DECODE(p_supp_trade_profile_rec.party_id,FND_API.g_miss_num,party_id,p_supp_trade_profile_rec.party_id) party_id,
DECODE(p_supp_trade_profile_rec.site_use_id,FND_API.g_miss_num,site_use_id,p_supp_trade_profile_rec.site_use_id) site_use_id,
DECODE(p_supp_trade_profile_rec.cust_account_id,FND_API.g_miss_num,cust_account_id,p_supp_trade_profile_rec.cust_account_id) cust_account_id,
DECODE(p_supp_trade_profile_rec.cust_acct_site_id,FND_API.g_miss_num,cust_acct_site_id,p_supp_trade_profile_rec.cust_acct_site_id) cust_acct_site_id,
creation_date ,
supplier_id,
supplier_site_id,
DECODE(p_supp_trade_profile_rec.attribute_category,FND_API.g_miss_char,attribute_category,p_supp_trade_profile_rec.attribute_category) attribute_category,
DECODE(p_supp_trade_profile_rec.attribute1,FND_API.g_miss_char,attribute1,p_supp_trade_profile_rec.attribute1) attribute1,
DECODE(p_supp_trade_profile_rec.attribute2,FND_API.g_miss_char,attribute2,p_supp_trade_profile_rec.attribute2) attribute2,
DECODE(p_supp_trade_profile_rec.attribute3,FND_API.g_miss_char,attribute3,p_supp_trade_profile_rec.attribute3) attribute3,
DECODE(p_supp_trade_profile_rec.attribute4,FND_API.g_miss_char,attribute4,p_supp_trade_profile_rec.attribute4) attribute4,
DECODE(p_supp_trade_profile_rec.attribute5,FND_API.g_miss_char,attribute5,p_supp_trade_profile_rec.attribute5) attribute5,
DECODE(p_supp_trade_profile_rec.attribute6,FND_API.g_miss_char,attribute6,p_supp_trade_profile_rec.attribute6) attribute6,
DECODE(p_supp_trade_profile_rec.attribute7,FND_API.g_miss_char,attribute7,p_supp_trade_profile_rec.attribute7) attribute7,
DECODE(p_supp_trade_profile_rec.attribute8,FND_API.g_miss_char,attribute8,p_supp_trade_profile_rec.attribute8) attribute8,
DECODE(p_supp_trade_profile_rec.attribute9,FND_API.g_miss_char,attribute9,p_supp_trade_profile_rec.attribute9) attribute9,
DECODE(p_supp_trade_profile_rec.attribute10,FND_API.g_miss_char,attribute10,p_supp_trade_profile_rec.attribute10) attribute10,
DECODE(p_supp_trade_profile_rec.attribute11,FND_API.g_miss_char,attribute11,p_supp_trade_profile_rec.attribute11) attribute11,
DECODE(p_supp_trade_profile_rec.attribute12,FND_API.g_miss_char,attribute12,p_supp_trade_profile_rec.attribute12) attribute12,
DECODE(p_supp_trade_profile_rec.attribute13,FND_API.g_miss_char,attribute13,p_supp_trade_profile_rec.attribute13) attribute13,
DECODE(p_supp_trade_profile_rec.attribute14,FND_API.g_miss_char,attribute14,p_supp_trade_profile_rec.attribute14) attribute14,
DECODE(p_supp_trade_profile_rec.attribute15,FND_API.g_miss_char,attribute15,p_supp_trade_profile_rec.attribute15) attribute15,
DECODE(p_supp_trade_profile_rec.attribute16,FND_API.g_miss_char,attribute16,p_supp_trade_profile_rec.attribute16) attribute16,
DECODE(p_supp_trade_profile_rec.attribute17,FND_API.g_miss_char,attribute17,p_supp_trade_profile_rec.attribute17) attribute17,
DECODE(p_supp_trade_profile_rec.attribute18,FND_API.g_miss_char,attribute18,p_supp_trade_profile_rec.attribute18) attribute18,
DECODE(p_supp_trade_profile_rec.attribute19,FND_API.g_miss_char,attribute19,p_supp_trade_profile_rec.attribute19) attribute19,
DECODE(p_supp_trade_profile_rec.attribute20,FND_API.g_miss_char,attribute20,p_supp_trade_profile_rec.attribute20) attribute20,
DECODE(p_supp_trade_profile_rec.attribute21,FND_API.g_miss_char,attribute21,p_supp_trade_profile_rec.attribute21) attribute21,
DECODE(p_supp_trade_profile_rec.attribute22,FND_API.g_miss_char,attribute22,p_supp_trade_profile_rec.attribute22) attribute22,
DECODE(p_supp_trade_profile_rec.attribute23,FND_API.g_miss_char,attribute23,p_supp_trade_profile_rec.attribute23) attribute23,
DECODE(p_supp_trade_profile_rec.attribute24,FND_API.g_miss_char,attribute24,p_supp_trade_profile_rec.attribute24) attribute24,
DECODE(p_supp_trade_profile_rec.attribute25,FND_API.g_miss_char,attribute25,p_supp_trade_profile_rec.attribute25) attribute25,
DECODE(p_supp_trade_profile_rec.attribute26,FND_API.g_miss_char,attribute26,p_supp_trade_profile_rec.attribute26) attribute26,
DECODE(p_supp_trade_profile_rec.attribute27,FND_API.g_miss_char,attribute27,p_supp_trade_profile_rec.attribute27) attribute27,
DECODE(p_supp_trade_profile_rec.attribute28,FND_API.g_miss_char,attribute28,p_supp_trade_profile_rec.attribute28) attribute28,
DECODE(p_supp_trade_profile_rec.attribute29,FND_API.g_miss_char,attribute29,p_supp_trade_profile_rec.attribute29) attribute29,
DECODE(p_supp_trade_profile_rec.attribute30,FND_API.g_miss_char,attribute30,p_supp_trade_profile_rec.attribute30) attribute30,
org_id ,
DECODE(p_supp_trade_profile_rec.pre_approval_flag ,FND_API.g_miss_char,pre_approval_flag,p_supp_trade_profile_rec.pre_approval_flag) pre_approval_flag,
DECODE(p_supp_trade_profile_rec.approval_communication ,FND_API.g_miss_char,approval_communication,p_supp_trade_profile_rec.approval_communication) approval_communication,
DECODE(p_supp_trade_profile_rec.gl_contra_liability_acct ,FND_API.g_miss_num, gl_contra_liability_acct,p_supp_trade_profile_rec.gl_contra_liability_acct) gl_contra_liability_acct,
DECODE(p_supp_trade_profile_rec.gl_cost_adjustment_acct ,FND_API.g_miss_num,gl_cost_adjustment_acct,p_supp_trade_profile_rec.gl_cost_adjustment_acct) gl_cost_adjustment_acct,
DECODE(p_supp_trade_profile_rec.default_days_covered ,FND_API.g_miss_num,default_days_covered,p_supp_trade_profile_rec.default_days_covered) default_days_covered,
DECODE(p_supp_trade_profile_rec.create_claim_price_increase ,FND_API.g_miss_char ,create_claim_price_increase,p_supp_trade_profile_rec.create_claim_price_increase) create_claim_price_increase,
DECODE(p_supp_trade_profile_rec.skip_approval_flag , FND_API.g_miss_char,skip_approval_flag,p_supp_trade_profile_rec.skip_approval_flag) skip_approval_flag,
DECODE(p_supp_trade_profile_rec.skip_adjustment_flag ,FND_API.g_miss_char ,skip_adjustment_flag, p_supp_trade_profile_rec.skip_adjustment_flag) skip_adjustment_flag,
DECODE(p_supp_trade_profile_rec.settlement_method_supplier_inc,FND_API.g_miss_char,settlement_method_supplier_inc,p_supp_trade_profile_rec.settlement_method_supplier_inc) settlement_method_supplier_inc,
DECODE(p_supp_trade_profile_rec.settlement_method_supplier_dec,FND_API.g_miss_char,settlement_method_supplier_dec,p_supp_trade_profile_rec.settlement_method_supplier_dec) settlement_method_supplier_dec,
DECODE(p_supp_trade_profile_rec.settlement_method_customer,FND_API.g_miss_char,settlement_method_customer,p_supp_trade_profile_rec.settlement_method_customer) settlement_method_customer,
DECODE(p_supp_trade_profile_rec.authorization_period ,FND_API.g_miss_num,authorization_period,p_supp_trade_profile_rec.authorization_period) authorization_period ,
DECODE(p_supp_trade_profile_rec.grace_days ,FND_API.g_miss_num,grace_days,p_supp_trade_profile_rec.grace_days) grace_days,
DECODE(p_supp_trade_profile_rec.allow_qty_increase ,FND_API.g_miss_char,allow_qty_increase,p_supp_trade_profile_rec.allow_qty_increase) allow_qty_increase,
DECODE(p_supp_trade_profile_rec.qty_increase_tolerance ,FND_API.g_miss_num,qty_increase_tolerance,p_supp_trade_profile_rec.qty_increase_tolerance) qty_increase_tolerance,
DECODE(p_supp_trade_profile_rec.request_communication ,FND_API.g_miss_char,request_communication,p_supp_trade_profile_rec.request_communication) request_communication,
DECODE(p_supp_trade_profile_rec.claim_communication ,FND_API.g_miss_char,claim_communication,p_supp_trade_profile_rec.claim_communication) claim_communication,
DECODE(p_supp_trade_profile_rec.claim_frequency ,FND_API.g_miss_num,claim_frequency,p_supp_trade_profile_rec.claim_frequency) claim_frequency,
DECODE(p_supp_trade_profile_rec.claim_frequency_unit ,FND_API.g_miss_char,claim_frequency_unit,p_supp_trade_profile_rec.claim_frequency_unit) claim_frequency_unit,
DECODE(p_supp_trade_profile_rec.claim_computation_basis ,FND_API.g_miss_num, claim_computation_basis, p_supp_trade_profile_rec.claim_computation_basis) claim_computation_basis,
DECODE(p_supp_trade_profile_rec.claim_currency_code ,FND_API.g_miss_char, claim_currency_code,p_supp_trade_profile_rec.claim_currency_code) claim_currency_code,
DECODE(p_supp_trade_profile_rec.min_claim_amt ,FND_API.g_miss_num ,min_claim_amt,p_supp_trade_profile_rec.min_claim_amt) min_claim_amt,
DECODE(p_supp_trade_profile_rec.min_claim_amt_line_lvl ,FND_API.g_miss_num,min_claim_amt_line_lvl,p_supp_trade_profile_rec.min_claim_amt_line_lvl) min_claim_amt_line_lvl,
DECODE(p_supp_trade_profile_rec.auto_debit ,FND_API.g_miss_char, auto_debit,p_supp_trade_profile_rec.auto_debit) auto_debit,
DECODE(p_supp_trade_profile_rec.days_before_claiming_debit , FND_API.g_miss_num, days_before_claiming_debit,p_supp_trade_profile_rec.days_before_claiming_debit) days_before_claiming_debit,
DECODE(p_supp_trade_profile_rec.ssd_imd_claim_flag ,FND_API.g_miss_char, ssd_imd_claim_flag,p_supp_trade_profile_rec.ssd_imd_claim_flag) ssd_imd_claim_flag,
security_group_id
FROM ozf_supp_trd_prfls_all
WHERE supp_trade_profile_id = p_supp_trade_profile_rec.supp_trade_profile_id ;
p_supp_trade_profile_rec.last_update_date := sysdate ;
p_supp_trade_profile_rec.last_updated_by := p_supp_trade_profile_rec.last_updated_by ;
SELECT cust_acct.party_id,
acct_site.cust_acct_site_id
FROM hz_cust_acct_sites_all acct_site ,
hz_cust_site_uses_all site,
hz_cust_accounts cust_acct
WHERE site.site_use_code = 'BILL_TO'
AND acct_site.status='A'
AND site.status='A'
AND cust_acct.status='A'
AND site.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id=cust_acct.cust_account_id
AND acct_site.cust_account_id = p_supp_trade_profile_rec.cust_account_id
AND site.site_use_id = p_supp_trade_profile_rec.site_use_id;
/* SELECT acct_site.cust_account_id ,
acct_site.cust_acct_site_id
FROM hz_cust_acct_sites_all acct_site ,
hz_party_sites party_site ,
hz_locations loc ,
hz_cust_site_uses_all site ,
hz_parties party ,
hz_cust_accounts cust_acct
WHERE site.site_use_code = 'BILL_TO'
AND site.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id= party_site.party_site_id
AND party_site.location_id = loc.location_id
AND acct_site.status='A'
AND acct_site.cust_account_id=cust_acct.cust_account_id
AND cust_acct.party_id=party.party_id
AND cust_acct.status='A'
AND site.status='A'
AND cust_acct.party_id = p_supp_trade_profile_rec.cust_account_id
AND site.site_use_id = p_supp_trade_profile_rec.site_use_id ; */
SELECT ap.vendor_id INTO l_supp_site_count
FROM ap_suppliers ap,
ap_supplier_sites_all aps,
po_lookup_codes plc,
hz_party_sites hps,
hz_locations hzl,
fnd_territories_vl fndt
WHERE aps.party_site_id = hps.party_site_id
AND hps.location_id = hzl.location_id
AND nvl(hps.end_date_active, sysdate) >= sysdate
AND hzl.country = fndt.territory_code
AND aps.vendor_id = ap.vendor_id
AND ap.vendor_type_lookup_code = plc.lookup_code (+)
AND plc.lookup_type (+) = 'VENDOR TYPE'
AND nvl(aps.rfq_only_site_flag, 'N') ='N'
AND NVL(aps.inactive_date, SYSDATE +1) > SYSDATE
AND aps.vendor_site_id = p_supp_trade_profile_rec.supplier_site_id
AND aps.org_id = p_supp_trade_profile_rec.org_id ;
SELECT COUNT(*) INTO l_supp_site_comb_count
FROM ozf_supp_trd_prfls_all
WHERE supplier_id = p_supp_trade_profile_rec.supplier_id
AND supplier_site_id = p_supp_trade_profile_rec.supplier_site_id ;
FND_MESSAGE.set_name('OZF', 'OZF_SELECT_CUSTOMER_NAME');
FND_MESSAGE.set_name('OZF', 'OZF_SELECT_CUSTOMER_NAME');
X_updated_code_con_tbl => l_upd_code_con_tbl ,
X_deleted_code_con_tbl => l_del_code_con_tbl) ;
X_updated_process_tbl => l_pro_ver_tbl ) ;
PROCEDURE Update_Supp_Trade_Profile(
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_supp_trade_profile_rec IN supp_trade_profile_rec_type ,
p_code_conversion_rec_tbl IN code_conversion_tbl_type,
p_price_protection_set_tbl IN process_setup_tbl_type,
X_created_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type,
X_updated_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type,
X_created_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type,
X_updated_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type,
X_deleted_codes_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Supp_Trade_Profile_all';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling Update Supplier Trade Profile');
Update_Supp_Trade_Profile(
p_api_version_number => p_api_version_number ,
p_init_msg_list => p_init_msg_list ,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_supp_trade_profile_rec => p_supp_trade_profile_rec );
X_updated_code_con_tbl => X_updated_codes_tbl ,
X_deleted_code_con_tbl => X_deleted_codes_tbl) ;
X_updated_process_tbl => X_updated_process_tbl) ;
END Update_Supp_Trade_Profile ;
PROCEDURE Update_Supp_Trade_Profile(
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_supp_trade_profile_rec IN supp_trade_profile_rec_type
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Supp_Trade_Profile';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Calling private Update profile');
OZF_SUPP_TRADE_PROFILE_PVT.Update_Supp_Trade_Profile( p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_supp_trade_profile_rec => l_pvt_supp_rec,
x_object_version_number => l_ver_num ) ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private update profile failed');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private update profile failed');
END Update_Supp_Trade_Profile ;
X_updated_code_con_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type ,
X_deleted_code_con_tbl OUT NOCOPY OZF_CODE_CONVERSION_PVT.supp_code_conversion_tbl_type)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Process_code_conversion';
select ORG_ID FROM ozf_supp_trd_prfls_all
WHERE supp_trade_profile_id= cv_supp_trade_profile_id ;
l_pvt_code_con_rec.LAST_UPDATE_DATE := l_code_conversion_rec.LAST_UPDATE_DATE ;
l_pvt_code_con_rec.LAST_UPDATED_BY := l_code_conversion_rec.LAST_UPDATED_BY ;
l_pvt_code_con_rec.LAST_UPDATE_LOGIN := l_code_conversion_rec.LAST_UPDATE_LOGIN;
SELECT ozf_supp_code_conv_all_s.nextval
INTO l_pvt_code_con_rec.CODE_CONVERSION_ID
FROM DUAL;
-- Add the valid record to update list
IF (x_return_status = FND_API.g_ret_sts_success ) THEN
l_upd_code_conv.extend ;
SELECT COUNT(1) INTO l_int_cc_count
FROM ozf_supp_code_conversions_all
WHERE code_conversion_id = l_pvt_code_con_rec.CODE_CONVERSION_ID ;
-- Mass insertion
FORALL indx IN 1..l_crt_code_conv.count
INSERT INTO ozf_supp_code_conversions_all(
code_conversion_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
supp_trade_profile_id,
code_conversion_type,
external_code,
internal_code,
description,
start_date_active,
end_date_active,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) VALUES (
l_crt_code_conv(indx).CODE_CONVERSION_ID,
l_crt_code_conv(indx).OBJECT_VERSION_NUMBER,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
l_crt_code_conv(indx).ORG_ID,
l_crt_code_conv(indx).SUPP_TRADE_PROFILE_ID,
DECODE( l_crt_code_conv(indx).code_conversion_type, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).code_conversion_type),
DECODE( l_crt_code_conv(indx).external_code, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).external_code),
DECODE( l_crt_code_conv(indx).internal_code, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).internal_code),
DECODE( l_crt_code_conv(indx).description, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).description),
DECODE( l_crt_code_conv(indx).start_date_active, FND_API.G_MISS_DATE, to_date(NULL), l_crt_code_conv(indx).start_date_active),
DECODE( l_crt_code_conv(indx).end_date_active, FND_API.G_MISS_DATE, to_date(NULL), l_crt_code_conv(indx).end_date_active),
DECODE( l_crt_code_conv(indx).attribute_category, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute_category),
DECODE( l_crt_code_conv(indx).attribute1, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute1),
DECODE( l_crt_code_conv(indx).attribute2, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute2),
DECODE( l_crt_code_conv(indx).attribute3, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute3),
DECODE( l_crt_code_conv(indx).attribute4, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute4),
DECODE( l_crt_code_conv(indx).attribute5, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute5),
DECODE( l_crt_code_conv(indx).attribute6, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute6),
DECODE( l_crt_code_conv(indx).attribute7, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute7),
DECODE( l_crt_code_conv(indx).attribute8, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute8),
DECODE( l_crt_code_conv(indx).attribute9, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute9),
DECODE( l_crt_code_conv(indx).attribute10, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute10),
DECODE( l_crt_code_conv(indx).attribute11, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute11),
DECODE( l_crt_code_conv(indx).attribute12, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute12),
DECODE( l_crt_code_conv(indx).attribute13, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute13),
DECODE( l_crt_code_conv(indx).attribute14, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute14),
DECODE( l_crt_code_conv(indx).attribute15, FND_API.g_miss_char, NULL, l_crt_code_conv(indx).attribute15)
);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Mass Insertion was successful' );
-- Mass update
FORALL indx IN 1..l_upd_code_conv.count
Update ozf_supp_code_conversions_all
SET
object_version_number = l_upd_code_conv(indx).object_version_number +1 ,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
org_id = l_upd_code_conv(indx).org_id,
supp_trade_profile_id = l_upd_code_conv(indx).supp_trade_profile_id ,
code_conversion_type = l_upd_code_conv(indx).code_conversion_type,
external_code = l_upd_code_conv(indx).external_code,
internal_code = l_upd_code_conv(indx).internal_code,
description = l_upd_code_conv(indx).description,
start_date_active = l_upd_code_conv(indx).start_date_active,
end_date_active = l_upd_code_conv(indx).end_date_active,
attribute_category = l_upd_code_conv(indx).attribute_category,
attribute1 = l_upd_code_conv(indx).attribute1,
attribute2 = l_upd_code_conv(indx).attribute2,
attribute3 = l_upd_code_conv(indx).attribute3,
attribute4 = l_upd_code_conv(indx).attribute4,
attribute5 = l_upd_code_conv(indx).attribute5,
attribute6 = l_upd_code_conv(indx).attribute6,
attribute7 = l_upd_code_conv(indx).attribute7,
attribute8 = l_upd_code_conv(indx).attribute8,
attribute9 = l_upd_code_conv(indx).attribute9,
attribute10 = l_upd_code_conv(indx).attribute10,
attribute11 = l_upd_code_conv(indx).attribute11,
attribute12 = l_upd_code_conv(indx).attribute12,
attribute13 = l_upd_code_conv(indx).attribute13,
attribute14 = l_upd_code_conv(indx).attribute14,
attribute15 = l_upd_code_conv(indx).attribute15
WHERE
code_conversion_id = l_upd_code_conv(indx).code_conversion_id ;
X_updated_code_con_tbl := l_upd_code_conv ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Mass Update was successful' );
-- Mass delete
FORALL indx IN 1..l_del_code_conv.count
DELETE FROM ozf_supp_code_conversions_all
WHERE
code_conversion_id = l_del_code_conv(indx).code_conversion_id ;
X_deleted_code_con_tbl := l_del_code_conv ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Mass Delete was successful' );
X_updated_process_tbl OUT NOCOPY OZF_PROCESS_SETUP_PVT.process_setup_tbl_type)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Process_price_protection';
SELECT NVL(ps.enabled_flag,'N') enabledFlag,
ps.process_setup_id processSetupId,
ps.process_setup_id objId,
ps.org_id orgId,
fl.meaning processName,
ps.object_version_number objVerNum,
fl.lookup_code processCode,
ps.supp_trade_profile_id suppTradeProfileId,
NVL(ps.automatic_flag,'N') automaticFlag,
ps.SECURITY_GROUP_ID secGrpId,
ps.attribute_category attribute_category,
ps.attribute1 attribute1,
ps.attribute1 attribute2,
ps.attribute1 attribute3,
ps.attribute1 attribute4,
ps.attribute1 attribute5,
ps.attribute1 attribute6,
ps.attribute1 attribute7,
ps.attribute1 attribute8,
ps.attribute1 attribute9,
ps.attribute1 attribute10,
ps.attribute1 attribute11,
ps.attribute1 attribute12,
ps.attribute1 attribute13,
ps.attribute1 attribute14,
ps.attribute1 attribute15
FROM dpp_lookups fl , ozf_process_setup_all ps
WHERE (ps.supp_trade_profile_id(+) = p_trade_prf_id
AND fl.lookup_type = 'DPP_EXECUTION_PROCESSES'
AND fl.lookup_code = ps.process_code (+)
AND fl.tag is not NULL
AND fl.enabled_flag = 'Y')
ORDER BY fl.lookup_code ;
SELECT org_id FROM ozf_supp_trd_prfls_all
WHERE supp_trade_profile_id = p_trade_prf_id ;
--- SET THE DATA FOR UPDATE : START
---------------------------------------------------------------------
IF (r_pp_data.processSetupId IS NOT NULL ) then
l_process_rec := null ;
l_process_rec.LAST_UPDATE_DATE := SYSDATE ;
IF (p_process_setup_tbl(indx).last_updated_by = FND_API.G_MISS_NUM) THEN
l_process_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID ;
l_process_rec.LAST_UPDATED_BY := p_process_setup_tbl(indx).last_updated_by ;
-- SET THE DATA FOR UPDATE : END
----------------------------------------------------------------------------------------
IF g_debug THEN
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Data for update' );
OZF_PROCESS_SETUP_PVT.Update_Process_Setup_Tbl(
P_Api_Version_Number => P_Api_Version_Number ,
P_Init_Msg_List => P_Init_Msg_List ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
X_Return_Status => X_Return_Status ,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
P_process_setup_Tbl => l_process_upd_tbl) ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF-STP',G_PKG_NAME||'.'||L_API_NAME||' Private Update process setup returned'||x_return_status );
X_updated_process_tbl := l_process_upd_tbl ;