Projects Contracts Expenditure query for invoice in Oracle Fusion

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)

You may like Bursting in BIP

More fusion apps tech

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
100% Free SEO Tools - Tool Kits PRO
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart