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.
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 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.
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.
from hz_parties hz,
where hz.party_name='Biogen Idec'
and pdi.project_id= ppa.project_id
and pdi.agreement_id= paa.agreement_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
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.
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.
DECODE (i.transfer_status_code,'P', 'RELEASED','X', 'REJECTED','T', 'TRANSFERRED','A', 'ACCEPTED','R', 'REJECTED','OTHER') chk_status,
FROM pa_draft_invoices_all i,
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
Edited by: Kranthi.K on May 31, 2009 10:39 PM