[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_return_status
FROM pa_ci_supplier_details
WHERE ci_transaction_id = p_ci_transaction_id;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name = p_vendor_name;
SELECT po.po_header_id
FROM po_headers_all po
WHERE po.segment1 = p_po_number
AND po.vendor_id = c_vendor_id
/* added this condition to cehck Po status is OPEN or APPRVOED */
AND NVL(po.closed_code,'XX') NOT in ('FINALLY CLOSED','CLOSED')
AND (( po.org_id = p_org_id
AND p_org_id is NOT NULL )
OR p_org_id is NULL
);
Elsif p_change_type = 'UPDATE' and p_po_number is NOT NULL and p_org_id is NOT NULL then
OPEN cur_po;
Elsif p_change_type = 'UPDATE' and p_po_number is NOT NULL and p_org_id is NULL then
OPEN cur_po;
SELECT pol.po_line_id
FROM po_lines_all pol
,po_headers_all poh
WHERE pol.po_header_id = poh.po_header_id
AND poh.po_header_id = c_po_header_id
AND pol.line_num = c_po_line_num
/* added this condition to cehck Po status is OPEN or APPRVOED */
AND NVL(poh.closed_code,'XX') NOT in ('FINALLY CLOSED','CLOSED');
ElsIf p_po_number is null and p_PO_LINE_NUM is null AND p_change_type = 'UPDATE' then
l_error_msg := 'PA_CISI_INVALID_CHANGE_TYPE';
Elsif p_po_number is null and p_PO_LINE_NUM is NOT Null AND p_change_type = 'UPDATE' then
l_error_msg := 'PA_CISI_INVALID_PO';
Elsif p_po_number is NOT Null and p_PO_LINE_NUM is Null AND p_change_type = 'UPDATE' then
l_error_msg := 'PA_CISI_POLINE_NULL';
SELECT po.currency_code
FROM po_headers_all po
WHERE po.po_header_id = c_po_header_id
AND po.currency_code = p_currency_code;
SELECT currency_code
FROM fnd_currencies -- Modified for Bug 4403203.
WHERE enabled_flag = 'Y'
AND trunc(sysdate) between nvl(start_date_active,trunc(sysdate))
and nvl(end_date_active,trunc(sysdate))
AND currency_code = p_currency_code;
PROCEDURE validate_insert_SI (
p_ROWID IN OUT NOCOPY PA_VC_1000_150
,p_RECORD_STATUS IN PA_VC_1000_150
,p_CI_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_CI_TYPE_ID IN PA_VC_1000_150
,p_CI_IMPACT_ID IN PA_VC_1000_150
,P_CALLING_MODE IN VARCHAR2
,P_CI_STATUS IN PA_VC_1000_150
,P_ORG_ID IN PA_VC_1000_150
,x_VENDOR_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_VENDOR_NAME IN PA_VC_1000_150
,x_PO_HEADER_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_PO_NUMBER IN PA_VC_1000_150
,x_PO_LINE_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_PO_LINE_NUM IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_ADJUSTED_TRANSACTION_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_CURRENCY_CODE IN PA_VC_1000_150
,p_CHANGE_AMOUNT IN PA_VC_1000_150 --PA_VC_1000_NUM
,p_CHANGE_TYPE IN PA_VC_1000_150
,p_CHANGE_DESCRIPTION IN PA_VC_1000_150
,p_ci_transaction_id IN OUT NOCOPY PA_VC_1000_150
,p_RECORD_ID IN OUT NOCOPY PA_VC_1000_150
,p_REC_RETURN_STATUS IN OUT NOCOPY PA_VC_1000_150
,x_return_status IN OUT NOCOPY VARCHAR2
,x_msg_data IN OUT NOCOPY VARCHAR2
,x_msg_count IN OUT NOCOPY NUMBER
) IS
l_error_msg_code varchar2(100):= null;
PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.validate_insert_SI');
l_vendor_id.delete;
l_po_header_id.delete;
l_po_line_id.delete;
l_return_status.delete;
l_rowid.delete;
l_ci_transaction_id.delete;
IF (p_calling_mode = 'VALIDATEANDINSERT') then
FOR i in 1 .. l_rec_count LOOP
/** print the inpput params **/
If l_debug_mode = 'Y' THEN
print_msg('p_RECORD_STATUS['||p_RECORD_STATUS(i)||']p_CI_ID['||p_CI_ID(i)||
']P_CI_STATUS['||P_CI_STATUS(i)||']p_VENDOR_NAME['||p_VENDOR_NAME(i)||
']p_PO_NUMBER['||p_PO_NUMBER(i)||']p_PO_LINE_NUM['||p_PO_LINE_NUM(i)||']p_CURRENCY_CODE['||
p_CURRENCY_CODE(i)||']p_CHANGE_AMOUNT['||p_CHANGE_AMOUNT(i)||']p_CHANGE_TYPE['||p_CHANGE_TYPE(i)||
']p_CHANGE_DESCRIPTION['||p_CHANGE_DESCRIPTION(i)||']p_rowid['||p_rowid(i)||
']p_ci_transaction_id['||p_ci_transaction_id(i)||']' );
print_msg('calling insert_row api');
PA_CI_SUPPLIER_PKG.insert_row (
x_rowid => l_rowid(i)
,x_ci_transaction_id => l_ci_transaction_id(i)
,p_CI_TYPE_ID => p_ci_type_id(i)
,p_CI_ID => p_CI_ID(i)
,p_CI_IMPACT_ID => p_ci_impact_id(i)
,p_VENDOR_ID => l_vendor_id(i)
,p_PO_HEADER_ID => l_po_header_id(i)
,p_PO_LINE_ID => l_po_line_id(i)
,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
,p_CREATED_BY => FND_GLOBAL.login_id
,p_CREATION_DATE => trunc(sysdate)
,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_ci_status => P_CI_STATUS(i)
,x_return_status => l_return_status(i)
,x_error_msg_code => l_error_msg_code );
print_msg('end of insert row api');
print_msg('calling update row api');
PA_CI_SUPPLIER_PKG.update_row (
p_rowid => l_rowid(i)
,p_ci_transaction_id => l_ci_transaction_id(i)
,p_CI_TYPE_ID => p_ci_type_id(i)
,p_CI_ID => p_CI_ID(i)
,p_CI_IMPACT_ID => p_ci_impact_id(i)
,p_VENDOR_ID => l_vendor_id(i)
,p_PO_HEADER_ID => l_po_header_id(i)
,p_PO_LINE_ID => l_po_line_id(i)
,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID(i)
,p_CURRENCY_CODE => p_CURRENCY_CODE(i)
,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT(i)
,p_CHANGE_TYPE => p_CHANGE_TYPE(i)
,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION(i)
,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_ci_status => P_CI_STATUS(i)
,x_return_status => l_return_status(i)
,x_error_msg_code => l_error_msg_code );
print_msg('end of update row api');
END validate_insert_SI;
IF (p_calling_mode = 'VALIDATEANDINSERT') then
/** print the inpput params **/
IF l_debug_mode = 'Y' THEN
print_msg('p_RECORD_STATUS['||p_RECORD_STATUS||']p_CI_ID['||p_CI_ID||
']p_VENDOR_NAME['||p_VENDOR_NAME||
']p_PO_NUMBER['||p_PO_NUMBER||']p_PO_LINE_NUM['||p_PO_LINE_NUM||']p_CURRENCY_CODE['||
p_CURRENCY_CODE||']p_CHANGE_AMOUNT['||p_CHANGE_AMOUNT||']p_CHANGE_TYPE['||p_CHANGE_TYPE||
']p_CHANGE_DESCRIPTION['||p_CHANGE_DESCRIPTION||']p_rowid['||p_rowid||
']p_ci_transaction_id['||p_ci_transaction_id||']p_org_id['||p_org_id||']' );
print_msg('calling insert_row api');
PA_CI_SUPPLIER_PKG.insert_row (
x_rowid => l_rowid
,x_ci_transaction_id => l_ci_transaction_id
,p_CI_TYPE_ID => p_ci_type_id
,p_CI_ID => p_CI_ID
,p_CI_IMPACT_ID => p_ci_impact_id
,p_VENDOR_ID => l_vendor_id
,p_PO_HEADER_ID => l_po_header_id
,p_PO_LINE_ID => l_po_line_id
,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
,p_CURRENCY_CODE => p_CURRENCY_CODE
,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
,p_CHANGE_TYPE => p_CHANGE_TYPE
,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
,p_CREATED_BY => FND_GLOBAL.login_id
,p_CREATION_DATE => trunc(sysdate)
,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_ci_status => l_CI_STATUS
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code );
print_msg('end of insert row api');
/** Check if the ci_transaction_id is already populated then update the row else
** insert the row with same ci_transaction_id. so that populating unnecessary sequence
** number can be avoided.
**/
If check_trx_exists(l_ci_transaction_id) = 'Y' then
IF l_debug_mode = 'Y' THEN
print_msg('calling update row api');
PA_CI_SUPPLIER_PKG.update_row (
p_rowid => l_rowid
,p_ci_transaction_id => l_ci_transaction_id
,p_CI_TYPE_ID => p_ci_type_id
,p_CI_ID => p_CI_ID
,p_CI_IMPACT_ID => p_ci_impact_id
,p_VENDOR_ID => l_vendor_id
,p_PO_HEADER_ID => l_po_header_id
,p_PO_LINE_ID => l_po_line_id
,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
,p_CURRENCY_CODE => p_CURRENCY_CODE
,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
,p_CHANGE_TYPE => p_CHANGE_TYPE
,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_ci_status => l_CI_STATUS
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code );
print_msg('end of update row api');
print_msg('calling insert_row api for record status CHANGED');
PA_CI_SUPPLIER_PKG.insert_row (
x_rowid => l_rowid
,x_ci_transaction_id => l_ci_transaction_id
,p_CI_TYPE_ID => p_ci_type_id
,p_CI_ID => p_CI_ID
,p_CI_IMPACT_ID => p_ci_impact_id
,p_VENDOR_ID => l_vendor_id
,p_PO_HEADER_ID => l_po_header_id
,p_PO_LINE_ID => l_po_line_id
,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
,p_CURRENCY_CODE => p_CURRENCY_CODE
,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
,p_CHANGE_TYPE => p_CHANGE_TYPE
,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
,p_CREATED_BY => FND_GLOBAL.login_id
,p_CREATION_DATE => trunc(sysdate)
,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_ci_status => l_CI_STATUS
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code );
print_msg('Calling PA_CI_IMPACTS_pub.create_ci_impact in Update');
print_msg('end of insert row api');
PROCEDURE deleteSIrecord(P_CALLING_MODE IN varchar2
,p_ROWID IN varchar2
,P_CI_TRANSACTION_ID IN number
,X_RETURN_STATUS IN OUT NOCOPY varchar2
,x_MSG_DATA IN OUT NOCOPY varchar2
,X_MSG_COUNT IN OUT NOCOPY number ) IS
l_debug_mode varchar2(1) := 'N';
PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.deleteSIrecord');
print_msg('inside deleteSIrecord api P_CALLING_MODE['||P_CALLING_MODE||']p_ROWID['||p_ROWID||
'] P_CI_TRANSACTION_ID['||P_CI_TRANSACTION_ID||']');
PA_CI_SUPPLIER_PKG.delete_row (p_ci_transaction_id => P_CI_TRANSACTION_ID);
print_msg('deleteSIrecord Error:'||sqlcode||sqlerrm);
END deleteSIrecord;
INSERT INTO PA_CI_SUPPLIER_DETAILS
(
CI_TRANSACTION_ID
,CI_TYPE_ID
,CI_ID
,CI_IMPACT_ID
,VENDOR_ID
,PO_HEADER_ID
,PO_LINE_ID
,ADJUSTED_CI_TRANSACTION_ID
,CURRENCY_CODE
,CHANGE_AMOUNT
,CHANGE_TYPE
,CHANGE_DESCRIPTION
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
SELECT
PA_CI_SUPPLIER_DETAILS_S.nextval
,ci.CI_TYPE_ID
,p_to_ci_item_id
,si.CI_IMPACT_ID
,si.VENDOR_ID
,si.PO_HEADER_ID
,si.PO_LINE_ID
,si.CI_TRANSACTION_ID
,si.CURRENCY_CODE
,si.CHANGE_AMOUNT
,si.CHANGE_TYPE
,si.CHANGE_DESCRIPTION
,NVL(FND_GLOBAL.login_id,-99)
,sysdate
,NVL(FND_GLOBAL.login_id,-99)
,sysdate
,NVL(FND_GLOBAL.login_id,-99)
FROM PA_CI_SUPPLIER_DETAILS si
,PA_CONTROL_ITEMS ci
WHERE si.CI_ID = p_from_ci_item_id
AND ci.ci_id = p_to_ci_item_id;
PROCEDURE DELETE_IMPACT(p_ci_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)IS
l_debug_mode varchar2(1) := 'N';
PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.delete_impact');
print_msg('Inside DELETE_IMPACT api p_ci_id['||p_ci_id||']' );
DELETE FROM PA_CI_SUPPLIER_DETAILS
WHERE ci_id = p_ci_id;
END DELETE_IMPACT;
PROCEDURE IS_SI_DELETE_OK(p_ci_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
) IS
cursor c1 is
SELECT CI_TRANSACTION_ID
FROM pa_ci_supplier_details
WHERE ci_id = p_ci_id;
PA_DEBUG.init_err_stack('PA_CI_SUPPLIER_UTILS.delete_impact');
print_msg('Inside IS_SI_DELETE_OK api p_ci_id['||p_ci_id||']' );
print_msg('Error From IS_SI_DELETE_OK :sqlerror:'||sqlcode||sqlerrm);
END IS_SI_DELETE_OK;
/** This is a overloaded function which makes calls to IS_SI_DELETE_OK plsql API
** and returns 'Y' to delete the records from supplier impact details
**/
FUNCTION IS_SI_DELETE_OK(p_ci_id IN NUMBER) return varchar2 IS
l_return_status varchar2(10);
PA_CI_SUPPLIER_UTILS.IS_SI_DELETE_OK
(p_ci_id =>p_ci_id
,x_return_status =>l_return_status
,x_msg_data =>l_err_msg_data
,x_msg_count =>l_msg_count
);
-- Indicates records exists in SI table so donot delete header lines (pa_ci_impacts)
l_return_flag := 'N';
-- No records exists in SI table so delete header lines (pa_ci_impacts)
l_return_flag := 'Y';
END IS_SI_DELETE_OK;
select to_char(p_amount, fnd_currency.get_format_mask(p_currency_code,30))
into l_string
from dual;