7 Replies Latest reply: Jun 1, 2009 12:39 AM by Kranthi.K RSS

    Projects related query??

    Kranthi.K
      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
          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
            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
              You are welcome. You can post your query here.
              • 4. Re: Projects related query??
                Kranthi.K
                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
                  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
                    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
                      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