[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT kfv.concatenated_segments
BULK COLLECT INTO l_category_tbl
FROM po_lines_all pol, mtl_categories_b_kfv kfv
WHERE pol.category_id = kfv.category_id
AND pol.po_header_id = p_document_id
AND NVL(pol.cancel_flag,'N') ='N';
SELECT DISTINCT kfv.concatenated_segments
BULK COLLECT INTO l_item_tbl
FROM po_lines_all pol, mtl_system_items_b_kfv kfv
WHERE pol.item_id = kfv.inventory_item_id
AND pol.po_header_id = p_document_id
AND NVL(pol.cancel_flag,'N') ='N';
x_category_tbl.delete();
x_item_tbl.delete();
PROCEDURE IS_po_update_allowed (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_header_id IN NUMBER,
p_callout_string IN VARCHAR2,
p_lock_flag IN VARCHAR2 DEFAULT 'N',
x_update_allowed OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR(30) := 'IS_PO_UPDATE_ALLOWED';
l_DONOT_update EXCEPTION;
X_update_allowed := 'Y';
SELECT nvl(authorization_status,'INCOMPLETE'), revision_num, type_lookup_code
,DECODE(type_lookup_code,'STANDARD','PO','BLANKET','PA','CONTRACT','PA',null)
INTO l_po_status,l_po_revision, l_doc_type_code
,l_document_type
FROM po_headers_all
WHERE po_header_id = p_header_id;
x_update_allowed := 'N';
RAISE l_DONOT_update;
MESSAGE =>'292:before status check po id -update allowed '||p_header_id||x_update_allowed);
p_mode => 'CHECK_UPDATEABLE',
p_lock_flag => p_lock_flag,
x_po_status_rec => l_po_status_rec,
x_return_status => x_return_status
);
x_update_allowed := l_po_status_Rec.updatable_flag(
l_po_status_rec.updatable_flag.FIRST);
IF x_update_allowed = 'N' then
RAISE l_DONOT_update;
x_update_allowed := 'N';
WHEN l_DONOT_UPDATE then
x_return_status := FND_API.G_RET_STS_ERROR;
x_update_allowed := 'N';
FND_MESSAGE.set_name('PO', 'PO_NO_UPDATE_ALLOWED');
MESSAGE =>'550:Exception l_donot_update ');
x_update_allowed := 'N';
x_update_allowed := 'N';
X_update_allowed := 'N';
END is_po_update_allowed;
x_update_allowed OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR(30) := 'Apply_template_Change';
l_update_not_allowed EXCEPTION;
x_update_allowed := 'N';
MESSAGE =>'50: x_update_allowed' ||x_update_allowed);
SELECT poh.conterms_exist_flag
INTO l_old_conterms_flag
FROM PO_HEADERS_ALL poh
WHERE poh.po_header_id = p_header_id;
RAISE l_update_not_allowed;
IS_PO_UPDATE_ALLOWED (
p_api_version => p_api_version,
p_header_id => p_header_id,
p_callout_string => p_callout_string,
p_lock_flag => 'Y',
x_update_allowed => x_update_allowed,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
MESSAGE =>'200:update allowed after calling is_po_update_allowed'||x_update_allowed);
ELSE -- if template is being deleted set dates to null, flag to N;
Update po_headers_all
Set conterms_exist_flag = l_conterms_exist_flag,
Conterms_articles_upd_date = l_date,
Conterms_DELIV_upd_date = l_date,
Last_update_date = sysdate,
Last_updated_by = FND_GLOBAl.USER_ID,
Last_update_login = FND_GLOBAL.LOGIN_ID
WHERE po_header_id= p_header_id;
MESSAGE =>'300:Po headers all updated for header id'||p_header_id);
WHEN l_update_not_allowed then
x_return_status := FND_API.G_RET_STS_ERROR;
X_update_allowed := 'N';
MESSAGE =>'450:Exception l_update_not_allowed ');
SELECT
DECODE(poh.org_id,poha.org_id,'N','OKC$B_ORGANIZATION')
,DECODE(poh.vendor_id,poha.vendor_id,'N','OKC$B_SUPPLIER_NAME')
,DECODE(poh.vendor_site_id,poha.vendor_site_id,'N','OKC$B_SUPPLIER_SITE')
,DECODE(poh.vendor_contact_id,poha.vendor_contact_id,'N','OKC$B_SUPPLIER_CONTACT')
,DECODE(poh.ship_to_location_id,poha.ship_to_location_id,'N','OKC$B_SHIP_TO_ADDRESS')
,DECODE(poh.bill_to_location_id,poha.bill_to_location_id,'N','OKC$B_BILL_TO_ADDRESS')
,DECODE(poh.currency_code,poha.currency_code,'N','OKC$B_TXN_CURRENCY')
,DECODE(poh.agent_id,poha.agent_id,'N','OKC$B_BUYER')
,DECODE(poh.blanket_total_amount,poha.blanket_total_amount,'N','OKC$B_AGREEMENT_AMOUNT_TXN')
,DECODE(poh.blanket_total_amount,poha.blanket_total_amount,'N','OKC$B_AGREEMENT_AMOUNT_FUNC')
,DECODE(poh.global_agreement_flag,poha.global_agreement_flag,'N','OKC$B_GLOBAL_FLAG')
,DECODE(poh.rate_type,poha.rate_type,'N','OKC$B_RATE_TYPE')
,DECODE(poh.rate_date,poha.rate_date,'N','OKC$B_RATE_DATE')
,DECODE(poh.rate,poha.rate,'N','OKC$B_RATE')
,DECODE(poh.terms_id ,poha.terms_id,'N','OKC$B_PAYMENT_TERMS')
,DECODE(poh.freight_terms_lookup_code,poha.freight_terms_lookup_code,'N','OKC$B_FREIGHT_TERMS')
,DECODE(poh.ship_via_lookup_code,poha.ship_via_lookup_code,'N','OKC$B_CARRIER')
,DECODE(poh.fob_lookup_code,poha.fob_lookup_code,'N','OKC$B_FOB')
,DECODE(poh.pay_on_code,poha.pay_on_code,'N','OKC$B_PAY_ON_CODE')
,DECODE(poh.acceptance_required_flag,poha.acceptance_required_flag,'N','OKC$B_ACCEPTANCE_METHOD')
,DECODE(poh.acceptance_due_date,poha.acceptance_due_date,'N','OKC$B_ACCEPTANCE_REQD_DATE')
,DECODE(poh.supply_agreement_flag,poha.supply_agreement_flag,'N','OKC$B_SUPPLY_AGREEMENT_FLAG')
,DECODE(poh.start_date,poha.start_date,'N','OKC$B_AGREEMENT_START_DATE')
,DECODE(poh.end_date,poha.end_date,'N','OKC$B_AGREEMENT_END_DATE')
,DECODE(poh.min_release_amount,poha.min_release_amount,'N','OKC$B_MINIMUM_RELEASE_AMT_TXN')
,DECODE(poh.min_release_amount,poha.min_release_amount,'N','OKC$B_MINIMUM_RELEASE_AMT_FUNC')
,poh.type_lookup_code
,DECODE(poh.shipping_control, poha.shipping_control, 'N', 'OKC$B_TRANSPORTATION_ARRANGED') --
INTO
l_po_attrib_tbl(1)
,l_po_attrib_tbl(2)
,l_po_attrib_tbl(3)
,l_po_attrib_tbl(4)
,l_po_attrib_tbl(5)
,l_po_attrib_tbl(6)
,l_po_attrib_tbl(7)
,l_po_attrib_tbl(8)
,l_po_attrib_tbl(9)
,l_po_attrib_tbl(10)
,l_po_attrib_tbl(11)
,l_po_attrib_tbl(12)
,l_po_attrib_tbl(13)
,l_po_attrib_tbl(14)
,l_po_attrib_tbl(15)
,l_po_attrib_tbl(16)
,l_po_attrib_tbl(17)
,l_po_attrib_tbl(18)
,l_po_attrib_tbl(19)
,l_po_attrib_tbl(20)
,l_po_attrib_tbl(21)
,l_po_attrib_tbl(22)
,l_po_attrib_tbl(23)
,l_po_attrib_tbl(24)
,l_po_attrib_tbl(25)
,l_po_attrib_tbl(26)
--before adding next running index here Note that l_po_attrib_tbl(27) and l_po_attrib_tbl(28)
--are used below for header amounts
,l_type_lookup_code
,l_po_attrib_tbl(29) --
FROM
po_headers_all poh
,po_headers_archive_all poha
WHERE poh.po_header_id = p_doc_id
AND poh.po_header_id = poha.po_header_id
AND poha.latest_external_flag = 'Y';
MESSAGE =>'120: selected columns with changed values');
MESSAGE =>'210: Delete l_po_var'||l_po_attrib_tbl(l_po_attribute_index));
l_po_attrib_tbl.DELETE(l_po_attribute_index);
MESSAGE =>'215: Deleted');
MESSAGE =>'220: Delete p_sys_var'||p_sys_var_tbl(l_sys_var_index));
p_sys_var_tbl.delete(l_sys_var_index);
MESSAGE =>'225: Deleted');
SELECT
poh.org_id
,poh.type_lookup_code
,poh.vendor_id
,poh.vendor_site_id
,pvs.state
,pvs.zip
,pvs.country
,poh.ship_to_location_id
,poh.bill_to_location_id
,poh.currency_code
,gsb.currency_code
--Bug#3809298.Selecting the following columns also to calculate the
--functional and transaction amounts.
,poh.type_lookup_code
,poh.rate
,cu.MINIMUM_ACCOUNTABLE_UNIT
,cu.precision
,cuf.MINIMUM_ACCOUNTABLE_UNIT
,cuf.precision
--Bug#3809298.Commenting out the below two calculations of funational
--and transaction amounts as they will be replaced subsequently.
/*Start of commenting for Bug#3809298 .
,decode(poh.type_lookup_code, 'STANDARD',l_po_total_amount,0)
,round(round(
decode (poh.type_lookup_code,
'STANDARD',l_po_total_amount,0)
* nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
)* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) po_total_amount_func
End of Commenting.for Bug#3809298 */
,nvl(poh.blanket_total_amount,0)
,round(round(
nvl(poh.blanket_total_amount,0) *
nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
)* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) agreement_amount_func
,NVL(poh.global_agreement_flag,'N')
,poh.rate_type
,poh.terms_id
,poh. freight_terms_lookup_code
,poh. ship_via_lookup_code
,poh. fob_lookup_code
,poh.pay_on_code
,nvl(poh.supply_agreement_flag, 'N') --
,nvl(poh.min_release_amount,0)
,round(round(
nvl(poh. min_release_amount,0) *
nvl(poh.rate,1)/nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cu.MINIMUM_ACCOUNTABLE_UNIT,null,cu.precision,0)
) * nvl(cu.MINIMUM_ACCOUNTABLE_UNIT,1) /
nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1),decode(cuf.MINIMUM_ACCOUNTABLE_UNIT,null,cuf.precision,0)
)* nvl(cuf.MINIMUM_ACCOUNTABLE_UNIT,1) min_release_amount_func
,PO_CORE_S.get_default_legal_entity_id(poh.org_id) -- Bug 4654758, Bug 4691758
,poh.segment1
,poh.revision_num
,poh.vendor_contact_id
,poh.agent_id
,poh.rate_date
,poh.rate
,poh.acceptance_required_flag
,poh.acceptance_due_date
,poh.start_date
,poh.end_date
,poh.shipping_control --
,poh.style_id || '-' || poh.type_lookup_code -- Bug 5063781
INTO
l_po_attrib_tbl(1).variable_value_id
,l_po_attrib_tbl(2).variable_value_id
,l_po_attrib_tbl(3).variable_value_id
,l_po_attrib_tbl(4).variable_value_id
,l_po_attrib_tbl(5).variable_value_id
,l_po_attrib_tbl(6).variable_value_id
,l_po_attrib_tbl(7).variable_value_id
,l_po_attrib_tbl(8).variable_value_id
,l_po_attrib_tbl(9).variable_value_id
,l_po_attrib_tbl(10).variable_value_id
,l_po_attrib_tbl(11).variable_value_id
--Bug#3809298.Commenting out l_po_attrib_tbl(12).variable_value_id
--and l_po_attrib_tbl(13).variable_value_id and replacing them
--with the local variables declared .
/* ,l_po_attrib_tbl(12).variable_value_id
,l_po_attrib_tbl(13).variable_value_id */
,l_poh_type_lookup_code
,l_poh_rate
,l_cu_MINIMUM_ACCOUNTABLE_UNIT
,l_cu_precision
,l_cuf_MINIMUM_ACCOUNTABLE_UNIT
,l_cuf_precision
--Bug#3809298.
,l_po_attrib_tbl(14).variable_value_id
,l_po_attrib_tbl(15).variable_value_id
,l_po_attrib_tbl(16).variable_value_id
,l_po_attrib_tbl(17).variable_value_id
,l_po_attrib_tbl(18).variable_value_id
,l_po_attrib_tbl(19).variable_value_id
,l_po_attrib_tbl(20).variable_value_id
,l_po_attrib_tbl(21).variable_value_id
,l_po_attrib_tbl(22).variable_value_id
,l_po_attrib_tbl(23).variable_value_id
,l_po_attrib_tbl(24).variable_value_id
,l_po_attrib_tbl(25).variable_value_id
,l_po_attrib_tbl(26).variable_value_id
,l_po_attrib_tbl(27).variable_value_id
,l_po_attrib_tbl(28).variable_value_id
,l_po_attrib_tbl(29).variable_value_id
,l_po_attrib_tbl(30).variable_value_id
,l_po_attrib_tbl(31).variable_value_id
,l_po_attrib_tbl(32).variable_value_id
,l_po_attrib_tbl(33).variable_value_id
,l_po_attrib_tbl(34).variable_value_id
,l_po_attrib_tbl(35).variable_value_id
,l_po_attrib_tbl(36).variable_value_id
,l_po_attrib_tbl(87).variable_value_id --
,l_po_attrib_tbl(88).variable_value_id --
FROM
po_headers_all poh
,FINANCIALS_SYSTEM_PARAMS_ALL FP
,FND_CURRENCIES_VL CU
,GL_SETS_OF_BOOKS gsb
,FND_CURRENCIES_VL cuf
,po_vendor_sites_all pvs
WHERE
poh.po_header_id = p_doc_id
AND poh.vendor_site_id = pvs.vendor_site_id(+)
AND poh.currency_code = cu.currency_code
AND nvl(poh.org_id,-99) = nvl(fp.org_id,-99)
AND FP.set_of_books_id = gsb.set_of_books_id
AND cuf.currency_code = gsb.currency_code
;
SELECT ROUND
(
ROUND ( l_po_total_amount * NVL (l_poh_rate, 1) / NVL (l_cu_minimum_accountable_unit, 1),
DECODE (l_cu_minimum_accountable_unit, NULL, l_cu_precision, 0)
)
* NVL (l_cu_minimum_accountable_unit, 1) / NVL (l_cuf_minimum_accountable_unit, 1),
DECODE (l_cuf_minimum_accountable_unit, NULL, l_cuf_precision, 0)
)
* NVL (l_cuf_minimum_accountable_unit, 1) po_total_amount_func
INTO l_po_attrib_tbl(13).variable_value_id
FROM DUAL;
SELECT poha.conterms_exist_flag
INTO l_archived_conterms_flag
FROM po_headers_archive_all poha
WHERE poha.po_header_id = p_header_id
AND poha.revision_num = l_signed_revision_num;
SELECT poh.type_lookup_code,
poh.org_id,
poh.segment1, -- Bug 4096095
nvl(poh.authorization_status,'INCOMPLETE'),
poh.revision_num,
poh.vendor_id,
poh.vendor_site_id,
poh.agent_id,
poh.conterms_exist_flag
INTO l_document_type,
l_org_id,
l_doc_number, -- Bug 4096095
l_status,
l_revision,
l_vendor_id,
l_vendor_site_id,
l_agent_id,
l_conterms_flag
FROM po_headers_all poh
WHERE poh.po_header_id = p_document_id;
SELECT nvl(poha.conterms_exist_flag,'N')
INTO l_archived_conterms_flag
FROM po_headers_archive_all poha
WHERE poha.po_header_id = p_po_header_id
AND poha.latest_external_flag = 'Y';