[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Values are 'SINGLE', 'RANGE', 'DELETE' |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE Revaluate_funding(
p_project_id IN NUMBER,
p_project_type_id IN NUMBER,
p_from_proj_number IN VARCHAR2,
p_to_proj_number IN VARCHAR2,
p_thru_date IN DATE,
p_rate_type IN VARCHAR2 ,
p_rate_date IN DATE,
p_baseline_flag IN VARCHAR2,
p_debug_mode IN VARCHAR2,
p_run_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_FromProjNum VARCHAR2(25);
IF p_run_mode = 'DELETE' THEN
get_delete_projects(
p_project_type_id => l_ProjTypeId,
p_from_proj_number => l_FromProjNum,
p_to_proj_number => l_ToProjNum,
p_run_mode => p_run_mode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END IF; /* p_run_mode = DELETE */
Insert_distribution_warnings(p_project_id => p_project_id,
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
Delete_unbaselined_adjmts(p_project_id => p_project_id,
p_run_mode => p_run_mode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
Insert_distribution_warnings(p_project_id => p_project_id,
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
/* This CURSOR selects all reporting set of books enabled for the primary set of book id
in PA */
/* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
new table gl_alc_ledger_rships_v and corresponding columns */
/* CURSOR rsob is SELECT rep.reporting_set_of_books_id reporting_set_of_books_id ,
rep.reporting_currency_code, rep.conversion_type
FROM gl_mc_reporting_options rep, pa_implementations imp
WHERE rep.primary_set_of_books_id = imp.set_of_books_id
AND enabled_flag = 'Y'
AND rep.org_id = imp.org_id
AND application_id = 275; */
CURSOR rsob is SELECT rep.ledger_id reporting_set_of_books_id ,
rep.currency_code reporting_currency_code,
rep.alc_default_conv_rate_type conversion_type
FROM gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275;
/* This CURSOR selects all reporting set of books enabled for the primary set of book id
in AR */
/* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
new table gl_alc_ledger_rships_v and corresponding columns */
/* CURSOR rsob_ar_mc is SELECT rep.reporting_set_of_books_id reporting_set_of_books_id ,
rep.reporting_currency_code, rep.conversion_type
FROM gl_mc_reporting_options rep, pa_implementations imp
WHERE rep.primary_set_of_books_id = imp.set_of_books_id
AND enabled_flag = 'Y'
AND rep.org_id = imp.org_id
AND application_id = PA_FUND_REVAL_UTIL.get_ar_application_id; */
CURSOR rsob_ar_mc is SELECT rep.ledger_id reporting_set_of_books_id ,
rep.currency_code reporting_currency_code,
rep.alc_default_conv_rate_type conversion_type
FROM gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND application_id = PA_FUND_REVAL_UTIL.get_ar_application_id;
SELECT set_of_books_id, nvl(ENABLE_MRC_FOR_FUND_FLAG , 'N')
into G_SET_OF_BOOKS_ID, G_MRC_FUND_ENABLED_FLAG FROM pa_implementations;
SELECT set_of_books_id, 'N' -- nvl(REVAL_MRC_FUNDING_FLAG , 'N')
into G_SET_OF_BOOKS_ID, G_MRC_FUND_ENABLED_FLAG FROM pa_implementations;
SELECT p.segment1, pt.project_type_id
INTO l_FromProjNum, l_ProjTypeId
FROM pa_projects p, pa_project_types pt
WHERE p.project_id = p_project_id
AND pt.project_type = p.project_type;
SELECT MIN(P.segment1)
INTO l_FromProjNum
FROM PA_PROJECTS P , PA_PROJECT_TYPES T
WHERE T.project_type_id (+) = l_ProjTypeId
AND P.Project_type = T.Project_type
AND T.Project_type_class_code = 'CONTRACT';
SELECT proj.seg1
INTO l_FromProjNum
FROM (
SELECT t.project_type_class_code, MIN(P.segment1) seg1
FROM PA_PROJECTS P , PA_PROJECT_TYPES T
WHERE T.project_type_id = NVL(l_ProjTypeId,0)
AND P.Project_type = T.Project_type
AND T.Project_type_class_code = 'CONTRACT'
AND NVL(l_ProjTypeId,0) <> 0
GROUP BY t.project_type_class_code
UNION
SELECT t.project_type_class_code, MIN(P.segment1) seg1
FROM PA_PROJECTS P , PA_PROJECT_TYPES T
WHERE P.Project_type = T.Project_type
AND T.Project_type_class_code = 'CONTRACT'
AND nvl(l_ProjTypeId,0) = 0
group by t.project_type_class_code
) proj;
SELECT MAX(P.segment1)
INTO l_ToProjNum
FROM PA_PROJECTS P , PA_PROJECT_TYPES T
WHERE T.project_type_id (+) = l_ProjTypeId
AND P.Project_type = T.Project_type
AND T.Project_type_class_code = 'CONTRACT';
SELECT proj.seg1
INTO l_ToProjNum
FROM (
SELECT t.project_type_class_code, MAX(P.segment1) seg1
FROM PA_PROJECTS P , PA_PROJECT_TYPES T
WHERE T.project_type_id = NVL(l_ProjTypeId,0)
AND P.Project_type = T.Project_type
AND T.Project_type_class_code = 'CONTRACT'
AND NVL(l_ProjTypeId,0) <> 0
GROUP BY t.project_type_class_code
UNION
SELECT t.project_type_class_code, MAX(P.segment1) seg1
FROM PA_PROJECTS P , PA_PROJECT_TYPES T
WHERE P.Project_type = T.Project_type
AND T.Project_type_class_code = 'CONTRACT'
AND nvl(l_ProjTypeId,0) = 0
group by t.project_type_class_code
) proj;
SELECT 'T' INTO x_exist_flag
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM pa_draft_revenues r
WHERE r.project_id = p_project_id
AND r.released_date IS NULL);
SELECT 'T' INTO x_exist_flag
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM pa_draft_invoices i
WHERE i.project_id = p_project_id
and i.released_by_person_id IS NULL);
SELECT 'T' INTO l_ExistFlag
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pa_project_fundings
WHERE project_id = p_project_id
AND funding_category = 'REVALUATION'
AND budget_type_code = 'DRAFT');
| Procedure : Delete_unbaselined_adjmts |
| Purpose : To delete (if any) unbaselined revaluation adjutment lines |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_run_mode IN Run Mode - SINGLE,RANGE, DELETE |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE Delete_Unbaselined_Adjmts (
p_project_id IN NUMBER,
p_run_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR fund_recs is
SELECT project_funding_id, agreement_id, project_id, task_id, projfunc_allocated_amount,
projfunc_realized_gains_amt, projfunc_realized_losses_amt, invproc_allocated_amount,
revproc_allocated_amount
FROM pa_project_fundings
WHERE project_id = p_project_id
AND funding_category = 'REVALUATION'
AND budget_type_code = 'DRAFT';
PA_DEBUG.g_err_stage := '-----------Entering PA_FUND_REVAL_PVT.Delete_Unbaselined_Adjmts-----------' ;
DELETE FROM pa_events
WHERE project_funding_id = l_FundingIdTab(i)
AND Project_ID = l_ProjectIdTab(i);
DELETE FROM pa_project_fundings
WHERE project_funding_id = l_FundingIdTab(i);
UPDATE pa_summary_project_fundings
SET projfunc_unbaselined_amount =
nvl(projfunc_unbaselined_amount,0) - nvl(l_ProjfuncAllocTab(i),0),
invproc_unbaselined_amount =
nvl(invproc_unbaselined_amount,0) - nvl(l_InvprocAllocTab(i),0),
revproc_unbaselined_amount =
nvl(revproc_unbaselined_amount,0) - nvl(l_RevprocAllocTab(i),0),
projfunc_realized_gains_amt =
nvl(projfunc_realized_gains_amt,0) - nvl(l_ProjfuncGainsTab(i),0),
projfunc_realized_losses_amt =
nvl(projfunc_realized_losses_amt,0) - nvl(l_ProjfuncLossTab(i),0)
WHERE agreement_id = l_AgreementIdTab(i)
AND project_id = l_ProjectIdTab(i)
AND nvl(task_id,-99) = nvl(l_TaskIdTab(i),-99);
/* Insert the details of project funding line deleted as delete process requires output */
IF p_run_mode = 'DELETE' THEN
l_ReasonCode := NULL;
Insert_distribution_warnings(
p_project_id => p_project_id,
p_task_id => l_TaskIdTab(j),
p_agreement_id => l_AgreementIdTab(j),
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
END IF; /*p_run_mode = 'DELETE' */
l_FundingIdTab.DELETE;
l_AgreementIdTab.DELETE;
l_ProjectIdTab.DELETE;
l_TaskIdTab.DELETE;
l_ProjfuncAllocTab.DELETE;
l_ProjfuncGainsTab.DELETE;
l_ProjfuncLossTab.DELETE;
l_InvprocAllocTab.DELETE;
l_RevprocAllocTab.DELETE;
PA_DEBUG.g_err_stage := '-----------Exiting PA_FUND_REVAL_PVT.Delete_Unbaselined_Adjmts-----------' ;
PA_DEBUG.g_err_stage := 'Delete_Unbaselined_Adjmts:' || x_msg_data ;
END Delete_Unbaselined_Adjmts;
| Procedure : Insert_distribution_warnings |
| Purpose : To insert rejection reasons in distribution table |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_agreement_id IN Agreement ID |
| p_task_id IN Task id of summary project funding |
| p_reason_code IN Rejection Reason |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE Insert_distribution_warnings(
p_project_id IN NUMBER,
p_agreement_id IN NUMBER DEFAULT NULL,
p_task_id IN NUMBER DEFAULT NULL,
p_reason_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(30) := NULL;
CURSOR rej_reason IS SELECT meaning FROM PA_LOOKUPS
WHERE lookup_type = 'FUNDING REVAL REJECTION'
AND lookup_code = p_reason_code;
PA_DEBUG.g_err_stage := '-----------Entering PA_FUND_REVAL_PVT.Insert_distribution_warnings-----------' ;
INSERT INTO PA_DISTRIBUTION_WARNINGS
(
PROJECT_ID, AGREEMENT_ID, TASK_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, WARNING_MESSAGE_CODE, WARNING_MESSAGE
)
VALUES
(
p_project_id, p_agreement_id, p_task_id, SYSDATE, G_LAST_UPDATED_BY,
SYSDATE, G_LAST_UPDATED_BY, G_REQUEST_ID, G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID, SYSDATE, p_reason_code, l_reason
);
PA_DEBUG.g_err_stage := '-----------Exiting PA_FUND_REVAL_PVT.Insert_distribution_warnings-----------' ;
PA_DEBUG.g_err_stage := 'Insert_distribution_warnings:' || x_msg_data ;
END Insert_distribution_warnings;
G_LAST_UPDATE_LOGIN := fnd_global.login_id;
G_LAST_UPDATED_BY := fnd_global.user_id;
/* This CURSOR selects all projects with the following criteria
a) should be contract projects
b) revaluate_funding_flag is enabled
c) Falls within the given range start/end project numbers */
CURSOR open_projects IS
SELECT P.project_id, P.baseline_funding_flag,
P.include_gains_losses_flag include_gains_losses_flag,
P.carrying_out_organization_id,
P.projfunc_bil_rate_type projfunc_bil_rate_type,
P.projfunc_bil_exchange_rate projfunc_bil_exchange_rate,
DECODE(P.invproc_currency_type,
'PROJECT_CURRENCY', P.project_bil_rate_type,
'PROJFUNC_CURRENCY', P.projfunc_bil_rate_type,
'FUNDING_CURRENCY', P.funding_rate_type) invproc_rate_type,
DECODE(P.invproc_currency_type,
'PROJECT_CURRENCY', P.project_bil_exchange_rate,
'PROJFUNC_CURRENCY', P.projfunc_bil_exchange_rate,
'FUNDING_CURRENCY', P.funding_exchange_rate) invproc_exchange_rate,
T.RLZD_GAINS_EVENT_TYPE_ID,
T.RLZD_LOSSES_EVENT_TYPE_ID
FROM pa_projects P, pa_project_types T
WHERE P.segment1 BETWEEN p_from_proj_number
AND p_to_proj_number
AND P.PROJECT_TYPE = T.PROJECT_TYPE
AND T.DIRECT_FLAG = 'Y'
AND T.PROJECT_TYPE_ID = NVL(P_PROJECT_TYPE_ID ,T.project_type_id)
AND NVL(P.revaluate_funding_flag, 'N') = 'Y'
AND NVL(P.template_flag, 'N') = 'N'
ORDER BY segment1 ;
SELECT event_type, description from pa_event_types
WHERE event_type_id = l_EventTypeId;
G_InvCompTab.DELETE;
G_RetnApplAmtTab.DELETE;
G_RevalCompTab.DELETE;
Insert_distribution_warnings(
p_project_id => G_ProjLvlGlobRec.project_id,
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
/*Bug 3986205 : Replaced p_project_id with G_ProjLvlGlobRec.project_id in the following INSERT statement */
INSERT INTO PA_DISTRIBUTION_WARNINGS
(
PROJECT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, WARNING_MESSAGE_CODE, WARNING_MESSAGE
)
VALUES
(
G_ProjLvlGlobRec.project_id, SYSDATE, G_LAST_UPDATED_BY,
SYSDATE, G_LAST_UPDATED_BY, G_REQUEST_ID, G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID, SYSDATE, 'BASELINE ERROR', l_msg_data
);
/* This CURSOR selects the summary project funding lines with non-zero baselined amount
for the given project in primary set of book id only*/
CURSOR get_spf_lines (l_ProjectId NUMBER) is
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SPF.agreement_id agreement_id,
SPF.task_id task_id,
SPF.funding_currency_code funding_currency_code,
SPF.project_currency_code project_currency_code,
SPF.projfunc_currency_code projfunc_currency_code,
SPF.invproc_currency_code invproc_currency_code,
SPF.total_baselined_amount total_baselined_amount,
SPF.projfunc_baselined_amount projfunc_baselined_amount,
SPF.invproc_baselined_amount invproc_baselined_amount,
SPF.projfunc_realized_gains_amt projfunc_realized_gains_amt,
SPF.projfunc_realized_losses_amt projfunc_realized_losses_amt,
SPF.projfunc_accrued_amount projfunc_accrued_amount,
SPF.invproc_billed_amount invproc_billed_amount,
PC.retention_level_code retention_level_code,
PC.customer_id customer_id
FROM pa_summary_project_fundings SPF, pa_agreements_all A, pa_project_customers PC
WHERE SPF.project_id = l_ProjectId
AND A.agreement_id = SPF.agreement_id
AND PC.customer_id = A.customer_id
AND PC.project_id = SPF.project_id
AND (NVL(SPF.total_baselined_amount,0) <> 0)
ORDER BY PC.customer_id, SPF.agreement_id, SPF.task_id , set_of_books_id;
/* This CURSOR selects the summary project funding lines with non-zero baselined amount
for the given project in primary and reporting set of book ids */
/* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
new table gl_alc_ledger_rships_v and corresponding columns */
/* mrc migration to SLA bug 4571438
CURSOR get_all_spf_lines (l_ProjectId NUMBER) is
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SPF.agreement_id agrmt_id,
SPF.task_id task_id,
SPF.funding_currency_code funding_currency_code,
SPF.project_currency_code project_currency_code,
SPF.projfunc_currency_code projfunc_currency_code,
SPF.invproc_currency_code invproc_currency_code,
SPF.total_baselined_amount total_baselined_amount,
SPF.projfunc_baselined_amount projfunc_baselined_amount,
SPF.invproc_baselined_amount invproc_baselined_amount,
SPF.projfunc_realized_gains_amt projfunc_realized_gains_amt,
SPF.projfunc_realized_losses_amt projfunc_realized_losses_amt,
SPF.projfunc_accrued_amount projfunc_accrued_amount,
SPF.invproc_billed_amount invproc_billed_amount,
PC.retention_level_code retention_level_code,
PC.customer_id cust_id
FROM pa_summary_project_fundings SPF, pa_agreements_all A, pa_project_customers PC
WHERE SPF.project_id = l_ProjectId
AND A.agreement_id = SPF.agreement_id
AND PC.customer_id = A.customer_id
AND PC.project_id = SPF.project_id
AND (NVL(SPF.total_baselined_amount,0) <> 0)
UNION
SELECT SPF_mc.set_of_books_id,
SPF.agreement_id agrmt_id,
SPF.task_id task_id,
SPF.funding_currency_code funding_currency_code,
'NA' project_currency_code,
SPF_mc.currency_code projfunc_currency_code,
SPF.invproc_currency_code invproc_currency_code,
SPF.total_baselined_amount total_baselined_amount,
SPF_mc.total_baselined_amount projfunc_baselined_amount,
SPF.invproc_baselined_amount invproc_baselined_amount,
SPF_mc.realized_gains_amt projfunc_realized_gains_amt,
SPF_mc.realized_losses_amt projfunc_realized_losses_amt,
SPF_mc.total_accrued_amount projfunc_accrued_amount,
SPF.invproc_billed_amount invproc_billed_amount,
PC.retention_level_code retention_level_code,
PC.customer_id cust_id
FROM pa_mc_sum_proj_fundings SPF_mc, pa_summary_project_fundings SPF,
pa_agreements_all A, pa_project_customers PC,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE SPF.project_id = l_ProjectId
AND A.agreement_id = SPF.agreement_id
AND PC.customer_id = A.customer_id
AND PC.project_id = SPF.project_id
AND (NVL(SPF.total_baselined_amount,0) <> 0)
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND spf_mc.set_of_books_id =rep.ledger_id
AND spf_mc.project_id = spf.project_id
AND spf_mc.agreement_id = spf.agreement_id
AND nvl(spf_mc.task_id,0) = nvl(spf.task_id,0)
)
ORDER BY cust_id, agrmt_id, task_id , set_of_books_id; */
G_RevalCompTab.DELETE; /* Intialize for each project */
G_RevalCompTab.DELETE; /* Initialize for next SPF record */
l_SetOfBookIdTab.DELETE;
l_AgreementIdTab.DELETE;
l_TaskIdTab.DELETE;
l_FCCurrTab.DELETE;
l_PCCurrTab.DELETE;
l_PFCCurrTab.DELETE;
l_IPCCurrTab.DELETE;
l_FCBaseAmtTab.DELETE;
l_PFCBaseAmtTab.DELETE;
l_IPCBaseAmtTab.DELETE;
l_PFCGainAmtTab.DELETE;
l_PFCLossAmtTab.DELETE;
l_PFCAccruedAmtTab.DELETE;
l_IPCBilledAmtTab.DELETE;
l_RetnLevelTab.DELETE;
l_CustomerIdTab.DELETE;
G_RevalCompTab.DELETE;
G_RevalCompTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_AgreementIdTab.DELETE;
l_TaskIdTab.DELETE;
l_FCCurrTab.DELETE;
l_PCCurrTab.DELETE;
l_PFCCurrTab.DELETE;
l_IPCCurrTab.DELETE;
l_FCBaseAmtTab.DELETE;
l_PFCBaseAmtTab.DELETE;
l_IPCBaseAmtTab.DELETE;
l_PFCGainAmtTab.DELETE;
l_PFCLossAmtTab.DELETE;
l_PFCAccruedAmtTab.DELETE;
l_IPCBilledAmtTab.DELETE;
l_RetnLevelTab.DELETE;
l_CustomerIdTab.DELETE;
Insert_distribution_warnings(
p_project_id => G_ProjLvlGlobRec.project_id,
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
G_InvCompTab.DELETE;
G_RetnApplAmtTab.DELETE;
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
di.draft_invoice_num,
dii.projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference,
di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND di.transfer_status_code = 'A'
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code,
di.system_reference,di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y');
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
di.draft_invoice_num drft_inv_num,
dii.projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference,
di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND di.transfer_status_code = 'A'
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code,
di.system_reference,di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT dii_mc.set_of_books_id,
di.draft_invoice_num drft_inv_num,
dii_mc.currency_code projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference,
di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
FROM pa_draft_invoice_items dii, pa_mc_draft_inv_items dii_mc, pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND di.transfer_status_code = 'A'
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii_mc.set_of_books_id, dii_mc.currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference,di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
)
ORDER BY drft_inv_num, set_of_books_id; */
l_RetnInvTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_DraftInvNumTab.DELETE;
l_PFCCurrTab.DELETE;
l_FCCurrTab.DELETE;
l_ITCCurrTab.DELETE;
l_SysRefTab.DELETE;
l_CancelFlgTab.DELETE;
l_CrMemoFlgTab.DELETE;
l_WrOffFlgTab.DELETE;
l_ClCrMemoFlgTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_RetnInvTab.DELETE;
l_RetnInvTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_DraftInvNumTab.DELETE;
l_PFCCurrTab.DELETE;
l_FCCurrTab.DELETE;
l_ITCCurrTab.DELETE;
l_SysRefTab.DELETE;
l_CancelFlgTab.DELETE;
l_ClCrMemoFlgTab.DELETE;
l_WrOffFlgTab.DELETE;
l_CrMemoFlgTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_ArAmtsTab.DELETE;
/* The following CURSOR will select all invoices for given project/agreement for
primary set of book id and will get executed for
a) project level funding/project level retention - p_task_id will be zero
b) project level funding/task level retention -p_task_id will be zero
All task level retention amounts will be summarized to project level funding amounts
*/
CURSOR get_proj_invoices IS
(SELECT 'REGULAR-PROJ' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
0 task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.released_date is not NULL /* Added for Bug 9453661 */
AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference, di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'RETENTION-PROJ' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
0 task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.released_date is not NULL /* Added for Bug 9453661 */
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference, di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y'))
ORDER BY drft_inv_num;
/* The following CURSOR will select all invoices for given project/agreement for
primary and reporting set of book ids */
/* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
new table gl_alc_ledger_rships_v and corresponding columns */
/* mrc migration to SLA bug 4571438
CURSOR get_all_proj_invoices IS
(SELECT 'REGULAR-PROJ' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
0 task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'RETENTION-PROJ' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
0 task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code,
dii.projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference,
di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'REGULAR-PROJ' invoice_type,
dii_mc.set_of_books_id,
0 task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code,
dii_mc.currency_code projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference,
di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,dii_mc.set_of_books_id,
dii.invproc_currency_code, dii_mc.currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'RETENTION-PROJ' invoice_type,
dii_mc.set_of_books_id,
0 task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code,
dii_mc.currency_code projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference,
di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = di.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,dii_mc.set_of_books_id,
dii.invproc_currency_code, dii_mc.currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference, di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
DECOde(di.draft_invoice_num_credited, NULL, 'N', 'Y')
)
ORDER BY drft_inv_num, set_of_books_id; */
/* The following CURSOR will select all invoices for given project/agreement for
primary set of book id and will get executed for
a) task level funding/task level retention
In the case of task level funding /project level retention, only the regular invoices will get selected
Also the retained amount for the task is obtained separately from RDL.DII.ERDL. To eliminate project
level retention lines from regular invoice, nvl(dii.task_id) <> 0 is added
Project level Retention invoices are not required as the retained amount will be calculated for each invoice
from RDL ERDL DII. These are eliminated by the check nvl(dii.task_id,0) <> 0
*/
CURSOR get_task_invoices IS
(SELECT 'REGULAR-TASK' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
dii.task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
/*
The following is commented and changed as below.
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
Standard line amount is required to get the billed amount in PA
Retention line amount is required to get the Net line amount (standard - retention)
Net line amount is required as AR amounts (invoice level) are for net invoice amount
In order to get the task level (line level) AR amounts, invoice level AR amounts will be
prorated.
(invoice level AR amount / invoice level net amount) * task level net amount
*/
/* Commented for bug 2794334
sum(decode(dii.invoice_line_type,'STANDARD',
dii.amount,0)) amount,
sum(decode(dii.invoice_line_type,'STANDARD',
dii.projfunc_bill_amount,0)) projfunc_bill_amount,
sum(decode(dii.invoice_line_type,'STANDARD',
dii.funding_bill_amount,0)) funding_bill_amount,
sum(decode(dii.invoice_line_type,'STANDARD',
dii.inv_amount,0)) inv_amount,
*/
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.amount)) amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.projfunc_bill_amount)) projfunc_bill_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.funding_bill_amount)) funding_bill_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.inv_amount)) inv_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.amount,0)) retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.projfunc_bill_amount,0)) projfunc_retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.funding_bill_amount,0)) funding_retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.inv_amount,0)) inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.released_date is not NULL /* Added for Bug 9453661 */
AND nvl(dii.task_id,0) <> 0
-- AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii.task_id,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'RETENTION-TASK' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
dii.task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND NVL(dii.task_id,0) <> 0
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.released_date is not NULL /* Added for Bug 9453661 */
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii.task_id,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference, di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y'))
ORDER BY drft_inv_num, task_id;
/* This CURSOR is same as previous CURSOR except that ti will select for both primary and reporting set of
books isd */
/* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
new table gl_alc_ledger_rships_v and corresponding columns */
/* mrc migration to SLA bug 4571438 CURSOR get_all_task_invoices IS
(SELECT 'REGULAR-TASK' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
dii.task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.amount)) amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.projfunc_bill_amount)) projfunc_bill_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.funding_bill_amount)) funding_bill_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.inv_amount)) inv_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.amount,0)) retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.projfunc_bill_amount,0)) projfunc_retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.funding_bill_amount,0)) funding_retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.inv_amount,0)) inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND nvl(dii.task_id,0) <> 0
-- AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii.task_id,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'RETENTION-TASK' invoice_type,
PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
dii.task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code, dii.projfunc_currency_code, dii.funding_currency_code,
di.inv_currency_code, di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND NVL(dii.task_id,0) <> 0
AND dii.draft_invoice_num = di.draft_invoice_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num, dii.task_id,
dii.invproc_currency_code,dii.projfunc_currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'REGULAR-TASK' invoice_type,
dii_mc.set_of_books_id,
dii.task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code,
dii_mc.currency_code projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.amount)) amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii_mc.amount)) projfunc_bill_amount, -- changed dii.projfunc_bill_amount to dii_mc.amount bug2827328
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.funding_bill_amount)) funding_bill_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
0, dii.inv_amount)) inv_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.amount,0)) retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii_mc.amount,0)) projfunc_retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.funding_bill_amount,0)) funding_retn_amount,
sum(decode(dii.invoice_line_type,'RETENTION',
dii.inv_amount,0)) inv_retn_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND nvl(dii.task_id,0) <> 0
-- AND dii.invoice_line_type <> 'RETENTION'
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,dii.task_id, dii_mc.set_of_books_id,
dii.invproc_currency_code, dii_mc.currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference , di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
UNION
SELECT 'RETENTION-TASK' invoice_type,
dii_mc.set_of_books_id,
dii.task_id,
di.draft_invoice_num drft_inv_num,
dii.invproc_currency_code,
dii_mc.currency_code projfunc_currency_code,
dii.funding_currency_code,
di.inv_currency_code,
di.system_reference, di.transfer_status_code,
nvl(di.canceled_flag, 'N') canceled_flag,
nvl(di.cancel_credit_memo_flag, 'N') cancel_credit_memo_flag,
nvl(di.write_off_flag, 'N') write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y') credit_memo_flag,
sum(dii.amount) amount,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount,
0 retn_amount,
0 projfunc_retn_amount,
0 funding_retn_amount,
0 inv_retn_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND NVL(dii.task_id,0) <> 0
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'Y'
AND di.invoice_date <= G_THRU_DATE
GROUP BY di.draft_invoice_num,dii.task_id, dii_mc.set_of_books_id,
dii.invproc_currency_code, dii_mc.currency_code,
dii.funding_currency_code, di.inv_currency_code, di.system_reference, di.transfer_status_code,
canceled_flag, cancel_credit_memo_flag, write_off_flag,
decode(di.draft_invoice_num_credited, NULL, 'N', 'Y')
)
ORDER BY drft_inv_num, task_id,set_of_books_id; */
l_InvTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_InvTypeTab.DELETE;
l_DraftInvNumTab.DELETE;
l_TaskIdTab.DELETE;
l_IPCCurrTab.DELETE;
l_PFCCurrTab.DELETE;
l_FCCurrTab.DELETE;
l_ITCCurrTab.DELETE;
l_SysRefTab.DELETE;
l_StatusCodeTab.DELETE;
l_CancelFlgTab.DELETE;
l_ClCrMemoFlgTab.DELETE;
l_WrOffFlgTab.DELETE;
l_CrMemoFlgTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_RetnAmtIPCTab.DELETE;
l_RetnAmtPFCTab.DELETE;
l_RetnAmtFCTab.DELETE;
l_RetnAmtITCTab.DELETE;
l_InvTab.DELETE;
l_InvTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_InvTypeTab.DELETE;
l_DraftInvNumTab.DELETE;
l_TaskIdTab.DELETE;
l_IPCCurrTab.DELETE;
l_PFCCurrTab.DELETE;
l_FCCurrTab.DELETE;
l_ITCCurrTab.DELETE;
l_SysRefTab.DELETE;
l_StatusCodeTab.DELETE;
l_CancelFlgTab.DELETE;
l_ClCrMemoFlgTab.DELETE;
l_WrOffFlgTab.DELETE;
l_CrMemoFlgTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_RetnAmtIPCTab.DELETE;
l_RetnAmtPFCTab.DELETE;
l_RetnAmtFCTab.DELETE;
l_RetnAmtITCTab.DELETE;
l_InvTab.DELETE;
l_InvTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_InvTypeTab.DELETE;
l_DraftInvNumTab.DELETE;
l_TaskIdTab.DELETE;
l_IPCCurrTab.DELETE;
l_PFCCurrTab.DELETE;
l_FCCurrTab.DELETE;
l_ITCCurrTab.DELETE;
l_SysRefTab.DELETE;
l_StatusCodeTab.DELETE;
l_CancelFlgTab.DELETE;
l_ClCrMemoFlgTab.DELETE;
l_WrOffFlgTab.DELETE;
l_CrMemoFlgTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_RetnAmtIPCTab.DELETE;
l_RetnAmtPFCTab.DELETE;
l_RetnAmtFCTab.DELETE;
l_RetnAmtITCTab.DELETE;
l_InvTab.DELETE;
l_InvTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_InvTypeTab.DELETE;
l_DraftInvNumTab.DELETE;
l_TaskIdTab.DELETE;
l_IPCCurrTab.DELETE;
l_PFCCurrTab.DELETE;
l_FCCurrTab.DELETE;
l_ITCCurrTab.DELETE;
l_SysRefTab.DELETE;
l_StatusCodeTab.DELETE;
l_CancelFlgTab.DELETE;
l_ClCrMemoFlgTab.DELETE;
l_WrOffFlgTab.DELETE;
l_CrMemoFlgTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_RetnAmtIPCTab.DELETE;
l_RetnAmtPFCTab.DELETE;
l_RetnAmtFCTab.DELETE;
l_RetnAmtITCTab.DELETE;
l_ArAmtsTab.DELETE;
l_AdjTotTab.DELETE;
l_InvTotTab.DELETE;
l_RetainedAmtTab.DELETE;
i_conversion_type_tab.DELETE;
i_to_currency_tab.DELETE;
i_from_currency_tab.DELETE;
i_amount_tab.DELETE;
i_user_validate_flag_tab.DELETE;
i_converted_amount_tab.DELETE;
i_denominator_tab.DELETE;
i_numerator_tab.DELETE;
i_rate_tab.DELETE;
i_status_tab.DELETE;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => i_status_tab(1),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
i_conversion_type_tab.DELETE;
i_to_currency_tab.DELETE;
i_from_currency_tab.DELETE;
i_amount_tab.DELETE;
i_user_validate_flag_tab.DELETE;
i_converted_amount_tab.DELETE;
i_denominator_tab.DELETE;
i_numerator_tab.DELETE;
i_rate_tab.DELETE;
i_status_tab.DELETE;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => i_status_tab(1),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
FROM pa_draft_invoice_items dii,pa_draft_invoices di
WHERE dii.project_id = p_project_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = p_draft_inv_num
AND di.draft_invoice_num = dii.draft_invoice_num
AND di.released_date is not NULL ; /* Added for Bug 9453661 */
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_inv_num
--AND dii.invoice_line_type <> 'RETENTION'
UNION
SELECT dii_mc.set_of_books_id set_of_books_id,
sum(dii.amount) amount,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount,
sum(dii.inv_amount) inv_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_inv_num
-- AND dii.invoice_line_type <> 'RETENTION'
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
GROUP BY dii_mc.set_of_books_id
)
ORDER by set_of_books_id; */
l_SetOfBookIdTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtITCTab.DELETE;
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SUM((rdl.retained_amount/rdl.bill_amount) * rdl.projfunc_bill_amount) retained_amt_pfc,
SUM((rdl.retained_amount/rdl.bill_amount) * rdl.funding_bill_amount) retained_amt_fc
FROM pa_cust_rev_dist_lines RDL, pa_draft_invoice_items DII,
pa_draft_invoices DI
WHERE DII.project_id = p_project_id
AND DI.project_id = DII.project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND DI.draft_invoice_num = DII.draft_invoice_num
AND DI.released_date is not NULL /* Added for Bug 9453661 */
AND NVL(DII.task_id,0) = p_task_id
AND RDL.project_id = DII.project_id
AND RDL.draft_invoice_num = DII.draft_invoice_num
AND RDL.draft_invoice_item_line_num = DII.line_num;
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SUM((rdl.retained_amount/rdl.bill_amount) * rdl.projfunc_bill_amount) retained_amt_pfc,
SUM((rdl.retained_amount/rdl.bill_amount) * rdl.funding_bill_amount) retained_amt_fc
FROM pa_cust_rev_dist_lines RDL, pa_draft_invoice_items DII
WHERE DII.project_id = p_project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND NVL(DII.task_id,0) = p_task_id
AND RDL.project_id = DII.project_id
AND RDL.draft_invoice_num = DII.draft_invoice_num
AND RDL.draft_invoice_item_line_num = DII.line_num
UNION
SELECT RDL_MC.set_of_books_id,
SUM((rdl.retained_amount/rdl.bill_amount) * rdl_mc.amount) retained_amt_pfc,
SUM((rdl.retained_amount/rdl.bill_amount) * rdl.funding_bill_amount) retained_amt_fc
FROM pa_cust_rev_dist_lines RDL, pa_draft_invoice_items DII, pa_mc_cust_rdl_all RDL_MC,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE DII.project_id = p_project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND NVL(DII.task_id,0) = p_task_id
AND RDL.project_id = DII.project_id
AND RDL.draft_invoice_num = DII.draft_invoice_num
AND RDL.draft_invoice_item_line_num = DII.line_num
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND RDL_MC.set_of_books_id =rep.ledger_id
AND RDL_MC.expenditure_item_id = RDL.expenditure_item_id
AND RDL_MC.line_num = RDL.line_num
GROUP by RDL_MC.set_of_books_id
)
ORDER by set_of_books_id; */
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SUM((erdl.retained_amount/erdl.amount) * erdl.projfunc_bill_amount) retained_amt_pfc,
SUM((erdl.retained_amount/erdl.amount) * erdl.funding_bill_amount) retained_amt_fc
FROM pa_cust_event_rdl_all ERDL, pa_draft_invoice_items DII,
pa_draft_invoices DI
WHERE DII.project_id = p_project_id
AND DI.project_id = DII.project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND DI.draft_invoice_num = DII.draft_invoice_num
AND DI.released_date is not NULL /* Added for Bug 9453661 */
AND NVL(DII.task_id,0) = p_task_id
AND ERDL.project_id = DII.project_id
AND ERDL.draft_invoice_num = DII.draft_invoice_num
AND ERDL.draft_invoice_item_line_num = DII.line_num;
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SUM((erdl.retained_amount/erdl.amount) * erdl.projfunc_bill_amount) retained_amt_pfc,
SUM((erdl.retained_amount/erdl.amount) * erdl.funding_bill_amount) retained_amt_fc
FROM pa_cust_event_rdl_all ERDL, pa_draft_invoice_items DII
WHERE DII.project_id = p_project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND NVL(DII.task_id,0) = p_task_id
AND ERDL.project_id = DII.project_id
AND ERDL.draft_invoice_num = DII.draft_invoice_num
AND ERDL.draft_invoice_item_line_num = DII.line_num
UNION
SELECT ERDL_MC.set_of_books_id,
SUM((erdl.retained_amount/erdl.amount) * ERDL_MC.amount) retained_amt_pfc,
SUM((erdl.retained_amount/erdl.amount) * erdl.funding_bill_amount) retained_amt_fc
FROM pa_cust_event_rdl_all ERDL, pa_draft_invoice_items DII,
pa_mc_cust_event_rdl_all ERDL_MC,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE DII.project_id = p_project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND NVL(DII.task_id,0) = p_task_id
AND ERDL.project_id = DII.project_id
AND ERDL.draft_invoice_num = DII.draft_invoice_num
AND ERDL.draft_invoice_item_line_num = DII.line_num
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND ERDL_MC.set_of_books_id =rep.ledger_id
AND ERDL_MC.project_id = ERDL.project_id
AND ERDL_MC.event_num = ERDL.event_num
AND NVL(ERDL_MC.task_id,0) = NVL(ERDL.task_id,0)
AND ERDL_MC.line_num = ERDL.line_num
GROUP by ERDL_MC.set_of_books_id
)
ORDER by set_of_books_id; */
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SUM((DII.retained_amount/DII.amount) * DII.projfunc_bill_amount) retained_amt_pfc,
SUM((DII.retained_amount/DII.amount) * DII.funding_bill_amount) retained_amt_fc
FROM pa_draft_invoice_items DII,pa_draft_invoices DI
WHERE DII.project_id = p_project_id
AND DI.project_id = DII.project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND DI.draft_invoice_num = DII.draft_invoice_num
AND DI.released_date is not NULL /* Added for Bug 9453661 */
AND DII.task_id = p_task_id
AND DII.event_num is not null;
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
SUM((DII.retained_amount/DII.amount) * DII.projfunc_bill_amount) retained_amt_pfc,
SUM((DII.retained_amount/DII.amount) * DII.funding_bill_amount) retained_amt_fc
FROM pa_draft_invoice_items DII
WHERE DII.project_id = p_project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND DII.event_num is not null
AND DII.task_id = p_task_id
UNION
SELECT DII_MC.set_of_books_id,
SUM((DII.retained_amount/DII.amount) * DII_MC.amount) retained_amt_pfc,
SUM((DII.retained_amount/DII.amount) * DII.funding_bill_amount) retained_amt_fc
FROM pa_draft_invoice_items DII, pa_mc_draft_inv_items DII_MC,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE DII.project_id = p_project_id
AND DII.draft_invoice_num = p_draft_inv_num
AND DII.task_id = p_task_id
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND DII_MC.set_of_books_id =rep.ledger_id
AND DII_MC.project_id = DII.project_id
AND DII_MC.draft_invoice_num = DII.draft_invoice_num
AND DII_MC.line_num = DII.line_num
AND DII.event_num is not null
GROUP by DII_MC.set_of_books_id
)
ORDER by set_of_books_id; */
SELECT 'T' INTO l_RetnExistFlag
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_inv_num
AND dii.invoice_line_type = 'RETENTION');
l_SetOfBookIdTab.DELETE;
l_RetainedAmtPFCTab.DELETE;
l_RetainedAmtFCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_RetainedAmtPFCTab.DELETE;
l_RetainedAmtFCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_RetainedAmtPFCTab.DELETE;
l_RetainedAmtFCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_RetainedAmtPFCTab.DELETE;
l_RetainedAmtFCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_RetainedAmtPFCTab.DELETE;
l_RetainedAmtFCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_RetainedAmtPFCTab.DELETE;
l_RetainedAmtFCTab.DELETE;
/* The following CURSOR will select all invoices for given project/agreement for
primary set of book id and will get executed for
a) project level funding
*/
CURSOR get_proj_invoices IS
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
0 task_id,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.released_date is not NULL /* Added for Bug 9453661 */
AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE;
/* The following CURSOR will select all invoices for given project/agreement for
primary and reporting set of book ids */
/* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
new table gl_alc_ledger_rships_v and corresponding columns */
/* mrc migration to SLA bug 4571438 CURSOR get_all_proj_invoices IS
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
0 task_id,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
UNION
SELECT dii_mc.set_of_books_id,
0 task_id,
sum(dii.amount) amount,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii,
pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY dii_mc.set_of_books_id
)
ORDER BY set_of_books_id; */
/* The following CURSOR will select all invoices for given project/agreement for
primary set of book id and will get executed for
a) task level funding
*/
CURSOR get_task_invoices IS
SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
dii.task_id,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.released_date is not NULL /* Added for Bug 9453661 */
AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY dii.task_id
ORDER BY task_id;
/* This CURSOR is same as previous CURSOR except that ti will select for both primary and reporting set of
books isd */
/* mrc migration to SLA bug 4571438 CURSOR get_all_task_invoices IS
(SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
dii.task_id,
sum(dii.amount) amount,
sum(dii.projfunc_bill_amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY dii.task_id
UNION
SELECT dii_mc.set_of_books_id,
dii.task_id,
sum(dii.amount) amount,
sum(dii_mc.amount) projfunc_bill_amount,
sum(dii.funding_bill_amount) funding_bill_amount
FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
gl_alc_ledger_rships_v rep, pa_implementations imp
WHERE di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND rep.source_ledger_id = imp.set_of_books_id
AND rep.relationship_enabled_flag = 'Y'
AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
AND rep.application_id = 275
AND dii_mc.set_of_books_id =rep.ledger_id
AND dii_mc.project_id = dii.project_id
AND dii_mc.draft_invoice_num = dii.draft_invoice_num
AND dii_mc.line_num = dii.line_num
AND nvl(di.retention_invoice_flag, 'N') = 'N'
AND di.invoice_date <= G_THRU_DATE
GROUP BY dii.task_id, dii_mc.set_of_books_id
)
ORDER BY task_id,set_of_books_id; */
l_SetOfBookIdTab.DELETE;
l_TaskIdTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_TaskIdTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_TaskIdTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtIPCTab.DELETE;
l_SetOfBookIdTab.DELETE;
l_TaskIdTab.DELETE;
l_BillAmtPFCTab.DELETE;
l_BillAmtFCTab.DELETE;
l_BillAmtIPCTab.DELETE;
SELECT NVL(org_id,-99) INTO l_OrgId FROM PA_IMPLEMENTATIONS;
l_from_currency_tab.DELETE;
l_to_currency_tab.DELETE;
l_conversion_date_tab.DELETE;
l_conversion_type_tab.DELETE;
l_amount_tab.DELETE;
l_user_validate_flag_tab.DELETE;
l_converted_amount_tab.DELETE;
l_denominator_tab.DELETE;
l_numerator_tab.DELETE;
l_rate_tab.DELETE;
l_status_tab.DELETE;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => l_status_tab(1),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
l_from_currency_tab.DELETE;
l_to_currency_tab.DELETE;
l_conversion_date_tab.DELETE;
l_conversion_type_tab.DELETE;
l_amount_tab.DELETE;
l_user_validate_flag_tab.DELETE;
l_converted_amount_tab.DELETE;
l_denominator_tab.DELETE;
l_numerator_tab.DELETE;
l_rate_tab.DELETE;
l_status_tab.DELETE;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => l_status_tab(1),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
l_from_currency_tab.DELETE;
l_to_currency_tab.DELETE;
l_conversion_date_tab.DELETE;
l_conversion_type_tab.DELETE;
l_amount_tab.DELETE;
l_user_validate_flag_tab.DELETE;
l_converted_amount_tab.DELETE;
l_denominator_tab.DELETE;
l_numerator_tab.DELETE;
l_rate_tab.DELETE;
l_status_tab.DELETE;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => l_status_tab(1),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
l_from_currency_tab.DELETE;
l_to_currency_tab.DELETE;
l_conversion_date_tab.DELETE;
l_conversion_type_tab.DELETE;
l_amount_tab.DELETE;
l_user_validate_flag_tab.DELETE;
l_converted_amount_tab.DELETE;
l_denominator_tab.DELETE;
l_numerator_tab.DELETE;
l_rate_tab.DELETE;
l_status_tab.DELETE;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => l_status_tab(1),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
insert the warning into distributions and
update the summary project funding with rejectionr reason as INVOICED_EXCEEDS_FUNDED
*/
/* Changes Start ------------------------------------ Commented for bug 3532963
IF (NVL(l_InvOvrFndFlag,'N') ='Y') AND (nvl(l_RevaluationIndex,1)=1) THEN /* Added AND condition for bug 3532963
ROLLBACK;
insert_rejection_reason_spf (
p_project_id => G_ProjLvlGlobRec.project_id,
p_agreement_id => p_agreement_id,
p_task_id => p_task_id,
p_reason_code => 'PA_FR_INVOICED_EXCEEDS_FUNDED',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
| Procedure : insert_rejection_reason_spf |
| Purpose : To insert rejection reason in SPF |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_agreement_id IN Agreement_id |
| p_task_id IN Task Id of summary project funding |
| p_reason_code IN Rejection reason code |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE insert_rejection_reason_spf(
p_project_id IN NUMBER,
p_agreement_id IN VARCHAR2,
p_task_id IN VARCHAR2,
p_reason_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(30) := NULL;
PA_DEBUG.g_err_stage := '-----------Entering PA_FUND_REVAL_PVT.insert_rejection_reason_spf-----------' ;
UPDATE pa_summary_project_fundings
SET reval_rejection_code = p_reason_code,
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN,
program_application_id = G_PROGRAM_APPLICATION_ID ,
program_id = G_PROGRAM_ID,
program_update_date= SYSDATE,
request_id= G_REQUEST_ID
WHERE project_id = p_project_id
AND agreement_id = p_agreement_id
AND nvl(task_id,0) = nvl(p_task_id,0);
Insert_distribution_warnings(
p_project_id => p_project_id,
p_task_id => p_task_id,
p_agreement_id => p_agreement_id,
p_reason_code => p_Reason_Code,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
PA_DEBUG.g_err_stage := '-----------Exiting PA_FUND_REVAL_PVT.insert_rejection_reason_spf-----------' ;
PA_DEBUG.g_err_stage := 'insert_rejection_reason_spf:' || x_msg_data ;
END insert_rejection_reason_spf;
SELECT 'Y' non_zero_amt
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM pa_project_fundings
WHERE project_funding_id = l_project_funding_id
AND ( projfunc_allocated_amount <> 0
OR projfunc_realized_gains_amt <> 0
OR projfunc_realized_losses_amt <> 0
OR invproc_allocated_amount <> 0)
/* mrc migration to SLA bug 4571438 UNION
SELECT NULL
FROM pa_mc_project_fundings
WHERE project_funding_id = l_project_funding_id
AND ( allocated_amount <> 0
OR realized_gains_amt <> 0
OR realized_losses_amt <> 0) */ );
/* Insert into table pa_project_fundings */
l_SobId := G_SET_OF_BOOKS_ID;
PA_PROJECT_FUNDINGS_PKG. Insert_Row (
X_Rowid => l_RowId ,
X_Project_Funding_Id => l_ProjectFundingId ,
X_Last_Update_Date => SYSDATE,
X_Last_Updated_By => G_LAST_UPDATED_BY ,
X_Creation_Date => SYSDATE ,
X_Created_By => G_LAST_UPDATED_BY ,
X_Last_Update_Login => G_LAST_UPDATE_LOGIN ,
X_Agreement_Id => G_RevalCompTab(l_SobId).agreement_id ,
X_Project_Id => G_RevalCompTab(l_SobId).project_id ,
X_Task_Id => G_RevalCompTab(l_SobId).task_id ,
X_Budget_Type_Code => 'DRAFT' ,
X_Allocated_Amount => 0 ,
X_Date_Allocated => G_RATE_DATE,
X_Attribute_Category => NULL ,
X_Attribute1 => NULL ,
X_Attribute2 => NULL ,
X_Attribute3 => NULL ,
X_Attribute4 => NULL ,
X_Attribute5 => NULL ,
X_Attribute6 => NULL ,
X_Attribute7 => NULL ,
X_Attribute8 => NULL ,
X_Attribute9 => NULL ,
X_Attribute10 => NULL ,
X_pm_funding_reference => NULL ,
X_pm_product_code => NULL ,
x_funding_currency_code => G_RevalCompTab(l_SobId).funding_currency_code ,
x_project_currency_code => G_RevalCompTab(l_SobId).project_currency_code ,
x_project_rate_type => NULL,
x_project_rate_date => NULL,
x_project_exchange_rate => NULL,
x_project_allocated_amount => NULL,
x_projfunc_currency_code => G_RevalCompTab(l_SobId).projfunc_currency_code ,
x_projfunc_rate_type => NULL,
x_projfunc_rate_date => NULL,
x_projfunc_exchange_rate => NULL,
x_projfunc_allocated_amount => G_RevalCompTab(l_SobId).projfunc_allocated_amount ,
x_invproc_currency_code => G_RevalCompTab(l_SobId).invproc_currency_code ,
x_invproc_rate_type => NULL,
x_invproc_rate_date => NULL,
x_invproc_exchange_rate => NULL,
x_invproc_allocated_amount => G_RevalCompTab(l_SobId).invproc_allocated_amount ,
x_revproc_currency_code => G_RevalCompTab(l_SobId).projfunc_currency_code ,
x_revproc_rate_type => G_RevalCompTab(l_SobId).reval_projfunc_rate_type ,
x_revproc_rate_date => NULL,
x_revproc_exchange_rate => NULL,
x_revproc_allocated_amount => G_RevalCompTab(l_SobId).projfunc_allocated_amount ,
x_funding_category => 'REVALUATION' ,
x_revaluation_through_date => G_THRU_DATE ,
x_revaluation_rate_date => G_RATE_DATE ,
x_reval_projfunc_rate_type => G_RevalCompTab(l_SobId).reval_projfunc_rate_type ,
x_revaluation_projfunc_rate => G_RevalCompTab(l_SobId).reval_projfunc_rate ,
x_reval_invproc_rate_type => G_RevalCompTab(l_SobId).reval_invproc_rate_type ,
x_revaluation_invproc_rate => G_RevalCompTab(l_SobId).reval_invproc_rate ,
x_funding_inv_applied_amount => G_RevalCompTab(l_SobId).funding_inv_applied_amount,
x_funding_inv_due_amount => G_RevalCompTab(l_SobId).funding_inv_due_amount,
x_funding_backlog_amount => G_RevalCompTab(l_SobId).funding_backlog_amount,
x_projfunc_realized_gains_amt => G_RevalCompTab(l_SobId).projfunc_realized_gains_amt,
x_projfunc_realized_losses_amt => G_RevalCompTab(l_SobId).projfunc_realized_losses_amt,
x_projfunc_inv_applied_amount => G_RevalCompTab(l_SobId).projfunc_inv_applied_amount,
x_projfunc_inv_due_amount => G_RevalCompTab(l_SobId).projfunc_inv_due_amount,
x_projfunc_backlog_amount => G_RevalCompTab(l_SobId).projfunc_backlog_amount,
x_non_updateable_flag => 'Y',
x_invproc_backlog_amount => G_RevalCompTab(l_SobId).invproc_backlog_amount,
x_funding_reval_amount => G_RevalCompTab(l_SobId).funding_reval_amount,
x_projfunc_reval_amount => G_RevalCompTab(l_SobId).projfunc_reval_amount,
x_projfunc_revalued_amount => G_RevalCompTab(l_SobId).projfunc_revalued_amount,
x_invproc_reval_amount => G_RevalCompTab(l_SobId).invproc_reval_amount,
x_invproc_revalued_amount => G_RevalCompTab(l_SobId).invproc_revalued_amount,
x_funding_revaluation_factor => G_RevalCompTab(l_SobId).funding_revaluation_factor,
x_request_id => G_REQUEST_ID,
x_program_application_id => G_PROGRAM_APPLICATION_ID,
x_program_id => G_PROGRAM_ID,
x_program_update_date => SYSDATE);
l_LogMsg := 'After Insert Project Funding Id:' || l_ProjectFundingId;
UPDATE pa_summary_project_fundings
SET projfunc_unbaselined_amount =
nvl(projfunc_unbaselined_amount,0) + G_RevalCompTab(l_SobId).projfunc_allocated_amount,
revproc_unbaselined_amount =
nvl(revproc_unbaselined_amount,0) + G_RevalCompTab(l_SobId).projfunc_allocated_amount,
invproc_unbaselined_amount =
nvl(invproc_unbaselined_amount,0) + G_RevalCompTab(l_SobId).invproc_allocated_amount,
projfunc_realized_gains_amt =
nvl(projfunc_realized_gains_amt,0) + G_RevalCompTab(l_SobId).projfunc_realized_gains_amt,
projfunc_realized_losses_amt =
nvl(projfunc_realized_losses_amt,0) + G_RevalCompTab(l_SobId).projfunc_realized_losses_amt,
reval_rejection_code = NULL
WHERE project_id = G_RevalCompTab(l_SobId).project_id
AND agreement_id = G_RevalCompTab(l_SobId).agreement_id
AND nvl(task_id,0) = nvl(G_RevalCompTab(l_SobId).task_id,0);
insert_event_record (
p_project_id => G_RevalCompTab(l_SobId).project_id,
p_task_id => G_RevalCompTab(l_SobId).task_id,
p_event_type => G_ProjLvlGlobRec.gain_event_type,
p_event_desc => G_ProjLvlGlobRec.gain_event_type,
p_Bill_trans_rev_amount => G_RevalCompTab(l_SobId).projfunc_realized_gains_amt ,
p_project_funding_id => l_ProjectFundingId,
p_agreement_id => G_RevalCompTab(l_SobId).agreement_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
insert_event_record (
p_project_id => G_RevalCompTab(l_SobId).project_id,
p_task_id => G_RevalCompTab(l_SobId).task_id,
p_event_type => G_ProjLvlGlobRec.loss_event_type,
p_event_desc => G_ProjLvlGlobRec.loss_event_type,
p_Bill_trans_rev_amount => G_RevalCompTab(l_SobId).projfunc_realized_losses_amt ,
p_project_funding_id => l_ProjectFundingId,
p_agreement_id => G_RevalCompTab(l_SobId).agreement_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
DELETE FROM pa_project_fundings
WHERE project_funding_id = l_ProjectFundingId;
| Procedure : insert_event_record |
| Purpose : To insert event record for gains/losses amount |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_task_id IN Task Id of summary project funding |
| p_event_type IN Event type (gain/loss event type ) |
| p_event_desc IN Event description (gain/loss event description ) |
| p_bill_trans_rev_amount IN Amount (Realized gain/loss amount) |
| p_project_funding_id IN Funding line Id for which this event is created |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE insert_event_record(
p_project_id IN NUMBER,
p_task_id IN NUMBER,
p_event_type IN VARCHAR2,
p_event_desc IN VARCHAR2,
p_Bill_trans_rev_amount IN NUMBER,
p_project_funding_id IN NUMBER,
p_agreement_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_RowId VARCHAR2(30);
SELECT 'Y' non_zero_amt
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM pa_events
WHERE event_id = l_EventId
AND bill_trans_rev_amount <> 0
/* mrc migration to SLA bug 4571438 UNION
SELECT NULL
FROM pa_mc_events
WHERE event_id = l_EventId
AND revenue_amount <> 0 */ );
PA_DEBUG.g_err_stage := '-----------Entering PA_FUND_REVAL_PVT.insert_event_record-----------' ;
zero dollar events in all reporting set of books. If zero, the record(s) will be deleted */
IF ((p_bill_trans_rev_amount <> 0) OR (G_PRIMARY_ONLY = 'N')) THEN
SELECT nvl(max(event_num),0) into l_EventNum
FROM pa_events
WHERE project_id = G_RevalCompTab(l_SobId).project_id
AND nvl(task_id,0) = nvl(G_RevalCompTab(l_SobId).task_id,0);
pa_events_pkg.insert_row (
X_Rowid => l_RowId ,
X_Event_Id => l_EventId ,
X_Task_Id => p_task_id ,
X_Event_Num => l_EventNum ,
X_Last_Update_Date => SYSDATE ,
X_Last_Updated_By => G_LAST_UPDATED_BY ,
X_Creation_Date => SYSDATE ,
X_Created_By => G_LAST_UPDATED_BY ,
X_Last_Update_Login => G_LAST_UPDATE_LOGIN ,
X_Event_Type => p_event_type ,
X_Description => p_event_desc ,
X_Bill_Amount => 0 ,
X_Revenue_Amount => 0 ,
X_Revenue_Distributed_Flag => 'N' ,
X_Zero_Revenue_Amount_Flag => l_ZeroRevAmtFlag,
X_Bill_Hold_Flag => 'N' ,
X_Completion_Date => G_RATE_DATE ,
X_Rev_Dist_Rejection_Code => NULL ,
X_Attribute_Category => NULL ,
X_Attribute1 => NULL ,
X_Attribute2 => NULL ,
X_Attribute3 => NULL ,
X_Attribute4 => NULL ,
X_Attribute5 => NULL ,
X_Attribute6 => NULL ,
X_Attribute7 => NULL ,
X_Attribute8 => NULL ,
X_Attribute9 => NULL ,
X_Attribute10 => NULL ,
X_Project_Id => p_project_id ,
X_Organization_Id => G_ProjLvlGlobRec.carrying_out_organization_id ,
X_Billing_Assignment_Id => NULL ,
X_Event_Num_Reversed => NULL ,
X_Calling_Place => NULL ,
X_Calling_Process => NULL ,
X_Bill_Trans_Currency_Code => G_RevalCompTab(l_SobId).projfunc_currency_code ,
X_Bill_Trans_Bill_Amount => 0 , -- Changed from NULL for bug2829565
X_Bill_Trans_rev_Amount => p_bill_trans_rev_amount ,
X_Project_Currency_Code => G_RevalCompTab(l_SobId).project_currency_code ,
X_Project_Rate_Type => NULL ,
X_Project_Rate_Date => NULL ,
X_Project_Exchange_Rate => NULL ,
X_Project_Inv_Rate_Date => NULL ,
X_Project_Inv_Exchange_Rate => NULL ,
X_Project_Bill_Amount => NULL ,
X_Project_Rev_Rate_Date => NULL ,
X_Project_Rev_Exchange_Rate => NULL ,
X_Project_Revenue_Amount => NULL ,
X_ProjFunc_Currency_Code => G_RevalCompTab(l_SobId).projfunc_currency_code ,
X_ProjFunc_Rate_Type => NULL ,
X_ProjFunc_Rate_Date => NULL ,
X_ProjFunc_Exchange_Rate => NULL ,
X_ProjFunc_Inv_Rate_Date => NULL ,
X_ProjFunc_Inv_Exchange_Rate => NULL ,
X_ProjFunc_Bill_Amount => NULL ,
X_ProjFunc_Rev_Rate_Date => NULL ,
X_Projfunc_Rev_Exchange_Rate => NULL ,
X_ProjFunc_Revenue_Amount => NULL ,
X_Funding_Rate_Type => NULL ,
X_Funding_Rate_Date => NULL ,
X_Funding_Exchange_Rate => NULL ,
X_Invproc_Currency_Code => G_RevalCompTab(l_SobId).invproc_currency_code ,
X_Invproc_Rate_Type => NULL ,
X_Invproc_Rate_Date => NULL ,
X_Invproc_Exchange_Rate => NULL ,
X_Revproc_Currency_Code => G_RevalCompTab(l_SobId).projfunc_currency_code ,
X_Revproc_Rate_Type => NULL ,
X_Revproc_Rate_Date => NULL ,
X_Revproc_Exchange_Rate => NULL ,
X_Inv_Gen_Rejection_Code => NULL ,
X_Adjusting_Revenue_Flag => NULL ,
X_non_updateable_flag => 'Y' ,
X_revenue_hold_flag => 'Y' ,
X_project_funding_id => p_project_funding_id,
X_agreement_id => p_agreement_id);
DELETE FROM pa_events
WHERE event_id = l_EventId;
PA_DEBUG.g_err_stage := '-----------Exiting PA_FUND_REVAL_PVT.insert_event_record-----------' ;
PA_DEBUG.g_err_stage := 'insert_event_record:' || x_msg_data ;
END insert_event_record;
| Purpose : To delete any rejection reason that is logged by revaluation process |
| for the request id |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_request_id IN Request ID |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE clear_distribution_warnings(
p_request_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(30) := NULL;
DELETE from pa_distribution_warnings
WHERE request_id = p_request_id;
Insert_distribution_warnings(
p_project_id => G_RevalCompTab(l_SobId).project_id,
p_agreement_id => G_RevalCompTab(l_SobId).agreement_id,
p_task_id => G_RevalCompTab(l_SobId).task_id,
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
Insert_distribution_warnings(
p_project_id => G_RevalCompTab(l_SobId).project_id,
p_agreement_id => G_RevalCompTab(l_SobId).agreement_id,
p_task_id => G_RevalCompTab(l_SobId).task_id,
p_reason_code => l_ReasonCode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
| Procedure : Get_Delete_Projects |
| Purpose : To open all projects eligible for funding revaluation and has |
| unbaselined adjustment lines |
| given project numbers |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_project_type_id IN Project Type ID |
| p_from_proj_number IN Start project number |
| p_to_proj_number IN End project number |
| p_run_mode IN Run mode |
| Values are 'SINGLE', 'RANGE' |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE get_delete_projects(
p_project_type_id IN NUMBER,
p_from_proj_number IN VARCHAR2,
p_to_proj_number IN VARCHAR2,
p_run_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
/* This CURSOR selects all projects with the following criteria
a) should be contract projects
b) revaluate_funding_flag is enabled
c) has unbaselined adjustments */
CURSOR open_projects IS
SELECT P.segment1, P.project_id, P.baseline_funding_flag,
P.include_gains_losses_flag include_gains_losses_flag
FROM pa_projects P, pa_project_types T
WHERE P.segment1 BETWEEN p_from_proj_number
AND p_to_proj_number
AND P.PROJECT_TYPE = T.PROJECT_TYPE
AND T.DIRECT_FLAG = 'Y'
AND T.PROJECT_TYPE_ID = NVL(P_PROJECT_TYPE_ID ,T.project_type_id)
AND NVL(P.revaluate_funding_flag, 'N') = 'Y'
AND NVL(P.template_flag, 'N') = 'N'
AND exists ( SELECT NULL
FROM pa_project_fundings
WHERE project_id = P.project_id
AND funding_category = 'REVALUATION'
AND budget_type_code = 'DRAFT')
ORDER BY segment1 ;
PA_DEBUG.g_err_stage := '-----------Entering PA_FUND_REVAL_PVT.get_delete_projects-----------' ;
Delete_unbaselined_adjmts(
p_project_id => proj_rec.project_id,
p_run_mode => p_run_mode,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
COMMIT; /* Commit delete for the current project */
PA_DEBUG.g_err_stage := '-----------Exiting PA_FUND_REVAL_PVT.get_delete_projects-----------' ;
END get_delete_projects;