[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select be.procedure_name proc_name, bea.billing_assignment_id bea_id,
bea.billing_extension_id be_id, bea.top_task_id task_id,
decode(be.amount_reqd_flag, 'Y', nvl(bea.amount, 0), 0) amt,
decode(be.percentage_reqd_flag, 'Y', nvl(bea.percentage, 0), 0)
percent
from pa_billing_extensions be, pa_billing_assignments bea -- , Commented for bug 3643409
-- pa_projects p Commented for bug 3643409
where -- p.project_id = x_project_id Commented for bug 3643409
-- and Commented for bug 3643409
bea.active_flag = 'Y'
and bea.billing_extension_id = be.billing_extension_id
and (be.calling_process = x_calling_process
or be.calling_process = 'Both')
and (bea.project_id = X_project_id
or bea.project_type = l_project_type
or bea.distribution_rule = l_distribution_rule)
-- Added above two lines for bug 3643409
-- or bea.project_type = p.project_type Commented for bug 3643409
-- or bea.distribution_rule = p.distribution_rule) Commented for bug 3643409
and
(
( x_calling_place = 'PRE' and nvl(be.pre_processing_flag,'N') = 'Y')
or ( x_calling_place = 'POST' and nvl(be.post_processing_flag,'N')= 'Y')
or ( x_calling_place = 'DEL' and nvl(be.call_before_del_flag,'N')= 'Y')
or ( x_calling_place = 'CANCEL' and nvl(be.call_after_cancel_inv_flag,'N')= 'Y')
or ( x_calling_place = 'WRITE-OFF' and nvl(be.call_after_woff_inv_flag,'N')= 'Y')
or ( x_calling_place = 'CONCESSION' and nvl(be.call_after_concession_inv_flag,'N')= 'Y') -- Added this line for Concession Invoice
or
(
( x_calling_place = 'ADJ' and nvl(be.call_after_adj_flag,'N')= 'Y')
or ( x_calling_place = 'REG' and nvl(be.call_after_reg_flag,'N')= 'Y')
or ( x_calling_place = 'POST-REG' and nvl(be.call_post_reg_flag,'N')= 'Y')
and
( nvl(be.trx_independent_flag, 'N') = 'Y'
or
( x_calling_process in ('Invoice','Both')
AND EXISTS
(select NULL from pa_draft_invoices pdi
where pdi.project_id = x_project_id
and pdi.request_id = x_request_id
-- and pdi.invoice_line_type <> 'NET ZERO ADJUSTMENT'
and (( x_calling_place = 'ADJ'
and pdi.draft_invoice_num_credited is not null)
OR
( x_calling_place IN ('REG' , 'POST-REG')
and pdi.draft_invoice_num_credited IS NULL)))
)
or
( x_calling_process in ('Revenue','Both')
AND EXISTS
(select NULL from pa_draft_revenues pdr
where pdr.project_id = x_project_id
and pdr.request_id = x_request_id
and (( x_calling_place = 'ADJ'
and pdr.draft_revenue_num_credited is not null)
OR
( x_calling_place IN ('REG','POST-REG')
and pdr.draft_revenue_num_credited IS NULL)))
)
)
)
)
order by be.processing_order, bea.billing_assignment_id;
SELECT distinct
decode(fund_level,
'PROJECT', decode(X2_task_id, NULL, NULL, X2_task_id),
'TASK', t.top_task_id,
t.top_task_id) tpid
FROM pa_tasks t
WHERE t.project_id = X_project_id
AND t.task_id = nvl(X2_task_id, t.task_id)
AND t.ready_to_distribute_flag =
decode(x_calling_process, 'Revenue', 'Y', 'Both', 'Y',
t.ready_to_distribute_flag)
AND t.ready_to_bill_flag =
decode(x_calling_process, 'Invoice', 'Y', 'Both', 'Y',
t.ready_to_bill_flag);
/* Added below select statement for bug 3643409 */
SELECT project_type,distribution_rule
INTO l_project_type,l_distribution_rule
FROM pa_projects_all
WHERE project_id = x_project_id;
PA_MCB_INVOICE_PKG.log_message('Before select of pa billing params v pa_billing.ccrev :');
SELECT default_cost_plan_type_id,default_rev_plan_type_id
INTO l_cost_plan_type_id,l_rev_plan_type_id
FROM pa_billing_extn_params_v;
PA_MCB_INVOICE_PKG.log_message('Rev part Before insert pa_billing.ccrev.insert_event 1 :'||to_char(Revenue));
pa_billing_pub.insert_event (
X_rev_amt => Revenue,
X_bill_amt => 0,
X_event_description => event_description,
X_audit_amount1 => amount_left,
X_audit_amount2 => revenue_amount,
X_audit_amount3 => budget_revenue,
X_audit_amount4 => event_revenue,
X_audit_amount5 => budget_cost,
X_audit_amount6 => cost_amount,
X_audit_cost_budget_type_code => l_cost_budget_type_code,
X_audit_rev_budget_type_code => l_rev_budget_type_code,
X_audit_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
X_error_message => l_error_message,
X_status => l_status
);
PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event rev :'||to_char(revenue_amount));
PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event budget rev :'||to_char(budget_revenue));
PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event amt left :'||to_char(amount_left));
PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event evt rev :'||to_char(event_revenue));
PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event budget cost :'||to_char(budget_cost));
PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event cst amt :'||to_char(cost_amount));
PA_MCB_INVOICE_PKG.log_message('inv part before insert pa_billing.ccrev.insert_event inv 2 :'||to_char(Invoice));
pa_billing_pub.insert_event (
X_rev_amt => 0,
X_bill_amt => Invoice,
X_event_description => Event_Description,
X_audit_amount1 => amount_left,
X_audit_amount2 => invoice_amount,
X_audit_amount3 => budget_revenue,
X_audit_amount4 => event_invoice,
X_audit_amount5 => budget_cost,
X_audit_amount6 => cost_amount,
X_audit_cost_budget_type_code => l_cost_budget_type_code,
X_audit_rev_budget_type_code => l_rev_budget_type_code,
X_audit_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
X_error_message => l_error_message,
X_status => l_status
);
PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event inv 2 :'||to_char(invoice_amount));
PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event bud rev 2 :'||to_char(budget_revenue));
PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event amt lft 2 :'||to_char(amount_left));
PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event evt inv 2 :'||to_char(event_invoice));
PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event bd cst 2 :'||to_char(budget_cost));
PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event cst amt 2 :'||to_char(cost_amount));
PROCEDURE Delete_Automatic_Events ( X_Project_id NUMBER,
X_request_id NUMBER DEFAULT NULL,
X_rev_inv_num NUMBER DEFAULT NULL,
X_calling_process VARCHAR2) IS
g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.Delete_Automatic_Events :');
DELETE from pa_events v
WHERE v.project_id = X_project_id
AND v.request_id+0 = X_request_id
AND (v.project_id, nvl(v.task_id, -1), v.event_num) IN
(SELECT l.project_id, nvl(l.task_id, -1), l.event_num
FROM pa_cust_event_rev_dist_lines l
WHERE l.project_id = X_project_id
AND l.line_num_reversed is null
AND l.draft_revenue_num = X_rev_inv_num)
AND EXISTS
(SELECT vt.event_type
FROM pa_event_types vt
WHERE vt.event_type_classification||'' = 'AUTOMATIC'
AND vt.event_type = v.event_type)
AND v.calling_process = X_calling_process;
PA_MCB_INVOICE_PKG.log_message('Deleted Revenue pa_billing.Delete_Automatic_Events :');
DELETE FROM PA_EVENTS V
WHERE V.Project_ID = X_project_id
AND (nvl(V.Task_ID, -1), V.Event_Num) IN
(select nvl(dii.Event_Task_ID, -1), dii.Event_Num
from pa_draft_invoice_items dii, pa_draft_invoices di
where di.Project_ID = X_project_id
and di.draft_invoice_num = X_rev_inv_num
and dii.Project_ID = di.Project_ID
and dii.draft_invoice_num = di.draft_invoice_num
and nvl(di.write_off_flag, 'N') = 'N')
AND V.Bill_Amount <> 0
AND V.calling_process = X_calling_process;
PA_MCB_INVOICE_PKG.log_message('Deleted Invoice pa_billing.Delete_Automatic_Events :');
PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.Delete_Automatic_Events :');
END Delete_Automatic_Events;
| Table. If there are discrepancies it updates the amounts |
| |
| Parameters are: |
| |
| X_Option : I - Update Only Invoice Amounts |
| R - Update Only Revenue Amounts |
| B - Update Both Revenue/Invoice Amounts |
| |
| X_proj_id : pa_projects.project_id |
| X_start_proj_num : Start project Number (pa_projects.segment1) |
| X_end_proj_num : End project Number (pa_projects.segment1) |
| |
| |
| Called from : PARGDR - Generate Draft Revenue |
| PAIGEN - Generate Draft Invoice |
| |
| Morg Orientation: Project Orientation. |
| |
| History: |
| 21-Mar-97 N. Chouhan Created |
| |
-----------------------------------------------------------------------------*/
PROCEDURE CHECK_SPF_AMOUNTS( X_option in varchar2,
X_proj_id in number,
X_start_proj_num in varchar2,
X_end_proj_num in varchar2) IS
l_project_id number;
| Cursor For Selecting AND Locking PA_PROJECTS TABLE |
--------------------------------------------------------------------------*/
/* CURSOR sel_proj is
SELECT project_id
FROM pa_projects
WHERE ( ( nvl(X_proj_id,0) <> 0
AND project_id = X_proj_id )
OR ( nvl(X_proj_id,0) = 0
AND segment1 between X_start_proj_num
and X_end_proj_num))
FOR UPDATE OF project_id; Commented for bug 3372249*/
SELECT project_id
FROM pa_projects
WHERE project_id = X_proj_id
FOR UPDATE OF project_id;
SELECT project_id
FROM pa_projects
WHERE segment1 between X_start_proj_num
and X_end_proj_num
FOR UPDATE OF project_id;
| Cursor For Selecting record having 0 accrued revenue |
--------------------------------------------------------------------------*/
CURSOR spf_acc_0 is
SELECT pf.agreement_id, pf.project_id, pf.task_id
FROM pa_summary_project_fundings pf
WHERE (pf.revproc_accrued_amount <> 0 /* MCB related changes */
/* The following added to fix bug 2249216 */
OR pf.PROJFUNC_ACCRUED_AMOUNT <> 0
OR pf.PROJECT_ACCRUED_AMOUNT <> 0
OR pf.TOTAL_ACCRUED_AMOUNT <> 0)
/* END fix bug 2249216 */
AND pf.project_id = l_project_id
AND NOT EXISTS
( SELECT null
FROM pa_draft_revenue_items dri,
pa_draft_revenues dr
WHERE dri.project_id = dr.project_id
AND dri.draft_revenue_num = dr.draft_revenue_num
AND ( nvl(pf.task_id,0) = 0
OR dri.task_id = pf.task_id )
AND dr.project_id = pf.project_id
AND dr.agreement_id+0 = pf.agreement_id);
| Cursor For Selecting record having bad accrued revenue data |
--------------------------------------------------------------------------*/
CURSOR spf_acc_amt is
SELECT pf.agreement_id, pf.project_id,
decode(p.project_level_funding_flag,'Y',0,pf.task_id) task_fund, /*Decode added for bug 3647592 */
sum(dri.amount) dri_amount, dri.revproc_currency_code,
sum(dri.projfunc_revenue_amount) dri_projfunc_amount,dri.projfunc_currency_code,
sum(dri.project_revenue_amount) dri_project_amount,dri.project_currency_code,
sum(dri.funding_revenue_amount) dri_funding_amount,dri.funding_currency_code
FROM pa_draft_revenue_items dri,
pa_draft_revenues dr,
pa_summary_project_fundings pf,
pa_projects p /* Added pa_projects for bug 3647592 */
WHERE dri.project_id = dr.project_id
AND dri.draft_revenue_num = dr.draft_revenue_num
AND ( (nvl(pf.task_id,0) = 0 AND nvl(p.project_level_funding_flag,'N')='Y')
OR dri.task_id = decode(p.project_level_funding_flag,'Y',0,pf.task_id) ) /* Added decode condition for bug 3647592 */
AND dr.project_id+0 = pf.project_id
AND dr.agreement_id = pf.agreement_id
AND pf.project_id = l_project_id
AND p.project_id = pf.project_id
AND exists (select 1 from pa_agreements paa where paa.agreement_id = pf.agreement_id
and dri.funding_currency_code = paa.agreement_currency_code) /* condition added for Bug 5956273*/
GROUP BY pf.agreement_id, pf.project_id, decode(p.project_level_funding_flag,'Y',0,pf.task_id),dri.revproc_currency_code,
dri.projfunc_currency_code,dri.project_currency_code,
dri.funding_currency_code; /* MCB related changes */
| Cursor For Selecting record having 0 billed amount |
--------------------------------------------------------------------------*/
CURSOR spf_bill_0 is
SELECT pf.agreement_id, pf.project_id, pf.task_id
FROM pa_summary_project_fundings pf
WHERE (pf.invproc_billed_amount <> 0 /* MCB related changes */
/* The following added to fix bug 2249216 */
OR pf.PROJFUNC_BILLED_AMOUNT <> 0
OR pf.PROJECT_BILLED_AMOUNT <> 0
OR pf.TOTAL_BILLED_AMOUNT <> 0)
/* END fix bug 2249216 */
AND pf.project_id = l_project_id
AND NOT EXISTS
( SELECT null
FROM pa_draft_invoice_items dii,
pa_draft_invoices di
WHERE dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND ( nvl(pf.task_id,0) = 0
OR dii.task_id = pf.task_id )
AND di.project_id = pf.project_id
AND dii.invoice_line_type<>'RETENTION' /* added for bug 2822610 */
AND di.agreement_id+0 = pf.agreement_id);
| Cursor For Selecting record having bad bill amount data for Projects |
| Funded at Project Level |
--------------------------------------------------------------------------*/
CURSOR spf_pl_bill_amt is
SELECT pf.agreement_id, pf.project_id,
sum(dii.amount) dii_amount,dii.invproc_currency_code,
sum(dii.projfunc_bill_amount) dii_projfunc_amount,dii.projfunc_currency_code,
sum(dii.project_bill_amount) dii_project_amount,dii.project_currency_code,
sum(dii.funding_bill_amount) dii_funding_amount,dii.funding_currency_code
FROM pa_draft_invoice_items dii,
pa_draft_invoices di,
pa_summary_project_fundings pf
WHERE dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.invoice_line_type <> 'RETENTION'
AND di.project_id+0 = pf.project_id
AND di.agreement_id = pf.agreement_id
AND nvl(pf.task_id, 0) = 0
AND pf.project_id = l_project_id
AND pf.total_baselined_amount > 0 /* 2094391 */
AND exists (select 1 from pa_agreements paa where paa.agreement_id = pf.agreement_id
and dii.funding_currency_code = paa.agreement_currency_code) /* condition added for Bug 5956273*/
GROUP BY pf.agreement_id, pf.project_id
,dii.invproc_currency_code,dii.projfunc_currency_code,dii.project_currency_code
, dii.funding_currency_code; /* MCB related changes */
| Cursor For Selecting record having bad bill amount data for Projects |
| Funded at Task Level |
--------------------------------------------------------------------------*/
CURSOR spf_tl_bill_amt is
/* This new currency procs. is being used which covers the MCB2 as well as old functionality */
SELECT pf.agreement_id, pf.project_id, pf.task_id
,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.amount * (1 -
( nvl(di.retention_percentage,0)/100 )) ), dii.invproc_currency_code) dii_amount,dii.invproc_currency_code,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.projfunc_bill_amount * (1 -
( nvl(di.retention_percentage,0)/100 )) ),dii.projfunc_currency_code) dii_projfunc_amount,dii.projfunc_currency_code,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.project_bill_amount * (1 -
( nvl(di.retention_percentage,0)/100 )) ),dii.project_currency_code) dii_project_amount,dii.project_currency_code,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.funding_bill_amount * (1 -
( nvl(di.retention_percentage,0)/100 )) ),dii.funding_currency_code) dii_funding_amount,dii.funding_currency_code
FROM pa_draft_invoice_items dii,
pa_draft_invoices di,
pa_summary_project_fundings pf
WHERE dii.project_id = di.project_id /* Bug#5081194 : Removed the +0 for perf issue */
AND dii.draft_invoice_num+0 = di.draft_invoice_num
AND pf.task_id = dii.task_id
AND dii.invoice_line_type <> 'RETENTION'
AND di.project_id = pf.project_id /* Bug#5081194 : Removed the +0 in di.project_id for perf issue */
AND di.agreement_id = pf.agreement_id
AND pf.project_id = l_project_id
AND pf.project_id = dii.project_id /* Bug#5081194 : added this condition */
AND pf.total_baselined_amount > 0 /* added for bug 3464050 */
AND exists (select 1 from pa_agreements paa where paa.agreement_id = pf.agreement_id
and dii.funding_currency_code = paa.agreement_currency_code) /* condition added for Bug 5956273*/
GROUP BY pf.agreement_id, pf.project_id, pf.task_id
,dii.invproc_currency_code,dii.projfunc_currency_code,
dii.project_currency_code,dii.funding_currency_code; /* MCB related changes */
UPDATE pa_summary_project_fundings pf
SET pf.total_accrued_amount = 0,
pf.revproc_accrued_amount = 0, /* MCB related changes */
pf.projfunc_accrued_amount = 0,
pf.project_accrued_amount = 0
WHERE pf.agreement_id = acc_0_rec.agreement_id
AND pf.project_id = acc_0_rec.project_id
AND nvl(pf.task_id,0) = nvl(acc_0_rec.task_id,0); /* MCB related changes */
UPDATE pa_summary_project_fundings pf
SET pf.total_accrued_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(acc_amt_rec.dri_funding_amount,acc_amt_rec.funding_currency_code),
pf.revproc_accrued_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(acc_amt_rec.dri_amount,acc_amt_rec.revproc_currency_code),
pf.projfunc_accrued_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(acc_amt_rec.dri_projfunc_amount,acc_amt_rec.projfunc_currency_code),
pf.project_accrued_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(acc_amt_rec.dri_project_amount,acc_amt_rec.project_currency_code)
WHERE pf.agreement_id = acc_amt_rec.agreement_id
AND pf.project_id = acc_amt_rec.project_id
AND nvl(pf.task_id,0) = nvl(acc_amt_rec.task_fund,0); /* changed task_id to task_fund for bug 3647592 */ /* added semi-colon for bug 3717388*/
UPDATE pa_summary_project_fundings pf
SET pf.total_billed_amount = 0,
pf.invproc_billed_amount = 0, /* MCB related changes */
pf.projfunc_billed_amount = 0,
pf.project_billed_amount = 0
WHERE pf.agreement_id = bill_0_rec.agreement_id
AND pf.project_id = bill_0_rec.project_id
AND nvl(pf.task_id,0) = nvl(bill_0_rec.task_id,0); /* MCB related changes */
UPDATE pa_summary_project_fundings pf
SET pf.total_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(pl_bill_amt_rec.dii_funding_amount,pl_bill_amt_rec.funding_currency_code),
pf.invproc_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(pl_bill_amt_rec.dii_amount,pl_bill_amt_rec.invproc_currency_code),
pf.projfunc_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(pl_bill_amt_rec.dii_projfunc_amount,pl_bill_amt_rec.projfunc_currency_code),
pf.project_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(pl_bill_amt_rec.dii_project_amount,pl_bill_amt_rec.project_currency_code)
WHERE pf.agreement_id = pl_bill_amt_rec.agreement_id
AND pf.project_id = pl_bill_amt_rec.project_id /* MCB related changes */
AND nvl(pf.task_id,0) = 0; /* 2094391 */
UPDATE pa_summary_project_fundings pf
SET pf.total_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(tl_bill_amt_rec.dii_funding_amount,tl_bill_amt_rec.funding_currency_code),
pf.invproc_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(tl_bill_amt_rec.dii_amount,tl_bill_amt_rec.invproc_currency_code),
pf.projfunc_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(tl_bill_amt_rec.dii_projfunc_amount,tl_bill_amt_rec.projfunc_currency_code),
pf.project_billed_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
(tl_bill_amt_rec.dii_project_amount,tl_bill_amt_rec.project_currency_code)
WHERE pf.agreement_id = tl_bill_amt_rec.agreement_id
AND pf.project_id = tl_bill_amt_rec.project_id
AND pf.task_id = tl_bill_amt_rec.task_id; /* MCB related changes */
SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
FROM PA_CUST_EVENT_RDL_ALL ERDL,
PA_DRAFT_REVENUES_ALL DR,
PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND ERDL.PROJECT_ID=DR.PROJECT_ID
AND E.EVENT_NUM = ERDL.EVENT_NUM
AND E.TASK_ID = ERDL.TASK_ID
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.PROJECT_ID = ERDL.PROJECT_ID
AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,
NULL, E.PROJECT_ID)
AND E.TASK_ID = p_task_id
AND NVL(DR.AGREEMENT_ID,0) = NVL(p_agreement_id,DR.AGREEMENT_ID)
AND E.PROJECT_ID = p_project_id
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM;
SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
SUM(NVL(ERDL.projfunc_revenue_amount,0)),
DECODE(p_funding_flag,'Y',SUM(NVL(ERDL.funding_revenue_amount,0)),0)
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
FROM PA_CUST_EVENT_RDL_ALL ERDL,
PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND E.EVENT_NUM = ERDL.EVENT_NUM
AND E.TASK_ID = ERDL.TASK_ID
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.PROJECT_ID = ERDL.PROJECT_ID
AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,
NULL, E.PROJECT_ID)
AND E.TASK_ID = p_task_id
AND E.PROJECT_ID = p_project_id;
/* This select is commented for MCB2, the same objective is fulfill by the above select */
/* SELECT SUM(NVL(E.revenue_amount,0)),SUM(NVL(E.project_revenue_amount,0)),
SUM(NVL(E.projfunc_revenue_amount,0)),SUM(NVL(E.funding_revenue_amount,0))
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount
FROM PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.TASK_ID = p_task_id
AND E.PROJECT_ID = p_project_id;
SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
FROM PA_CUST_EVENT_RDL_ALL ERDL,
PA_DRAFT_REVENUES_ALL DR,
PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND ERDL.PROJECT_ID=DR.PROJECT_ID
AND E.EVENT_NUM = ERDL.EVENT_NUM
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.PROJECT_ID = ERDL.PROJECT_ID
AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL, NULL, E.PROJECT_ID)
AND NVL(DR.AGREEMENT_ID,0) = NVL(p_agreement_id,DR.AGREEMENT_ID)
AND E.PROJECT_ID = p_project_id
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0); /* Added for bug 1504680 */
SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
SUM(NVL(ERDL.projfunc_revenue_amount,0)),
DECODE(p_funding_flag,'Y',SUM(NVL(ERDL.funding_revenue_amount,0)),0)
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
FROM PA_CUST_EVENT_RDL_ALL ERDL,
PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG ,'N') = 'Y'
AND E.EVENT_NUM = ERDL.EVENT_NUM
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.PROJECT_ID = ERDL.PROJECT_ID
AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL, NULL, E.PROJECT_ID)
AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0) /* Added for bug 1504680 */
AND E.PROJECT_ID = p_project_id;
/* This select is commented for MCB2, the same objective is fulfill by the above select */
/*
SELECT SUM(NVL(E.revenue_amount,0)),SUM(NVL(E.project_revenue_amount,0)),
SUM(NVL(E.projfunc_revenue_amount,0)),SUM(NVL(E.funding_revenue_amount,0))
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount
FROM PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.PROJECT_ID = p_project_id; */
SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
FROM PA_CUST_EVENT_RDL_ALL ERDL,
PA_DRAFT_REVENUES_ALL DR,
PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND ERDL.PROJECT_ID=DR.PROJECT_ID
AND E.EVENT_NUM = ERDL.EVENT_NUM
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
AND E.PROJECT_ID = ERDL.PROJECT_ID
AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,NULL, E.PROJECT_ID)
AND DR.AGREEMENT_ID = p_agreement_id
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0); /* Added for bug 1504680 */
SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
FROM PA_CUST_EVENT_RDL_ALL ERDL,
PA_DRAFT_REVENUES_ALL DR,
PA_EVENT_TYPES ET,
PA_EVENTS E
WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
AND ERDL.PROJECT_ID=DR.PROJECT_ID
AND E.EVENT_NUM = ERDL.EVENT_NUM
AND ET.EVENT_TYPE = E.EVENT_TYPE
AND ET.EVENT_TYPE_CLASSIFICATION ='WRITE OFF'
AND E.PROJECT_ID = ERDL.PROJECT_ID
AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,
NULL, E.PROJECT_ID)
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0); /* Added for bug 1504680 */
insert_error_message boolean;
insert_error_message := FALSE;
Error (Invalid Length inside a variable string). Instead the select
statement below has been used for populating labor_sch_type .
This is a workaround and needs to be removed in future the select
below is unneccessary and will affect performance */
/* Indicator variables Bug# 634414 */
NC := 1206;
select t.labor_sch_type
into labor_sch_type
from pa_tasks t, pa_expenditure_items_all e
where t.task_id = e.task_id
and e.expenditure_item_id = ei_id( j );
insert_error_message := TRUE;
select nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
into l_mcb_cost_flag
from pa_projects_all
where project_id =(select project_id from pa_expenditure_items_all where expenditure_item_id=ei_id(j));
IF ( insert_error_message ) THEN
IF (stage = 200) THEN
reason( j ) := 'NO_IND_RATE_SCH_REVISION';
Select 'Y'
Into l_exist_flag
From dual
Where exists ( select null
from pa_tasks
where project_id = p_project_id
and customer_id = p_customer_id
and task_id = top_task_id
and decode(p_task_id
, null, top_task_id
, p_task_id) = top_task_id
);