[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Purpose : To update the pa_events table (bill transaction currency to |
| revenue processing currency |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN project Id |
| p_request_id IN Id for the current Run |
| p_event_type IN Type of events - to identify the AUTOMATIC events and |
| other events |
| p_calling_place IN |
| acc_thru_date IN Input parameter given in When we Generate revenue |
| p_project_rate_date IN Project Rate date |
p_projfunc_rate_dateIN Project Functional Rate date |
| x_return_status IN OUT Return status of this procedure |
| x_msg_count IN OUT Error message count |
| x_msg_data IN OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
/* Funding Revaluation Changes : Added the realized gain and loss event type */
g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
SELECT start_date,end_date
INTO l_start_date,l_end_date
FROM pa_periods
WHERE p_date between start_date and end_date;
SELECT event_id,
bill_trans_currency_code,
bill_trans_rev_amount,
project_currency_code,
project_rate_type,
project_rate_date,
project_exchange_rate,
projfunc_currency_code,
projfunc_rate_type,
projfunc_rate_date,
projfunc_exchange_rate,
revproc_currency_code,
revproc_rate_type,
revproc_rate_date,
revproc_exchange_rate,
'N' /* Bug 2563738 */
FROM pa_events v
WHERE v.project_id = p_project_id
AND v.request_id = p_request_id
AND v. revenue_distributed_flag = 'D'
AND nvl(v.task_id, -1) IN
(SELECT decode(v.task_id, null, -1, t.task_id )
FROM pa_tasks t
WHERE t.project_id = p_project_id
AND t.ready_to_distribute_flag ||'' = 'Y'
)
AND TRUNC(v.completion_date) <= TRUNC(NVL(p_acc_thru_dt,sysdate)) /* Bug#3118592 */
AND (DECODE(NVL(v.bill_trans_rev_amount, 0), 0 ,
DECODE(NVL(v.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
AND v.calling_place = p_calling_place
AND EXISTS
(SELECT vt.event_type
FROM pa_event_types vt
WHERE vt.event_type = v.event_type
AND vt.event_type_classification||''= 'AUTOMATIC'
)
AND ( v.calling_process||'' = 'Revenue'
OR ( v.calling_process||'' = 'Invoice'
AND EXISTS
(SELECT 'Invoice is released'
FROM pa_draft_invoice_items drii,
pa_draft_invoices dri
WHERE drii.project_id = p_project_id
AND nvl(drii.event_task_id, -1) = nvl( v.task_id, -1)
AND drii.event_num = v.event_num
AND dri.project_id = drii.project_id
AND dri.draft_invoice_num = drii.draft_invoice_num
AND dri.released_date is not null
)))
AND p_event_type = 'AUTOMATIC'
UNION
SELECT event_id,
bill_trans_currency_code,
bill_trans_rev_amount,
project_currency_code,
project_rate_type,
project_rate_date,
project_exchange_rate,
projfunc_currency_code,
projfunc_rate_type,
projfunc_rate_date,
projfunc_exchange_rate,
revproc_currency_code,
revproc_rate_type,
revproc_rate_date,
revproc_exchange_rate,
DECODE(vt.event_type_classification , 'REALIZED_GAINS', 'Y',
'REALIZED_LOSSES', 'Y','N')
FROM pa_events evt,
pa_event_types vt
WHERE evt.project_id = p_project_id
AND evt.request_id = p_request_id
AND evt.revenue_distributed_flag = 'D'
AND TRUNC(evt.completion_date) <= TRUNC(NVL(p_acc_thru_dt,sysdate)) /* Bug#3118592 */
AND (DECODE(NVL(evt.bill_trans_rev_amount, 0), 0 ,
DECODE(NVL(evt.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
AND vt.event_type = evt.event_type ||''
AND vt.event_type_classification ||'' IN
('WRITE ON','WRITE OFF','MANUAL','REALIZED_GAINS','REALIZED_LOSSES')
AND EXISTS ( SELECT 'ready to distribute top task exists'
FROM pa_tasks tsk
WHERE tsk.project_id = p_project_id
AND tsk.task_id = NVL( evt.task_id, tsk.task_id )
AND tsk.ready_to_distribute_flag ||'' = 'Y'
)
/* AND EXISTS ( SELECT 'Write on or Write off or Manual events exists'
FROM pa_event_types vt
WHERE vt.event_type = evt.event_type ||''
AND vt.event_type_classification ||'' IN
('WRITE ON','WRITE OFF','MANUAL','REALIZED_GAINS','REALIZED_LOSSES')
) */
AND p_event_type = 'MANUAL';
l_event_id_tab.delete;
l_bill_trans_curr_code_tab.delete;
l_bill_trans_rev_amount_tab.delete;
l_project_curr_code_tab.delete;
l_project_rate_type_tab.delete;
l_project_rate_date_tab.delete;
l_project_exchange_rate_tab.delete;
l_project_amount_tab.delete;
l_project_denominator_tab.delete;
l_project_numerator_tab.delete;
l_projfunc_curr_code_tab.delete;
l_projfunc_rate_type_tab.delete;
l_projfunc_rate_date_tab.delete;
l_projfunc_exchange_rate_tab.delete;
l_projfunc_amount_tab.delete;
l_projfunc_denominator_tab.delete;
l_projfunc_numerator_tab.delete;
l_revproc_curr_code_tab.delete;
l_revproc_rate_type_tab.delete;
l_revproc_rate_date_tab.delete;
l_revproc_exchange_rate_tab.delete;
l_revproc_amount_tab.delete;
l_revproc_denominator_tab.delete;
l_revproc_numerator_tab.delete;
l_project_status_tab.delete;
l_projfunc_status_tab.delete;
l_revproc_status_tab.delete;
l_user_validate_flag_tab.delete;
l_Rgain_Rloss_flag.delete;
UPDATE pa_events
SET project_revenue_amount =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N',l_project_amount_tab(i), NULL)), NULL),
project_rate_type =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N', l_project_rate_type_tab(i),
project_rate_type)), project_rate_type),
project_rev_rate_date =
DECODE(l_project_status_tab(i), 'N',
--Modified for Bug3087885
-- (DECODE(l_projfunc_status_tab(i), 'N',l_project_rate_date_tab(i), NULL)), NULL),
(DECODE(l_projfunc_status_tab(i), 'N',
DECODE(l_project_rate_type_tab(i), 'User', null, l_project_rate_date_tab(i)),
NULL)), NULL),
project_rev_exchange_rate =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N',l_project_exchange_rate_tab(i), NULL)), NULL),
projfunc_revenue_amount =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N',l_projfunc_amount_tab(i), NULL)), NULL),
projfunc_rate_type =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N', l_projfunc_rate_type_tab(i),
projfunc_rate_type)), projfunc_rate_type),
projfunc_rev_rate_date =
DECODE(l_project_status_tab(i), 'N',
--Modified for Bug3087885
-- (DECODE(l_projfunc_status_tab(i), 'N',l_projfunc_rate_date_tab(i), NULL)), NULL),
(DECODE(l_projfunc_status_tab(i), 'N',
DECODE(l_projfunc_rate_type_tab(i), 'User', null, l_projfunc_rate_date_tab(i)),
NULL)), NULL),
projfunc_rev_exchange_rate =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N',l_projfunc_exchange_rate_tab(i), NULL)), NULL),
revenue_amount =
DECODE(l_revproc_amount_tab(i), NULL, 0, l_revproc_amount_tab(i)),
revproc_rate_type =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N', l_revproc_rate_type_tab(i),
revproc_rate_type)), revproc_rate_type),
revproc_rate_date =
DECODE(l_project_status_tab(i), 'N',
--Modified for Bug3087885
-- (DECODE(l_projfunc_status_tab(i), 'N',l_revproc_rate_date_tab(i), revproc_rate_date)),
-- revproc_rate_date),
(DECODE(l_projfunc_status_tab(i), 'N',
DECODE(l_revproc_rate_type_tab(i), 'User', null, l_revproc_rate_date_tab(i)),
DECODE(l_revproc_rate_type_tab(i), 'User', null, revproc_rate_date))),
DECODE(l_revproc_rate_type_tab(i), 'User', null, revproc_rate_date)),
revproc_exchange_rate =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N',l_revproc_exchange_rate_tab(i), revproc_exchange_rate)),
revproc_exchange_rate),
revenue_distributed_flag =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N',revenue_distributed_flag, 'N')),
'N'),
rev_dist_rejection_code =
DECODE(l_project_status_tab(i), 'N',
(DECODE(l_projfunc_status_tab(i), 'N', NULL, l_projfunc_status_tab(i))),
l_project_status_tab(i))
WHERE event_id = l_event_id_tab(i);
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in Events table : ' || SQL%ROWCOUNT);
| Purpose : To update the pa_expenditure_items_all table
| (bill transaction currency to revenue processing currency
|
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN project Id |
| ei_id IN Expenditure item id
| p_request_id IN Id for the current Run |
| p_pa_date IN Project Accounting date |
| x_return_status IN OUT Return status of this procedure |
| x_msg_count IN OUT Error message count |
| x_msg_data IN OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE ei_amount_conversion(
p_project_id IN NUMBER,
p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
p_request_id IN NUMBER,
p_pa_date IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2,
x_rej_reason IN OUT NOCOPY VARCHAR2) IS
/*Commented for bug 9767275 */
/*CURSOR ei_amt_csr (p_request_id NUMBER) IS
SELECT expenditure_item_id,
expenditure_item_date, /* Added for bug 5919299*
bill_trans_raw_revenue,
bill_trans_adjusted_revenue,
bill_trans_currency_code
FROM pa_expenditure_items_all
WHERE request_id = p_request_id
AND revenue_distributed_flag = 'D'
AND bill_trans_raw_revenue IS NOT NULL
AND raw_revenue IS NULL;*/
/* cursor for select the expenditure details based on current request id */
/*OPEN ei_amt_csr( p_request_id); */ /*Commented for bug 9767275*/
l_ei_id_tab.delete;
l_ei_date_tab.delete; /* Added for bug 5907315*/
l_raw_revenue_amount_tab.delete; /*Added for bug 9767275*/
l_revenue_distributed_flag_tab.delete; /*Added for bug 9767275*/
l_bill_trans_rev_amount_tab.delete;
l_bill_trans_bill_rate_tab.delete;
l_bill_trans_curr_code_tab.delete;
l_bill_trans_adj_rev_tab.delete;
l_bill_trans_adj_rate_tab.delete;
l_revproc_rate_type_tab.delete;
l_revproc_rate_date_tab.delete;
l_revproc_exchange_rate_tab.delete;
l_revproc_curr_code_tab.delete;
l_revproc_amount_tab.delete;
l_project_curr_code_tab.delete;
l_project_rate_date_tab.delete;
l_project_rate_type_tab.delete;
l_project_exchange_rate_tab.delete;
l_project_amount_tab.delete;
l_project_rev_status_tab.delete;
l_projfunc_curr_code_tab.delete;
l_projfunc_rate_date_tab.delete;
l_projfunc_rate_type_tab.delete;
l_projfunc_exchange_rate_tab.delete;
l_projfunc_amount_tab.delete;
l_projfunc_rev_status_tab.delete;
l_revproc_adj_rev_tab.delete;
l_revproc_bill_rate_tab.delete;
l_revproc_adj_rate_tab.delete;
l_user_validate_flag_tab.delete;
l_denominator_tab.delete;
l_numerator_tab.delete;
l_raw_rev_status_tab.delete;
l_final_error_status_tab.delete;
/* If any records select in the fetch then go for conversion */
/*Added the following for bug 9767275*/
BEGIN
j := 1;
SELECT expenditure_item_date,
bill_trans_raw_revenue,
bill_trans_adjusted_revenue,
bill_trans_currency_code,
revenue_distributed_flag,
raw_revenue
INTO l_ei_date_tab(j),
l_bill_trans_rev_amount_tab(j),
l_bill_trans_adj_rev_tab(j),
l_bill_trans_curr_code_tab(j),
l_revenue_distributed_flag_tab(j),
l_raw_revenue_amount_tab(j)
FROM pa_expenditure_items_all
WHERE expenditure_item_id = p_ei_id(I)
AND revenue_distributed_flag = 'D'
AND bill_trans_raw_revenue IS NOT NULL
AND raw_revenue IS NULL;
l_denominator_tab.delete;
l_numerator_tab.delete;
for easy to use in following UPDATE */
l_final_error_status_tab(I) := 'N';
UPDATE pa_expenditure_items_all
SET raw_revenue =
DECODE(l_final_error_status_tab(i), 'N', l_revproc_amount_tab(i), NULL),
adjusted_revenue =
DECODE(l_final_error_status_tab(i), 'N',
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_revproc_adj_rev_tab(i),
l_revproc_curr_code_tab(i)), NULL),
project_raw_revenue =
DECODE(l_final_error_status_tab(i), 'N', l_project_amount_tab(i), NULL),
projfunc_raw_revenue =
DECODE(l_final_error_status_tab(i), 'N', l_projfunc_amount_tab(i), NULL),
bill_trans_raw_revenue =
DECODE(l_final_error_status_tab(i), 'N', bill_trans_raw_revenue, NULL),
bill_trans_adjusted_revenue =
DECODE(l_final_error_status_tab(i), 'N', bill_trans_adjusted_revenue, NULL),
accrued_revenue =
DECODE(l_final_error_status_tab(i), 'N', accrued_revenue, NULL),
accrual_rate =
DECODE(l_final_error_status_tab(i), 'N', accrual_rate, NULL),
revenue_distributed_flag =
DECODE(l_final_error_status_tab(i), 'N', revenue_distributed_flag, 'N'),
rev_dist_rejection_code =
DECODE(l_final_error_status_tab(i), 'N',NULL, l_final_error_status_tab(i)),
revproc_currency_code = l_revproc_curr_code_tab(i),
revproc_rate_type = l_revproc_rate_type_tab(i),
-- revproc_rate_date = l_revproc_rate_date_tab(i), --Modified for Bug3137196
revproc_rate_date = decode(l_revproc_rate_type_tab(i), 'User', null, l_revproc_rate_date_tab(i)),
revproc_exchange_rate = l_revproc_exchange_rate_tab(i),
projfunc_currency_code = l_projfunc_curr_code_tab(i),
project_rev_rate_type = l_project_rate_type_tab(i),
-- project_rev_rate_date = l_project_rate_date_tab(i), --Modified for Bug3137196
project_rev_rate_date = decode(l_project_rate_type_tab(i), 'User', null, l_project_rate_date_tab(i)),
project_rev_exchange_rate = l_project_exchange_rate_tab(i),
projfunc_rev_rate_type = l_projfunc_rate_type_tab(i),
-- projfunc_rev_rate_date = l_projfunc_rate_date_tab(i), --Modified for Bug3137196
projfunc_rev_rate_date = decode(l_projfunc_rate_type_tab(i), 'User', null, l_projfunc_rate_date_tab(i)),
projfunc_rev_exchange_rate = l_projfunc_exchange_rate_tab(i)
WHERE expenditure_item_id = l_ei_id_tab(i);
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in EI table : ' || SQL%ROWCOUNT);
| Purpose : To update the RDLltable |
| (bill transaction currency to revenue processing currency) |
| |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN project Id |
| ei_id IN Expenditure item id |
| p_request_id IN Id for the current Run |
| p_raw_revenue IN raw revenue from EI table |
| p_bill_trans_raw_revenue IN bill trans raw revenue from EI table. |
| p_project_raw_revenue IN Project Raw Revenue |
| p_projfunc_raw_revenue IN Project Functional raw Revenue |
| p_funding_rate_date IN Funding Rate Date |
| x_return_status IN OUT Return status of this procedure |
| x_msg_count IN OUT Error message count |
| x_msg_data IN OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE rdl_amount_conversion(
p_project_id IN NUMBER,
p_request_id IN NUMBER,
p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
p_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
p_bill_trans_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
p_project_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
p_projfunc_raw_revenue IN PA_PLSQL_DATATYPES.Char30TabTyp,
p_funding_rate_date IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2) IS
CURSOR rdl_amt_csr (p_project_id NUMBER,
p_request_id NUMBER) IS
SELECT rdl.expenditure_item_id,
ei.expenditure_item_date, /* Added for bug 5907315*/
rdl.line_num,
rdl.draft_revenue_num,
rdl.bill_trans_currency_code,
rdl.amount,
rdl.project_currency_code,
rdl.project_rev_rate_type,
rdl.project_rev_rate_date,
rdl.project_rev_exchange_rate,
rdl.projfunc_currency_code,
rdl.projfunc_rev_rate_type,
rdl.projfunc_rev_rate_date,
rdl.projfunc_rev_exchange_rate,
rdl.funding_currency_code,
rdl.funding_rev_rate_type,
rdl.funding_rev_rate_date,
rdl.funding_rev_exchange_rate,
nvl(ei.adjusted_revenue, ei.raw_revenue),
nvl(ei.bill_trans_adjusted_revenue, ei.bill_trans_raw_revenue),
ei.project_raw_revenue,
ei.projfunc_raw_revenue,
RDL.REVTRANS_CURRENCY_CODE,
RDL.REVPROC_REVTRANS_RATE_TYPE,
RDL.REVPROC_REVTRANS_RATE_DATE,
RDL.REVPROC_REVTRANS_EX_RATE
FROM pa_cust_rev_dist_lines rdl,
pa_expenditure_items_all ei
WHERE rdl.project_id = p_project_id
AND ei.expenditure_item_id = rdl.expenditure_item_id
AND rdl.request_id = p_request_id
AND rdl.bill_trans_amount is NULL ;
funding amount and update the rev_dist_rejection_code
ii) Call the round currency function for bill_trans_amount, project_revenue_amount,
projfunc_revenue_amount for calculated values
iii) Funding amount converted through the convert_amount_bulk API, its rounded automatically
as per the funding currency code, so not necessary to call */
/*Bug:16102971 Added NVL function to the revtrans_amount calculation line */
FORALL I IN 1 .. l_ei_id_tab.COUNT
UPDATE pa_cust_rev_dist_lines
SET bill_trans_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
l_BTC_amount_tab(i),l_bill_trans_currency_code_tab(i)),
project_revenue_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
l_project_amount_tab(i),l_project_currency_code_tab(i)),
projfunc_revenue_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
l_projfunc_amount_tab(i),l_projfunc_currency_code_tab(i)),
funding_revenue_amount =
DECODE(l_funding_status_tab(I), 'N',
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
l_funding_amount_tab(I),l_funding_currency_code_tab(i)),
NULL),
funding_rev_rate_type =
DECODE(l_funding_status_tab(I), 'N', l_funding_rev_rate_type_tab(I),NULL),
funding_rev_rate_date =
DECODE(l_funding_status_tab(I), 'N', l_funding_rev_rate_date_tab(i),funding_rev_rate_date),
funding_rev_exchange_rate =
DECODE(l_funding_status_tab(I), 'N', l_funding_rev_xchg_rate_tab(i), funding_rev_exchange_rate),
revtrans_currency_code =
DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_currency_code_tab(I),NULL),
revtrans_amount =
DECODE(l_revtrans_status_tab(I), 'N',
NVL(PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
l_revtrans_amount_tab(I),l_revtrans_currency_code_tab(i)),0), NULL),
revproc_revtrans_rate_type =
DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_rate_type_tab(I),NULL),
revproc_revtrans_rate_date =
DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_rate_date_tab(i),NULL),
revproc_revtrans_ex_rate =
DECODE(l_revtrans_status_tab(I), 'N', l_revtrans_xchg_rate_tab(i), NULL)
WHERE expenditure_item_id = l_ei_id_tab(I)
AND line_num = l_line_num_tab(I);
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in RDL :' || SQL%ROWCOUNT);
UPDATE pa_draft_revenues
SET generation_error_flag = 'Y',
transfer_rejection_reason = l_error_revtrans_status_tab(j)
WHERE project_id = p_project_id
AND draft_revenue_num = l_error_rt_dr_rev_num_tab(J);
UPDATE pa_draft_revenues
SET generation_error_flag = 'Y',
transfer_rejection_reason = l_error_funding_status_tab(J)
WHERE project_id = p_project_id
AND draft_revenue_num = l_error_draft_rev_num_tab(J);
| Purpose : To update the ERDL table |
| (bill transaction currency to Funding Currency) |
| |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_btc_code IN Bill transaction currency code |
| p_btc_amount IN Bill transaction amount |
| p_funding_curr_code IN Funding currency code to convert funding amount |
| x_funding_rate_type IN OUT Funding Rate type to convert funding amount |
| x_funding_rate_rate IN OUT Funding Rate date to convert funding amount |
| x_funding_exchange_rate IN OUT Funding Exchange Rate to convert funding amount |
| x_funding_amount IN OUT Converted funding amount |
| x_funding_convert_status IN OUT If converted the pass NULL else pass error code |
| x_return_status IN OUT Return status of this procedure |
| x_msg_count IN OUT Error message count |
| x_msg_data IN OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE erdl_amount_conversion( p_project_id IN NUMBER,
p_draft_revenue_num IN NUMBER,
p_btc_code IN VARCHAR2,
p_btc_amount IN VARCHAR2,
p_funding_rate_date IN VARCHAR2,
p_funding_curr_code IN VARCHAR2,
x_funding_rate_type IN OUT NOCOPY VARCHAR2,
x_funding_rate_date IN OUT NOCOPY VARCHAR2,
x_funding_exchange_rate IN OUT NOCOPY VARCHAR2,
x_funding_amount IN OUT NOCOPY VARCHAR2,
x_funding_convert_status IN OUT NOCOPY VARCHAR2,
p_projfunc_curr_code IN VARCHAR2,
p_projfunc_amount IN VARCHAR2,
p_projfunc_rate_type IN VARCHAR2,
p_projfunc_rate_date IN VARCHAR2,
p_projfunc_exch_rate IN VARCHAR2,
p_revtrans_curr_code IN VARCHAR2,
p_calling_place IN VARCHAR2,
x_revtrans_rate_type IN OUT NOCOPY VARCHAR2,
x_revtrans_rate_date IN OUT NOCOPY VARCHAR2,
x_revtrans_exch_rate IN OUT NOCOPY VARCHAR2,
x_revtrans_amount IN OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2
) IS
l_btc_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
UPDATE pa_draft_revenues
SET generation_error_flag = 'Y',
transfer_rejection_reason = l_funding_status_tab(1)
WHERE project_id = p_project_id
AND draft_revenue_num = p_draft_revenue_num;
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated as error in Draft Revenue : ' || SQL%ROWCOUNT);
UPDATE pa_draft_revenues
SET generation_error_flag = 'Y',
transfer_rejection_reason = l_funding_status_tab(1)
WHERE project_id = p_project_id
AND draft_revenue_num = p_draft_revenue_num;
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated as error in Draft Revenue : ' || SQL%ROWCOUNT);
| Purpose : To update the pa_expenditure_items_all table
| (bill transaction currency to revenue processing currency for |
| forecast revenue
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN project Id |
| ei_id IN Expenditure item id
| p_request_id IN Id for the current Run |
| p_pa_date IN Project Accounting date |
| x_return_status IN OUT Return status of this procedure |
| x_msg_count IN OUT Error message count |
| x_msg_data IN OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE ei_fcst_amount_conversion(
p_project_id IN NUMBER,
p_ei_id IN PA_PLSQL_DATATYPES.IdTabTyp,
p_request_id IN NUMBER,
p_pa_date IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2) IS
CURSOR ei_fcst_amt_csr (p_request_id NUMBER) IS
SELECT project_id, /* 2456371 */
expenditure_item_id,
bill_trans_forecast_revenue,
bill_trans_forecast_curr_code
FROM pa_expenditure_items_all
WHERE request_id = p_request_id
AND revenue_distributed_flag = 'F'
AND bill_trans_forecast_revenue IS NOT NULL
AND forecast_revenue IS NULL
ORDER BY project_id; /* 2456371 */
/* 2456371 - cursor for select the expenditure details based on current request id */
OPEN ei_fcst_amt_csr( p_request_id);
l_ei_id_tab.delete;
l_bill_trans_rev_amount_tab.delete;
l_bill_trans_curr_code_tab.delete;
l_project_id_tab.delete; /* 2456371 */
l_revproc_rate_type_tab.delete;
l_revproc_rate_date_tab.delete;
l_revproc_exchange_rate_tab.delete;
l_revproc_curr_code_tab.delete;
l_revproc_amount_tab.delete;
l_project_curr_code_tab.delete;
l_project_rate_date_tab.delete;
l_project_rate_type_tab.delete;
l_project_exchange_rate_tab.delete;
l_project_amount_tab.delete;
l_project_rev_status_tab.delete;
l_projfunc_curr_code_tab.delete;
l_projfunc_rate_date_tab.delete;
l_projfunc_rate_type_tab.delete;
l_projfunc_exchange_rate_tab.delete;
l_projfunc_amount_tab.delete;
l_projfunc_rev_status_tab.delete;
l_user_validate_flag_tab.delete;
l_denominator_tab.delete;
l_numerator_tab.delete;
l_raw_rev_status_tab.delete;
UPDATE pa_expenditure_items_all
SET forecast_revenue =
DECODE(l_raw_rev_status_tab(i), 'N', l_revproc_amount_tab(i), NULL),
projfunc_fcst_rate_type = l_revproc_rate_type_tab(i),
projfunc_fcst_rate_date = l_revproc_rate_date_tab(i),
projfunc_fcst_exchange_rate = l_revproc_exchange_rate_tab(i),
rev_dist_rejection_code =
DECODE(l_raw_rev_status_tab(i), 'N',NULL, l_raw_rev_status_tab(i))
WHERE expenditure_item_id = l_ei_id_tab(i);
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated in EI table for forecast revenue: ' || SQL%ROWCOUNT);
G_LAST_UPDATE_LOGIN := fnd_global.login_id;
G_LAST_UPDATED_BY := fnd_global.user_id;
SELECT draft_revenue_num,
EI,
line_num,
amt_value,
diff
INTO
l_draft_revenue_num,
l_exp_id,
l_exp_line_num,
l_amount_value,
l_diff_amount
FROM
(SELECT draft_revenue_num,
amt_value,
EI,
line_num,
sgn,
rank()over( order by amt_value) AS rnk,
lead(amt_value)over(order by amt_value)- amt_value diff
FROM
(SELECT draft_revenue_num,
SUM(NVL(DECODE(SIGN(RDL.REVTRANS_AMOUNT),1,RDL.REVTRANS_AMOUNT),0))amt_value,
MAX(expenditure_item_id) EI,max(line_num)line_num,
'POSITIVE' sgn
FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = P_PROJECT_ID
AND rdl.request_id = P_REQUEST_ID
AND REVTRANS_AMOUNT >0
GROUP BY draft_revenue_num
UNION
SELECT draft_revenue_num,
SUM(NVL(DECODE(SIGN(RDL.REVTRANS_AMOUNT),-1,RDL.REVTRANS_AMOUNT),0))*-1 amt_value,
MAX(expenditure_item_id) EI, max(line_num)line_num,
'NEGATIVE' sgn
FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = P_PROJECT_ID
AND rdl.request_id = P_REQUEST_ID
AND REVTRANS_AMOUNT <0
GROUP BY draft_revenue_num
) order by amt_value
)
WHERE rnk=1 ;
UPDATE PA_CUST_REV_DIST_LINES_ALL
SET REVTRANS_AMOUNT= REVTRANS_AMOUNT + l_diff_amount
WHERE PROJECT_ID = P_PROJECT_ID
AND REQUEST_ID = P_REQUEST_ID
AND DRAFT_REVENUE_NUM=l_draft_revenue_num
and expenditure_item_id=l_exp_id
and line_num=l_exp_line_num ;