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.
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
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.
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