Projects Contracts Expenditure query for invoice in Oracle Fusion

2199 Views

SaveSavedRemoved 0

While building reports for Projects Contracts Expenditure and related invoice you would need to know the tables and joins. here is a basic query for fetching Contract Invoice projects distributions.

Below is the screen with Contract Receivables invoice lines . The lines have project expenditures in distributions.

Navigation > amy.marlin login> Contracts management > contracts > search with contract no and invoice number or project name.

image 3 Projects Contracts Expenditure query for invoice in Oracle Fusion 1

The expenditure details are stored at distribution level in table PJB_INV_LINE_DISTS , you will need to join with pjf_projects_all_vl , pjc_exp_items_all , pjb_inv_line_dists , pjf_exp_types_vl pet and any other tables as required to get the data.

Important Contract tables for Oracle Fusion Cloud

1. OKC_K_HEADERS_ALL_B
2. OKC_K_HEADERS_TL
3. OKC_K_LINES_B
4. OKC_K_PARTY_ROLES_B
5. OKC_K_SALES_CREDITS
6. OKC_K_STATUS_HISTORY_B
7. OKC_PRICING_TERMS
8. OKC_CONTACTS
9. OKC_CONTRACT_TYPES_B
10. OKC_K_CHARGE_COMPONENTS

SELECT ral.line_number,
       PILD.transaction_id,
       PEX .expenditure_item_id,
       Decode(PEt.expenditure_type_name, 'Professional', 'Labor',
                                         'Non-Labor') expenditure_type_name,
       pa.segment1,
       pa.name,
       pild.transaction_id                            trxnumber,
       pild.trns_curr_billed_amt                      invoiceamt,
       pex.unit_of_measure,
       PPN.full_name                                  Consultant,
       PEx.quantity
FROM   ra_customer_trx_all rac,
       ra_customer_trx_lines_all ral,
       pjf_projects_all_vl PA,
       pjc_exp_items_all PEX,
       pjb_inv_line_dists PILD,
       pjf_exp_types_vl pet,
       per_all_people_f ppf,
       per_person_names_f ppn
WHERE  ral.customer_trx_id = rac.customer_trx_id
       AND rac.org_id = ral.org_id
       AND ral.line_type = 'LINE'
       AND PA.project_id = PEX.project_id
       AND PILD.transaction_id = PEX .expenditure_item_id
       --AND pa.segment1 = pex.orig_transaction_reference
       AND PA.project_id = ral.interface_line_attribute10
       AND rac.trx_number = :P_RTRX_NO
       AND ral.line_number = Nvl(:P_Line, ral.line_number)
       AND Decode(PEt.expenditure_type_name, 'Professional', 'Labor',
                                             'Non-Labor') = Nvl(:p_exp,
           Decode(PEt.expenditure_type_name, 'Professional', 'Labor',
                                             'Non-Labor'))
       AND ral.interface_line_attribute2 = pild.contract_id
       AND pild.invoice_line_id = ral.interface_line_attribute5
       AND pex.expenditure_type_id = pet.expenditure_type_id
       --and ral.interface_line_attribute3=19
       --and pild.contract_id =300000195588727
       AND rac.ct_reference = :P_ContractNo
       AND pex.incurred_by_person_id = ppf.person_id(+)
       AND ppf.person_id = ppn.person_id
       AND ppn.name_type = 'GLOBAL'
       AND Trunc (SYSDATE) BETWEEN ppf.effective_start_date AND
                                   ppf.effective_end_date
       AND Trunc (SYSDATE) BETWEEN ppn.effective_start_date AND
                                   ppn.effective_end_date
UNION
SELECT ral.line_number,
       PILD.transaction_id,
       PEX .expenditure_item_id,
       Decode(PEt.expenditure_type_name, 'Professional', 'Labor',
                                         'Non-Labor') expenditure_type_name,
       pa.segment1,
       pa.name,
       pild.transaction_id                            trxnumber,
       pild.trns_curr_billed_amt                      invoiceamt,
       pex.unit_of_measure,
       ''                                             Consultant,
       PEx.quantity
FROM   ra_customer_trx_all rac,
       ra_customer_trx_lines_all ral,
       pjf_projects_all_vl PA,
       pjc_exp_items_all PEX,
       pjb_inv_line_dists PILD,
       pjf_exp_types_vl pet
--per_all_people_f ppf,
--per_person_names_f ppn
WHERE  ral.customer_trx_id = rac.customer_trx_id
       AND rac.org_id = ral.org_id
       AND ral.line_type = 'LINE'
       AND PA.project_id = PEX.project_id
       AND PILD.transaction_id = PEX .expenditure_item_id
       --AND pa.segment1 = pex.orig_transaction_reference
       AND PA.project_id = ral.interface_line_attribute10
       AND rac.trx_number = :P_RTRX_NO
       AND ral.line_number = Nvl(:P_Line, ral.line_number)
       AND Decode(PEt.expenditure_type_name, 'Professional', 'Labor',
                                             'Non-Labor') = Nvl(:p_exp,
           Decode(PEt.expenditure_type_name, 'Professional', 'Labor',
                                             'Non-Labor'))
       AND ral.interface_line_attribute2 = pild.contract_id
       AND pild.invoice_line_id = ral.interface_line_attribute5
       AND pex.expenditure_type_id = pet.expenditure_type_id
       --and ral.interface_line_attribute3=19
       --and pild.contract_id =300000195588727
       AND rac.ct_reference = :P_ContractNo
       --AND pex.incurred_by_person_id = ppf.person_id(+)
       --AND ppf.person_id = ppn.person_id
       --AND ppn.name_type = 'GLOBAL'
       --AND Trunc (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
       --AND Trunc (SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
       AND pex.expenditure_type_id = 300000047429469 --(enter exptype id for non professional invoice)