This discussion is archived
7 Replies Latest reply: May 31, 2009 10:39 PM by Kranthi.K RSS

Projects related query??

Kranthi.K Guru
Currently Being Moderated
Hi ,

Iam not aware of projects module,but i got a requirement to built a projects module related query in R12 module.

My requirement is i need to built projects invoice query.All important columns like customer_name,project_number,
agreement,task_no,task_types,quantity,uom,invoice_amount this is what they told.

Please can any1 who have projects module knowledge,help me by building the query.

Regards,
Kranthi.
  • 1. Re: Projects related query??
    653160 Journeyer
    Currently Being Moderated
    Hi,

    You can use the pa_draft_invoices_v , you have most of the columns there. If task level information is needed, you
    can use pa_draft_invoice_items table.

    Thanks,
    Sathish Raju
    www.projectsaccounting.com
  • 2. Re: Projects related query??
    Kranthi.K Guru
    Currently Being Moderated
    Hi sathish,

    Thanks for replying me.

    i checked out the tables you have posted and tried to built a query.Was not able to find suitable joins for some tables.As i dont know projects module iam not understanding the flow or functionality.Blindly querying the tables

    If you built me the query it would be greatly appreciated.Tommorow i will post the query uptill where i did.


    Thanks,
    Regards,
    Kranthi.
  • 3. Re: Projects related query??
    653160 Journeyer
    Currently Being Moderated
    You are welcome. You can post your query here.
  • 4. Re: Projects related query??
    Kranthi.K Guru
    Currently Being Moderated
    Hi sathish,

    Here goes my query.Until pa_agreements_all i got correct data.From here on iam getting repeating rows and stuck here as i dont know flow.Please complete my query below is my requirement.

    select hz.party_id,
    hz.party_number customer_number,
    hz.party_name customer_name,
    hca.cust_account_id,
    hca.account_number cust_account,
    pdi.draft_invoice_num,
    ppa.project_id,
    ppa.name project_name,
    ppa.segment1 project_number,
    hao.organization_id,
    hao.name operating_unit,
    paa.agreement_num,
    pae.quantity,
    pae.unit_of_measure,
    pt.task_number,
    pt.task_name

    from hz_parties hz,
    hz_cust_accounts hca,
    pa_draft_invoices_all pdi,
    pa_projects_all ppa,
    hr_all_organization_units hao,
    pa_agreements_all paa,
    pa_expenditure_items_all pae,
    pa_tasks pt

    where hz.party_name='Biogen Idec'
    and hz.party_id=hca.party_id
    and hca.cust_account_id=pdi.customer_id
    and pdi.project_id= ppa.project_id
    and ppa.segment1='MCFT01BGN001'
    and pdi.agreement_id= paa.agreement_id
    and ppa.carrying_out_organization_id=hao.organization_id
    and pae.project_id=ppa.project_id
    and ppa.carrying_out_organization_id=pae.organization_id
    and pae.task_id=pt.task_id



    Requirement:-Invoice report,were i should capture the above columns including agreement_amount,task_type(task_deatils) i think i.e. is it freight or sample courier and others,AR_no,invoice_value,due_date.This is my requirement.

    Hope you build me the complete query,it whould be greatly appreciated

    Regards,
    Kranthi
  • 5. Re: Projects related query??
    653160 Journeyer
    Currently Being Moderated
    Hello,

    Below is a generic query that i use. I modified it according to your needs. Set the org_id before running the query. This query meets most of your needs.

    SELECT i.ROWID row_id, i.project_id,ii.task_id, p.segment1 project_number,pat.task_number,
    ttype.task_type,p.NAME project_name, p.distribution_rule,
    dr.meaning distribution_rule_m, p.project_type,
    ps.project_status_name project_status_m,
    p.project_status_code project_status_code,
    org.organization_id project_organization_id,
    org.NAME project_organization_name, a.customer_id, c.customer_name,
    c.customer_number, i.agreement_id, a.agreement_num, a.agreement_type,
    a.term_id, a.amount, t.NAME term_name, i.draft_invoice_num,
    i.transfer_status_code, lk.lookup_code invoice_status_code,
    lk.meaning invoice_status_m,
    DECODE (i.generation_error_flag,
    'Y', 'ERROR',
    DECODE (i.approved_date,
    NULL, 'UNAPPROVED',
    DECODE (i.released_date,
    NULL, 'UNRELEASED',
    DECODE (i.transfer_status_code,
    'P', 'RELEASED',
    'X', 'REJECTED',
    'T', 'TRANSFERRED',
    'A', 'ACCEPTED',
    'R', 'REJECTED',
    'OTHER'
    )
    )
    )
    ) chk_status,
    i.bill_through_date, i.approved_date, i.approved_by_person_id,
    emp2.full_name approved_by_person_name,
    emp2.employee_number approved_by_person_number, i.released_date,
    i.released_by_person_id, emp.full_name released_by_person_name,
    emp.employee_number released_by_person_number, i.invoice_date,
    i.ra_invoice_number, i.pa_date, i.gl_date, i.creation_date,
    i.invoice_comment, i.draft_invoice_num_credited, i.canceled_flag,
    i.cancel_credit_memo_flag, i.write_off_flag, i.converted_flag,
    i.extracted_date, i.retention_percentage, i.inv_currency_code,
    i.inv_currency_code, i.inv_rate_type, i.inv_rate_date,
    i.inv_exchange_rate, con.user_conversion_type,
    p.project_currency_code, lk3.meaning invoice_class,
    i.attribute_category, i.attribute1, i.attribute2, i.attribute3,
    i.attribute4, i.attribute5, i.attribute6, i.attribute7, i.attribute8,
    i.attribute9, i.attribute10, i.generation_error_flag,
    i.transferred_date, i.transfer_rejection_reason,
    DECODE (i.generation_error_flag,
    'Y', 'Y',
    DECODE (i.transfer_status_code, 'R', 'Y', 'X', 'Y', 'N')
    ) exception_flag,
    SUM (NVL (ii.unearned_revenue_cr, 0)),
    SUM (NVL (ii.unbilled_receivable_dr, 0)), i.system_reference,
    i.customer_bill_split, i.invoice_set_id,
    SUM (ii.inv_amount) invoice_amount, SUM (NVL (ii.amount, 0)) amount,
    DECODE (i.system_reference,
    NULL, SUM (NVL (ii.inv_amount, 0)),
    NULL
    ) original_balance,
    DECODE (i.system_reference,
    NULL, SUM (NVL (ii.inv_amount, 0)),
    NULL
    ) balance_due,
    NULL amount_applied, NULL amount_credited, NULL write_off,
    i.last_update_date, i.last_updated_by, i.last_update_login,
    i.program_application_id, i.program_id, i.program_update_date,
    i.bill_to_address_id, i.ship_to_address_id,
    pa_security.allow_update (i.project_id),
    pa_security.view_labor_costs (i.project_id),
    i.receivable_code_combination_id, i.rounding_code_combination_id,
    i.unbilled_code_combination_id, i.unearned_code_combination_id,
    i.woff_code_combination_id,
    SUM (DECODE (i.transfer_status_code, 'A', ii.acct_amount, 0)
    ) recv_inv_amt,
    SUM (DECODE (i.transfer_status_code, 'A', ii.rounding_amount, 0)
    ) round_amt,
    i.LANGUAGE, f.nls_language,
    DECODE (i.transfer_status_code, 'A', i.acctd_curr_code, NULL),
    DECODE (i.transfer_status_code, 'A', i.acctd_rate_type, NULL),
    DECODE (i.transfer_status_code, 'A', i.acctd_rate_date, NULL),
    DECODE (i.transfer_status_code, 'A', i.acctd_exchg_rate, NULL),
    pa_output_tax.get_draft_invoice_tax_amt (i.system_reference),
    DECODE (NVL (pt.cc_prvdr_flag, 'N'),
    'Y', 'INTERCOMPANY INVOICE',
    DECODE (NVL (pc.bill_another_project_flag, 'N'),
    'Y', 'INTER-PROJECT INVOICE',
    'EXTERNAL INVOICE'
    )
    ) invoice_category,
    pi.invoice_batch_source_id, pi.cc_ic_ar_batch_source_id,
    i.cc_project_id, i.ib_ap_transfer_status_code,
    i.ib_ap_transfer_error_code, ii.invproc_currency_code,
    SUM (ii.amount) invprc_bill_amount, ii.projfunc_currency_code,
    SUM (NVL (ii.projfunc_bill_amount, 0)) projfunc_bill_amount,
    SUM (ii.project_bill_amount) project_bill_amount,
    ii.funding_currency_code,
    SUM (ii.funding_bill_amount) funding_bill_amount,
    i.projfunc_invtrans_rate_type projfunc_invtrans_rate_type,
    pa_multi_currency.get_user_conversion_type
    (i.projfunc_invtrans_rate_type),
    i.projfunc_invtrans_rate_date projfunc_invtrans_rate_date,
    TO_NUMBER (i.projfunc_invtrans_ex_rate) projfunc_invtrans_ex_rate,
    i.retention_invoice_flag, i.retn_code_combination_id,
    i.credit_memo_reason_code, i.bill_to_customer_id,
    i.ship_to_customer_id, bill_c.customer_number, bill_c.customer_name,
    ship_c.customer_number, ship_c.customer_name, NULL, NULL, NULL, NULL
    FROM pa_draft_invoices i,
    pa_draft_inv_items_bas ii,
    pa_projects p,
    pa_tasks pat,
    pa_proj_elements ppe,
    pa_task_types ttype,
    pa_project_types pt,
    pa_project_customers pc,
    pa_project_statuses ps,
    pa_distribution_rules dr,
    hr_all_organization_units org,
    pa_agreements_all a,
    pa_draft_invoices org_inv,
    pa_conversion_types_v con,
    pa_implementations pi,
    gl_sets_of_books gsob,
    fnd_languages f,
    ra_terms t,
    pa_customers_v c,
    per_all_people_f emp,
    per_all_people_f emp2,
    pa_lookups lk,
    pa_lookups lk3,
    pa_customers_v bill_c,
    pa_customers_v ship_c
    WHERE 'Y' IN (SELECT pa_security.allow_query (i.project_id)
    FROM SYS.DUAL)
    AND p.project_type = pt.project_type
    AND p.project_id = pc.project_id
    AND nvl(ii.task_id,ii.event_task_id ) = pat.task_id
    and ppe.project_id = p.project_id
    and ppe.element_number = pat.task_number
    and ppe.object_type = 'PA_TASKS'
    and ppe.type_id = ttype.task_type_id
    AND a.customer_id = pc.customer_id
    AND p.project_id = i.project_id
    AND p.project_status_code = ps.project_status_code
    AND ps.status_type = 'PROJECT'
    AND i.agreement_id = a.agreement_id
    AND pi.set_of_books_id = gsob.set_of_books_id
    AND con.conversion_type(+) = i.inv_rate_type
    AND dr.distribution_rule = p.distribution_rule
    AND org_inv.project_id(+) = i.project_id
    AND org_inv.draft_invoice_num(+) = i.draft_invoice_num_credited
    AND t.term_id = a.term_id
    AND ii.project_id(+) = i.project_id
    AND ii.draft_invoice_num(+) = i.draft_invoice_num
    AND p.carrying_out_organization_id = org.organization_id
    AND c.customer_id = a.customer_id
    AND emp.person_id(+) = i.released_by_person_id
    AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
    AND emp2.person_id(+) = i.approved_by_person_id
    AND TRUNC (SYSDATE) BETWEEN emp2.effective_start_date(+) AND emp2.effective_end_date(+)
    AND lk3.lookup_type = 'INVOICE_CLASS'
    AND lk3.lookup_code =
    DECODE (org_inv.canceled_flag,
    'Y', 'CANCEL',
    DECODE (i.write_off_flag,
    'Y', 'WRITE_OFF',
    DECODE (NVL (i.draft_invoice_num_credited, 0),
    0, 'INVOICE',
    'CREDIT_MEMO'
    )
    )
    )
    AND lk3.enabled_flag = 'Y'
    AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (lk3.start_date_active,
    SYSDATE - 1
    )
    )
    AND TRUNC (NVL (lk3.end_date_active, SYSDATE))
    AND lk.lookup_type = 'INVOICE STATUS'
    AND lk.lookup_code =
    DECODE (i.generation_error_flag,
    'Y', 'GENERATION ERROR',
    DECODE (i.approved_date,
    NULL, 'UNAPPROVED',
    DECODE (i.released_date,
    NULL, 'APPROVED',
    DECODE (i.transfer_status_code,
    'P', 'RELEASED',
    'X', 'REJECTED IN TRANSFER',
    'T', 'TRANSFERRED',
    'A', 'ACCEPTED',
    'R', 'REJECTED'
    )
    )
    )
    )
    AND i.LANGUAGE = f.language_code(+)
    AND bill_c.customer_id(+) = i.bill_to_customer_id
    AND ship_c.customer_id(+) = i.ship_to_customer_id
    GROUP BY i.ROWID,
    i.project_id,
    ii.task_id,
    pat.task_number,
    ttype.task_type,
    p.segment1,
    p.NAME,
    p.distribution_rule,
    dr.meaning,
    p.project_type,
    ps.project_status_name,
    p.project_status_code,
    org.organization_id,
    org.NAME,
    a.customer_id,
    c.customer_name,
    c.customer_number,
    i.agreement_id,
    a.agreement_num,
    a.agreement_type,
    a.term_id,
    a.amount,
    t.NAME,
    i.draft_invoice_num,
    i.transfer_status_code,
    lk.lookup_code,
    lk.meaning,
    DECODE (i.generation_error_flag,
    'Y', 'ERROR',
    DECODE (i.approved_date,
    NULL, 'UNAPPROVED',
    DECODE (i.released_date,
    NULL, 'UNRELEASED',
    DECODE (i.transfer_status_code,
    'P', 'RELEASED',
    'X', 'REJECTED IN TRANSFER',
    'T', 'TRANSFERRED',
    'A', 'ACCEPTED',
    'R', 'REJECTED',
    'OTHER'
    )
    )
    )
    ),
    i.bill_through_date,
    i.approved_date,
    i.approved_by_person_id,
    emp2.full_name,
    emp2.employee_number,
    i.released_date,
    i.released_by_person_id,
    emp.full_name,
    emp.employee_number,
    i.invoice_date,
    i.ra_invoice_number,
    i.pa_date,
    i.gl_date,
    i.creation_date,
    i.invoice_comment,
    i.draft_invoice_num_credited,
    i.canceled_flag,
    i.cancel_credit_memo_flag,
    i.write_off_flag,
    i.converted_flag,
    i.extracted_date,
    i.retention_percentage,
    i.inv_currency_code,
    i.inv_rate_type,
    i.inv_rate_date,
    i.inv_exchange_rate,
    con.user_conversion_type,
    p.project_currency_code,
    lk3.meaning,
    i.attribute_category,
    i.attribute1,
    i.attribute2,
    i.attribute3,
    i.attribute4,
    i.attribute5,
    i.attribute6,
    i.attribute7,
    i.attribute8,
    i.attribute9,
    i.attribute10,
    i.generation_error_flag,
    i.transferred_date,
    i.transfer_rejection_reason,
    DECODE (i.generation_error_flag,
    'Y', 'Y',
    DECODE (i.transfer_status_code, 'R', 'Y', 'X', 'Y', 'N')
    ),
    i.system_reference,
    i.customer_bill_split,
    i.invoice_set_id,
    i.last_update_date,
    i.last_updated_by,
    i.last_update_login,
    i.program_application_id,
    i.program_id,
    i.program_update_date,
    i.bill_to_address_id,
    i.ship_to_address_id,
    p.template_flag,
    i.receivable_code_combination_id,
    i.rounding_code_combination_id,
    i.unbilled_code_combination_id,
    i.unearned_code_combination_id,
    i.woff_code_combination_id,
    i.LANGUAGE,
    f.nls_language,
    i.acctd_curr_code,
    i.acctd_rate_type,
    i.acctd_rate_date,
    i.acctd_exchg_rate,
    DECODE (NVL (pt.cc_prvdr_flag, 'N'),
    'Y', 'INTERCOMPANY INVOICE',
    DECODE (NVL (pc.bill_another_project_flag, 'N'),
    'Y', 'INTER-PROJECT INVOICE',
    'EXTERNAL INVOICE'
    )
    ),
    pi.invoice_batch_source_id,
    pi.cc_ic_ar_batch_source_id,
    i.cc_project_id,
    i.ib_ap_transfer_status_code,
    i.ib_ap_transfer_error_code,
    ii.invproc_currency_code,
    ii.projfunc_currency_code,
    ii.funding_currency_code,
    i.projfunc_invtrans_rate_type,
    i.projfunc_invtrans_rate_date,
    TO_NUMBER (i.projfunc_invtrans_ex_rate),
    i.retention_invoice_flag,
    i.retn_code_combination_id,
    i.credit_memo_reason_code,
    i.bill_to_customer_id,
    i.ship_to_customer_id,
    bill_c.customer_number,
    bill_c.customer_name,
    ship_c.customer_number,
    ship_c.customer_name

    Thanks,
    Sathish Raju
    www.projectsaccounting.com
  • 6. Re: Projects related query??
    Kranthi.K Guru
    Currently Being Moderated
    Hi Sathish,

    Thanks for quick reply,as its end of day for me i will look forward it tommorow.


    But the query you gave has too much information,i need to filter it and then see what i need so i get what i desire.
    Its nice of you to post me the query.If any further doubts i will clarify with you.


    Thanks,
    Kranthi.
  • 7. Re: Projects related query??
    Kranthi.K Guru
    Currently Being Moderated
    Hi sathish,

    I have filtered according to my requirement and got stuck with the query.iam getting duplicate rows,i think i missed some table or join.Iam not understanding.Please do look at it and correct the query and post it.Update me as quickly as possible.

    The requirement is monthly invoice report.

    SELECT i.project_id,
    ii.task_id,
    p.segment1 project_number,
    pat.task_number,
    ttype.task_type,
    p.NAME project_name,
    p.project_type,
    p.project_status_code status_code,
    org.organization_id organization_id,
    org.NAME organization_name,
    a.customer_id,
    c.party_name,
    i.agreement_id,
    a.agreement_num,
    a.agreement_type,
    a.term_id,
    pae.expenditure_type,
    pae.quantity,
    pae.unit_of_measure,
    a.amount agreement_amount,
    ii.inv_amount invoice_amount,
    t.NAME term_name,
    i.draft_invoice_num,
    i.transfer_status_code,
    DECODE (i.transfer_status_code,'P', 'RELEASED','X', 'REJECTED','T', 'TRANSFERRED','A', 'ACCEPTED','R', 'REJECTED','OTHER') chk_status,
    emp2.full_name approved_by_person_name,
    emp2.employee_number approved_by_person_number,
    emp.full_name released_by_person_name,
    emp.employee_number released_by_person_number,
    i.invoice_date,
    i.ra_invoice_number,
    i.gl_date,
    i.inv_currency_code


    FROM pa_draft_invoices_all i,
    pa_draft_inv_items_bas ii,
    pa_projects_all p,
    pa_expenditure_items_all pae,
    pa_tasks pat,
    pa_proj_elements ppe,
    pa_task_types ttype,
    pa_project_customers pc,
    hr_all_organization_units org,
    pa_agreements_all a,
    pa_draft_invoices_all org_inv,
    ra_terms t,
    hz_parties c,
    per_all_people_f emp,
    per_all_people_f emp2


    WHERE p.project_id = pc.project_id
    AND nvl(ii.task_id,ii.event_task_id ) = pat.task_id
    and ppe.project_id = p.project_id
    and ppe.element_number = pat.task_number
    and ppe.object_type = 'PA_TASKS'
    and ppe.type_id = ttype.task_type_id
    AND a.customer_id = pc.customer_id
    AND p.project_id = i.project_id
    AND p.segment1='MCFT01AVL007''MCFT05BGN001'
    AND p.project_id = pae.project_id
    AND i.agreement_id = a.agreement_id
    AND org_inv.project_id(+) = i.project_id
    AND org_inv.draft_invoice_num(+) = i.draft_invoice_num_credited
    AND t.term_id = a.term_id
    AND ii.project_id(+) = i.project_id
    AND ii.draft_invoice_num(+) = i.draft_invoice_num
    AND p.carrying_out_organization_id = org.organization_id
    AND c.party_id = a.customer_id
    --AND c.party_name like 'BIOGEN%'
    AND emp.person_id(+) = i.released_by_person_id
    AND emp2.person_id(+) = i.approved_by_person_id



    By,
    Kranthi.

    Edited by: Kranthi.K on May 31, 2009 10:39 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points