[Home] [Help]
50:
51: CURSOR sel_inv_autolink IS
52: SELECT /*+ leading(aha) */ pdi.project_id , pdi.draft_invoice_num , EI.document_header_id -- leading Hint Added for bug#12404156
53: AP_INVOICE_ID , 'AUTOLINK' link_type
54: FROM pa_projects prj ,
55: pa_draft_invoices pdi ,
56: pa_draft_invoice_items pdii ,
57: pa_cust_rev_dist_lines crdl ,
58: pa_expenditure_items ei ,
96: SELECT pwp.project_id
97: , pwp.draft_invoice_num
98: , pwp.AP_INVOICE_ID
99: ,'MANUAL' link_type
100: FROM pa_projects prj
101: , pa_draft_invoices pdi
102: , pa_pwp_linked_invoices pwp
103: ,( select distinct invoice_id
104: from ap_holds_all
152: )
153: THEN 'N'
154: ELSE 'Y'
155: END, 'Y') payment_status
156: FROM pa_projects prj
157: , ra_customer_trx rac
158: , pa_draft_invoices pdi
159: , pa_draft_invoice_items pdii
160: , pa_events pae
202: */
203: CURSOR cur_inv_unbillable IS
204: SELECT /*+ leading(aha) */ DISTINCT EI.DOCUMENT_HEADER_ID -- leading Hint Added for bug#12404156
205: FROM PA_EXPENDITURE_ITEMS EI
206: ,PA_PROJECTS PROJ
207: ,PA_TASKS TASK
208: ,PA_TASKS TOPTASK
209: ,
210: ( select distinct invoice_id
230: AND NOT EXISTS
231: (SELECT /*+ INDEX(EI2 PA_EXPENDITURE_ITEMS_N27)*/
232: 1
233: FROM PA_EXPENDITURE_ITEMS EI2
234: ,PA_PROJECTS PROJ
235: ,PA_TASKS TASK
236: ,PA_TASKS TOPTASK
237: WHERE EI2.DOCUMENT_HEADER_ID =EI.DOCUMENT_HEADER_ID
238: AND EI2.PROJECT_ID = PROJ.PROJECT_ID
307:
308:
309: if (p_proj_num is null) then
310: if ( P_from_proj_num is null ) then
311: select min(pap.segment1) into start_project_num from pa_projects pap where project_type = nvl(p_project_type,pap.project_type) ;
312: else
313: start_project_num := P_from_proj_num;
314: end if;
315: if (p_to_proj_num is null ) then
312: else
313: start_project_num := P_from_proj_num;
314: end if;
315: if (p_to_proj_num is null ) then
316: select max(pap.segment1) into end_project_num from pa_projects pap where project_type = nvl(p_project_type,pap.project_type) ;
317: else
318: end_project_num := p_to_proj_num;
319: end if;
320: else
692: WHERE EXISTS
693: (
694: -- revnue generated but unbilled transactions exist for the AP Invoice
695: SELECT 1
696: FROM pa_projects_all proj,
697: pa_tasks task,
698: pa_tasks toptask,
699: pa_expenditure_items ei,
700: pa_cust_rev_dist_lines crdl
721: -- Pending revenue generation
722: SELECT 1
723: FROM
724: pa_expenditure_items ei ,
725: pa_projects_all proj,
726: pa_tasks task,
727: pa_tasks toptask
728: WHERE
729: ei.DOCUMENT_HEADER_ID = p_invoice_id
747: FROM PA_EXPENDITURE_ITEMS EI
748: , PA_CUST_REV_DIST_LINES CRDL
749: , PA_DRAFT_INVOICE_ITEMS PDII
750: , PA_DRAFT_INVOICES PDI
751: , PA_PROJECTS PRJ
752: , PA_TASKS TASK
753: , PA_TASKS TOPTASK
754: WHERE EI.PROJECT_ID = TASK.PROJECT_ID
755: AND EI.TASK_ID = TASK.TASK_ID
791: )
792: OR exists
793: -- unpaid manually linked invoices exist
794: (SELECT 1
795: FROM PA_PROJECTS PRJ
796: , PA_DRAFT_INVOICES PDI
797: , PA_PWP_LINKED_INVOICES PWP
798: WHERE PRJ.PROJECT_ID = PDI.PROJECT_ID
799: AND PDI.PROJECT_ID = PWP.PROJECT_ID -- Bug 7720228
868: -- Function : is_eligible
869: -- Type :Private
870: -- Purpose : To find if all AUTO_RELEASE_PWP_INV is yes for all
871: -- related projects.
872: -- Note : Checks if PA_PROJECTS_ALL.AUTO_RELEASE_PWP_INV is Y/N.
873: -- Assumptions :
874: -- Parameter :
875: -- Parameters Type Required Description and Purpose
876: -- --------------------------- ------ -------- --------------------------------------------------------
888: SELECT 'N' into v_tmp
889: FROM dual
890: WHERE EXISTS
891: ( select 1 from
892: pa_projects proj,
893: pa_expenditure_items_all ei
894: where
895: ei.project_id = proj.project_id and
896: ei.document_header_id = p_invoice_id and