[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(MAX(line_num),0) +1
INTO last_line_num
FROM pa_draft_invoice_items
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
net zero line num is cached. It is updated to its negative value */
FUNCTION Get_NetZero_Line(p_project_id IN NUMBER,
p_draft_invoice_num IN NUMBER) RETURN NUMBER IS
NetZero_line_num NUMBER;
SELECT line_num
INTO NetZero_line_num
FROM pa_draft_invoice_items
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND invoice_line_type = 'NET ZERO ADJUSTMENT';
Update pa_draft_invoice_items
set line_num = NetZero_line_num * (-1)
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND invoice_line_type = 'NET ZERO ADJUSTMENT';
CURSOR cur_inv_group_columns IS SELECT grp.column_code column_code,
fmtdet.text text,
fmtdet.start_position start_position,
fmtdet.end_position end_position,
NVL(fmtdet.right_justify_flag,'N') right_justify_flag
FROM pa_invoice_group_columns grp,
pa_invoice_formats fmt,
pa_invoice_format_details fmtdet,
pa_projects_all pr
WHERE pr.retention_invoice_format_id = fmt.invoice_format_id
AND fmt.invoice_format_id = fmtdet.invoice_format_id
AND grp.invoice_group_column_id = fmtdet.invoice_group_column_id
and pr.project_id =p_project_id
ORDER BY fmtdet.start_position;
/*PROCEDURE Update_Retention_Balances( p_retention_rule_id IN NUMBER DEFAULT NULL, bug 2681003,
removed the default values to ensure GSCC complaince */
PROCEDURE Update_Retention_Balances( p_retention_rule_id IN NUMBER ,
p_project_id IN NUMBER ,
/*p_task_id IN NUMBER DEFAULT NULL,
removed the default values to ensure GSCC complaince */
p_task_id IN NUMBER ,
p_agreement_id IN NUMBER,
p_customer_id IN NUMBER,
p_amount IN NUMBER,
p_change_type IN VARCHAR2,
p_request_id IN NUMBER ,
p_invproc_currency IN VARCHAR2,
p_project_currency IN VARCHAR2,
p_project_amount IN NUMBER,
p_projfunc_currency IN VARCHAR2,
p_projfunc_amount IN NUMBER,
p_funding_currency IN VARCHAR2,
p_funding_amount IN NUMBER) IS
TmpFlag VARCHAR2(1):='N';
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
pa_retention_util.write_log('Entering pa_retention_pkg.Update_Retention_Balances');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Retained Amount');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Retained Amount');
-- Before update the retained amount, make sure there is a record for this agreement
-- , rule, project id.
BEGIN
SELECT 'Y' INTO TmpFlag
FROM pa_project_retentions
WHERE project_id = p_project_id
AND retention_rule_id = p_retention_rule_id
AND agreement_id = p_agreement_id
AND NVL(task_id,-99) = NVL(p_task_id,-99);
SELECT pa_project_retentions_s.NEXTVAL
INTO l_project_retn_id
FROM DUAL;
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert NEW Record Project Retentions');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
INSERT INTO pa_project_retentions
( PROJECT_RETENTION_ID,
PROJECT_ID,
TASK_ID,
AGREEMENT_ID,
RETENTION_RULE_ID,
INVPROC_CURRENCY_CODE,
TOTAL_RETAINED,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_TOTAL_RETAINED,
PROJECT_CURRENCY_CODE,
PROJECT_TOTAL_RETAINED,
FUNDING_CURRENCY_CODE,
FUNDING_TOTAL_RETAINED,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(l_project_retn_id,
p_project_id,
p_task_id,
p_agreement_id,
p_retention_rule_id,
p_invproc_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_amount, p_invproc_currency),
p_projfunc_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_projfunc_amount,p_projfunc_currency),
p_project_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_project_amount,p_project_currency),
p_funding_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_funding_amount,p_funding_currency),
l_program_application_id,
l_program_update_date,
p_request_id,
sysdate,
l_last_updated_by,
l_last_update_date,
l_last_updated_by);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Project Retentions');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
UPDATE pa_project_retentions
SET total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(total_retained,0) + NVL(p_amount,0), invproc_currency_code),
project_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(project_total_retained,0) + NVL(p_project_amount,0),project_currency_code),
projfunc_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(projfunc_total_retained,0) + NVL(p_projfunc_amount,0),projfunc_currency_code),
funding_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(funding_total_retained,0) + NVL(p_funding_amount,0), funding_currency_code)
WHERE project_id = p_project_id
AND agreement_id = p_agreement_id
AND retention_rule_id = p_retention_rule_id;
-- Update the project,agreement and task level balance
BEGIN
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
SELECT 'Y' INTO TmpFlag
FROM pa_summary_project_retn
WHERE project_id = p_project_id
AND nvl(task_id,-99) = NVL(p_task_id,-99)
AND agreement_id = p_agreement_id;
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert New Record SPR ');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
INSERT INTO pa_summary_project_retn
(
PROJECT_ID,
TASK_ID,
AGREEMENT_ID,
CUSTOMER_ID,
INVPROC_CURRENCY_CODE,
TOTAL_RETAINED,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_TOTAL_RETAINED,
PROJECT_CURRENCY_CODE,
PROJECT_TOTAL_RETAINED,
FUNDING_CURRENCY_CODE,
FUNDING_TOTAL_RETAINED,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(
p_project_id,
p_task_id,
p_agreement_id,
p_customer_id,
p_invproc_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_amount,p_invproc_currency),
p_projfunc_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_projfunc_amount,p_projfunc_currency),
p_project_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_project_amount,p_project_currency),
p_funding_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_funding_amount, p_funding_currency),
l_program_application_id,
l_program_update_date,
p_request_id,
sysdate,
l_last_updated_by,
l_last_update_date,
l_last_updated_by);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
UPDATE pa_summary_project_retn
SET total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(total_retained,0) + NVL(p_amount,0), invproc_currency_code),
project_total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(project_total_retained,0) + NVL(p_project_amount,0),project_currency_code),
projfunc_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(projfunc_total_retained,0) + NVL(p_projfunc_amount,0),projfunc_currency_code),
funding_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(funding_total_retained,0) + NVL(p_funding_amount,0),funding_currency_code)
WHERE project_id = p_project_id
AND NVL(task_id,-99) = NVL(p_task_id,-99)
AND agreement_id = p_agreement_id;
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Rule level Balance ');
-- Update the rule level balance
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
UPDATE pa_proj_retn_rules
SET total_retained = NVL(total_retained,0) +
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( NVL(p_amount,0),p_invproc_currency),
projfunc_total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(projfunc_total_retained,0) + NVL(p_projfunc_amount,0), p_projfunc_currency),
project_total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
NVL(project_total_retained,0) + NVL(p_project_amount,0), p_project_currency)
WHERE retention_rule_id = p_retention_rule_id;
-- Update project or top task, agreement level
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
UPDATE pa_summary_project_retn
SET total_billed = NVL(total_billed,0) +
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( NVL(p_amount,0),p_invproc_currency),
project_total_billed = NVL(project_total_billed,0) +
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( NVL(p_project_amount,0),p_project_currency),
projfunc_total_billed = NVL(projfunc_total_billed,0) +
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(NVL(p_projfunc_amount,0),p_projfunc_currency),
funding_total_billed = NVL(funding_total_billed,0) +
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(NVL(p_funding_amount,0), p_funding_currency)
WHERE project_id = p_project_id
AND NVL(task_id,-99) = NVL(p_task_id,-99)
AND agreement_id = p_agreement_id;
pa_retention_util.write_log('No of Records are Updated : ' || sql%rowcount);
it should not be deleted
Delete the 0 Amount Summary Retn Records and project_retention records
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_project_retentions ');
DELETE FROM pa_project_retentions
WHERE project_id = p_project_id
AND NVL(task_id,-99) = NVL(p_task_id,-99)
AND agreement_id = p_agreement_id
AND retention_rule_id = p_retention_rule_id
AND NVL(total_retained,0) = 0
AND NVL(project_total_retained,0) = 0
AND NVL(projfunc_total_retained,0) = 0
AND NVL(funding_total_retained,0) = 0;
pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_summary_project_retn ');
DELETE FROM pa_summary_project_retn
WHERE project_id = p_project_id
AND NVL(task_id,-99) = NVL(p_task_id,-99)
AND agreement_id = p_agreement_id
AND NVL(total_retained,0) = 0
AND NVL(project_total_retained,0) = 0
AND NVL(projfunc_total_retained,0) = 0
AND NVL(funding_total_retained,0) = 0
AND NVL(total_billed,0) = 0
AND NVL(project_total_billed,0) = 0
AND NVL(projfunc_total_billed,0) = 0
AND NVL(funding_total_billed,0) = 0;
pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Leaving from Update Retention Balances ');
pa_retention_util.write_log('Update_Retention_Balances: ' || 'Oracle Error ' || sqlerrm);
END Update_Retention_Balances;
PROCEDURE Update_Retn_Bill_Trans_Amount(p_project_id IN NUMBER,
p_draft_invoice_num IN NUMBER,
p_bill_trans_currency IN VARCHAR2,
p_request_id IN NUMBER) IS
BEGIN
UPDATE pa_draft_invoice_items
SET bill_trans_currency_code = p_bill_trans_currency,
inv_amount =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(bill_trans_bill_amount, trim(p_bill_trans_currency)),
request_id = p_request_id
WHERE DRAFT_invoice_num = p_draft_invoice_num
AND invoice_line_type = 'RETENTION'
AND project_id = p_project_id;
pa_retention_util.write_log('Update_Retn_Bill_Trans_Amount: ' || 'Oracle Error ' || sqlerrm);
END Update_Retn_Bill_Trans_Amount;
LastUpdatedBy NUMBER:= fnd_global.user_id;
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
UpdateRDL BOOLEAN := FALSE;
UpdateERDL BOOLEAN := FALSE;
UpdateDII BOOLEAN := FALSE;
SELECT AMT.retention_rule_id retention_rule_id,
AMT.source_type source_type,
AMT.invoice_amount invoice_amount,
AMT.pfc_invoice_amount pfc_invoice_amount,
AMT.pc_invoice_amount pc_invoice_amount,
AMT.fc_invoice_amount fc_invoice_amount,
AMT.btc_invoice_amount btc_invoice_amount
FROM ( SELECT rdl.retention_rule_id retention_rule_id,'RDL' source_type,
SUM(rdl.bill_amount) invoice_amount,
SUM(rdl.projfunc_bill_amount) pfc_invoice_amount,
SUM(rdl.project_bill_amount) pc_invoice_amount,
SUM(rdl.funding_bill_amount) fc_invoice_amount,
SUM(rdl.bill_trans_bill_amount) btc_invoice_amount
FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = p_draft_invoice_num
GROUP BY rdl.retention_rule_id
UNION
SELECT erdl.retention_rule_id retention_rule_id,
'ERDL' source_type,
--SUM(erdl.amount) invoice_amount, --Modified for Bug3604143
decode(InvProcCurrType, 'PROJECT_CURRENCY', SUM(erdl.project_bill_amount),
'PROJFUNC_CURRENCY', SUM(erdl.projfunc_bill_amount),
'FUNDING_CURRENCY', SUM(erdl.funding_bill_amount)) invoice_amount,
SUM(erdl.projfunc_bill_amount) pfc_invoice_amount,
SUM(erdl.project_bill_amount) pc_invoice_amount,
SUM(erdl.funding_bill_amount) fc_invoice_amount,
SUM(erdl.bill_trans_amount) btc_invoice_amount
FROM pa_cust_event_rdl_all erdl
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = p_draft_invoice_num
GROUP BY erdl.retention_rule_id
UNION
SELECT dii.retention_rule_id retention_rule_id,
'EVENT' source_type,
SUM(dii.amount) invoice_amount,
SUM(dii.projfunc_bill_amount) pfc_invoice_amount,
SUM(dii.project_bill_amount) pc_invoice_amount,
SUM(dii.funding_bill_amount) fc_invoice_amount,
SUM(dii.bill_trans_bill_amount) btc_invoice_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = p_draft_invoice_num
AND dii.event_num IS NOT NULL
GROUP BY dii.retention_rule_id ) AMT,
PA_PROJ_RETN_RULES RT
where amt.retention_rule_id = RT.retention_rule_id
ORDER BY RT.task_id, RT.expenditure_category, RT.expenditure_type, RT.NON_LABOR_RESOURCE,
RT.REVENUE_CATEGORY_CODE, RT.EVENT_TYPE, RT.EFFECTIVE_START_DATE, RT.EFFECTIVE_END_DATE;
SELECT rt.retention_rule_id retention_rule_id,
rt.retention_percentage retention_percentage,
rt.retention_amount retention_amount,
rt.threshold_amount threshold_amount ,
rt.total_retained total_retained,
rt.retention_level_code retention_level_code,
rt.non_labor_resource non_labor_resource,
rt.expenditure_type expenditure_type,
rt.expenditure_category expenditure_category,
rt.event_type event_type,
rt.revenue_category_code revenue_category_code,
rt.effective_start_date effective_start_date,
rt.effective_end_date effective_end_date ,
tsk.task_number task_number,
tsk.task_name task_name,
rt.task_id task_id
FROM pa_proj_retn_rules rt, pa_tasks tsk
WHERE rt.retention_rule_id = RetentionRuleID
AND rt.task_id = tsk.task_id(+);
-- Select the Funding Currency
SELECT agreement_currency_code
INTO FundingCurrency
FROM pa_agreements_all agr,pa_draft_invoices_all di
WHERE agr.agreement_id = di.agreement_id
AND di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num;
SELECT pr.project_currency_code,
pr.projfunc_currency_code,
decode(pr.invproc_currency_type,'PROJECT_CURRENCY', pr.project_currency_code,
'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
'FUNDING_CURRENCY', FundingCurrency) Invproc_currency, pr.invproc_currency_type
INTO ProjectCurrency, ProjFuncCUrrency, InvProcCurrency, InvProcCurrType
FROM pa_projects_all pr
WHERE pr.project_id = p_project_id;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Select Funding Currency ');
-- Select the Funding Currency
SELECT agreement_currency_code
INTO FundingCurrency
FROM pa_agreements_all agr,pa_draft_invoices_all di
WHERE agr.agreement_id = di.agreement_id
AND di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num;
SELECT pr.project_currency_code,
pr.projfunc_currency_code,
decode(pr.invproc_currency_type,'PROJECT_CURRENCY', pr.project_currency_code,
'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
'FUNDING_CURRENCY', FundingCurrency) Invproc_currency
INTO ProjectCurrency, ProjFuncCUrrency, InvProcCurrency
FROM pa_projects_all pr
WHERE pr.project_id = p_project_id;
UpdateRDL := TRUE;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateRDL= True');
UpdateERDL := TRUE;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateERDL= True');
UpdateDII := TRUE;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateDII= True');
UpdateRDL := TRUE;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateRDL= True');
UpdateERDL := TRUE;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateERDL= True');
UpdateDII := TRUE;
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateDII= True');
SELECT meaning
INTO l_revenue_category_meaning
FROM pa_lookups
WHERE lookup_code =retn_rule_rec.revenue_category_code
AND lookup_type = 'REVENUE CATEGORY';
SELECT meaning
INTO l_revenue_category_meaning1
FROM pa_lookups
WHERE lookup_code =retn_rule_rec.revenue_category_code
AND lookup_type = 'REVENUE CATEGORY';
-- Insert a Retention Line
INSERT INTO pa_draft_invoice_items
( PROJECT_ID,
DRAFT_INVOICE_NUM,
LINE_NUM,
AMOUNT,
TEXT,
INVOICE_LINE_TYPE,
TASK_ID,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_BILL_AMOUNT,
PROJECT_CURRENCY_CODE,
PROJECT_BILL_AMOUNT,
FUNDING_CURRENCY_CODE,
FUNDING_BILL_AMOUNT,
BILL_TRANS_BILL_AMOUNT,
INVPROC_CURRENCY_CODE,
RETENTION_RULE_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
/* Bug 3087998 Code and number order is different in values list. Changing here to match the same
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER
*/
OUTPUT_TAX_EXEMPT_NUMBER,
OUTPUT_TAX_EXEMPT_REASON_CODE
)
VALUES (p_project_id,
p_draft_invoice_num,
LastLineNum,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
(-1) * CurRetainAmount, InvProcCurrency),
SUBSTR(RetnLineText,1,240),
'RETENTION',
retn_rule_rec.task_id,
ProjFuncCUrrency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
(-1) * PFCCurRetainAmount,ProjfuncCurrency),
ProjectCurrency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
(-1) * PCCurRetainAmount,ProjectCurrency),
FundingCurrency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
(-1) * FCCurRetainAmount,FundingCurrency),
(-1) * BTCCurRetainAmount,
InvProcCurrency,
RetentionRuleId,
l_last_update_login,
sysdate,
l_last_updated_by,
sysdate,
l_created_by,
p_request_id,
l_program_application_id,
l_program_id,
sysdate,
p_Output_tax_code,
p_Output_tax_exempt_flag,
p_Output_tax_exempt_number,
p_Output_exempt_reason_code);
pa_retention_util.write_log('Rows Inserted : '||sql%rowcount);
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Call Update_Retention_Balances');
Update_Retention_Balances(p_retention_rule_id=>retn_rule_rec.retention_rule_id,
p_project_id =>p_project_id,
p_task_id =>retn_rule_rec.task_id,
p_agreement_id =>AgreementId,
p_customer_id =>p_customer_id,
p_amount =>CurRetainAmount,
p_change_type =>'RETAINED',
p_request_id =>p_request_id,
p_invproc_currency =>InvProcCurrency,
p_project_currency =>ProjectCurrency,
p_project_amount => PCCurRetainAmount,
p_projfunc_currency =>ProjFuncCurrency,
p_projfunc_amount =>PFCCurRetainAmount,
p_funding_currency =>FundingCurrency,
p_funding_amount =>FCCurRetainAmount);
-- UPDATE RDL
IF (UpdateRDL) THEN
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update RDLs ');
UPDATE pa_cust_rev_dist_lines_all
SET retn_draft_invoice_num = p_draft_invoice_num,
retn_draft_invoice_line_num = LastLineNum,
retained_amount =
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
((NVL(bill_amount,0)/NVL(InvoiceAmount,0))
* NVL(CurRetainAmount,0)),invproc_currency_code)
WHERE retention_rule_id = retn_rule_rec.retention_rule_id
AND draft_invoice_num = p_draft_invoice_num
AND request_id = p_request_id
AND project_id = p_project_id;
-- Update ERDL
IF (UpdateERDL) THEN
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update ERDLs ');
UPDATE pa_cust_event_rdl_all
SET retn_draft_invoice_num = p_draft_invoice_num,
retn_draft_invoice_line_num = LastLineNum,
retained_amount =
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
((NVL(amount,0)/NVL(InvoiceAmount,0))
* NVL(CurRetainAmount,0)),invproc_currency_code)
WHERE retention_rule_id = retn_rule_rec.retention_rule_id
AND draft_invoice_num = p_draft_invoice_num
AND request_id = p_request_id
AND project_id = p_project_id;
-- Update DII
IF (UpdateDII) THEN
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update DII ');
UPDATE pa_draft_invoice_items
SET retn_draft_invoice_num = p_draft_invoice_num,
retn_draft_invoice_line_num = LastLineNum,
retained_amount =
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
((NVL(amount,0)/NVL(InvoiceAmount,0))
* NVL(CurRetainAmount,0)), invproc_currency_code)
WHERE retention_rule_id = retn_rule_rec.retention_rule_id
AND draft_invoice_num = p_draft_invoice_num
AND request_id = p_request_id
AND project_id = p_project_id
AND invoice_line_type <> 'RETENTION';
UpdateRDL := FALSE;
UpdateERDL := FALSE;
UpdateDII := FALSE;
UpdateRDL := TRUE;
UpdateERDL:= TRUE;
UpdateDII := TRUE;
CURSOR cur_proj_cust IS SELECT pc.project_id project_id,
pc.customer_id customer_id,
pc.retention_level_code retention_level,
pc.bill_to_address_id bill_to_address_id,
pc.ship_to_address_id ship_to_address_id,
imp.set_of_books_id set_of_books_id,
pc.bill_to_customer_id bill_to_customer_id,
pc.ship_to_customer_id ship_to_customer_id,/*Added for customer account relation*/
/* TCA changes
ras.site_use_id bill_site_use_id,
ras1.site_use_id ship_site_use_id
*/
hz_site.site_use_id bill_site_use_id,
hz_site1.site_use_id ship_site_use_id
FROM pa_project_customers pc,
pa_projects_all pr,
pa_implementations_all imp,
/* TCA changes
ra_site_uses ras,
ra_site_uses ras1
*/
hz_cust_site_uses hz_site,
hz_cust_site_uses hz_site1
WHERE EXISTS (SELECT NULL
FROM pa_draft_invoices_all di,
pa_agreements_all agr, pa_proj_retn_rules rt
WHERE di.project_id = p_project_id
AND di.request_id = p_request_id
AND di.agreement_id = agr.agreement_id
AND agr.customer_id = pc.customer_id
AND rt.project_id = pc.project_id
AND rt.customer_id = pc.customer_id
AND ( (NVL(rt.threshold_amount,0) - NVL(rt.total_retained,0)) > 0
OR NVL(threshold_amount,0) =0) )
AND pc.project_id = p_project_id
AND pc.project_id = pr.project_id
/* Shared services changes: removed NVL from the org_id join.*/
AND pr.org_id = imp.org_id
/*Added for bug 2938422*/
/* TCA changes
and ras.address_id = pc.bill_to_address_id
and ras.site_use_code = 'BILL_TO'
and ras.status = 'A'
and ras1.address_id = pc.ship_to_address_id
and ras1.site_use_code = 'SHIP_TO'
and ras1.status = 'A';
CURSOR cur_proj_inv IS SELECT di.project_id,
di.draft_invoice_num,
agr.agreement_id agreement_id,
NVL(pr.inv_by_bill_trans_curr_flag,'N') inv_by_bill_trans_curr_flag,
di.inv_currency_code invoice_currency_code
FROM pa_draft_invoices_all di, pa_agreements_all agr,
pa_projects_all pr
WHERE NVL(di.canceled_flag,'N') ='N'
AND NVL(di.cancel_credit_memo_flag ,'N') ='N'
AND NVL(di.draft_invoice_num_credited,0) = 0
AND di.request_id = p_request_id
AND di.project_id = p_project_id
AND agr.agreement_id = di.agreement_id
AND agr.customer_id = ProjCustRec.customer_id
and di.project_id = pr.project_id;
-- 1. Update the Non Labor Resource Override in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='NON_LABOR'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND ei.non_labor_resource = rt.non_labor_resource
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND exists (select null FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='NON_LABOR'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND ei.non_labor_resource = rt.non_labor_resource
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1)) ;
pa_retention_util.write_log('RDL: No Records Update At Non Labor Level ' ||sql%rowcount);
-- 2. Update the Expenditure Type Level Override in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.expenditure_type = ei.expenditure_type
AND rt.retention_level_code='EXPENDITURE_TYPE'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND rdl.retention_rule_id IS NULL
AND EXISTS( select null
FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.expenditure_type = ei.expenditure_type
AND rt.retention_level_code='EXPENDITURE_TYPE'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
pa_retention_util.write_log('RDL: No Records Update At Expenditure Type Level ' ||sql%rowcount);
-- 3. Update the Expenditure Category Level Override in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99) )
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_expenditure_types et
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
-- AND rt.expenditure_type = ei.expenditure_type
AND ei.expenditure_type = et.expenditure_type
AND et.expenditure_category = rt.expenditure_category
AND rt.retention_level_code='EXPENDITURE_CATEGORY'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date +1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND rdl.retention_rule_id IS NULL
AND EXISTS (SELECT NULL
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_expenditure_types et
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
-- AND rt.expenditure_type = ei.expenditure_type
AND ei.expenditure_type = et.expenditure_type
AND et.expenditure_category = rt.expenditure_category
AND rt.retention_level_code='EXPENDITURE_CATEGORY'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date +1));
pa_retention_util.write_log('RDL: No Records Update At Expenditure Category Level ' ||sql%rowcount);
-- 4. Update the Project Level Retention Setup in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='PROJECT'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND rdl.retention_rule_id IS NULL
AND EXISTS( SELECT NULL
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='PROJECT'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date));
pa_retention_util.write_log('RDL: No Records Update At Project Level ' ||sql%rowcount);
-- 1. Update the Event Type Level Retention Setup in ERDLs
UPDATE pa_cust_event_rdl_all erdl
SET erdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99) )
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date, evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code)
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND erdl.retention_rule_id IS NULL
AND EXISTS(SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date, evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code);
pa_retention_util.write_log('ERDL: No Records Update At Event Type Level ' ||sql%rowcount);
-- 2. Update the Revenue Category Level Retention Setup in ERDLs
UPDATE pa_cust_event_rdl_all erdl
SET erdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) -NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code)
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND erdl.retention_rule_id IS NULL
AND EXISTS (SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code);
pa_retention_util.write_log('ERDL: No Records Update At Revenue Category Level ' ||sql%rowcount);
-- 3. Update the Project Level Retention Setup in ERDLs
UPDATE pa_cust_event_rdl_all erdl
SET erdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='PROJECT'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
AND TRUNC(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1))
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND erdl.retention_rule_id IS NULL
AND EXISTS(SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='PROJECT'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1));
pa_retention_util.write_log('ERDL: No Records Update At Project Level ' ||sql%rowcount);
-- 1. Update the Event Type Level Retention Setup in DII only for EVENTS
/* Bug 3258414: The update statements below are done only for Events of type MANUAL or AUTOMATIC.
The WRITE ON events are already updated using the ERDL table above. */
UPDATE pa_draft_invoice_items dii
SET dii.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num is NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
AND dii.event_num IS NOT NULL
AND dii.retention_rule_id IS NULL
AND EXISTS( SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date +1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num is NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
pa_retention_util.write_log('DII: No Records Update At Event Type Level ' ||sql%rowcount);
-- 2. Update the Revenue Category Level Retention Setup in DII for events
UPDATE pa_draft_invoice_items dii
SET dii.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num IS NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
AND dii.event_num IS NOT NULL
AND dii.retention_rule_id IS NULL
AND exists (SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num IS NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
pa_retention_util.write_log('DII: No Records Update At Revenue Category Level ' ||sql%rowcount);
-- 3. Update the Project Level Retention Setup in DIIs for Events
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'PROJECT Defaults Level ');
UPDATE pa_draft_invoice_items dii
SET dii.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt ,
pa_event_types evttyp /* Bug 3258414 */
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='PROJECT'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND dii.event_num IS NOT NULL
AND evt.event_type = evttyp.event_type /* Bug 3258414 */
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
AND dii.event_num IS NOT NULL
AND dii.retention_rule_id IS NULL
AND EXISTS(SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp /* Bug 3258414 */
WHERE rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='PROJECT'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND dii.event_num IS NOT NULL
AND evt.event_type = evttyp.event_type /* Bug 3258414 */
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
pa_retention_util.write_log('DII: No Records Update At Project Level ' ||sql%rowcount);
-- 1. Update the Non Labor Resource Override in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='NON_LABOR'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND ei.non_labor_resource = rt.non_labor_resource
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND EXISTS(
SELECT NULL
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='NON_LABOR'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND ei.non_labor_resource = rt.non_labor_resource
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
pa_retention_util.write_log('RDL: No Records Update At Non Labor Level ' ||sql%rowcount);
-- 2. Update the Expenditure Type Level Override in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.expenditure_type = ei.expenditure_type
AND rt.retention_level_code='EXPENDITURE_TYPE'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND rdl.retention_rule_id IS NULL
AND EXISTS(
SELECT NULL
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.expenditure_type = ei.expenditure_type
AND rt.retention_level_code='EXPENDITURE_TYPE'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
pa_retention_util.write_log('RDL: No Records Update At Expenditure Type Level ' ||sql%rowcount);
-- 3. Update the Expenditure Category Level Override in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_expenditure_types et,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
-- AND rt.expenditure_type = ei.expenditure_type
AND ei.expenditure_type = et.expenditure_type
AND et.expenditure_category = rt.expenditure_category
AND rt.retention_level_code='EXPENDITURE_CATEGORY'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND rdl.retention_rule_id IS NULL
AND EXISTS(
SELECT NULL
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_expenditure_types et,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
-- AND rt.expenditure_type = ei.expenditure_type
AND ei.expenditure_type = et.expenditure_type
AND et.expenditure_category = rt.expenditure_category
AND rt.retention_level_code='EXPENDITURE_CATEGORY'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
pa_retention_util.write_log('RDL: No Records Update At Expenditure Category Level ' ||sql%rowcount);
-- 4. Update the Project Level Retention Setup in RDLs
UPDATE pa_cust_rev_dist_lines_all rdl
SET rdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) -NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='TOP_TASK'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
WHERE rdl.project_id = p_project_id
AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND rdl.retention_rule_id IS NULL
AND EXISTS(SELECT NULL
FROM pa_proj_retn_rules rt,
pa_expenditure_items_all ei,
pa_tasks tsk
WHERE tsk.task_id = ei.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='TOP_TASK'
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND trunc(ei.expenditure_item_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
pa_retention_util.write_log('RDL: No Records Update At Project Level ' ||sql%rowcount);
-- 1. Update the Event Type Level Retention Setup in ERDLs
UPDATE pa_cust_event_rdl_all erdl
SET erdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and nvl(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code)
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND erdl.retention_rule_id IS NULL
AND EXISTS
(SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and nvl(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code);
pa_retention_util.write_log('ERDL: No Records Update At Event Type Level ' ||sql%rowcount);
-- 2. Update the Revenue Category Level Retention Setup in ERDLs
UPDATE pa_cust_event_rdl_all erdl
SET erdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code)
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND erdl.retention_rule_id IS NULL
AND EXISTS (SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
AND evttyp.revenue_category_code= rt.revenue_category_code);
pa_retention_util.write_log('ERDL: No Records Update At Revenue Category Level ' ||sql%rowcount);
-- 3. Update the Top Task Level Retention Setup in ERDLs
UPDATE pa_cust_event_rdl_all erdl
SET erdl.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='TOP_TASK'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1))
WHERE erdl.project_id = p_project_id
AND erdl.request_id = p_request_id
AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
AND erdl.retention_rule_id IS NULL
AND EXISTS( SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='TOP_TASK'
AND erdl.event_num = evt.event_num
AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
AND erdl.project_id = evt.project_id
AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1));
pa_retention_util.write_log('ERDL: No Records Update At Project Level ' ||sql%rowcount);
-- 1. Update the Event Type Level Retention Setup in DII only for EVENTS
UPDATE pa_draft_invoice_items dii
SET dii.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num is NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
AND dii.event_num IS NOT NULL
AND dii.retention_rule_id IS NULL
AND EXISTS( SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='EVENT_TYPE'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evt.event_type = rt.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num is NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
;
pa_retention_util.write_log('DII: No Records Update At Event Type Level ' ||sql%rowcount);
-- 2. Update the Revenue Category Level Retention Setup in DII for events
UPDATE pa_draft_invoice_items dii
SET dii.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num IS NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
AND dii.event_num IS NOT NULL
AND dii.retention_rule_id IS NULL
AND EXISTS( SELECT NULL
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp,
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='REVENUE_CATEGORY'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
AND evt.event_type = evttyp.event_type
AND evttyp.revenue_category_code= rt.revenue_category_code
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.event_num IS NOT NULL
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
pa_retention_util.write_log('DII: No Records Update At Revenue Category Level ' ||sql%rowcount);
-- 3. Update the Top Task Level Retention Setup in DIIs for Events
UPDATE pa_draft_invoice_items dii
SET dii.retention_rule_id=
( SELECT DECODE(NVL(rt.threshold_amount,0),0,
rt.retention_rule_id,
DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
-1,rt.retention_rule_id,-99))
FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp, /* Bug 3258414 */
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='TOP_TASK'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date +1)
AND dii.event_num IS NOT NULL
AND evttyp.event_type=evt.event_type /* Bug 3258414 */
/* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
WHERE dii.project_id = p_project_id
AND dii.request_id = p_request_id
AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
AND dii.event_num IS NOT NULL
AND dii.retention_rule_id IS NULL
AND EXISTS
(SELECT NULL FROM pa_proj_retn_rules rt,
pa_events evt,
pa_event_types evttyp, /* Bug 3258414 */
pa_tasks tsk
WHERE tsk.task_id = evt.task_id
AND tsk.top_task_id = rt.task_id
AND rt.customer_id = ProjCustRec.customer_id
AND rt.project_id = p_project_id
AND rt.retention_level_code='TOP_TASK'
AND dii.event_num = evt.event_num
AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
AND dii.project_id = evt.project_id
AND trunc(evt.completion_date) BETWEEN
rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date +1)
AND dii.event_num IS NOT NULL
AND evttyp.event_type=evt.event_type /* Bug 3258414 */
/* AND evttyp.event_type_classification in ('MANUAL','AUTOMATIC') Bug 3258414 - changed for bug 3478802*/
AND evttyp.event_type_classification <> 'WRITE ON'
AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
pa_retention_util.write_log('DII: No Records Update At Project Level ' ||sql%rowcount);
After retention lines are generated , last line num is updated onto this cached line */
NetZeroLineNum:= Get_NetZero_Line(p_project_id => p_project_id,
p_draft_invoice_num=>draftinvoicenum);
/* Update net zero line to the last line after retention lines */
UPDATE PA_DRAFT_INVOICE_ITEMS
SET LINE_NUM = LastLineNum
WHERE PROJECT_ID = p_project_id
AND DRAFT_INVOICE_NUM = draftinvoicenum
AND LINE_NUM = NetZeroLineNum * (-1);
UPDATE PA_CUST_REV_DIST_LINES
SET DRAFT_INVOICE_ITEM_LINE_NUM = LastLineNum
WHERE PROJECT_ID = p_project_id
AND DRAFT_INVOICE_NUM = draftinvoicenum
AND DRAFT_INVOICE_ITEM_LINE_NUM = NetZeroLineNum;
UPDATE PA_CUST_EVENT_REV_DIST_LINES
SET DRAFT_INVOICE_ITEM_LINE_NUM = LastLineNum
WHERE PROJECT_ID = p_project_id
AND DRAFT_INVOICE_NUM = draftinvoicenum
AND DRAFT_INVOICE_ITEM_LINE_NUM = NetZeroLineNum;
pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Call Update_Retn_Bill_Trans_Amount ');
Update_Retn_Bill_Trans_Amount(p_project_id=>p_project_id,
p_draft_invoice_num =>DraftInvoiceNum,
p_bill_trans_currency =>ProjInvRec.invoice_currency_code,
p_request_id =>p_request_id);
LastUpdatedBy NUMBER:= fnd_global.user_id;
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
/* Select all the creditmemo invoices */
CURSOR cur_cm_invoice IS
SELECT draft_invoice_num credit_invoice_num,
draft_invoice_num_credited org_invoice_num
FROM pa_draft_invoices_all
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num_credited IS NOT NULL
ORDER BY draft_invoice_num;
SELECT NVL(rdl.retention_rule_id,-1) retention_rule_id,
SUM(DECODE(NVL(rdl.retention_rule_id,-1) ,-1,
NVL(rdl.bill_amount,0) ,NVL(rdl.retained_amount,0) ) ) invoice_amount
FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = p_project_id
-- AND rdl.request_id = p_request_id
AND rdl.draft_invoice_num = l_credit_invoice_num
GROUP BY NVL(rdl.retention_rule_id,-1); */
SELECT retention_rule_id, sum(invoice_amount) invoice_amount
FROM
(SELECT NVL(rdl.retention_rule_id,-1) retention_rule_id,
SUM(DECODE(NVL(rdl.retention_rule_id,-1) ,-1,
NVL(rdl.bill_amount,0) ,NVL(rdl.retained_amount,0) ) ) invoice_amount
FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = p_project_id
AND rdl.draft_invoice_num = l_credit_invoice_num
GROUP BY NVL(rdl.retention_rule_id,-1)
UNION
SELECT NVL(di.retention_rule_id,-1) retention_rule_id,
SUM(DECODE(NVL(di.retention_rule_id,-1) ,-1,
NVL(di.amount,0) ,NVL(di.retained_amount,0) ) ) invoice_amount
FROM pa_draft_invoice_items di
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = l_credit_invoice_num
AND di.event_num is not null
GROUP BY NVL(di.retention_rule_id,-1)) CR_RET
GROUP BY retention_rule_id;
SELECT NVL(retention_percentage,0)/100
INTO l_retention_percentage
FROM pa_draft_invoices
WHERE project_id = p_project_id
AND draft_invoice_num = l_org_invoice_num;
SELECT CreditMemoRec.invoice_amount invoice_amount,
dii.text text,
dii.invoice_line_type invoice_line_type,
dii.task_id task_id,
dii.event_task_id event_task_id,
dii.event_num event_num,
dii.ship_to_address_id ship_to_address_id,
dii.taxable_flag taxable_flag,
-- dii.output_vat_tax_id output_vat_tax_id,
dii.output_tax_classification_code output_tax_code,
dii.output_tax_exempt_flag output_tax_exempt_flag,
dii.output_tax_exempt_reason_code output_tax_exempt_reason_code,
dii.output_tax_exempt_number output_tax_exempt_number,
dii.translated_text translated_text,
dii.projfunc_currency_code projfunc_currency_code,
((dii.projfunc_bill_amount/dii.amount) *
CreditMemoRec.invoice_amount) projfunc_bill_amount,
dii.project_currency_code project_currency_code,
((dii.project_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) project_bill_amount,
dii.funding_currency_code funding_currency_code,
((dii.funding_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) funding_bill_amount,
dii.funding_rate_date funding_rate_date, dii.funding_exchange_rate funding_exchange_rate,
dii.funding_rate_type funding_rate_type,dii.invproc_currency_code invproc_currency_code ,
dii.bill_trans_currency_code bill_trans_currency_code,
--dii.bill_trans_bill_amount bill_trans_bill_amount, --Modified for Bug3558364
((dii.bill_trans_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) bill_trans_bill_amount,
dii.retention_rule_id retention_rule_id,
di.agreement_id agreement_id,
agr.customer_id customer_id,
rtn.task_id rtn_task_id,
dii.line_num line_num_credited
FROM pa_draft_invoice_items dii, pa_draft_invoices_all di,
pa_agreements_all agr,
pa_proj_retn_rules rtn
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = l_org_invoice_num
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.agreement_id = agr.agreement_id
and dii.retention_rule_id = rtn.retention_rule_id
AND dii.retention_rule_id = CreditMemoRec.Retention_rule_id
AND dii.invoice_line_type ='RETENTION'
UNION
SELECT CreditMemoRec.invoice_amount invoice_amount,
dii.text text,
dii.invoice_line_type invoice_line_type,
dii.task_id task_id,
dii.event_task_id event_task_id,
dii.event_num event_num,
dii.ship_to_address_id ship_to_address_id,
dii.taxable_flag taxable_flag,
-- dii.output_vat_tax_id output_vat_tax_id,
dii.output_tax_classification_code output_tax_code,
dii.output_tax_exempt_flag output_tax_exempt_flag,
dii.output_tax_exempt_reason_code output_tax_exempt_reason_code,
dii.output_tax_exempt_number output_tax_exempt_number,
dii.translated_text translated_text,
dii.projfunc_currency_code projfunc_currency_code,
((dii.projfunc_bill_amount/dii.amount) *
CreditMemoRec.invoice_amount) projfunc_bill_amount,
dii.project_currency_code project_currency_code,
((dii.project_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) project_bill_amount,
dii.funding_currency_code funding_currency_code,
((dii.funding_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) funding_bill_amount,
dii.funding_rate_date funding_rate_date, dii.funding_exchange_rate funding_exchange_rate,
dii.funding_rate_type funding_rate_type,dii.invproc_currency_code invproc_currency_code ,
dii.bill_trans_currency_code bill_trans_currency_code,
--dii.bill_trans_bill_amount bill_trans_bill_amount, --Modified for Bug3558364
((dii.bill_trans_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) bill_trans_bill_amount,
dii.retention_rule_id retention_rule_id,
di.agreement_id agreement_id,
agr.customer_id customer_id,
dii.retention_rule_id rtn_task_id, -- To get the retention lines
dii.line_num line_num_credited
FROM pa_draft_invoice_items dii, pa_draft_invoices_all di,
pa_agreements_all agr
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = l_org_invoice_num
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.agreement_id = agr.agreement_id
AND NVL(dii.retention_rule_id,-1) = -1
AND CreditMemoRec.Retention_rule_id = -1
AND dii.invoice_line_type ='RETENTION'
) LOOP
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
INSERT INTO pa_draft_invoice_items
( PROJECT_ID, DRAFT_INVOICE_NUM, LINE_NUM,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, AMOUNT, TEXT,
INVOICE_LINE_TYPE, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, TASK_ID,
EVENT_TASK_ID, EVENT_NUM, SHIP_TO_ADDRESS_ID,
TAXABLE_FLAG, LAST_UPDATE_LOGIN,
INV_AMOUNT, OUTPUT_TAX_CLASSIFICATION_CODE, OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE, OUTPUT_TAX_EXEMPT_NUMBER,
TRANSLATED_TEXT, PROJFUNC_CURRENCY_CODE, PROJFUNC_BILL_AMOUNT,
PROJECT_CURRENCY_CODE, PROJECT_BILL_AMOUNT, FUNDING_CURRENCY_CODE,
FUNDING_BILL_AMOUNT, FUNDING_RATE_DATE, FUNDING_EXCHANGE_RATE,
FUNDING_RATE_TYPE , INVPROC_CURRENCY_CODE, BILL_TRANS_CURRENCY_CODE,
BILL_TRANS_BILL_AMOUNT, RETENTION_RULE_ID,
DRAFT_INV_LINE_NUM_CREDITED)
VALUES(
p_project_id, l_credit_invoice_num, lastlinenum,
sysdate, LASTUPDATEDBY, SYSDATE,
l_created_by,
(-1) * InvoiceLines.invoice_amount,
InvoiceLines.text,
InvoiceLines.invoice_line_type, p_request_id, l_program_application_id,
l_program_id, sysdate, InvoiceLines.task_id,
InvoiceLines.event_task_id, InvoiceLines.event_num, InvoiceLines.ship_to_address_id,
InvoiceLines.taxable_flag, l_last_update_login,
null, InvoiceLines.output_tax_code, InvoiceLines.output_tax_exempt_flag,
InvoiceLines.output_tax_exempt_reason_code, InvoiceLines.output_tax_exempt_number,
InvoiceLines.translated_text, InvoiceLines.projfunc_currency_code,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( (-1) * InvoiceLines.projfunc_bill_amount,
invoicelines.projfunc_currency_code),
invoicelines.project_currency_code,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((-1) * invoicelines.project_bill_amount,
invoicelines.project_currency_code),
InvoiceLines.funding_currency_code,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((-1) * InvoiceLines.funding_bill_amount,
InvoiceLines.funding_currency_code),
InvoiceLines.funding_rate_date, InvoiceLines.funding_exchange_rate,
InvoiceLines.funding_rate_type ,InvoiceLines.invproc_currency_code,
InvoiceLines.bill_trans_currency_code,
DECODE(NVL(InvoiceLines.bill_trans_bill_amount,0),0,0,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((-1) * InvoiceLines.bill_trans_bill_amount,
InvoiceLines.bill_trans_currency_code)),
InvoiceLines.retention_rule_id,
InvoiceLines.line_num_credited);
pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Calling Update_Retention_Balances ');
Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
p_project_id =>p_project_id ,
p_task_id =>InvoiceLines.rtn_Task_id,
p_agreement_id =>InvoiceLines.agreement_id,
p_customer_id =>InvoiceLines.customer_id,
p_amount => InvoiceLines.invoice_amount,
p_change_type => 'RETAINED' ,
p_request_id =>p_request_id,
p_invproc_currency =>InvoiceLines.invproc_currency_code ,
p_project_currency =>InvoiceLines.project_currency_code,
p_project_amount => InvoiceLines.project_bill_amount,
p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
p_projfunc_amount =>InvoiceLines.projfunc_bill_amount,
p_funding_currency =>InvoiceLines.funding_currency_code ,
p_funding_amount =>InvoiceLines.funding_bill_amount);
pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Update RDL with new Retention Line Number');
-- Update the RDL
UPDATE pa_cust_rev_dist_lines_all
SET retn_draft_invoice_num = l_credit_invoice_num,
retn_draft_invoice_line_num = LastLineNum
WHERE retention_rule_id = InvoiceLines.Retention_rule_id
AND draft_invoice_num = l_credit_invoice_num
AND project_id = p_project_id;
pa_retention_util.write_log('RDL Number of rows updated : ' || sql%rowcount);
UPDATE pa_draft_invoice_items
SET retn_draft_invoice_num = l_credit_invoice_num,
retn_draft_invoice_line_num = LastLineNum
WHERE retention_rule_id = InvoiceLines.Retention_rule_id
AND draft_invoice_num = l_credit_invoice_num
AND project_id = p_project_id
AND invoice_line_type <> 'RETENTION';
pa_retention_util.write_log('DII Number of rows updated : ' || sql%rowcount);
LastUpdatedBy NUMBER:= fnd_global.user_id;
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
UPDATE PA_DRAFT_INVOICES
SET CANCELED_FLAG = 'Y',
INVOICE_COMMENT =
(select rtrim(upper(l.meaning)||' '||
rtrim(SUBSTRB(i.invoice_comment,1,232)))
from pa_lookups l,
pa_draft_invoices i
where i.project_Id = p_project_id
and i.draft_invoice_num = p_draft_invoice_num
and l.lookup_type = 'INVOICE_CREDIT_TYPE'
and l.lookup_code = 'CANCEL')
WHERE PROJECT_ID = p_project_id
AND DRAFT_INVOICE_NUM = p_draft_invoice_num
AND nvl(CANCELED_FLAG, 'N') <> 'Y';
SELECT NVL( MAX(p.draft_invoice_num) + 1, 1)
INTO TmpInvoiceNum
FROM pa_draft_invoices_all p
WHERE p.project_id = p_project_id;
INSERT INTO pa_draft_invoices_all
(
PROJECT_ID ,
DRAFT_INVOICE_NUM ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
TRANSFER_STATUS_CODE ,
GENERATION_ERROR_FLAG ,
AGREEMENT_ID ,
PA_DATE ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CUSTOMER_BILL_SPLIT ,
BILL_THROUGH_DATE ,
INVOICE_COMMENT ,
INVOICE_DATE ,
GL_DATE ,
CANCELED_FLAG ,
LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
RETENTION_PERCENTAGE ,
--INVOICE_SET_ID , /* Commented for Bug 2448872 */
ORG_ID ,
INV_CURRENCY_CODE ,
INV_RATE_TYPE ,
INV_RATE_DATE ,
INV_EXCHANGE_RATE ,
BILL_TO_ADDRESS_ID ,
SHIP_TO_ADDRESS_ID ,
ACCTD_CURR_CODE ,
ACCTD_RATE_TYPE ,
ACCTD_RATE_DATE ,
ACCTD_EXCHG_RATE ,
LANGUAGE ,
INVPROC_CURRENCY_CODE ,
PROJFUNC_INVTRANS_RATE_TYPE ,
PROJFUNC_INVTRANS_RATE_DATE ,
PROJFUNC_INVTRANS_EX_RATE ,
PA_PERIOD_NAME ,
GL_PERIOD_NAME ,
RETENTION_INVOICE_FLAG ,
DRAFT_INVOICE_NUM_CREDITED ,
CANCEL_CREDIT_MEMO_FLAG , -- Added for Bug 2448872
APPROVED_BY_PERSON_ID , -- Added for Bug 2448872
APPROVED_DATE , -- Added for Bug 2448872
CUSTOMER_ID ,
BILL_TO_CUSTOMER_ID ,
SHIP_TO_CUSTOMER_ID ,
BILL_TO_CONTACT_ID ,
SHIP_TO_CONTACT_ID )
SELECT
p_project_id ,
TmpInvoiceNum,
SYSDATE,
LastUpdatedBy,
sysdate,
l_created_by,
'P',
'N',
di.agreement_id,
TmpPaDate,
p_request_id,
l_program_application_id,
l_program_id,
sysdate,
di.customer_bill_split,
di.bill_through_date,
di.INVOICE_COMMENT,
TmpInvoiceDate,
TmpGlDate,
'N' ,
l_last_update_login,
di.ATTRIBUTE_CATEGORY,
di.ATTRIBUTE1,
di.ATTRIBUTE2,
di.ATTRIBUTE3,
di.ATTRIBUTE4,
di.ATTRIBUTE5,
di.ATTRIBUTE6,
di.ATTRIBUTE7,
di.ATTRIBUTE8,
di.ATTRIBUTE9,
di.ATTRIBUTE10,
di.RETENTION_PERCENTAGE,
-- di.INVOICE_SET_ID, /* Commented for Bug 2448872 */
di.ORG_ID,
di.INV_CURRENCY_CODE,
di.INV_RATE_TYPE,
di.INV_RATE_DATE,
di.INV_EXCHANGE_RATE,
di.BILL_TO_ADDRESS_ID,
di.SHIP_TO_ADDRESS_ID,
di.ACCTD_CURR_CODE,
di.ACCTD_RATE_TYPE,
di.ACCTD_RATE_DATE,
di.ACCTD_EXCHG_RATE,
di.LANGUAGE,
di.INVPROC_CURRENCY_CODE ,
di.PROJFUNC_INVTRANS_RATE_TYPE ,
di.PROJFUNC_INVTRANS_RATE_DATE ,
di.PROJFUNC_INVTRANS_EX_RATE ,
pa_billing.getpaperiodname,
pa_billing.getglperiodname ,
di.RETENTION_INVOICE_FLAG,
p_draft_invoice_num,
'Y', -- Added for Bug 2448872
di.approved_by_person_id, -- Added for Bug 2448872
di.approved_date, -- Added for Bug 2448872
di.customer_id,
di.bill_to_customer_id,
di.ship_to_customer_id,
di.bill_to_contact_id,
di.ship_to_contact_id /*last 3 columns added for
customer account relation enhancement*/
FROM pa_draft_invoices_all di
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num;
FOR InvoiceLines IN (SELECT dii.line_num line_Num ,
dii. invproc_currency_code invproc_currency_code,
dii.amount amount,
dii.projfunc_currency_code projfunc_currency_code,
dii.projfunc_bill_amount projfunc_bill_amount,
dii.project_currency_code project_currency_code,
dii.project_bill_amount project_bill_amount,
dii.funding_currency_code funding_currency_code,
dii.funding_bill_amount funding_bill_amount,
dii.event_task_id event_task_id,
dii.taxable_flag taxable_flag,
--dii.output_vat_tax_id output_vat_tax_id,
dii.output_tax_classification_code,
dii.funding_rate_date funding_rate_date,
dii.funding_rate_type funding_rate_type,
dii.funding_exchange_rate funding_exchange_rate,
dii.invoice_line_type invoice_line_type,
dii.output_tax_exempt_flag output_tax_exempt_flag,
dii.output_tax_exempt_reason_code output_tax_exempt_reason_code,
dii.output_tax_exempt_number output_tax_exempt_number,
dii.translated_text translated_text,
dii.text text,
dii.event_num event_num,
dii.task_id task_id,
dii.retention_rule_id retention_rule_id,
dii.ship_to_address_id ship_to_address_id,
dii.bill_trans_currency_code bill_trans_currency_code,
dii.bill_trans_bill_amount bill_trans_bill_amount,
di.agreement_id agreement_id,
agr.customer_id customer_id,
nvl(rtn.task_id, dii.task_id) rtn_task_id,
dii.inv_amount inv_amount
FROM pa_draft_invoice_items dii, pa_draft_invoices_all di,
pa_agreements_all agr,
pa_proj_retn_rules rtn
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num
AND di.agreement_id = agr.agreement_id
AND di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND dii.retention_rule_id = rtn.retention_rule_id(+)) LOOP
INSERT INTO pa_draft_invoice_items
( PROJECT_ID, DRAFT_INVOICE_NUM, LINE_NUM,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, AMOUNT, TEXT,
INVOICE_LINE_TYPE, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, TASK_ID,
EVENT_TASK_ID, EVENT_NUM, SHIP_TO_ADDRESS_ID,
TAXABLE_FLAG, LAST_UPDATE_LOGIN,
INV_AMOUNT, OUTPUT_TAX_CLASSIFICATION_CODE, OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE, OUTPUT_TAX_EXEMPT_NUMBER,
TRANSLATED_TEXT, PROJFUNC_CURRENCY_CODE, PROJFUNC_BILL_AMOUNT,
PROJECT_CURRENCY_CODE, PROJECT_BILL_AMOUNT, FUNDING_CURRENCY_CODE,
FUNDING_BILL_AMOUNT, FUNDING_RATE_DATE, FUNDING_EXCHANGE_RATE,
FUNDING_RATE_TYPE , INVPROC_CURRENCY_CODE, BILL_TRANS_CURRENCY_CODE,
BILL_TRANS_BILL_AMOUNT, RETENTION_RULE_ID,
DRAFT_INV_LINE_NUM_CREDITED
)
VALUES(
p_project_id, TmpInvoiceNum, InvoiceLines.line_num,
sysdate, LastUpdatedBy, SYSDATE,
l_created_by, (-1) * InvoiceLines.amount, InvoiceLines.text,
InvoiceLines.invoice_line_type, p_request_id, l_program_application_id,
l_program_id, sysdate, InvoiceLines.task_id,
InvoiceLines.event_task_id, InvoiceLines.event_num,
InvoiceLines.ship_to_address_id,
InvoiceLines.taxable_flag, l_last_update_login,
(-1) * InvoiceLines.inv_amount, InvoiceLines.output_tax_classification_code,
InvoiceLines.output_tax_exempt_flag,
InvoiceLines.output_tax_exempt_reason_code, InvoiceLines.output_tax_exempt_number,
InvoiceLines.translated_text, InvoiceLines.projfunc_currency_code,
(-1) * InvoiceLines.projfunc_bill_amount,
InvoiceLines.project_currency_code, (-1) * InvoiceLines.project_bill_amount,
InvoiceLines.funding_currency_code,
(-1) * InvoiceLines.funding_bill_amount,
InvoiceLines.funding_rate_date, InvoiceLines.funding_exchange_rate,
InvoiceLines.funding_rate_type ,InvoiceLines.invproc_currency_code,
InvoiceLines.bill_trans_currency_code,
InvoiceLines.bill_trans_bill_amount, InvoiceLines.retention_rule_id,
InvoiceLines.line_num
);
Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
p_project_id =>p_project_id ,
p_task_id =>InvoiceLines.rtn_Task_id,
p_agreement_id =>InvoiceLines.agreement_id,
p_customer_id =>InvoiceLines.customer_id,
p_amount => (-1) * InvoiceLines.amount,
p_change_type => 'BILLED' ,
p_request_id =>p_request_id,
p_invproc_currency =>InvoiceLines.invproc_currency_code ,
p_project_currency =>InvoiceLines.project_currency_code,
p_project_amount => (-1) * InvoiceLines.project_bill_amount,
p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
p_projfunc_amount => (-1) * InvoiceLines.projfunc_bill_amount,
p_funding_currency => InvoiceLines.funding_currency_code ,
p_funding_amount => (-1) * InvoiceLines.funding_bill_amount);
INSERT INTO pa_retn_invoice_details
( RETN_INVOICE_DETAIL_ID, PROJECT_ID, DRAFT_INVOICE_NUM,
LINE_NUM , PROJECT_RETENTION_ID, TOTAL_RETAINED,
INVPROC_CURRENCY_CODE, PROJFUNC_CURRENCY_CODE,
PROJFUNC_TOTAL_RETAINED, PROJECT_CURRENCY_CODE,
PROJECT_TOTAL_RETAINED , FUNDING_CURRENCY_CODE,
FUNDING_TOTAL_RETAINED, PROGRAM_APPLICATION_ID ,
PROGRAM_UPDATE_DATE, REQUEST_ID,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PROGRAM_ID )
SELECT
pa_retn_invoice_details_s.nextval,
p_project_id,
TmpInvoiceNum,
rtndet.line_num,
rtndet.project_retention_id,
rtndet.total_retained,
rtndet.invproc_currency_code,
rtndet.projfunc_currency_code,
rtndet.projfunc_total_retained,
rtndet.project_currency_code,
rtndet.project_total_retained,
rtndet.funding_currency_code,
rtndet.funding_total_retained,
l_program_application_id,
sysdate, p_request_id,
sysdate, l_created_by,
sysdate, LastUpdatedBy,
l_program_id
FROM pa_retn_invoice_details rtndet
WHERE rtndet.project_id = p_project_id
AND rtndet.draft_invoice_num = p_draft_invoice_num;
FOR InvoiceLines IN (SELECT
dii. invproc_currency_code invproc_currency_code,
dii.amount amount, dii.projfunc_currency_code projfunc_currency_code,
dii.projfunc_bill_amount projfunc_bill_amount,
dii.project_currency_code project_currency_code,
dii.project_bill_amount project_bill_amount,
dii.funding_currency_code funding_currency_code,
dii.funding_bill_amount funding_bill_amount,
dii.task_id task_id,
dii.retention_rule_id retention_rule_id,
di.agreement_id agreement_id,
agr.customer_id customer_id,
rtn.task_id rtn_task_id
FROM pa_draft_invoice_items dii,
pa_draft_invoices_all di,
pa_agreements_all agr,
pa_proj_retn_rules rtn
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num
AND di.agreement_id = agr.agreement_id
AND di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND dii.invoice_line_type = 'RETENTION'
-- # Fix for 2366314
AND dii.retention_rule_id = rtn.retention_rule_id
AND dii.retention_rule_id is not null) LOOP
Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
p_project_id =>p_project_id ,
p_task_id =>InvoiceLines.rtn_Task_id,
p_agreement_id =>InvoiceLines.agreement_id,
p_customer_id =>InvoiceLines.customer_id,
p_amount => InvoiceLines.amount,
-- # Fix for 2366314 (-1) * InvoiceLines.amount,
p_change_type => 'RETAINED' ,
p_request_id =>p_request_id,
p_invproc_currency =>InvoiceLines.invproc_currency_code ,
p_project_currency =>InvoiceLines.project_currency_code,
p_project_amount => InvoiceLines.project_bill_amount,
-- # Fix for 2366314 (-1) * InvoiceLines.project_bill_amount,
p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
p_projfunc_amount => InvoiceLines.projfunc_bill_amount,
-- # Fix for 2366314 (-1) * InvoiceLines.projfunc_bill_amount,
p_funding_currency => InvoiceLines.funding_currency_code ,
p_funding_amount => InvoiceLines.funding_bill_amount);
PROCEDURE Invoice_Delete_Action(p_request_id IN NUMBER,
p_invoice_type IN VARCHAR2,
p_project_id IN NUMBER,
p_draft_invoice_num IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
TmpChangeType VARCHAR2(15);
pa_retention_util.write_log('Invoice_Delete_Action ');
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Change Type : ' || tmpChangeType);
FOR InvoiceLines IN (SELECT
decode(tmpChangeType,'BILLED', -1 * dii.amount ,
dii.amount ) amount,
dii.task_id task_id,
dii.invproc_currency_code invproc_currency_code,
decode(tmpChangeType,'BILLED', -1 * dii.projfunc_bill_amount,
dii.projfunc_bill_amount) projfunc_bill_amount,
dii.projfunc_currency_code projfunc_currency_code,
decode(tmpChangeType,'BILLED', -1 * dii.project_bill_amount,
dii.project_bill_amount) project_bill_amount,
dii.project_currency_code project_currency_code,
dii.funding_currency_code funding_currency_code,
decode( tmpChangeType,'BILLED', -1 * dii.funding_bill_amount,
dii.funding_bill_amount) funding_bill_amount,
dii.retention_rule_id retention_rule_id,
di.agreement_id agreement_id,
agr.customer_id customer_id,
NVL(rtn.task_id,dii.task_id) rtn_task_id
FROM pa_draft_invoice_items dii,
pa_draft_invoices_all di,
pa_agreements_all agr,
pa_proj_retn_rules rtn
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num
AND di.agreement_id = agr.agreement_id
AND di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND dii.retention_rule_id = rtn.retention_rule_id (+)
AND dii.invoice_line_type='RETENTION') LOOP
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id,
p_project_id =>p_project_id,
p_task_id =>InvoiceLines.rtn_Task_id,
p_agreement_id =>InvoiceLines.agreement_id,
p_customer_id =>InvoiceLines.customer_id,
p_amount =>InvoiceLines.amount,
p_change_type => TmpChangeType ,
p_request_id =>p_request_id,
p_invproc_currency =>InvoiceLines.invproc_currency_code,
p_project_currency =>InvoiceLines.project_currency_code,
p_project_amount => InvoiceLines.project_bill_amount,
p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
p_projfunc_amount => InvoiceLines.projfunc_bill_amount,
p_funding_currency => InvoiceLines.funding_currency_code ,
p_funding_amount => InvoiceLines.funding_bill_amount);
-- Delete the retention invoice details
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Delete Retention Invoice Details ');
DELETE FROM pa_retn_invoice_details
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
-- Call to delete the mc records
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Delete MRC Retention Invoice Details ');
p_action=>'DELETE',
p_request_id=>p_request_id);
pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Oracle Error ' || sqlerrm);
END Invoice_Delete_Action;
/* Renamed the procedure from invoice_write_off to update_credit_retn_balances for Bug3525910 */
PROCEDURE update_credit_retn_balances(p_request_id IN NUMBER,
p_invoice_type IN VARCHAR2,
p_credit_action IN VARCHAR2, --Added this parameter for Bug3525910
p_project_id IN NUMBER,
p_draft_invoice_num IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
BEGIN
IF p_credit_action = 'WRITE_OFF' OR p_credit_action = 'CONCESSION' THEN --Added this IF condition for Bug3525910,
--Added concession check for 4290823
IF p_invoice_type ='PROJECT_INVOICE' THEN
FOR InvoiceLines IN (SELECT
dii. invproc_currency_code invproc_currency_code,
dii.amount amount, dii.projfunc_currency_code projfunc_currency_code,
dii.projfunc_bill_amount projfunc_bill_amount,
dii.project_currency_code project_currency_code,
dii.project_bill_amount project_bill_amount,
dii.funding_currency_code funding_currency_code,
dii.funding_bill_amount funding_bill_amount,
dii.task_id task_id,
dii.retention_rule_id retention_rule_id,
di.agreement_id agreement_id,
agr.customer_id customer_id,
rtn.task_id rtn_task_id
FROM pa_draft_invoice_items dii,
pa_draft_invoices_all di,
pa_agreements_all agr,
pa_proj_retn_rules rtn
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num
AND di.agreement_id = agr.agreement_id
AND di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND dii.invoice_line_type = 'RETENTION'
-- # Fix for 2366314
AND dii.retention_rule_id = rtn.retention_rule_id
AND dii.retention_rule_id is not null) LOOP
Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
p_project_id =>p_project_id ,
p_task_id =>InvoiceLines.rtn_Task_id,
p_agreement_id =>InvoiceLines.agreement_id,
p_customer_id =>InvoiceLines.customer_id,
p_amount => (-1) * InvoiceLines.amount,
p_change_type => 'RETAINED' ,
p_request_id =>p_request_id,
p_invproc_currency =>InvoiceLines.invproc_currency_code ,
p_project_currency =>InvoiceLines.project_currency_code,
p_project_amount => (-1) * InvoiceLines.project_bill_amount,
p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
p_projfunc_amount => (-1) * InvoiceLines.projfunc_bill_amount,
p_funding_currency => InvoiceLines.funding_currency_code ,
p_funding_amount => (-1) * InvoiceLines.funding_bill_amount);
FOR InvoiceLines IN (SELECT
dii. invproc_currency_code invproc_currency_code,
dii.amount amount, dii.projfunc_currency_code projfunc_currency_code,
dii.projfunc_bill_amount projfunc_bill_amount,
dii.project_currency_code project_currency_code,
dii.project_bill_amount project_bill_amount,
dii.funding_currency_code funding_currency_code,
dii.funding_bill_amount funding_bill_amount,
dii.task_id task_id,
dii.retention_rule_id retention_rule_id,
di.agreement_id agreement_id,
agr.customer_id customer_id,
dii.task_id dii_task_id
FROM pa_draft_invoice_items dii,
pa_draft_invoices_all di,
pa_agreements_all agr
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = p_draft_invoice_num
AND di.agreement_id = agr.agreement_id
AND di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND dii.invoice_line_type = 'RETENTION') LOOP
Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
p_project_id =>p_project_id ,
p_task_id =>InvoiceLines.dii_Task_id,
p_agreement_id =>InvoiceLines.agreement_id,
p_customer_id =>InvoiceLines.customer_id,
p_amount =>InvoiceLines.amount,
p_change_type => 'BILLED' ,
p_request_id =>p_request_id,
p_invproc_currency =>InvoiceLines.invproc_currency_code ,
p_project_currency =>InvoiceLines.project_currency_code,
p_project_amount => InvoiceLines.project_bill_amount,
p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
p_projfunc_amount =>InvoiceLines.projfunc_bill_amount,
p_funding_currency =>InvoiceLines.funding_currency_code ,
p_funding_amount =>InvoiceLines.funding_bill_amount);
END update_credit_retn_balances;
PROCEDURE Delete_Unused_Retention_Lines(
P_Project_ID IN NUMBER,
P_Task_ID IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_Cust_Top_Task_Flag VARCHAR2(1);
Select ENABLE_TOP_TASK_CUSTOMER_FLAG, ENABLE_TOP_TASK_INV_MTH_FLAG
Into l_Cust_Top_Task_Flag, l_Inv_Method_Top_Task_Flag
From PA_Projects_All
Where Project_ID = P_Project_ID;
Delete From PA_Proj_Retn_Rules -- PA_Project_Retentions
Where Project_ID = P_Project_ID
And Task_ID = P_Task_ID;
pa_retention_util.write_log('Retention_Delete_Action: ' || 'Oracle Error ' || sqlerrm);
END Delete_Unused_Retention_Lines;