PA Federal SF 1034

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Federal SF 1034
Short Name: PAXFBPVS_XML
DB package:
SELECT   DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',AG.AGREEMENT_NUM,OK.CONTRACT_NUMBER) CONTRACT_NUMBER,
         DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',CINV.INVOICE_DATE,OKE_HEADER.START_DATE) INVOICE_DATE, 
         CINV.INVOICE_DATE DVP,
         INV.BILL_THROUGH_DATE BILL_DATE,
         NVL(CINV.RA_INVOICE_NUMBER,' ') RA_INVOICE_NUMBER
FROM      PA_DRAFT_INVOICE_ACRN ACRN,
          PA_DRAFT_CONSOLIDATED_INV_ALL CINV,
          OKE_PA_BILLING_PARAMS_V OK,
          PA_DRAFT_INVOICES_ALL  INV,
          PA_AGREEMENTS_ALL AG,
          OKE_K_HEADERS_FULL_V OKE_HEADER
WHERE     ACRN.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND       ACRN.PROJECT_ID        = INV.PROJECT_ID
AND       CINV.DRAFT_INVOICE_NUM=INV.CONSOLIDATED_INV_NUM
AND       CINV.BILL_GROUP_ID=INV.BILL_GROUP_ID
AND       ACRN.PROJECT_ID        = OK.PROJECT_ID(+)
AND       NVL(ACRN.TASK_ID,0)    = NVL(OK.TASK_ID,0)
AND       CINV.RA_INVOICE_NUMBER = :ARINVNUM  
AND       AG.AGREEMENT_ID=INV.AGREEMENT_ID
AND ROWNUM=1
GROUP BY  OK.CONTRACT_NUMBER,
          CINV.INVOICE_DATE, 
          CINV.RA_INVOICE_NUMBER,
          AG.AGREEMENT_NUM,
          INV.BILL_THROUGH_DATE,
          OKE_HEADER.START_DATE 
UNION
SELECT   DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',AG.AGREEMENT_NUM,OK.CONTRACT_NUMBER) CONTRACT_NUMBER,
         DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',CINV.INVOICE_DATE,OKE_HEADER.START_DATE) INVOICE_DATE, 
         CINV.INVOICE_DATE DVP,
         INV.BILL_THROUGH_DATE BILL_DATE,
         NVL(CINV.RA_INVOICE_NUMBER,' ') RA_INVOICE_NUMBER
FROM      PA_DRAFT_INVOICE_ACRN ACRN,
          PA_DRAFT_CONSOLIDATED_INV_ALL CINV,
          OKE_PA_BILLING_PARAMS_V OK,
          PA_DRAFT_INVOICES_ALL  INV,
          PA_AGREEMENTS_ALL AG,
          OKE_K_HEADERS_FULL_V OKE_HEADER
WHERE     ACRN.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND       ACRN.PROJECT_ID        = INV.PROJECT_ID
AND       CINV.DRAFT_INVOICE_NUM=INV.CONSOLIDATED_INV_NUM
AND       CINV.BILL_GROUP_ID=INV.BILL_GROUP_ID
AND       ACRN.PROJECT_ID        = OK.PROJECT_ID(+)
AND       NVL(ACRN.TASK_ID,0)    = NVL(OK.TASK_ID,0)
AND       CINV.BILL_GROUP_ID=:BILL_GROUP AND CINV.DRAFT_INVOICE_NUM=:CONSINVNUM
AND       AG.AGREEMENT_ID=INV.AGREEMENT_ID
AND ROWNUM=1
GROUP BY  OK.CONTRACT_NUMBER,
          CINV.INVOICE_DATE, 
          CINV.RA_INVOICE_NUMBER,
          AG.AGREEMENT_NUM,
          INV.BILL_THROUGH_DATE,
           OKE_HEADER.START_DATE 
UNION 
SELECT    DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',AG.AGREEMENT_NUM,OK.CONTRACT_NUMBER) CONTRACT_NUMBER,
          DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',INV.INVOICE_DATE,OKE_HEADER.START_DATE) INVOICE_DATE,
          INV.INVOICE_DATE DVP, 
          INV.BILL_THROUGH_DATE BILL_DATE,
          NVL(INV.RA_INVOICE_NUMBER,' ') RA_INVOICE_NUMBER
FROM      PA_DRAFT_INVOICE_ACRN ACRN,
          PA_DRAFT_INVOICES_ALL INV,
          OKE_PA_BILLING_PARAMS_V OK,                                                                  
          PA_AGREEMENTS_ALL AG,
          OKE_K_HEADERS_FULL_V OKE_HEADER
WHERE     ACRN.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND       ACRN.PROJECT_ID        = INV.PROJECT_ID
AND       ACRN.PROJECT_ID        = OK.PROJECT_ID(+)
AND       NVL(ACRN.TASK_ID,0)    = NVL(OK.TASK_ID,0)
AND       INV.RA_INVOICE_NUMBER  = :ARINVNUM 
AND       AG.AGREEMENT_ID=INV.AGREEMENT_ID
AND       NVL(OKE_HEADER.PROJECT_ID,0)=NVL(INV.PROJECT_ID,0)
AND       INV.CONSOLIDATED_INV_NUM IS NULL
AND ROWNUM=1
GROUP BY  OK.CONTRACT_NUMBER,
          INV.INVOICE_DATE,
          INV.BILL_THROUGH_DATE,
          INV.RA_INVOICE_NUMBER,
          AG.AGREEMENT_NUM,
          OKE_HEADER.START_DATE
UNION 
SELECT    DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',AG.AGREEMENT_NUM,OK.CONTRACT_NUMBER) CONTRACT_NUMBER,
          DECODE(NVL(OK.CONTRACT_NUMBER,'X'),'X',INV.INVOICE_DATE,OKE_HEADER.START_DATE) INVOICE_DATE,
          INV.INVOICE_DATE DVP, 
          INV.BILL_THROUGH_DATE BILL_DATE,
          NVL(INV.RA_INVOICE_NUMBER,' ') RA_INVOICE_NUMBER
FROM      PA_DRAFT_INVOICE_ACRN ACRN,
          PA_DRAFT_INVOICES_ALL INV,
          OKE_PA_BILLING_PARAMS_V OK,                                                                  
          PA_AGREEMENTS_ALL AG,
          OKE_K_HEADERS_FULL_V OKE_HEADER
WHERE     ACRN.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND       ACRN.PROJECT_ID        = INV.PROJECT_ID
AND       ACRN.PROJECT_ID        = OK.PROJECT_ID(+)
AND       NVL(ACRN.TASK_ID,0)    = NVL(OK.TASK_ID,0)
AND       INV.PROJECT_ID=:PRJNUM AND INV.DRAFT_INVOICE_NUM=:DINUM
AND       AG.AGREEMENT_ID=INV.AGREEMENT_ID
AND       NVL(OKE_HEADER.PROJECT_ID,0)=NVL(INV.PROJECT_ID,0)
AND       INV.CONSOLIDATED_INV_NUM IS NULL
AND ROWNUM=1
GROUP BY  OK.CONTRACT_NUMBER,
          INV.INVOICE_DATE,
          INV.BILL_THROUGH_DATE,
          INV.RA_INVOICE_NUMBER,
          AG.AGREEMENT_NUM,
          OKE_HEADER.START_DATE
Parameter Name SQL text Validation
AR Invoice Number