[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 'Y'
from pa_ci_supplier_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_mem_id
and expenditure_type = p_expenditure_type
and currency_code = p_currency_code
and change_type = 'CREATE'
and vendor_id = p_vendor_id
and expenditure_org_id = p_expenditure_org_id
and need_by_date = p_need_by_date;
select 'Y'
from pa_ci_supplier_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_mem_id
and expenditure_type = p_expenditure_type
and currency_code = p_currency_code
and change_type = 'UPDATE'
and po_line_id = p_po_line_id;
select 'Y'
from pa_ci_direct_cost_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_mem_id
and expenditure_type = p_expenditure_type
and currency_code = p_currency_code;
select 'Y'
from pa_ci_supplier_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_mem_id
and expenditure_type = p_expenditure_type
and currency_code = p_currency_code
and change_type = 'CREATE'
and vendor_id = p_vendor_id
and expenditure_org_id = p_expenditure_org_id
and need_by_date = p_need_by_date
and ci_transaction_id <> p_ci_transaction_id;
select 'Y'
from pa_ci_supplier_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_mem_id
and expenditure_type = p_expenditure_type
and currency_code = p_currency_code
and change_type = 'UPDATE'
and po_line_id = p_po_line_id
and 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_Task_Id IN PA_VC_1000_150
,p_Resource_List_Mem_Id IN PA_VC_1000_150
,p_From_Date IN PA_VC_1000_150
,p_To_Date IN PA_VC_1000_150
,p_Estimated_Cost IN PA_VC_1000_150
,p_Quoted_Cost IN PA_VC_1000_150
,p_Negotiated_Cost IN PA_VC_1000_150
,p_Burdened_cost IN PA_VC_1000_150
,p_revenue_override_rate IN PA_VC_1000_150
,p_audit_history_number in number
,p_current_audit_flag in varchar2
,p_Original_supp_trans_id in number
,p_Source_supp_trans_id in number
,p_Sup_ref_no in number
,p_version_type in varchar2 default 'ALL'
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type in varchar2
,p_expenditure_org_id in number
,p_change_reason_code in varchar2
,p_quote_negotiation_reference in varchar2
,p_need_by_date in varchar2
-- gboomina modified for supplier cost 12.1.3 requirement - end
,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_Task_Id => p_Task_Id(i)
,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id(i)
,p_From_Date => p_From_Date(i)
,p_To_Date => p_To_Date(i)
,p_Estimated_Cost => p_Estimated_Cost(i)
,p_Quoted_Cost => p_Quoted_Cost(i)
,p_Negotiated_Cost => p_Negotiated_Cost(i)
,p_Burdened_cost => p_Burdened_cost(i)
,p_Revenue => null
,p_revenue_override_rate => p_revenue_override_rate(i)
,p_audit_history_number => p_audit_history_number
,p_current_audit_flag => 'Y'
,p_Original_supp_trans_id => p_Original_supp_trans_id
,p_Source_supp_trans_id => p_Source_supp_trans_id
,p_Sup_ref_no => p_Sup_ref_no
,p_version_type => p_version_type
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type => p_expenditure_type
,p_expenditure_org_id => p_expenditure_org_id
,p_change_reason_code => p_change_reason_code
,p_quote_negotiation_reference => p_quote_negotiation_reference
,p_need_by_date => p_need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
,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_Task_Id => p_Task_Id(i)
,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id(i)
,p_From_Date => p_From_Date(i)
,p_To_Date => p_To_Date(i)
,p_Estimated_Cost => p_Estimated_Cost(i)
,p_Quoted_Cost => p_Quoted_Cost(i)
,p_Negotiated_Cost => p_Negotiated_Cost(i)
,p_Burdened_cost => p_Burdened_cost(i)
,p_Revenue => null
,p_revenue_override_rate => p_revenue_override_rate(i)
,p_audit_history_number => p_audit_history_number
,p_current_audit_flag => p_current_audit_flag
,p_Original_supp_trans_id => p_Original_supp_trans_id
,p_Source_supp_trans_id => p_Source_supp_trans_id
,p_ci_status => p_Sup_ref_no
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type => p_expenditure_type
,p_expenditure_org_id => p_expenditure_org_id
,p_change_reason_code => p_change_reason_code
,p_quote_negotiation_reference => p_quote_negotiation_reference
,p_need_by_date => p_need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
,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;
SELECT ci_impact_id
FROM pa_ci_impacts pci
WHERE pci.ci_id = p_ci_id
AND pci.IMPACT_TYPE_CODE = 'SUPPLIER';
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||
-- gboomina addded for 12.1.3 supplier cost requirement
']EXPENDITURE_TYPE['||p_expenditure_type||
']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 => l_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_Task_Id => p_Task_Id
,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
,p_From_Date => p_From_Date
,p_To_Date => p_To_Date
,p_Estimated_Cost => p_Estimated_Cost
,p_Quoted_Cost => p_Quoted_Cost
,p_Negotiated_Cost => p_Negotiated_Cost
,p_Burdened_cost => p_Burdened_cost
,p_Revenue => p_Revenue
,p_revenue_override_rate => p_revenue_override_rate
,p_audit_history_number => p_audit_history_number
,p_current_audit_flag => p_current_audit_flag
,p_Original_supp_trans_id => p_Original_supp_trans_id
,p_Source_supp_trans_id => p_Source_supp_trans_id
,p_Sup_ref_no => p_Sup_ref_no
,p_version_type => p_version_type
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type => p_expenditure_type
,p_expenditure_org_id => p_expenditure_org_id
,p_change_reason_code => p_change_reason_code
,p_quote_negotiation_reference => p_quote_negotiation_reference
,p_need_by_date => p_need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
,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_Task_Id => p_Task_Id
,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
,p_From_Date => p_From_Date
,p_To_Date => p_To_Date
,p_Estimated_Cost => p_Estimated_Cost
,p_Quoted_Cost => p_Quoted_Cost
,p_Negotiated_Cost => p_Negotiated_Cost
,p_Burdened_cost => p_Burdened_cost
,p_Revenue => p_Revenue
,p_revenue_override_rate =>p_revenue_override_rate
,p_audit_history_number => p_audit_history_number
,p_current_audit_flag => p_current_audit_flag
,p_Original_supp_trans_id => p_Original_supp_trans_id
,p_Source_supp_trans_id => p_Source_supp_trans_id
,p_ci_status => p_Sup_ref_no
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type => p_expenditure_type
,p_expenditure_org_id => p_expenditure_org_id
,p_change_reason_code => p_change_reason_code
,p_quote_negotiation_reference => p_quote_negotiation_reference
,p_need_by_date => p_need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
,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_Task_Id => p_Task_Id
,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
,p_From_Date => p_From_Date
,p_To_Date => p_To_Date
,p_Estimated_Cost => p_Estimated_Cost
,p_Quoted_Cost => p_Quoted_Cost
,p_Negotiated_Cost => p_Negotiated_Cost
,p_Burdened_cost => p_Burdened_cost
,p_Revenue => p_Revenue
,p_revenue_override_rate =>p_revenue_override_rate
,p_audit_history_number => p_audit_history_number
,p_current_audit_flag => p_current_audit_flag
,p_Original_supp_trans_id => p_Original_supp_trans_id
,p_Source_supp_trans_id => p_Source_supp_trans_id
,p_Sup_ref_no => p_Sup_ref_no
,p_version_type => p_version_type
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type => p_expenditure_type
,p_expenditure_org_id => p_expenditure_org_id
,p_change_reason_code => p_change_reason_code
,p_quote_negotiation_reference => p_quote_negotiation_reference
,p_need_by_date => p_need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
,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;
SELECT
decode (NVL(original_ci_id,0),0,ci_id, original_ci_id)
FROM pa_control_items
WHERE ci_id = p_ci_id;
SELECT
decode (NVL(all_resource_list_id,0),0,cost_resource_list_id , all_resource_list_id)
FROM pa_proj_fp_options pfo, pa_budget_versions pbv
WHERE pbv.project_id = p_project_id AND
pbv.current_working_flag = 'Y' AND
pbv.budget_status_code = 'W' AND
Nvl(pbv.approved_cost_plan_type_flag,'N') = 'Y' AND
pfo.fin_plan_version_id = pbv.budget_version_id;
SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
WHERE
Project_Id = l_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' ) ;
SELECT sum(change_amount) INTO total_cost
FROM pa_ci_supplier_details_v
WHERE
ci_id = l_ci_id;
PROCEDURE delete_supplier_costs(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE ,
p_ci_id IN NUMBER,
p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
) IS
l_api_version number := 1;
l_api_name CONSTANT VARCHAR2(30) := 'delete_supplier_costs';
select project_id
from pa_control_items
where ci_id = p_ci_id;
SELECT budget_version_id FROM pa_budget_versions
WHERE ci_id = p_ci_id;
PA_CI_SUPPLIER_PKG.delete_row (p_ci_transaction_id => p_ci_transaction_id_tbl(i));
p_action_type => 'DELETE',
p_bvid => l_budget_version_id,
p_ci_id => p_ci_id,
p_line_id_tbl => p_ci_transaction_id_tbl,
p_project_id => l_project_id,
p_task_id_tbl => p_task_id_tbl,
p_currency_code_tbl => p_currency_code_tbl,
p_rlmi_id_tbl => p_rlmi_id_tbl,
p_res_assgn_id_tbl => p_resource_assignment_id_tbl
);
end delete_supplier_costs;
l_last_updated_by number;
l_last_update_date date;
l_last_update_login number;
select project_id
from pa_control_items
where ci_id = p_ci_id;
SELECT budget_version_id FROM pa_budget_versions
WHERE ci_id = p_ci_id and version_type IN ('ALL', 'COST');
cursor get_budget_update_method is
Select typ.impact_budget_type_code
from pa_ci_types_b typ, pa_control_items items
where items.ci_type_id=typ.ci_type_id and ci_id = p_ci_id;
l_budget_update_method varchar2(30);
select start_date, end_date from po_headers_all
where po_header_id = c_po_header_id;
select nvl(raw_cost, 0) raw_cost,
from_change_date, to_change_date,
burdened_cost
from pa_ci_supplier_details
where ci_id = p_ci_id
and ci_transaction_id = c_sc_line_id;
open get_budget_update_method;
fetch get_budget_update_method into l_budget_update_method;
close get_budget_update_method;
if (l_budget_update_method = 'DIRECT_COST_ENTRY') then
-- check for uniqueness of the record
is_record_unique(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_ci_id => p_ci_id
,p_task_id => l_task_id
,p_resource_list_mem_id => l_resource_list_member_id
,p_expenditure_type => l_expenditure_type
,p_currency_code => l_currency_code
,p_vendor_id => l_vendor_id
,p_expenditure_org_id => l_expenditure_org_id
,p_need_by_date => l_need_by_date
,p_po_line_id => l_po_line_id
,p_record_status => p_record_status_tbl(i)
,p_ci_transaction_id => l_ci_transaction_id );
PA_CI_SUPPLIER_PKG.insert_row (
x_rowid => l_rowid
,x_ci_transaction_id => l_ci_transaction_id
,p_CI_TYPE_ID => l_ci_type_id
,p_CI_ID => l_ci_id
,p_CI_IMPACT_ID => l_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 => l_adjusted_ci_transaction_id
,p_CURRENCY_CODE => l_currency_code
,p_CHANGE_AMOUNT => l_change_amount
,p_CHANGE_TYPE => l_change_type
,p_CHANGE_DESCRIPTION => l_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_Task_Id => l_task_id
,p_Resource_List_Mem_Id => l_resource_list_member_id
,p_From_Date => l_from_change_date
,p_To_Date => l_to_change_date
,p_Estimated_Cost => l_estimated_cost
,p_Quoted_Cost => l_quoted_cost
,p_Negotiated_Cost => l_negotiated_cost
,p_Burdened_cost => l_burdened_cost
,p_Revenue => l_revenue
,p_revenue_override_rate => l_revenue_override_rate
,p_audit_history_number => l_audit_history_number
,p_current_audit_flag => l_current_audit_flag
,p_Original_supp_trans_id => l_original_supp_trans_id
,p_Source_supp_trans_id => l_source_supp_trans_id
,p_Sup_ref_no => l_sup_quote_ref_no
,p_version_type => p_version_type
,p_expenditure_type => l_expenditure_type
,p_expenditure_org_id => l_expenditure_org_id
,p_change_reason_code => l_change_reason_code
,p_quote_negotiation_reference => l_quote_negotiation_ref
,p_need_by_date => l_need_by_date
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code );
if (l_budget_update_method = 'DIRECT_COST_ENTRY') then
-- check for uniqueness of the record
is_record_unique(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_ci_id => p_ci_id
,p_task_id => l_task_id
,p_resource_list_mem_id => l_resource_list_member_id
,p_expenditure_type => l_expenditure_type
,p_currency_code => l_currency_code
,p_vendor_id => l_vendor_id
,p_expenditure_org_id => l_expenditure_org_id
,p_need_by_date => l_need_by_date
,p_po_line_id => l_po_line_id
,p_record_status => p_record_status_tbl(i)
,p_ci_transaction_id => l_ci_transaction_id );
-- for bug 9840053: calculate the burdened_cost and pass on to the update_row()
if sc_line_row.raw_cost <> l_change_amount and sc_line_row.raw_cost <> 0 then
if sc_line_row.burdened_cost is not null then
l_burdened_cost := (sc_line_row.burdened_cost/sc_line_row.raw_cost) * l_change_amount;
PA_CI_SUPPLIER_PKG.update_row (
p_rowid => l_rowid
,p_ci_transaction_id => l_ci_transaction_id
,p_CI_TYPE_ID => l_ci_type_id
,p_CI_ID => l_ci_id
,p_CI_IMPACT_ID => l_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 => l_adjusted_ci_transaction_id
,p_CURRENCY_CODE => l_currency_code
,p_CHANGE_AMOUNT => l_change_amount
,p_CHANGE_TYPE => l_change_type
,p_CHANGE_DESCRIPTION => l_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_Task_Id => l_task_id
,p_Resource_List_Mem_Id => l_resource_list_member_id
,p_From_Date => l_from_change_date
,p_To_Date => l_to_change_date
,p_Estimated_Cost => l_estimated_cost
,p_Quoted_Cost => l_quoted_cost
,p_Negotiated_Cost => l_negotiated_cost
,p_Burdened_cost => l_burdened_cost
,p_Revenue => l_revenue
,p_revenue_override_rate => l_revenue_override_rate
,p_audit_history_number => l_audit_history_number
,p_current_audit_flag => l_current_audit_flag
,p_Original_supp_trans_id => l_original_supp_trans_id
,p_Source_supp_trans_id => l_source_supp_trans_id
,p_Sup_ref_no => l_sup_quote_ref_no
,p_version_type => p_version_type
,p_expenditure_type => l_expenditure_type
,p_expenditure_org_id => l_expenditure_org_id
,p_change_reason_code => l_change_reason_code
,p_quote_negotiation_reference => l_quote_negotiation_ref
,p_need_by_date => l_need_by_date
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code );
open get_budget_update_method;
fetch get_budget_update_method into l_budget_update_method;
close get_budget_update_method;
if (l_budget_update_method = 'DIRECT_COST_ENTRY') then
--if (l_ci_transaction_id_tbl.count > 0) then
open get_project_id;
p_action_type => 'INSERT',
p_bvid => l_budget_version_id,
p_ci_id => p_ci_id,
p_line_id_tbl => b_ins_sc_line_id_tbl,
p_project_id => l_project_id,
p_task_id_tbl => b_ins_task_id_tbl,
p_currency_code_tbl => b_ins_currency_code_tbl,
p_rlmi_id_tbl => b_ins_rlmi_id_tbl,
p_res_assgn_id_tbl => b_ins_res_assgn_id_tbl,
p_quantity_tbl => b_ins_quantity_tbl, -- this will be null for supplier cost
p_raw_cost_tbl => b_ins_raw_cost_tbl
);
update pa_ci_supplier_details pcsc
set (resource_assignment_id, FROM_CHANGE_DATE,
TO_CHANGE_DATE, burdened_cost) =
(select prac.resource_assignment_id,
decode(pcsc.FROM_CHANGE_DATE,
null,pra.planning_start_date, pcsc.FROM_CHANGE_DATE),
decode(pcsc.TO_CHANGE_DATE,
null, pra.planning_end_date, pcsc.TO_CHANGE_DATE),
pcsc.raw_cost * prac.txn_average_burden_cost_rate
from pa_resource_assignments pra, pa_resource_asgn_curr prac
where pra.budget_version_id = l_budget_version_id
and pra.task_id = pcsc.task_id
and pra.resource_list_member_id = pcsc.resource_list_member_id
and prac.txn_currency_code = pcsc.currency_code
and prac.resource_assignment_id = pra.resource_assignment_id)
where ci_id = p_ci_id
and source_supp_trans_id = b_ins_sc_line_id_tbl(i);
p_action_type => 'UPDATE',
p_bvid => l_budget_version_id,
p_ci_id => p_ci_id,
p_line_id_tbl => b_upd_sc_line_id_tbl,
p_project_id => l_project_id,
p_task_id_tbl => b_upd_task_id_tbl,
p_currency_code_tbl => b_upd_currency_code_tbl,
p_rlmi_id_tbl => b_upd_rlmi_id_tbl,
p_res_assgn_id_tbl => b_upd_res_assgn_id_tbl,
p_quantity_tbl => b_upd_quantity_tbl, -- this will be null for supplier cost
p_raw_cost_tbl => b_upd_raw_cost_tbl
);
update pa_ci_supplier_details pcsc
set (resource_assignment_id, FROM_CHANGE_DATE,
TO_CHANGE_DATE, burdened_cost) =
(select prac.resource_assignment_id,
decode(pcsc.FROM_CHANGE_DATE,
null,pra.planning_start_date, pcsc.FROM_CHANGE_DATE),
decode(pcsc.TO_CHANGE_DATE,
null, pra.planning_end_date, pcsc.TO_CHANGE_DATE),
pcsc.raw_cost * prac.txn_average_burden_cost_rate
from pa_resource_assignments pra, pa_resource_asgn_curr prac
where pra.budget_version_id = l_budget_version_id
and pra.task_id = pcsc.task_id
and pra.resource_list_member_id = pcsc.resource_list_member_id
and prac.txn_currency_code = pcsc.currency_code
and prac.resource_assignment_id = pra.resource_assignment_id)
where ci_id = p_ci_id
and source_supp_trans_id = b_upd_sc_line_id_tbl(i);