[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
and updated the pa_invoices table
----------------------------------------------------------------------------*/
FUNCTION format_proj_curr_code
RETURN VARCHAR2 IS
BEGIN
/* Changed the Field length to 22 from 15 for Bug#2337109 pcchandr 16-May-2002 */
return(fnd_currency.get_format_mask(pa_invoice_currency.g_currency_code,22));
| This Private Procedure Insert_Distrbution_Warning Inserts draft Invoice |
| distribution warning. |
+----------------------------------------------------------------------------*/
Procedure Insert_Distrbution_Warning ( P_Project_ID in number,
P_Draft_Invoice_Num in number,
P_User_ID in number,
P_Request_ID in number,
P_Invoice_Set_ID in number,
P_Lookup_Type in varchar2,
P_Error_Message_Code in varchar2) is
l_error_message pa_lookups.meaning%TYPE;
SELECT Meaning
INTO l_error_message
FROM PA_Lookups
WHERE Lookup_Type = P_Lookup_Type
AND Lookup_Code = P_Error_Message_Code;
INSERT INTO PA_DISTRIBUTION_WARNINGS
(
PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
)
VALUES
(
P_Project_ID, P_Draft_Invoice_Num, sysdate, P_User_ID,
sysdate, P_User_ID, P_Request_ID, l_error_message
);
INSERT INTO PA_DISTRIBUTION_WARNINGS
(
PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
)
SELECT Project_ID, Draft_Invoice_Num, sysdate, P_User_ID,
sysdate, P_User_ID, P_Request_ID, l_error_message
FROM PA_Draft_Invoices_ALL
WHERE Invoice_Set_ID = P_Invoice_Set_ID
AND project_id = p_project_id ; /* Fix for Performance bug 4942339 */
END Insert_Distrbution_Warning;
select dii.rowid row_id, /** Bug 2324299 **/
dii.line_num line_num,
dii.amount amount,
NVL(di.retention_invoice_flag,'N') retention_invoice_flag
/* Added for Payroll Billing ER #11847630 - Start */
,nvl(dii.credit_process_flag,'N') credit_process_flag
,dii.inv_exchange_rate
/* Added for Payroll Billing ER #11847630 - End */
from pa_draft_invoice_items dii,
pa_draft_invoices_all di
where dii.project_id = P_Project_Id
and dii.draft_invoice_num = P_Draft_Inv_Num
and dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
and dii.project_id = di.project_id
and dii.draft_invoice_num = di.draft_invoice_num
AND NVL(di.retention_invoice_flag,'N') ='N'
UNION
select dii.rowid row_id, /** Bug 2324299 **/
dii.line_num line_num,
dii.amount amount,
NVL(di.retention_invoice_flag,'N') retention_invoice_flag
/* Added for Payroll Billing ER #11847630 - Start */
,nvl(dii.credit_process_flag,'N') credit_process_flag
,dii.inv_exchange_rate
/* Added for Payroll Billing ER #11847630 - End */
from pa_draft_invoice_items dii,
pa_draft_invoices_all di
where dii.project_id = P_Project_Id
and dii.draft_invoice_num = P_Draft_Inv_Num
and dii.invoice_line_type = 'RETENTION'
and dii.project_id = di.project_id
and dii.draft_invoice_num = di.draft_invoice_num
AND NVL(di.retention_invoice_flag,'N') ='Y'
order by line_num;
select dii.rowid dii_rowid,
di.retention_percentage retention,
dii.amount amt,
dii.line_num line,
dii.projfunc_bill_amount,
NVL(di.retention_invoice_flag,'N') retention_invoice_flag
from pa_draft_invoices di,
pa_draft_invoice_items dii
where di.project_id = dii.project_id
and di.draft_invoice_num = dii.draft_invoice_num
and di.project_id = P_Project_Id
and di.draft_invoice_num = P_Draft_Inv_Num
and dii.invoice_line_type = 'RETENTION'
AND NVL(di.retention_invoice_flag,'N') ='N' ;
select PROJFUNC_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
INVPROC_CURRENCY_TYPE,
PROJFUNC_BIL_EXCHANGE_RATE, -- FP_M Changes
PROJFUNC_BIL_RATE_DATE_CODE,
PROJFUNC_BIL_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE,
ProjFunc_Attr_For_AR_Flag -- FP_M changes
into l_func_curr,
l_project_currency_code,
l_invproc_currency_type,
l_projfunc_Exchange_Rate,
l_PFC_Exchg_Rate_Date_Code, -- FP_M Changes
l_projfunc_Exchg_Rate_type,
l_projfunc_Exchg_Rate_Date,
l_ProjFunc_Attr_For_AR_Flag
from pa_projects_all
where project_id = P_Project_Id;
SELECT NVL(di.retention_invoice_flag,'N')
INTO l_retention_invoice_flag
FROM pa_draft_invoices_all di
WHERE di.project_id = P_Project_Id
AND di.draft_invoice_num = P_Draft_Inv_Num;
/* SELECT funding_currency_code
INTO l_funding_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum=1
GROUP BY funding_currency_code
HAVING sum(total_baselined_amount) <> 0; Commented for bug 3147272*/
/* added the following select statement for bug 3147272*/
SELECT funding_currency_code
INTO l_funding_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum=1
AND NVL(total_baselined_amount,0) > 0;
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Selecting Inv Trans Currency COde');
/* for Invoice generation, select currency code and conversion attribute from pa_project_customers. */
select inv_currency_code,
-- nvl(inv_rate_date,P_Bill_Thru_Date), /* commented for mcb2 to use invoice_date */
nvl(inv_rate_date,NVL(l_invoice_date,P_Bill_Thru_Date)),
inv_rate_type,
inv_exchange_rate
into l_inv_currency_code,
l_inv_rate_date,
l_inv_rate_type,
l_inv_exchange_rate
from pa_project_customers
where project_id = P_Project_Id
and customer_id = P_Customer_id ;
Update pa_draft_invoices_all
set inv_currency_code = l_invproc_currency_code
,inv_rate_type = NULL
,inv_rate_date = NULL
,inv_exchange_rate = NULL
,projfunc_invtrans_rate_type = NULL
,projfunc_invtrans_rate_date = NULL
,projfunc_invtrans_ex_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = amount
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set inv_currency_code = l_invproc_currency_code
,inv_rate_type = NULL
,inv_rate_date = NULL
,inv_exchange_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = amount
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = NULL
,projfunc_invtrans_rate_date = NULL
,projfunc_invtrans_ex_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = NULL
,projfunc_invtrans_rate_date = NULL
,projfunc_invtrans_ex_rate = NULL
,inv_currency_code = l_inv_currency_code
,inv_rate_type = NULL
,inv_rate_date = NULL
,inv_exchange_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = projfunc_bill_amount
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
/* Update the invoice header 's invoice currency code and
conversion attribute */
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DI ');
Update pa_draft_invoices_all
set inv_currency_code = l_inv_currency_code
,inv_rate_type = l_inv_rate_type
,inv_rate_date = l_inv_rate_date
,inv_exchange_rate = l_rate
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num ;
/* Update the line 's invoice currency amount*/
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII ');
update pa_draft_invoice_items
set inv_amount = l_inv_amt
,inv_rate_type = decode(CREDIT_PROCESs_FLAG,'X',inv_rate_type,l_inv_rate_type)
,inv_rate_date = decode(CREDIT_PROCESs_FLAG,'X',inv_rate_date,l_inv_rate_date)
,inv_exchange_rate = decode(CREDIT_PROCESs_FLAG,'X',inv_exchange_rate,l_rate)
where rowid = cur_get_std_lines.row_id; /** Bug 2324299 **/
/* Update the invoice header 's invoice currency code and
conversion attribute */
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DI ');
Update pa_draft_invoices_all
set inv_currency_code = l_inv_currency_code
,inv_rate_type = l_inv_rate_type
,inv_rate_date = l_inv_rate_date
,inv_exchange_rate = l_rate
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num ;
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII for Retention ');
update pa_draft_invoice_items
set inv_amount = l_inv_ret_amt
where rowid = cur_get_ret_lines.dii_rowid;
SELECT NVL(sum(amount * inv_exchange_rate),0)
INTO l_conv_rev_amt
FROM pa_draft_invoice_items
WHERE project_id = P_Project_Id
AND draft_invoice_num = P_Draft_Inv_Num
AND NVL(credit_process_flag ,'N') = 'X';
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII for Adjustment ');
Update PA_DRAFT_INVOICE_ITEMS dii
SET dii.Inv_amount = pa_currency.round_trans_currency_amt(
dii.Inv_amount - nvl(l_round_off_amt,0),l_inv_currency_code)
Where dii.project_id = P_Project_Id
and dii.Draft_Invoice_num = P_Draft_Inv_Num
and dii.Line_Num = l_max_line_num;
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII for net zero ');
update pa_draft_invoice_items
set inv_amount = 0
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num
and invoice_line_type = 'NET ZERO ADJUSTMENT';
SELECT NVL(sum(dii.inv_amount),0)/NVL(sum(dii.projfunc_bill_amount),0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND rownum=1;
SELECT sum(NVL(dii.projfunc_bill_amount,0))
INTO l_sum_projfunc_bill_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT sum(NVL(dii.inv_amount,0))
INTO l_sum_inv_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND rownum=1;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = 'User'
/* ,projfunc_invtrans_rate_date = sysdate commented for bug 3485407 and modified as follows ..*/
,projfunc_invtrans_rate_date = invoice_date /* for bug 3485407 */
,projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = l_projfunc_Exchg_Rate_type
,projfunc_invtrans_rate_date = DECODE(l_PFC_Exchg_Rate_Date_Code,
'PA_INVOICE_DATE', l_invoice_date, -- Fix for bug 3836514
l_projfunc_Exchg_Rate_Date)
,projfunc_invtrans_ex_rate = decode(l_projfunc_Exchg_Rate_type,'User',(1/l_Projfunc_Exchange_Rate),l_Rate)/* Added for bug 7575486*/
/*DECODE(l_PFC_Exchg_Rate_Date_Code, 'PA_INVOICE_DATE',
decode(l_projfunc_Exchg_Rate_type,'User',l_Projfunc_Exchange_Rate,l_Rate),
l_Projfunc_Exchange_Rate)Modified for Bug 7417980, commented for bug 7575486*/
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
-- implemented then in draft Invoices, update the Project Functional Invoice Transaction
-- exchange rate as the Project Functional Exchange Rate
-- otherwise update Project Functional Invoice Transaction exchange rate as the
-- invoice exchange rate i.e. derived rate
-- Modified this Update statement for fixing the bug 3693879
-- Commented the below condition for 3693879; when IPC=PFC and PFC <> ITC,
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = l_projfunc_Exchg_Rate_type
,projfunc_invtrans_rate_date = DECODE(l_PFC_Exchg_Rate_Date_Code,
'PA_INVOICE_DATE', l_invoice_date, -- Fix for Bug 3836514
l_projfunc_Exchg_Rate_Date)
,projfunc_invtrans_ex_rate = DECODE(l_PFC_Exchg_Rate_Date_Code, 'PA_INVOICE_DATE', l_Rate,
l_Projfunc_Exchange_Rate)
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = inv_rate_type
,projfunc_invtrans_rate_date = inv_rate_date
,projfunc_invtrans_ex_rate = inv_exchange_rate
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
select Inv_Currency_code,
Inv_rate_date,
Inv_rate_type,
Inv_exchange_rate
from pa_draft_invoices
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
select Projfunc_invtrans_rate_date,
Projfunc_invtrans_rate_type,
Projfunc_invtrans_ex_rate
from pa_draft_invoices
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
select PROJFUNC_CURRENCY_CODE
from PA_PROJECTS_ALL
where PROJECT_ID = P_Project_Id;
PROCEDURE Update_CRMemo_Invamt ( P_Project_Id IN NUMBER,
P_Draft_Inv_Num IN NUMBER,
P_Draft_Inv_Num_Credited IN NUMBER)
IS
g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
select sum(dii.amount),
sum(dii.inv_amount),
nvl(di.canceled_flag,'N'),max(dii.line_num) /*Added max line for bug 6501526*/
from pa_draft_invoice_items dii,
pa_draft_invoices di
where di.project_id = P_Project_Id
and di.draft_invoice_num = P_Draft_Inv_Num_Credited
and di.project_id = dii.project_id
and di.draft_invoice_num = dii.draft_invoice_num
group by nvl(di.canceled_flag,'N');
/* commented for bug 1633744 .. select modified as below this comment
select cmdii.amount amt,
pa_currency.round_trans_currency_amt(((l_tot_inv_amt/l_tot_amt)
*cmdii.amount),cmdi.inv_currency_code) line_amt,
cmdi.retention_percentage retper,
cmdi.inv_currency_code curcode,
cmdii.line_num line_num
from pa_draft_invoices cmdi,
pa_draft_invoice_items cmdii
where cmdi.project_id = P_Project_Id
and cmdi.draft_invoice_num = P_Draft_Inv_Num
and cmdi.project_id = cmdii.project_id
and cmdi.draft_invoice_num = cmdii.draft_invoice_num
and cmdii.invoice_line_type not in
('RETENTION','NET ZERO ADJUSTMENT')
for update of cmdii.amount
order by cmdii.line_num;
select cmdii.amount amt,
pa_currency.round_trans_currency_amt((cmdii.amount* cmdii1.inv_amount/cmdii1.amount ),cmdi.inv_currency_code) line_amt,
cmdi.retention_percentage retper,
cmdi.inv_currency_code curcode,
cmdii.line_num line_num
from pa_draft_invoices cmdi,
pa_draft_invoice_items cmdii,
pa_draft_invoice_items cmdii1
where cmdi.project_id = P_Project_Id
and cmdi.draft_invoice_num = P_Draft_Inv_Num
and cmdi.project_id = cmdii.project_id
and cmdi.draft_invoice_num = cmdii.draft_invoice_num
and cmdii.project_id = cmdii1.project_id
and cmdii1.draft_invoice_num = cmdi.draft_invoice_num_credited
and cmdii.draft_inv_line_num_credited = cmdii1.line_num
and cmdii1.invoice_line_type not in
('RETENTION','NET ZERO ADJUSTMENT')
for update of cmdii.inv_amount
order by cmdii.line_num;
select amount,
line_num
from pa_draft_invoice_items
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num
and invoice_line_type = 'RETENTION'
for update of amount;
select cmdii.amount amt,
pa_currency.round_trans_currency_amt((cmdii.amount* cmdii1.inv_amount/cmdii1.amount ),cmdi.inv_currency_code) line_amt,
cmdii.line_num line_num
from pa_draft_invoices cmdi,
pa_draft_invoice_items cmdii,
pa_draft_invoice_items cmdii1
where cmdi.project_id = P_Project_Id
and cmdi.draft_invoice_num = P_Draft_Inv_Num
and cmdi.project_id = cmdii.project_id
and cmdi.draft_invoice_num = cmdii.draft_invoice_num
and cmdii.project_id = cmdii1.project_id
and cmdii1.draft_invoice_num = cmdi.draft_invoice_num_credited
and cmdii.draft_inv_line_num_credited = cmdii1.line_num
and cmdii1.invoice_line_type = 'RETENTION'
for update of cmdii.inv_amount
order by cmdii.line_num;
PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Inside Update cr memo');
select PROJFUNC_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
INVPROC_CURRENCY_TYPE,
PROJFUNC_BIL_EXCHANGE_RATE, -- Added for bug 7575486
PROJFUNC_BIL_RATE_DATE_CODE,
PROJFUNC_BIL_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE,
ProjFunc_Attr_For_AR_Flag -- bug 7575486
into l_func_curr,
l_project_currency_code,
l_invproc_currency_type,
l_projfunc_Exchange_Rate, -- Added for bug 7575486
l_PFC_Exchg_Rate_Date_Code,
l_projfunc_Exchg_Rate_type,
l_projfunc_Exchg_Rate_Date,
l_ProjFunc_Attr_For_AR_Flag -- bug 7575486
from pa_projects_all
where project_id = P_Project_Id;
/* SELECT funding_currency_code
INTO l_funding_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum=1
GROUP BY funding_currency_code
HAVING sum(total_baselined_amount) <> 0; Commented the code for bug 3147272*/
/* added the following select statement for bug 3147272 */
SELECT funding_currency_code
INTO l_funding_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum=1
AND NVL(total_baselined_amount,0) > 0;
Update pa_draft_invoices_all
set inv_currency_code = l_invproc_currency_code
,inv_rate_type = NULL
,inv_rate_date = NULL
,inv_exchange_rate = NULL
,projfunc_invtrans_rate_type = NULL
,projfunc_invtrans_rate_date = NULL
,projfunc_invtrans_ex_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = amount
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set inv_currency_code = l_invproc_currency_code
,inv_rate_type = NULL
,inv_rate_date = NULL
,inv_exchange_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = amount
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = NULL
,projfunc_invtrans_rate_date = NULL
,projfunc_invtrans_ex_rate = NULL
,inv_currency_code = l_inv_currency_code
,inv_rate_type = NULL
,inv_rate_date = NULL
,inv_exchange_rate = NULL
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = projfunc_bill_amount
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoice_items
set inv_amount = cur_get_adjust_cm.line_amt
where current of get_adjust_cm;
update pa_draft_invoice_items
set inv_amount = l_ret_amt
where current of get_ret_amt;
update pa_draft_invoice_items dii
set inv_amount= cur_mult_ret_amt.line_amt
where current of get_mult_ret_amt;
update pa_draft_invoice_items
set inv_amount = inv_amount - l_round_off_amt
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num
and line_num = l_max_line_num
and draft_inv_line_num_credited = l_max_line; /*Added for bug 6501526*/
Update pa_draft_invoice_items dii
set dii.inv_amount = ( select (-1)*dii1.inv_amount
from pa_draft_invoice_items dii1
where dii1.project_id = dii.project_id
and dii1.draft_invoice_num = P_Draft_Inv_Num_credited
and dii1.line_num
= dii.draft_inv_line_num_credited)
where dii.project_id = P_Project_Id
and dii.draft_Invoice_Num = P_Draft_Inv_Num;
SELECT NVL(sum(dii.inv_amount),0)/NVL(sum(dii.amount),0)
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT NVL(dii.inv_amount,0)/NVL(dii.amount,0)
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
AND nvl(dii.amount,0)<>0
AND rownum=1 ;
SELECT sum(NVL(dii.projfunc_bill_amount,0))
INTO l_sum_projfunc_bill_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT sum(NVL(dii.inv_amount,0))
INTO l_sum_inv_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.amount,0))
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
having sum(nvl(dii.amount,0)) <> 0;
SELECT NVL(dii.inv_amount,0)/NVL(dii.amount,0)
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
AND nvl(dii.amount,0) <> 0
AND rownum=1;
SELECT di.inv_rate_type,di.inv_rate_date
INTO l_inv_rate_type,l_inv_rate_date
FROM pa_draft_invoices_all di,pa_draft_invoices_all cmdi
WHERE cmdi.draft_invoice_num_credited= di.draft_invoice_num
AND cmdi.project_id=di.project_id
AND cmdi.draft_invoice_num=P_Draft_Inv_Num
AND cmdi.project_id=P_Project_Id;
Update pa_draft_invoices_all
set inv_currency_code = l_inv_currency_code
-- ,inv_rate_type = 'User'
--,inv_rate_date = sysdate Should be picked from main invoice: Bug 2689348
-- code changed for bug 2689348
,inv_rate_type = l_inv_rate_type
,inv_rate_date = l_inv_rate_date
,inv_exchange_rate = l_rate
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
SELECT NVL(sum(dii.inv_amount),0)/NVL(sum(dii.projfunc_bill_amount),0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND rownum=1;
SELECT sum(NVL(dii.projfunc_bill_amount,0))
INTO l_sum_projfunc_bill_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT sum(NVL(dii.inv_amount,0))
INTO l_sum_inv_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num;
SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = P_Draft_Inv_Num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND rownum=1;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = 'User'
/* ,projfunc_invtrans_rate_date = sysdate commented for bug 3485407 and modified as follows ..*/
,projfunc_invtrans_rate_date = invoice_date /* for bug 3485407 */
,projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
select invoice_date
into l_invoice_date
from pa_draft_invoices_all
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = l_projfunc_Exchg_Rate_type
,projfunc_invtrans_rate_date = DECODE(l_PFC_Exchg_Rate_Date_Code,
'PA_INVOICE_DATE', l_invoice_date,
l_projfunc_Exchg_Rate_Date)
,projfunc_invtrans_ex_rate = decode(l_projfunc_Exchg_Rate_type,'User',(1/l_Projfunc_Exchange_Rate),l_Rate)
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
Update pa_draft_invoices_all
set projfunc_invtrans_rate_type = inv_rate_type
,projfunc_invtrans_rate_date = inv_rate_date
,projfunc_invtrans_ex_rate = inv_exchange_rate
-- DECODE(NVL(inv_exchange_rate,0),0,0, 1/NVL(inv_exchange_rate,0))
where project_id = P_Project_Id
and draft_invoice_num = P_Draft_Inv_Num;
END Update_CRMemo_Invamt;
| unapproved invoices and update the appropriate fields of invoice |
| Header and Details. This procedure is only called from PAIGEN. |
|__________________________________________________________________*/
Procedure Recalculate_Driver( P_Request_ID in number,
P_User_ID in number,
P_Project_ID in number,
p_calling_process IN VARCHAR2 DEFAULT 'PROJECT_INVOICES')
IS
g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
/* Cursor for Select All Unapproved invoices created in This Run */
/* Bug 5413168: skip invoices without any invoice line. generation_
error_flag not stamped yet for this case until paicnl */
CURSOR UNAPP_INV_CUR is
SELECT i.project_id,
i.draft_invoice_num,
decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
'CREDIT_MEMO') invoice_class,
agr.customer_id,
i.bill_through_date,
i.draft_invoice_num_credited
FROM pa_draft_invoices i,
pa_agreements_all agr
WHERE i.request_id = P_Request_ID
AND nvl(i.generation_error_flag, 'N') = 'N'
AND i.project_id+0 = P_Project_ID
AND i.agreement_id = agr.agreement_id
AND p_calling_process = 'PROJECT_INVOICES'
AND EXISTS (SELECT 1 from PA_DRAFT_INVOICE_ITEMS dii
WHERE dii.project_id = i.project_id
AND dii.draft_invoice_num = i.draft_invoice_num)
UNION
SELECT i.project_id,
i.draft_invoice_num,
decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
'CREDIT_MEMO') invoice_class,
agr.customer_id,
i.bill_through_date,
i.draft_invoice_num_credited
FROM pa_draft_invoices i,
pa_agreements_all agr
WHERE i.request_id = P_Request_ID
AND nvl(i.generation_error_flag, 'N') = 'N'
AND i.project_id+0 = P_Project_ID
AND i.agreement_id = agr.agreement_id
AND p_calling_process = 'RETENTION_INVOICES'
AND i.retention_invoice_flag ='Y'
AND EXISTS (SELECT 1 from PA_DRAFT_INVOICE_ITEMS dii
WHERE dii.project_id = i.project_id
AND dii.draft_invoice_num = i.draft_invoice_num);
PA_MCB_INVOICE_PKG.log_message ('recalculATE: ' || 'Insert distribution warning ');
Insert_Distrbution_Warning ( P_Project_ID =>l_Project_ID,
P_Draft_Invoice_Num =>l_draft_invoice_num,
P_User_ID =>P_User_ID,
P_Request_ID =>P_Request_ID,
P_Invoice_Set_ID =>NULL,
P_Lookup_Type =>'INVOICE_CURRENCY',
P_Error_Message_Code=>l_out_status);
/* Bug 2450414 - Inserted the Invoice Generation Error */
IF l_out_status = 'PA_NO_EXCH_RATE_EXISTS' OR l_out_status ='PA_CURR_NOT_VALID'
OR l_out_status = 'PA_USR_RATE_NOT_ALLOWED'
THEN
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message ('recalculATE: ' || 'Invoice Generation Error is set....'||l_out_status);
UPDATE pa_draft_invoices_all
SET generation_error_flag='Y',
transfer_rejection_reason= (SELECT meaning FROM pa_lookups
WHERE lookup_type='INVOICE_CURRENCY'
AND lookup_code=l_out_status)
WHERE project_id=l_Project_ID
AND draft_invoice_num=l_Draft_Invoice_Num;
PA_INVOICE_CURRENCY.Update_CRMemo_Invamt
(P_Project_Id =>l_Project_ID,
P_Draft_Inv_Num =>l_draft_invoice_num,
P_Draft_Inv_Num_Credited =>l_cr_inv_num );