PA Federal SF 1035 Copy link

SELECT FUNDING_CATEGORY,
       SUM(PROJFUNC_BASELINED_AMOUNT) FUND_AMT,
       BILL_THROUGH_DATE
FROM 
(SELECT SPF.PROJFUNC_BASELINED_AMOUNT  PROJFUNC_BASELINED_AMOUNT,
        SPF.FUNDING_CATEGORY  FUNDING_CATEGORY,
        PDI.BILL_THROUGH_DATE
FROM PA_DRAFT_INVOICES_ALL PDI,
     PA_SUMM_PROJ_FUNDING_CATEGORY SPF,
     PA_DRAFT_INVOICE_ITEMS PDII,
     PA_PROJECTS_ALL PA,
     PA_DRAFT_CONSOLIDATED_INV_ALL PDCI
WHERE SPF.PROJECT_ID=PDI.PROJECT_ID 
AND NVL(SPF.TASK_ID,0)=NVL(PDII.TASK_ID,0) 
AND NVL(PA.billing_funds_check_fc_flag,'N') = 'Y'
AND PDII.DRAFT_INVOICE_NUM=PDI.DRAFT_INVOICE_NUM
AND PDI.PROJECT_ID=PDII.PROJECT_ID
AND NVL(PDII.TASK_ID,0) =NVL(:TASK_ID,0)
AND Nvl(PDCI.DRAFT_INVOICE_NUM(+), 0)=Nvl(PDI.CONSOLIDATED_INV_NUM, 0)
AND Nvl(PDCI.BILL_GROUP_ID(+), 0) = Nvl(PDI.BILL_GROUP_ID, 0)
AND ( (PDI.CONSOLIDATED_INV_NUM IS NULL AND (PDI.RA_INVOICE_NUMBER =:ARINVNUM OR (PDI.RA_INVOICE_NUMBER IS NULL AND (PDI.PROJECT_ID=:PRJNUM AND PDI.DRAFT_INVOICE_NUM=:DINUM))))
       OR
      (PDI.CONSOLIDATED_INV_NUM IS NOT NULL AND (PDCI.RA_INVOICE_NUMBER = :ARINVNUM OR (PDCI.RA_INVOICE_NUMBER IS NULL AND (PDCI.BILL_GROUP_ID=:BILL_GROUP AND PDCI.DRAFT_INVOICE_NUM=:CONSINVNUM))))
     )
GROUP BY SPF.FUNDING_CATEGORY,
         SPF.PROJFUNC_BASELINED_AMOUNT,
         PDI.PROJECT_ID,
         PDII.TASK_ID,
         PDI.BILL_THROUGH_DATE) T
GROUP BY  FUNDING_CATEGORY,
          BILL_THROUGH_DATE
UNION
SELECT FUNDING_CATEGORY,
       SUM(PROJFUNC_BASELINED_AMOUNT) FUND_AMT,
       BILL_THROUGH_DATE
FROM 
(SELECT PF.PROJFUNC_ALLOCATED_AMOUNT  PROJFUNC_BASELINED_AMOUNT,
        PF.FUNDING_CATEGORY  FUNDING_CATEGORY,
        PDI.BILL_THROUGH_DATE
FROM PA_DRAFT_INVOICES_ALL PDI,
     PA_PROJECT_FUNDINGS PF,
     PA_DRAFT_INVOICE_ITEMS PDII,
     PA_PROJECTS_ALL PA,
     PA_DRAFT_CONSOLIDATED_INV_ALL PDCI
WHERE PF.PROJECT_ID=PDI.PROJECT_ID 
AND NVL(PF.TASK_ID,0)=NVL(PDII.TASK_ID,0) 
AND NVL(PA.billing_funds_check_fc_flag,'N') = 'N'
AND PDII.DRAFT_INVOICE_NUM=PDI.DRAFT_INVOICE_NUM
AND PDI.PROJECT_ID=PDII.PROJECT_ID
AND NVL(PDII.TASK_ID,0) =NVL(:TASK_ID,0)
AND Nvl(PDCI.DRAFT_INVOICE_NUM(+), 0)=Nvl(PDI.CONSOLIDATED_INV_NUM, 0)
AND Nvl(PDCI.BILL_GROUP_ID(+), 0) = Nvl(PDI.BILL_GROUP_ID, 0)
AND ( (PDI.CONSOLIDATED_INV_NUM IS NULL AND (PDI.RA_INVOICE_NUMBER =:ARINVNUM OR (PDI.RA_INVOICE_NUMBER IS NULL AND (PDI.PROJECT_ID=:PRJNUM AND PDI.DRAFT_INVOICE_NUM=:DINUM))))
       OR
      (PDI.CONSOLIDATED_INV_NUM IS NOT NULL AND (PDCI.RA_INVOICE_NUMBER = :ARINVNUM OR (PDCI.RA_INVOICE_NUMBER IS NULL AND (PDCI.BILL_GROUP_ID=:BILL_GROUP AND PDCI.DRAFT_INVOICE_NUM=:CONSINVNUM))))
     )
GROUP BY PF.FUNDING_CATEGORY,
         PF.PROJFUNC_ALLOCATED_AMOUNT,
         PDI.PROJECT_ID,
         PDII.TASK_ID,
         PDI.BILL_THROUGH_DATE) T
GROUP BY  FUNDING_CATEGORY,
          BILL_THROUGH_DATE