[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Modified this select for bug 3677900. This select will check how many customers are funding this project
*/
SELECT count(*)
INTO l_count
FROM pa_project_customers pc
WHERE pc.project_id = p_project_id
AND EXISTS (
SELECT
spf.project_id project_id
,spf.agreement_id
,agr.customer_id customer_id
FROM
pa_agreements_all agr
, pa_summary_project_fundings spf
WHERE agr.customer_id = pc.customer_id
AND agr.agreement_id = spf.agreement_id
AND spf.project_id = pc.project_id
AND spf.project_id = p_project_id
);
SELECT
( SUM(NVL(spf.project_baselined_amount,0)) ) Total_PC_Funding
,( SUM(NVL(spf.project_accrued_amount,0))) PC_Rev_Accrued
,( (SUM(NVL(spf.project_baselined_amount,0)) ) - (SUM(NVL(spf.project_accrued_amount,0))) ) PC_Rev_backlog
,( SUM(NVL(spf.project_billed_amount,0))) PC_Inv_Invoiced
,( (SUM(NVL(spf.project_baselined_amount,0)) ) - (SUM(NVL(spf.project_billed_amount,0))) ) PC_Inv_backlog
,( SUM(NVL(spf.projfunc_baselined_amount,0)) ) Total_PFC_Funding
,( SUM(NVL(spf.projfunc_accrued_amount,0))) PFC_Rev_Accrued
,( (SUM(NVL(spf.projfunc_baselined_amount,0)) ) - (SUM(NVL(spf.projfunc_accrued_amount,0))) ) PFC_Rev_backlog
,( SUM(NVL(spf.projfunc_billed_amount,0))) PFC_Inv_Invoiced
,( (SUM(NVL(spf.projfunc_baselined_amount,0)) ) - (SUM(NVL(spf.projfunc_billed_amount,0))) ) PFC_Inv_backlog
INTO
l_proj_funding_amt ,
l_proj_rev_accured ,
l_proj_rev_backlog ,
l_proj_inv_invoiced ,
l_proj_inv_backlog ,
l_projfunc_funding_amt ,
l_projfunc_rev_accured ,
l_projfunc_rev_backlog ,
l_projfunc_inv_invoiced ,
l_projfunc_inv_backlog
FROM pa_summary_project_fundings spf
WHERE spf.project_id = p_project_id;
SELECT
SUM(dii.projfunc_bill_amount) pfc_inv_amt,
SUM(dii.project_bill_amount) pc_inv_amt
INTO
l_projfunc_inv_due_unaccepted,
l_proj_inv_due_unaccepted
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii
WHERE dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.transfer_status_code <> 'A'
AND di.project_id = p_project_id
AND di.system_reference IS NULL
GROUP BY di.project_id;
SELECT 'T' into l_accepted_exist
FROM dual
WHERE EXISTS
(SELECT null
FROM pa_draft_invoices_all di
WHERE di.project_id = p_project_id
AND di.transfer_status_code = 'A'
AND di.system_reference IS NOT NULL) ;
SELECT
pa_inv.project_id,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) * ar.amount_line_items_remaining)
inv_due_pfc,
SUM(((pa_inv.pfc_inv_amt)/ar.amount_line_items_original)*(ar.amount_line_items_original ))
inv_orig_pfc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) * ar.tax_original) tax_orig_pfc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) * ar.tax_remaining) tax_due_pfc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) * ar.amount_line_items_remaining)
inv_due_pc,
SUM(((pa_inv.pc_inv_amt)/ar.amount_line_items_original)*(ar.amount_line_items_original )) inv_orig_pc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) * ar.tax_original) tax_orig_pc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) * ar.tax_remaining) tax_due_pc
INTO
l_project_id,
l_projfunc_inv_due,
l_projfunc_inv_orig,
l_projfunc_tax_orig,
l_projfunc_tax_due,
l_proj_inv_due,
l_proj_inv_orig,
l_proj_tax_orig,
l_proj_tax_due
FROM ar_payment_schedules_all ar,
(SELECT di.project_id,
di.system_reference system_reference,
SUM(dii.projfunc_bill_amount) pfc_inv_amt,
SUM(dii.project_bill_amount) pc_inv_amt
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii
WHERE di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND di.transfer_status_code = 'A'
AND di.system_reference IS NOT NULL
GROUP BY di.project_id,di.system_reference) pa_inv
WHERE pa_inv.project_id = p_project_id
AND pa_inv.system_reference = ar.customer_trx_id
GROUP BY pa_inv.project_id;
SELECT
SUM(DECODE(system_linkage_function,'BTC',NVL(project_burdened_cost,0),NVL(project_raw_cost,0))) Proj_billable_cost
,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0,DECODE(system_linkage_function,'BTC',
NVL(project_burdened_cost,0),NVL(project_raw_cost,0)),0),0) ) Proj_unbill_cost
, SUM(DECODE(system_linkage_function,'BTC',NVL(burden_cost,0),NVL(raw_cost,0))) Projfunc_billable_cost
,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0,DECODE(system_linkage_function,'BTC',
NVL(burden_cost,0),NVL(raw_cost,0)),0),0) ) Projfunc_unbill_cost
*/
SELECT
SUM(NVL(project_burdened_cost,0)) Proj_billable_cost
,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0, NVL(project_burdened_cost,0) ,0),0) )Proj_unbill_cost
, SUM(NVL(burden_cost,0)) Projfunc_billable_cost
,SUM(DECODE(NVL(event_num,0),0,DECODE(NVL(bill_amount,0),0, NVL(burden_cost,0),0),0) ) Projfunc_unbill_cost
INTO
l_proj_billable_cost
,l_proj_unbilled_cost
,l_projfunc_billable_cost
,l_projfunc_unbilled_cost
FROM pa_expenditure_items_all
WHERE project_id = p_project_id
AND nvl(billable_flag,'N') = 'Y';
SELECT COUNT(*)
INTO l_pc_count
FROM pa_events eve
WHERE eve.project_id = p_project_id
AND eve.bill_trans_currency_code <> eve.project_currency_code;
SELECT
SUM(NVL(eve.bill_trans_bill_amount,0)) total_pc_unbilled_with_partial
INTO
l_pc_unbill_eve_amt_with_part
FROM pa_events eve
WHERE eve.project_id = p_project_id
AND nvl(eve.billed_flag,'N') = 'N'
AND 1 > ( SELECT COUNT(*)
FROM pa_events eve2
WHERE eve2.project_id = p_project_id
AND eve2.bill_trans_currency_code <> eve2.project_currency_code);
SELECT COUNT(*)
INTO l_pfc_count
FROM pa_events eve
WHERE eve.project_id = p_project_id
AND eve.bill_trans_currency_code <> eve.projfunc_currency_code;
SELECT
SUM(NVL(eve.bill_trans_bill_amount,0)) tot_pfc_unbilled_with_partial
INTO
l_pfc_unbill_eve_amt_with_part
FROM pa_events eve
WHERE eve.project_id = p_project_id
AND nvl(eve.billed_flag,'N') = 'N'
AND 1 > ( SELECT COUNT(*)
FROM pa_events eve2
WHERE eve2.project_id = p_project_id
AND eve2.bill_trans_currency_code <> eve2.projfunc_currency_code);
SELECT
SUM(NVL(dii.project_bill_amount,0)) pc_total_partial_billed,
SUM(NVL(dii.projfunc_bill_amount,0)) pfc_total_partial_billed
INTO
l_pc_total_part_bill_amt,
l_pfc_total_part_bill_amt
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND EXISTS ( SELECT null
FROM pa_events eve
WHERE eve.event_num = dii.event_num
AND eve.billed_flag = 'N');
SELECT
SUM(NVL(dii.project_bill_amount,0)) pc_total_partial_billed,
SUM(NVL(dii.projfunc_bill_amount,0)) pfc_total_partial_billed
INTO
l_pc_total_part_bill_amt,
l_pfc_total_part_bill_amt
FROM pa_draft_invoice_items dii, pa_events eve
WHERE eve.project_id = p_project_id
and eve.project_id = dii.project_id
and nvl(eve.task_id, 0) = nvl(dii.task_id,0)
and eve.event_num = nvl(dii.event_num,0)
and nvl(eve.billed_flag,'N') = 'N';
SELECT
/*
SUM(NVL(project_total_billed,0)) - SUM(NVL(project_total_retained,0)) Proj_Unbilled_Retn
,SUM(NVL(projfunc_total_billed,0)) - SUM(NVL(projfunc_total_retained,0)) Projfunc_Unbilled_Retn
*/
SUM(NVL(project_total_retained,0)) - SUM(NVL(project_total_billed,0)) Proj_Unbilled_Retn
,SUM(NVL(projfunc_total_retained,0)) - SUM(NVL(projfunc_total_billed,0)) Projfunc_Unbilled_Retn
INTO
l_proj_unbilled_retn
,l_projfunc_unbilled_retn
FROM pa_summary_project_retn
WHERE project_id = p_project_id;
SELECT
SUM(NVL(dii.project_bill_amount,0)) unapproved_project_invoice,
SUM(NVL(dii.projfunc_bill_amount,0)) unapproved_project_invoice
INTO
l_proj_unapprov_inv_amt,
l_projfunc_unapprov_inv_amt
FROM pa_draft_invoice_items dii,pa_draft_invoices_all di
WHERE dii.draft_invoice_num = di.draft_invoice_num
AND dii.project_id = di.project_id
AND di.project_id = p_project_id
AND di.approved_by_person_id IS NULL;
DELETE pa_bill_workbench_temp;
INSERT
INTO pa_bill_workbench_temp(
PROJECT_ID ,
PC_FUNDING ,
PC_REV_ACCRUED ,
PC_REV_BACKLOG ,
PC_WRITEOFF ,
PC_UBR ,
PC_UER ,
PC_INVOICED ,
PC_INV_BACKLOG ,
PC_PAID ,
PC_DUE ,
PC_BILLABLE_COST ,
PC_UNBILLED_COST ,
PC_UNBILLED_EVENTS ,
PC_UNBILLED_RETENTION ,
PC_UNAPPRO_INVOICES ,
PC_TAX ,
PC_UBR_APPLICAB_FLAG ,
PC_UER_APPLICAB_FLAG ,
PC_UNBIL_EVE_APPLICAB_FLAG ,
PFC_FUNDING ,
PFC_REV_ACCRUED ,
PFC_REV_BACKLOG ,
PFC_WRITEOFF ,
PFC_UBR ,
PFC_UER ,
PFC_INVOICED ,
PFC_INV_BACKLOG ,
PFC_PAID ,
PFC_DUE ,
PFC_BILLABLE_COST ,
PFC_UNBILLED_COST ,
PFC_UNBILLED_EVENTS ,
PFC_UNBILLED_RETENTION ,
PFC_UNAPPRO_INVOICES ,
PFC_TAX ,
PFC_UNBIL_EVE_APPLICAB_FLAG ,
NEXT_INVOICE_DATE ,
Multi_Customer_Flag
)
VALUES(
p_project_id ,
p_proj_funding_amt ,
p_proj_rev_accured ,
p_proj_rev_backlog ,
p_proj_rev_writeoff ,
p_proj_ubr ,
p_proj_uer ,
p_proj_inv_invoiced ,
p_proj_inv_backlog ,
p_proj_inv_paid ,
p_proj_inv_due ,
p_proj_billable_cost ,
p_proj_unbilled_cost ,
p_proj_unbilled_events ,
p_proj_unbilled_retn ,
p_proj_unapproved_inv_amt ,
p_proj_tax ,
p_pc_ubr_applicab_flag ,
p_pc_uer_applicab_flag ,
p_pc_unbil_eve_applicab_flag ,
p_projfunc_funding_amt ,
p_projfunc_rev_accured ,
p_projfunc_rev_backlog ,
p_projfunc_rev_writeoff ,
p_projfunc_ubr ,
p_projfunc_uer ,
p_projfunc_inv_invoiced ,
p_projfunc_inv_backlog ,
p_projfunc_inv_paid ,
p_projfunc_inv_due ,
p_projfunc_billable_cost ,
p_projfunc_unbilled_cost ,
p_projfunc_unbilled_events ,
p_projfunc_unbilled_retn ,
p_projfunc_unapprov_inv_amt ,
p_projfunc_tax ,
p_pfc_unbil_eve_applicab_flag ,
p_next_invoice_date ,
p_multi_customer_flag
);
DELETE pa_bill_wrkbench_inv_temp;
INSERT
INTO pa_bill_wrkbench_inv_temp(
PROJECT_ID
,AGREEMENT_ID
,CUSTOMER_ID
,CUSTOMER_NAME
,PC_FUNDING
,PC_INVOICED
,PC_DUE_ACCEPTED
,PC_DUE_PENDING
,PC_TAX
,PC_TAX_DUE
,PFC_FUNDING
,PFC_INVOICED
,PFC_DUE_ACCEPTED
,PFC_DUE_PENDING
,PFC_TAX
,PFC_TAX_DUE
)
SELECT
spf.project_id project_id
,spf.agreement_id
-- ,ra.customer_id customer_id
-- ,ra.customer_name||' ('||ra.customer_number||')' Customer
,cust_acct.cust_account_id customer_id
,substrb(party.party_name,1,50)||' ('||cust_acct.account_number||')' Customer
,SUM(NVL(spf.project_baselined_amount,0)) pc_Baselined
,NULL pc_invoiced
,NULL pc_due_accepted
,NULL pc_due_pending
,NULL pc_tax
,NULL pc_tax_due
,SUM(NVL(spf.projfunc_baselined_amount,0)) pfc_Baselined
,NULL pfc_Invoiced
,NULL pfc_due_accepted
,NULL pfc_due_pending
,NULL pfc_tax
,NULL pfc_tax_due
FROM
-- ra_customers ra
hz_parties party
, hz_cust_accounts cust_acct
, pa_agreements_all agr
, pa_project_customers pc
, pa_summary_project_fundings spf
-- WHERE ra.customer_id = agr.customer_id
WHERE cust_acct.cust_account_id= agr.customer_id
AND cust_acct.party_id = party.party_id
AND agr.customer_id = pc.customer_id
AND agr.agreement_id = spf.agreement_id
AND spf.project_id = pc.project_id
AND spf.project_id = p_project_id
GROUP BY /*ra.customer_name,ra.customer_id,ra.customer_number*/
substrb(party.party_name,1,50),cust_acct.account_number,cust_acct.cust_account_id,spf.project_id,spf.agreement_id;
UPDATE pa_bill_wrkbench_inv_temp pbw
SET (pbw.pc_invoiced,pbw.pfc_invoiced ) =
(SELECT
SUM(NVL(dii.project_bill_amount,0)) projinv_amt
,SUM(NVL(dii.projfunc_bill_amount,0)) projfuncinv_amt
FROM pa_draft_invoices_all di , pa_draft_invoice_items dii , pa_agreements_all agr
WHERE dii.draft_invoice_num = di.draft_invoice_num
AND dii.project_id = di.project_id
AND agr.customer_id = di.customer_id
AND agr.agreement_id = di.agreement_id
AND di.customer_id = pbw.customer_id
AND di.agreement_id = pbw.agreement_id
AND di.project_id = pbw.project_id
AND (
( 'INV_ALL' = p_inv_filter)
OR ( 'INV_APPRO' = p_inv_filter AND di.approved_by_person_id IS NOT NULL
AND di.released_date IS NULL)
OR ( 'INV_CREDITS' = p_inv_filter AND di.draft_invoice_num_credited IS NOT NULL )
OR ( 'INV_RETN_BILL_INV' = p_inv_filter AND NVL(di.retention_invoice_flag,'N') = 'Y' )
OR ( 'INV_UNAPPRO' = p_inv_filter AND di.approved_by_person_id IS NULL )
OR ( 'INV_RELEASE' = p_inv_filter AND di.transfer_status_code = 'P'
AND di.released_date IS NOT NULL )
OR ( 'INV_ACCEPT' = p_inv_filter AND di.transfer_status_code = 'A'
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_REJECT' = p_inv_filter AND (di.transfer_status_code = 'X'
OR di.transfer_status_code = 'R')
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_ERROR' = p_inv_filter AND NVL(di.generation_error_flag,'N') = 'Y' )
)
GROUP BY di.project_id,di.customer_id,di.agreement_id);
UPDATE pa_bill_wrkbench_inv_temp pbw
SET (pbw.pc_due_accepted,pbw.pfc_due_accepted, pbw.pc_tax, pbw.pfc_tax, pbw.pc_tax_due, pbw.pfc_tax_due ) =
( SELECT
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
ar.amount_line_items_remaining) due_accepted_pc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
ar.amount_line_items_remaining) due_accepted_pfc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
ar.tax_original) tax_pc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
ar.tax_original) tax_pfc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
ar.tax_remaining) tax_due_pc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
ar.tax_remaining) tax_due_pfc
FROM ar_payment_schedules_all ar,
(SELECT di.project_id,
di.customer_id,
di.agreement_id,
di.system_reference system_reference,
SUM(dii.project_bill_amount) pc_inv_amt,
SUM(dii.projfunc_bill_amount) pfc_inv_amt
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii,
PA_BILL_WRKBENCH_INV_TEMP pbwi
WHERE di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND di.transfer_status_code = 'A'
AND di.customer_id = pbwi.customer_id
AND di.agreement_id = pbwi.agreement_id
AND di.project_id = pbwi.project_id
AND di.system_reference IS NOT NULL
AND (
( 'INV_ALL' = p_inv_filter)
OR ( 'INV_APPRO' = p_inv_filter
AND di.approved_by_person_id IS NOT NULL
AND di.released_date IS NULL)
OR ( 'INV_CREDITS' = p_inv_filter
AND di.draft_invoice_num_credited IS NOT NULL )
OR ( 'INV_RETN_BILL_INV' = p_inv_filter
AND NVL(di.retention_invoice_flag,'N') = 'Y' )
OR ( 'INV_UNAPPRO' = p_inv_filter
AND di.approved_by_person_id IS NULL )
OR ( 'INV_RELEASE' = p_inv_filter
AND di.transfer_status_code = 'P'
AND di.released_date IS NOT NULL )
OR ( 'INV_ACCEPT' = p_inv_filter
AND di.transfer_status_code = 'A'
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_REJECT' = p_inv_filter
AND (di.transfer_status_code = 'X'
OR di.transfer_status_code = 'R')
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_ERROR' = p_inv_filter
AND NVL(di.generation_error_flag,'N') = 'Y' )
)
GROUP BY di.project_id,di.customer_id,di.agreement_id,
di.system_reference) pa_inv
WHERE pa_inv.project_id = pbw.project_id
AND pa_inv.customer_id = pbw.customer_id
AND pa_inv.agreement_id = pbw.agreement_id
AND pa_inv.system_reference = ar.customer_trx_id
AND ar.amount_line_items_original <> 0 /* Condition added for bug 5230465 */
GROUP BY pa_inv.project_id, pa_inv.customer_id,pa_inv.agreement_id);
UPDATE pa_bill_wrkbench_inv_temp pbw
SET (pbw.pc_due_pending,pbw.pfc_due_pending ) =
( SELECT
SUM(dii.project_bill_amount) ,
SUM(dii.projfunc_bill_amount)
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii
WHERE di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND di.transfer_status_code <> 'A'
AND di.customer_id = pbw.customer_id
AND di.agreement_id = pbw.agreement_id
AND di.project_id = pbw.project_id
AND di.system_reference IS NULL
AND (
( 'INV_ALL' = p_inv_filter)
OR ( 'INV_APPRO' = p_inv_filter
AND di.approved_by_person_id IS NOT NULL
AND di.released_date IS NULL)
OR ( 'INV_CREDITS' = p_inv_filter
AND di.draft_invoice_num_credited IS NOT NULL )
OR ( 'INV_RETN_BILL_INV' = p_inv_filter
AND NVL(di.retention_invoice_flag,'N') = 'Y' )
OR ( 'INV_UNAPPRO' = p_inv_filter
AND di.approved_by_person_id IS NULL )
OR ( 'INV_RELEASE' = p_inv_filter
AND di.transfer_status_code = 'P'
AND di.released_date IS NOT NULL )
OR ( 'INV_ACCEPT' = p_inv_filter
AND di.transfer_status_code = 'A'
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_REJECT' = p_inv_filter
AND (di.transfer_status_code = 'X'
OR di.transfer_status_code = 'R')
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_ERROR' = p_inv_filter
AND NVL(di.generation_error_flag,'N') = 'Y' )
)
GROUP BY di.project_id,di.customer_id,di.agreement_id);
INSERT
INTO pa_bill_wrkbench_inv_temp(
PROJECT_ID
,AGREEMENT_ID
,CUSTOMER_ID
,CUSTOMER_NAME
,PC_FUNDING
,PC_INVOICED
,PC_DUE_ACCEPTED
,PC_DUE_PENDING
,PC_TAX
,PC_TAX_DUE
,PFC_FUNDING
,PFC_INVOICED
,PFC_DUE_ACCEPTED
,PFC_DUE_PENDING
,PFC_TAX
,PFC_TAX_DUE
)
SELECT
spf.project_id project_id
,spf.agreement_id
-- ,ra.customer_id customer_id
-- ,ra.customer_name||' ('||ra.customer_number||')' Customer
,cust_acct.cust_account_id customer_id
,substrb(party.party_name,1,50)||' ('||cust_acct.account_number||')' Customer
,SUM(NVL(spf.project_baselined_amount,0)) pc_Baselined
,NULL pc_invoiced
,NULL pc_due_accepted
,NULL pc_due_pending
,NULL pc_tax
,NULL pc_tax_due
,SUM(NVL(spf.projfunc_baselined_amount,0)) pfc_Baselined
,NULL pfc_Invoiced
,NULL pfc_due_accepted
,NULL pfc_due_pending
,NULL pfc_tax
,NULL pfc_tax_due
FROM
-- ra_customers ra
hz_parties party
, hz_cust_accounts cust_acct
, pa_agreements_all agr
, pa_project_customers pc
, pa_summary_project_fundings spf
, pa_draft_invoices_all di
-- WHERE ra.customer_id = agr.customer_id
WHERE cust_acct.cust_account_id= agr.customer_id
AND cust_acct.party_id = party.party_id
AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND di.agreement_id = agr.agreement_id
AND di.customer_id = agr.customer_id
AND di.project_id = spf.project_id
AND agr.customer_id = pc.customer_id
AND agr.agreement_id = spf.agreement_id
AND spf.project_id = pc.project_id
AND spf.agreement_id = NVL(p_agreement_id,spf.agreement_id)
AND spf.project_id = p_project_id
GROUP BY /*ra.customer_name,ra.customer_id,ra.customer_number*/
substrb(party.party_name,1,50),cust_acct.account_number,cust_acct.cust_account_id,spf.project_id,spf.agreement_id;
UPDATE pa_bill_wrkbench_inv_temp pbw
SET (pbw.pc_invoiced,pbw.pfc_invoiced ) =
(SELECT
SUM(NVL(dii.project_bill_amount,0)) projinv_amt
,SUM(NVL(dii.projfunc_bill_amount,0)) projfuncinv_amt
FROM pa_draft_invoices_all di , pa_draft_invoice_items dii , pa_agreements_all agr
WHERE dii.draft_invoice_num = di.draft_invoice_num
AND dii.project_id = di.project_id
AND agr.customer_id = di.customer_id
AND agr.agreement_id = di.agreement_id
AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND di.customer_id = pbw.customer_id
AND di.agreement_id = pbw.agreement_id
AND di.project_id = pbw.project_id
AND (
( 'INV_ALL' = p_inv_filter)
OR ( 'INV_APPRO' = p_inv_filter AND di.approved_by_person_id IS NOT NULL
AND di.released_date IS NULL)
OR ( 'INV_CREDITS' = p_inv_filter AND di.draft_invoice_num_credited IS NOT NULL )
OR ( 'INV_RETN_BILL_INV' = p_inv_filter AND NVL(di.retention_invoice_flag,'N') = 'Y' )
OR ( 'INV_UNAPPRO' = p_inv_filter AND di.approved_by_person_id IS NULL )
OR ( 'INV_RELEASE' = p_inv_filter AND di.transfer_status_code = 'P'
AND di.released_date IS NOT NULL )
OR ( 'INV_ACCEPT' = p_inv_filter AND di.transfer_status_code = 'A'
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_REJECT' = p_inv_filter AND (di.transfer_status_code = 'X'
OR di.transfer_status_code = 'R')
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_ERROR' = p_inv_filter AND NVL(di.generation_error_flag,'N') = 'Y' )
)
GROUP BY di.project_id,di.customer_id,di.agreement_id);
UPDATE pa_bill_wrkbench_inv_temp pbw
SET (pbw.pc_due_accepted,pbw.pfc_due_accepted, pbw.pc_tax, pbw.pfc_tax, pbw.pc_tax_due, pbw.pfc_tax_due ) =
( SELECT
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
ar.amount_line_items_remaining) due_accepted_pc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
ar.amount_line_items_remaining) due_accepted_pfc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
ar.tax_original) tax_pc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
ar.tax_original) tax_pfc,
SUM(( pa_inv.pc_inv_amt /ar.amount_line_items_original) *
ar.tax_remaining) tax_due_pc,
SUM(( pa_inv.pfc_inv_amt /ar.amount_line_items_original) *
ar.tax_remaining) tax_due_pfc
FROM ar_payment_schedules_all ar,
(SELECT di.project_id,
di.customer_id,
di.agreement_id,
di.system_reference system_reference,
SUM(dii.project_bill_amount) pc_inv_amt,
SUM(dii.projfunc_bill_amount) pfc_inv_amt
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii,
PA_BILL_WRKBENCH_INV_TEMP pbwi
WHERE di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND di.transfer_status_code = 'A'
AND di.customer_id = pbwi.customer_id
AND di.agreement_id = pbwi.agreement_id
AND di.project_id = pbwi.project_id
AND di.system_reference IS NOT NULL
AND (
( 'INV_ALL' = p_inv_filter)
OR ( 'INV_APPRO' = p_inv_filter
AND di.approved_by_person_id IS NOT NULL
AND di.released_date IS NULL)
OR ( 'INV_CREDITS' = p_inv_filter
AND di.draft_invoice_num_credited IS NOT NULL )
OR ( 'INV_RETN_BILL_INV' = p_inv_filter
AND NVL(di.retention_invoice_flag,'N') = 'Y' )
OR ( 'INV_UNAPPRO' = p_inv_filter
AND di.approved_by_person_id IS NULL )
OR ( 'INV_RELEASE' = p_inv_filter
AND di.transfer_status_code = 'P'
AND di.released_date IS NOT NULL )
OR ( 'INV_ACCEPT' = p_inv_filter
AND di.transfer_status_code = 'A'
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_REJECT' = p_inv_filter
AND (di.transfer_status_code = 'X'
OR di.transfer_status_code = 'R')
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_ERROR' = p_inv_filter
AND NVL(di.generation_error_flag,'N') = 'Y' )
)
GROUP BY di.project_id,di.customer_id,di.agreement_id,
di.system_reference) pa_inv
WHERE pa_inv.project_id = pbw.project_id
AND pa_inv.customer_id = pbw.customer_id
AND pa_inv.agreement_id = pbw.agreement_id
AND pa_inv.system_reference = ar.customer_trx_id
AND ar.amount_line_items_original <> 0 /* Condition added for bug 5230465 */
GROUP BY pa_inv.project_id, pa_inv.customer_id,pa_inv.agreement_id);
UPDATE pa_bill_wrkbench_inv_temp pbw
SET (pbw.pc_due_pending,pbw.pfc_due_pending ) =
( SELECT
SUM(dii.project_bill_amount) ,
SUM(dii.projfunc_bill_amount)
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii
WHERE di.project_id = dii.project_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND di.agreement_id = NVL(p_agreement_id,di.agreement_id)
AND di.draft_invoice_num = NVL(p_draft_num,di.draft_invoice_num)
AND UPPER(NVL(di.ra_invoice_number,'-99')) = UPPER(NVL(p_ar_number,NVL(di.ra_invoice_number,'-99')))
AND NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_creation_frm_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_creation_to_date)),NVL(TRUNC(di.creation_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_invoice_frm_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_invoice_to_date)),NVL(TRUNC(di.invoice_date),TRUNC(SYSDATE)))
AND NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)) BETWEEN
NVL(LTRIM(RTRIM(p_gl_frm_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND NVL(LTRIM(RTRIM(p_gl_to_date)),NVL(TRUNC(di.gl_date),TRUNC(SYSDATE)))
AND di.transfer_status_code <> 'A'
AND di.customer_id = pbw.customer_id
AND di.agreement_id = pbw.agreement_id
AND di.project_id = pbw.project_id
AND di.system_reference IS NULL
AND (
( 'INV_ALL' = p_inv_filter)
OR ( 'INV_APPRO' = p_inv_filter
AND di.approved_by_person_id IS NOT NULL
AND di.released_date IS NULL)
OR ( 'INV_CREDITS' = p_inv_filter
AND di.draft_invoice_num_credited IS NOT NULL )
OR ( 'INV_RETN_BILL_INV' = p_inv_filter
AND NVL(di.retention_invoice_flag,'N') = 'Y' )
OR ( 'INV_UNAPPRO' = p_inv_filter
AND di.approved_by_person_id IS NULL )
OR ( 'INV_RELEASE' = p_inv_filter
AND di.transfer_status_code = 'P'
AND di.released_date IS NOT NULL )
OR ( 'INV_ACCEPT' = p_inv_filter
AND di.transfer_status_code = 'A'
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_REJECT' = p_inv_filter
AND (di.transfer_status_code = 'X'
OR di.transfer_status_code = 'R')
AND NVL(di.generation_error_flag,'N') <> 'Y' )
OR ( 'INV_ERROR' = p_inv_filter
AND NVL(di.generation_error_flag,'N') = 'Y' )
)
GROUP BY di.project_id,di.customer_id,di.agreement_id);
* If there is no rows in this table then inserting null row so that
* temp table should not return null pointer exception
**/
INSERT
INTO pa_bill_wrkbench_inv_temp(
PROJECT_ID
,AGREEMENT_ID
,CUSTOMER_ID
,CUSTOMER_NAME
,PC_FUNDING
,PC_INVOICED
,PC_DUE_ACCEPTED
,PC_DUE_PENDING
,PC_TAX
,PC_TAX_DUE
,PFC_FUNDING
,PFC_INVOICED
,PFC_DUE_ACCEPTED
,PFC_DUE_PENDING
,PFC_TAX
,PFC_TAX_DUE)
SELECT
p_project_id
,-1
,-1
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM
dual
WHERE NOT EXISTS( SELECT 'x'
FROM pa_bill_wrkbench_inv_temp a
WHERE a.project_id = p_project_id);
SELECT
SUM(ar.amount_line_items_remaining + ar.tax_remaining)
INTO G_ar_amount
FROM ar_payment_schedules_all ar
WHERE p_system_reference IS NOT NULL
AND p_transfer_status_code = 'A'
AND ar.customer_trx_id = p_system_reference;
SELECT
SUM(ar.tax_original)
INTO l_tax_amount
FROM ar_payment_schedules_all ar
WHERE p_system_reference IS NOT NULL
AND p_transfer_status_code = 'A'
AND ar.customer_trx_id = p_system_reference;
SELECT
MULTI_CURRENCY_BILLING_FLAG,
PROJFUNC_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
PROJECT_BIL_RATE_TYPE,
PROJECT_BIL_RATE_DATE,
PROJECT_BIL_EXCHANGE_RATE,
UNBILLED_RECEIVABLE_DR,
UNEARNED_REVENUE_CR
INTO
l_mcb_flag_tab(1),
l_projfunc_currency_code_tab(1),
l_prj_currency_code_tab(1),
l_prj_rate_type_tab(1),
l_prj_rate_date_tab(1),
l_prj_exch_rate_tab(1),
l_ubr_dr_tab(1),
l_uer_cr_tab(1)
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = P_PROJECT_ID;