Query: PA DRAFT INVOICE LINK TO AR INVOICE

PA DRAFT INVOICE LINK TO AR INVOICE

From Flexfield setup in the application:

For RA_CUSTOMER_TRX_ALL AND RA_CUSTOMER_TRX_LINES_ALL

Interface Line context = ‘PROJECTS INVOICES’

Interface_Line_attribute1 -> Project_number

Interface_Line_attribute2 -> Draft Invoice Number

Interface_Line_attribute3 -> Agreement Number

Interface_Line_attribute4 -> Project Organization

Interface_Line_attribute5 -> Project Manager

Interface_Line_attribute6 -> Line Number

Interface_Line_attribute7 -> Type

Interface_Line_attribute8 -> Line Type

And might be able to depend on:

PA_DRAFT_INVOICES_ALL.SYSTEM_REFERENCE = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID

If the above assumptions are correct then the following SQL should be able to link PA Draft Invoices to AR Invoices:

select PAP.project_id,PAP.segment1 PROJECT_NUMBER,

PAT.TASK_NUMBER, PAP.name,hp.party_name customer,PAP.target_start_date,PAP.target_finish_date,

PAP.project_type,ps.project_system_status_code status,

paa.agreement_num,paa.agreement_type,PAP.project_value,

pdi.transfer_status_code,L.MEANING, L.DESCRIPTION, pdi.transfer_rejection_reason,

PDI.DRAFT_INVOICE_NUM, PDI.CREATION_DATE, PDI.LAST_UPDATE_DATE, PDI.RA_INVOICE_NUMBER, PDI.INVOICE_COMMENT,

PDII.LINE_NUM,PDII.INVOICE_LINE_TYPE, PDII.AMOUNT, PDII.TEXT, ct.trx_number AR_inv_num, ct.complete_flag, ct.status_trx,

ctl.line_number, ctl.line_type, ctl.description inv_line_desc, ctl.extended_amount, ctl.revenue_amount

,ctl.interface_line_attribute1 Project_number

,ctl.interface_line_attribute2 Draft_Invoice_Number

,ctl.interface_line_attribute3 Agreement_Number

,ctl.interface_line_attribute4 Project_Organization

,ctl.interface_line_attribute5 Project_Manager

,ctl.interface_line_attribute6 Line_Number

,ctl.interface_line_attribute7 Type

–,ctl.interface_line_attribute8 Line_Type

–,ct.*

from

APPS.pa_projects_all pap

JOIN APPS.pa_draft_invoices_all pdi ON (pap.project_id=pdi.project_id)

JOIN APPS.pa_draft_invoice_items pdii on (pdi.project_id = pdii.project_id and pdi.draft_invoice_num=pdii.draft_invoice_num)

JOIN APPS.PA_TASKS PAT ON (PDII.TASK_ID = PAT.TASK_ID)

JOIN APPS.pa_agreements_all paa ON (PDI.AGREEMENT_ID = PAA.AGREEMENT_ID)

JOIN APPS.pa_project_statuses ps ON (PAP.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE)

JOIN APPS.hz_cust_accounts_all hca ON (PDI.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID)

JOIN APPS.hz_parties hp ON (HCA.PARTY_ID = HP.PARTY_ID)

LEFT OUTER JOIN APPS.PA_LOOKUPS L ON (PDI.TRANSFER_STATUS_CODE = L.LOOKUP_CODE AND L.LOOKUP_TYPE = ‘TRANSFER STATUS’)

LEFT OUTER JOIN apps.ra_customer_trx_lines_all ctl on (PDI.SYSTEM_REFERENCE = CTL.CUSTOMER_TRX_ID AND PDII.LINE_NUM = CTL.INTERFACE_LINE_ATTRIBUTE6)

LEFT OUTER JOIN apps.ra_customer_trx_all ct ON (CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID)

where

PAP.PROJECT_ID = &PROJECT_ID

order by PAP.SEGMENT1, PDI.DRAFT_INVOICE_NUM

There might be rejected transactions in PA Draft Invoices – here is how to find those transactions:

select DISTINCT pdi.org_id, PAP.SEGMENT1 PROJECT_NUM, pdi.transfer_status_code,L.MEANING, L.DESCRIPTION, pdi.transfer_rejection_reason,

PDI.DRAFT_INVOICE_NUM, PDI.CREATION_DATE, PDI.LAST_UPDATE_DATE, PDI.RA_INVOICE_NUMBER, PDI.INVOICE_COMMENT,

PAP.TARGET_START_DATE, PAP.TARGET_FINISH_DATE, PAP.PROJECT_TYPE, ORG.NAME ORG_NAME, PS.PROJECT_SYSTEM_STATUS_CODE PROJ_STATUS,

PAPF.FULL_NAME PROJ_MANAGER, PAA.AGREEMENT_NUM, PAA.AGREEMENT_TYPE, PAP.PROJECT_ID

from apps.pa_draft_invoices_all pdi

LEFT OUTER JOIN APPS.PA_LOOKUPS L ON (PDI.TRANSFER_STATUS_CODE = L.LOOKUP_CODE AND L.LOOKUP_TYPE = ‘TRANSFER STATUS’)

JOIN APPS.PA_PROJECTS_ALL PAP ON (PDI.PROJECT_ID = PAP.PROJECT_ID)

JOIN APPS.PA_AGREEMENTS_ALL PAA ON (PDI.AGREEMENT_ID = PAA.AGREEMENT_ID)

JOIN APPS.hr_organization_units org ON (PAP.carrying_out_organization_id = ORG.ORGANIZATION_ID)

JOIN APPS.pa_project_players papp ON (PAP.PROJECT_ID = PAPP.PROJECT_ID AND PAPP.PROJECT_ROLE_TYPE = ‘PROJECT MANAGER’AND SYSDATE < NVL(PAPP.END_DATE_ACTIVE, SYSDATE+10))

JOIN APPS.per_all_people_f papf ON (PAPP.PERSON_ID = PAPF.PERSON_ID)

JOIN APPS.pa_project_statuses ps ON (PAP.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE)

where pdi.system_reference is null

AND PDI.ORG_ID = &ORG_ID

order by PDI.ORG_ID, PAP.SEGMENT1, PDI.DRAFT_INVOICE_NUM