VIEW: APPS.PAFV_CUSTOMER_INVOICES

Source

View Text - Preformatted

SELECT ppa.project_id , pdi.draft_invoice_num , pdi.approved_date , pdi.released_date , pdi.invoice_date , pdi.unearned_revenue_cr , pdi.unbilled_receivable_dr , '_LA:pdi.transfer_status_code:PA_LOOKUPS:TRANSFER STATUS:MEANING' , '_LA:pdi.generation_error_flag:FND_LOOKUPS:YES_NO:MEANING' , pdi.bill_through_date , pdi.invoice_comment , pdi.RA_invoice_number , pdi.transferred_date , pdi.transfer_rejection_reason , pdi.gl_date , pl.meaning , rct.trx_number , rct.trx_date , '_DF:PA:PA_DRAFT_INVOICES_DESC_FLEX:pdi' , pdi.agreement_id , pdi.approved_by_person_id , pap1.full_name , pdi.released_by_person_id , pap2.full_name , pa.description , pdi.system_reference , pdi.draft_invoice_num_credited , pdi.org_id , tl.name , pdi.LAST_UPDATE_DATE , pdi.LAST_UPDATED_BY , pdi.CREATION_DATE , pdi.CREATED_BY FROM pa_draft_invoices_all pdi , pa_projects_all ppa , ra_customer_trx_all rct , pa_agreements_all pa , pa_draft_invoices_all pdic , pa_lookups pl , per_all_people_f pap1 , per_all_people_f pap2 , hr_all_organization_units hro, hr_all_organization_units_tl tl WHERE pdi.project_id = ppa.project_id and pdi.agreement_id = pa.agreement_id and pdi.system_reference = rct.customer_trx_id(+) and pdic.project_id(+) = pdi.project_id and pdic.DRAFT_INVOICE_NUM (+) = pdi.DRAFT_INVOICE_NUM_CREDITED AND pl.LOOKUP_TYPE = 'INVOICE_CLASS' AND pl.LOOKUP_CODE = DECODE(pdic.CANCELED_FLAG, 'Y', 'CANCEL', DECODE(pdi.WRITE_OFF_FLAG, 'Y', 'WRITE_OFF', DECODE(NVL(pdi.DRAFT_INVOICE_NUM_CREDITED, 0), 0, 'INVOICE','CREDIT_MEMO'))) AND '_SEC:pdi.org_id' is not null AND pdi.approved_by_person_id=pap1.person_id (+) AND trunc(sysdate) between pap1.effective_start_date (+) and pap1.effective_end_date (+) AND pdi.released_by_person_id=pap2.person_id (+) AND trunc(sysdate) between pap2.effective_start_date (+) and pap2.effective_end_date (+) AND pdi.org_id=hro.organization_id (+) AND pdi.org_id=tl.organization_id (+) AND tl.language = userenv('LANG') WITH READ ONLY

View Text - HTML Formatted

SELECT PPA.PROJECT_ID
, PDI.DRAFT_INVOICE_NUM
, PDI.APPROVED_DATE
, PDI.RELEASED_DATE
, PDI.INVOICE_DATE
, PDI.UNEARNED_REVENUE_CR
, PDI.UNBILLED_RECEIVABLE_DR
, '_LA:PDI.TRANSFER_STATUS_CODE:PA_LOOKUPS:TRANSFER STATUS:MEANING'
, '_LA:PDI.GENERATION_ERROR_FLAG:FND_LOOKUPS:YES_NO:MEANING'
, PDI.BILL_THROUGH_DATE
, PDI.INVOICE_COMMENT
, PDI.RA_INVOICE_NUMBER
, PDI.TRANSFERRED_DATE
, PDI.TRANSFER_REJECTION_REASON
, PDI.GL_DATE
, PL.MEANING
, RCT.TRX_NUMBER
, RCT.TRX_DATE
, '_DF:PA:PA_DRAFT_INVOICES_DESC_FLEX:PDI'
, PDI.AGREEMENT_ID
, PDI.APPROVED_BY_PERSON_ID
, PAP1.FULL_NAME
, PDI.RELEASED_BY_PERSON_ID
, PAP2.FULL_NAME
, PA.DESCRIPTION
, PDI.SYSTEM_REFERENCE
, PDI.DRAFT_INVOICE_NUM_CREDITED
, PDI.ORG_ID
, TL.NAME
, PDI.LAST_UPDATE_DATE
, PDI.LAST_UPDATED_BY
, PDI.CREATION_DATE
, PDI.CREATED_BY
FROM PA_DRAFT_INVOICES_ALL PDI
, PA_PROJECTS_ALL PPA
, RA_CUSTOMER_TRX_ALL RCT
, PA_AGREEMENTS_ALL PA
, PA_DRAFT_INVOICES_ALL PDIC
, PA_LOOKUPS PL
, PER_ALL_PEOPLE_F PAP1
, PER_ALL_PEOPLE_F PAP2
, HR_ALL_ORGANIZATION_UNITS HRO
, HR_ALL_ORGANIZATION_UNITS_TL TL
WHERE PDI.PROJECT_ID = PPA.PROJECT_ID
AND PDI.AGREEMENT_ID = PA.AGREEMENT_ID
AND PDI.SYSTEM_REFERENCE = RCT.CUSTOMER_TRX_ID(+)
AND PDIC.PROJECT_ID(+) = PDI.PROJECT_ID
AND PDIC.DRAFT_INVOICE_NUM (+) = PDI.DRAFT_INVOICE_NUM_CREDITED
AND PL.LOOKUP_TYPE = 'INVOICE_CLASS'
AND PL.LOOKUP_CODE = DECODE(PDIC.CANCELED_FLAG
, 'Y'
, 'CANCEL'
, DECODE(PDI.WRITE_OFF_FLAG
, 'Y'
, 'WRITE_OFF'
, DECODE(NVL(PDI.DRAFT_INVOICE_NUM_CREDITED
, 0)
, 0
, 'INVOICE'
, 'CREDIT_MEMO')))
AND '_SEC:PDI.ORG_ID' IS NOT NULL
AND PDI.APPROVED_BY_PERSON_ID=PAP1.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN PAP1.EFFECTIVE_START_DATE (+)
AND PAP1.EFFECTIVE_END_DATE (+)
AND PDI.RELEASED_BY_PERSON_ID=PAP2.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN PAP2.EFFECTIVE_START_DATE (+)
AND PAP2.EFFECTIVE_END_DATE (+)
AND PDI.ORG_ID=HRO.ORGANIZATION_ID (+)
AND PDI.ORG_ID=TL.ORGANIZATION_ID (+)
AND TL.LANGUAGE = USERENV('LANG') WITH READ ONLY