eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PROJ_BILLING_DETAIL_VIEW

Source

View Text - Preformatted


SELECT decode(p.invoice_method, 'EVENT', 'EVENT', 'WORK') , p.project_id , p.segment1 , p.name , NVL(p.retn_accounting_flag,'N') , to_char(pdi.draft_invoice_num) , to_char(pdi.invoice_date) , to_char(pdi.bill_through_date) , round(to_number(trunc(sysdate) - nvl(pdi.invoice_date,nvl(p.start_date,p.creation_date)))) , to_char(PA_Billing_Cycles_Pkg.Get_Next_Billing_Date( p.project_id, nvl(p.start_date,p.creation_date), p.billing_cycle_id, p.billing_offset, NULL, nvl(pdi.bill_through_date,pdi.creation_date))) , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , p.enable_top_task_inv_mth_flag , 0 from pa_project_types_all pt, pa_draft_invoices pdi, pa_projects_all p WHERE pa_project_utils.Check_prj_stus_action_allowed (p.project_status_code, 'GENERATE_INV') = 'Y' and p.project_type = pt.project_type and pt.direct_flag = 'Y' and pdi.project_id = p.project_id and pdi.draft_invoice_Num = PA_Billing_Cycles_Pkg.Get_Last_Released_Invoice_Num( p.project_id ) and PA_Billing_Cycles_Pkg.Get_Last_Released_Invoice_Num(p.project_id) IS NOT NULL and (p.org_id is null or p.org_id = pt.org_id) UNION ALL select decode(p.invoice_method, 'EVENT', 'EVENT', 'WORK') , p.project_id , p.segment1 , p.name , NVL(p.retn_accounting_flag,'N') , null , null , null , round(to_number(trunc(sysdate)- nvl(p.start_date,p.creation_date))) , to_char(PA_Billing_Cycles_Pkg.Get_Next_Billing_Date( p.project_id, nvl(p.start_date,p.creation_date), p.billing_cycle_id, p.billing_offset, NULL, NULL )) , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , p.enable_top_task_inv_mth_flag , 0 from pa_projects_all p, pa_project_types_all pt WHERE pa_project_utils.Check_prj_stus_action_allowed (p.project_status_code, 'GENERATE_INV') = 'Y' and p.project_type = pt.project_type and pt.direct_flag = 'Y' and PA_Billing_Cycles_Pkg.Get_Last_Released_Invoice_Num(p.project_id) IS NULL and (p.org_id is null or p.org_id = pt.org_id) UNION ALL select decode(p.invoice_method, 'EVENT', 'EVENT', 'WORK'), p.project_id, p.segment1, p.name, NVL(p.retn_accounting_flag,'N'), null, null, null, 0, null, min(decode(substr(p.distribution_rule,instr(p.distribution_rule,'/')+1,length(p.distribution_rule)),'EVENT',e.bill_amount,0)), 0, 0, 0, min(decode(substr(p.distribution_rule,instr(p.distribution_rule,'/')+1,length(p.distribution_rule)),'EVENT',e.bill_trans_bill_amount,0)), 0, 0, 0, 0, 0, p.enable_top_task_inv_mth_flag, min(e.event_id) event_id from pa_projects p, pa_events e where p.project_id = e.project_id and e.event_id = ( select min(event_id) from pa_events a where a.BILLED_FLAG ='N' and a.completion_date is not null and p.project_id = a.project_id and a.completion_date = ( select min(e2.completion_date) from pa_events e2 where e2.BILLED_FLAG ='N' and a.project_id = e2.project_id and e2.completion_date is not null ) ) group by decode(p.invoice_method, 'EVENT', 'EVENT', 'WORK'), p.project_id, p.segment1, p.name, NVL(p.retn_accounting_flag,'N'),p.enable_top_task_inv_mth_flag UNION ALL select null, pdi.project_id, null, null, null, null, null, null, 0, null, 0, decode(pdi.released_date,'',sum(pdii.amount),0), 0, decode(pdi.released_date,'',0,sum(pdii.amount)), 0, decode(pdi.released_date,'',sum(pdii.projfunc_bill_amount),0), 0, decode(pdi.released_date,'',0,sum(pdii.projfunc_bill_amount)), decode(pdi.released_date,'',0,sum(DECODE(pdii.invoice_line_type,'RETENTION',0,DECODE(nvl(pdi.write_off_flag,'N'),'Y',0,pdii.projfunc_bill_amount)))), decode(pdi.released_date,'',0,sum(DECODE(pdii.invoice_line_type,'RETENTION',DECODE(nvl(pdi.write_off_flag,'N'),'Y',0,pdii.projfunc_bill_amount),0))), null, 0 from pa_draft_invoices pdi, pa_draft_invoice_items pdii where pdi.project_id = pdii.project_id and pdi.draft_invoice_num = pdii.draft_invoice_num Group by pdi.project_id, pdi.released_date UNION ALL select null, pdr.project_id, null, null, null, null, null, null, 0, null, 0, 0, sum(pdri.amount) revenue, 0, 0, 0, sum(pdri.projfunc_revenue_amount) , 0, 0, 0, null, 0 FROM pa_draft_revenues pdr, pa_draft_revenue_items pdri where pdr.project_id = pdri.project_id and pdr.draft_revenue_num = pdri.draft_revenue_num Group by pdr.project_id

View Text - HTML Formatted

SELECT DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, TO_CHAR(PDI.DRAFT_INVOICE_NUM)
, TO_CHAR(PDI.INVOICE_DATE)
, TO_CHAR(PDI.BILL_THROUGH_DATE)
, ROUND(TO_NUMBER(TRUNC(SYSDATE) - NVL(PDI.INVOICE_DATE
, NVL(P.START_DATE
, P.CREATION_DATE))))
, TO_CHAR(PA_BILLING_CYCLES_PKG.GET_NEXT_BILLING_DATE( P.PROJECT_ID
, NVL(P.START_DATE
, P.CREATION_DATE)
, P.BILLING_CYCLE_ID
, P.BILLING_OFFSET
, NULL
, NVL(PDI.BILL_THROUGH_DATE
, PDI.CREATION_DATE)))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, 0
FROM PA_PROJECT_TYPES_ALL PT
, PA_DRAFT_INVOICES PDI
, PA_PROJECTS_ALL P
WHERE PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (P.PROJECT_STATUS_CODE
, 'GENERATE_INV') = 'Y'
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.DIRECT_FLAG = 'Y'
AND PDI.PROJECT_ID = P.PROJECT_ID
AND PDI.DRAFT_INVOICE_NUM = PA_BILLING_CYCLES_PKG.GET_LAST_RELEASED_INVOICE_NUM( P.PROJECT_ID )
AND PA_BILLING_CYCLES_PKG.GET_LAST_RELEASED_INVOICE_NUM(P.PROJECT_ID) IS NOT NULL
AND (P.ORG_ID IS NULL OR P.ORG_ID = PT.ORG_ID) UNION ALL SELECT DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, NULL
, NULL
, NULL
, ROUND(TO_NUMBER(TRUNC(SYSDATE)- NVL(P.START_DATE
, P.CREATION_DATE)))
, TO_CHAR(PA_BILLING_CYCLES_PKG.GET_NEXT_BILLING_DATE( P.PROJECT_ID
, NVL(P.START_DATE
, P.CREATION_DATE)
, P.BILLING_CYCLE_ID
, P.BILLING_OFFSET
, NULL
, NULL ))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, 0
FROM PA_PROJECTS_ALL P
, PA_PROJECT_TYPES_ALL PT
WHERE PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (P.PROJECT_STATUS_CODE
, 'GENERATE_INV') = 'Y'
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.DIRECT_FLAG = 'Y'
AND PA_BILLING_CYCLES_PKG.GET_LAST_RELEASED_INVOICE_NUM(P.PROJECT_ID) IS NULL
AND (P.ORG_ID IS NULL OR P.ORG_ID = PT.ORG_ID) UNION ALL SELECT DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, NULL
, NULL
, NULL
, 0
, NULL
, MIN(DECODE(SUBSTR(P.DISTRIBUTION_RULE
, INSTR(P.DISTRIBUTION_RULE
, '/')+1
, LENGTH(P.DISTRIBUTION_RULE))
, 'EVENT'
, E.BILL_AMOUNT
, 0))
, 0
, 0
, 0
, MIN(DECODE(SUBSTR(P.DISTRIBUTION_RULE
, INSTR(P.DISTRIBUTION_RULE
, '/')+1
, LENGTH(P.DISTRIBUTION_RULE))
, 'EVENT'
, E.BILL_TRANS_BILL_AMOUNT
, 0))
, 0
, 0
, 0
, 0
, 0
, P.ENABLE_TOP_TASK_INV_MTH_FLAG
, MIN(E.EVENT_ID) EVENT_ID
FROM PA_PROJECTS P
, PA_EVENTS E
WHERE P.PROJECT_ID = E.PROJECT_ID
AND E.EVENT_ID = ( SELECT MIN(EVENT_ID)
FROM PA_EVENTS A
WHERE A.BILLED_FLAG ='N'
AND A.COMPLETION_DATE IS NOT NULL
AND P.PROJECT_ID = A.PROJECT_ID
AND A.COMPLETION_DATE = ( SELECT MIN(E2.COMPLETION_DATE)
FROM PA_EVENTS E2
WHERE E2.BILLED_FLAG ='N'
AND A.PROJECT_ID = E2.PROJECT_ID
AND E2.COMPLETION_DATE IS NOT NULL ) ) GROUP BY DECODE(P.INVOICE_METHOD
, 'EVENT'
, 'EVENT'
, 'WORK')
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, NVL(P.RETN_ACCOUNTING_FLAG
, 'N')
, P.ENABLE_TOP_TASK_INV_MTH_FLAG UNION ALL SELECT NULL
, PDI.PROJECT_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 0
, NULL
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, SUM(PDII.AMOUNT)
, 0)
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(PDII.AMOUNT))
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, SUM(PDII.PROJFUNC_BILL_AMOUNT)
, 0)
, 0
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(PDII.PROJFUNC_BILL_AMOUNT))
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(DECODE(PDII.INVOICE_LINE_TYPE
, 'RETENTION'
, 0
, DECODE(NVL(PDI.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, PDII.PROJFUNC_BILL_AMOUNT))))
, DECODE(PDI.RELEASED_DATE
, ''
, 0
, SUM(DECODE(PDII.INVOICE_LINE_TYPE
, 'RETENTION'
, DECODE(NVL(PDI.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, PDII.PROJFUNC_BILL_AMOUNT)
, 0)))
, NULL
, 0
FROM PA_DRAFT_INVOICES PDI
, PA_DRAFT_INVOICE_ITEMS PDII
WHERE PDI.PROJECT_ID = PDII.PROJECT_ID
AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM GROUP BY PDI.PROJECT_ID
, PDI.RELEASED_DATE UNION ALL SELECT NULL
, PDR.PROJECT_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 0
, NULL
, 0
, 0
, SUM(PDRI.AMOUNT) REVENUE
, 0
, 0
, 0
, SUM(PDRI.PROJFUNC_REVENUE_AMOUNT)
, 0
, 0
, 0
, NULL
, 0
FROM PA_DRAFT_REVENUES PDR
, PA_DRAFT_REVENUE_ITEMS PDRI
WHERE PDR.PROJECT_ID = PDRI.PROJECT_ID
AND PDR.DRAFT_REVENUE_NUM = PDRI.DRAFT_REVENUE_NUM GROUP BY PDR.PROJECT_ID