PA Federal SF 1034 Copy link

Description

Categories: BI Publisher
Application: Projects
Source: MGT: Federal SF 1034
Short Name: PAXFBPVS_XML

DB package:

SQL 106 lines

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

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

Parameters 1

Parameter	Type	List of values SQL
AR Invoice Number	Char

Description

SQL 106 lines

Parameters 1

Find out more

Features

Download

FAQ