[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ leading(aha) */ pdi.project_id , pdi.draft_invoice_num , EI.document_header_id -- leading Hint Added for bug#12404156
AP_INVOICE_ID , 'AUTOLINK' link_type
FROM pa_projects prj ,
pa_draft_invoices pdi ,
pa_draft_invoice_items pdii ,
pa_cust_rev_dist_lines crdl ,
pa_expenditure_items ei ,
( select distinct invoice_id
from ap_holds_all
where hold_lookup_code = 'Pay When Paid'
and release_reason is null ) AHA ----- Alias query added for bug#12404156
WHERE AHA.invoice_id = ei.document_header_id
AND prj.project_type = NVL(p_project_type ,prj.project_type)
AND prj.AUTO_RELEASE_PWP_INV = 'Y'
AND prj.segment1 BETWEEN start_project_num AND end_project_num
AND prj.project_id = pdi.project_id
AND pdi.customer_id = NVL(p_customer_number,pdi.customer_id )
AND pdi.project_id = pdii.project_id
AND pdi.draft_invoice_num = pdii.draft_invoice_num
AND PDII.project_id = crdl.project_id
AND pdii.draft_invoice_num = crdl.draft_invoice_num
AND pdii.line_num = crdl.draft_invoice_item_line_num
AND crdl.expenditure_item_id = ei.expenditure_item_id
AND ei.document_header_id IS NOT NULL
AND ei.system_linkage_function = 'VI'
AND ei.transaction_source ='AP INVOICE'
AND ((rec_date_from IS NULL AND rec_date_to IS NULL ) OR
EXISTS
( SELECT 1
FROM ra_customer_trx rac
WHERE exists (select 'x' from AR_RECEIVABLE_APPLICATIONS_ALL ARA where ARA.STATUS = 'APP'
AND ara.APPLICATION_TYPE = 'CASH'
AND ARA.applied_customer_trx_id = RAC.customer_trx_id
and trunc ( ARA.APPLY_DATE ) between trunc(nvl(rec_date_from,ARA.APPLY_DATE-1)) and
trunc(nvl(rec_date_to,ARA.APPLY_DATE + 1)) )
AND rac.interface_header_attribute1 = prj.segment1
AND rac.interface_header_attribute2 = TO_CHAR ( pdi.draft_invoice_num ) ---- Modified the exists clause for bug#12404156
AND rac.interface_header_context = ( SELECT NAME
FROM RA_BATCH_SOURCES RBS ,
PA_IMPLEMENTATIONS PI
WHERE PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID )))
UNION
-- To pickup manual links
SELECT pwp.project_id
, pwp.draft_invoice_num
, pwp.AP_INVOICE_ID
,'MANUAL' link_type
FROM pa_projects prj
, pa_draft_invoices pdi
, pa_pwp_linked_invoices pwp
,( select distinct invoice_id
from ap_holds_all
where hold_lookup_code = 'Pay When Paid'
and release_reason is null ) AHA ----- Alias query added for bug#12404156
WHERE AHA.invoice_id = pwp.AP_INVOICE_ID
AND prj.project_type = NVL(p_project_type ,prj.project_type)
AND prj.segment1 BETWEEN start_project_num AND end_project_num
AND prj.project_id = pdi.project_id
AND pdi.customer_id = NVL(p_customer_number,pdi.customer_id )
AND pdi.draft_invoice_num = PWP.draft_invoice_num
AND pdi.project_id = PWP.project_id
AND ((rec_date_from IS NULL AND rec_date_to IS NULL ) OR
EXISTS -- atleast one reciept applied between rec start and end date params
(SELECT 1
FROM ra_customer_trx rac
WHERE exists ( select 'x'
FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
WHERE ARA.STATUS = 'APP'
AND ara.APPLICATION_TYPE = 'CASH'
AND ARA.applied_customer_trx_id = RAC.customer_trx_id
and trunc(ARA.APPLY_DATE) between trunc(nvl(rec_date_from,ARA.APPLY_DATE-1)) and trunc(nvl(rec_date_to,ARA.APPLY_DATE + 1)))
AND rac.interface_header_attribute1 = prj.segment1
AND rac.interface_header_attribute2 = TO_CHAR ( pdi.draft_invoice_num ) ---- Modified the exists clause for bug#12404156
AND rac.interface_header_context = ( SELECT NAME
FROM RA_BATCH_SOURCES RBS ,
PA_IMPLEMENTATIONS PI
WHERE PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID )))
;
SELECT /*+ leading(prj) */ pdi.project_id -- leading Hint Added for bug#12404156
,pdi.draft_invoice_num
,'UNLINKED' link_type
, NVL(
CASE
WHEN 1 =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM ar_payment_schedules_all arp
WHERE /*arp.status = 'OP' Bug 8284969 */
ARP.AMOUNT_DUE_REMAINING <> 0
AND Sign(ARP.AMOUNT_DUE_ORIGINAL) = Sign(ARP.AMOUNT_DUE_REMAINING )
AND rac.customer_trx_id = arp.customer_trx_id
)
)
THEN 'N'
ELSE 'Y'
END, 'Y') payment_status
FROM pa_projects prj
, ra_customer_trx rac
, pa_draft_invoices pdi
, pa_draft_invoice_items pdii
, pa_events pae
, pa_event_types pet
WHERE prj.project_type = NVL(p_project_type ,prj.project_type)
AND prj.AUTO_RELEASE_PWP_INV = 'N'
AND prj.segment1 BETWEEN start_project_num AND end_project_num
AND prj.project_id = pdi.project_id
AND pdi.customer_id = NVL(p_customer_number,pdi.customer_id )
AND pdi.project_id = pdii.project_id
AND pdi.project_id = pdii.project_id
AND pdi.draft_invoice_num = pdii.draft_invoice_num
AND pdii.project_id = pae.project_id
AND nvl(pdii.task_id,-999) = nvl(pae.task_id,-999)
AND pdii.event_num = pae.event_num
AND pae.event_type = pet.event_type
AND pet.event_type_classification IN ('AUTOMATIC','MANUAL')
AND rac.interface_header_attribute1 = prj.segment1
AND rac.interface_header_attribute2 = TO_CHAR(pdi.draft_invoice_num)
AND rac.interface_header_context = ( SELECT NAME
FROM
RA_BATCH_SOURCES RBS
,PA_IMPLEMENTATIONS PI
WHERE
PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
) --Bug 8204634
AND ((rec_date_from IS NULL AND rec_date_to IS NULL ) OR --Bug 8294296
EXISTS -- atleast one reciept applied between rec start and end date params
(SELECT 1
FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
WHERE ARA.STATUS = 'APP'
AND ara.APPLICATION_TYPE = 'CASH'
AND ARA.applied_customer_trx_id = RAC.customer_trx_id
and trunc(ARA.APPLY_DATE) between trunc(nvl(rec_date_from,ARA.APPLY_DATE-1)) and trunc(nvl(rec_date_to,ARA.APPLY_DATE + 1))
))
AND NOT EXISTS
(SELECT 1 FROM PA_PWP_LINKED_INVOICES
WHERE PROJECT_ID = pdi.project_id
AND DRAFT_INVOICE_NUM = pdi.draft_invoice_num );
SELECT /*+ leading(aha) */ DISTINCT EI.DOCUMENT_HEADER_ID -- leading Hint Added for bug#12404156
FROM PA_EXPENDITURE_ITEMS EI
,PA_PROJECTS PROJ
,PA_TASKS TASK
,PA_TASKS TOPTASK
,
( select distinct invoice_id
from ap_holds_all
where hold_lookup_code = 'Pay When Paid'
and release_reason is null ) aha ----- Alias query added for bug#12404156
WHERE aha.invoice_id = ei.document_header_id
AND proj.project_type = NVL(p_project_type ,proj.project_type)
AND proj.AUTO_RELEASE_PWP_INV = 'Y'
AND proj.segment1 BETWEEN start_project_num AND end_project_num
AND PROJ.PROJECT_ID = EI.PROJECT_ID
AND EI.DOCUMENT_HEADER_ID IS NOT NULL
AND EI.SYSTEM_LINKAGE_FUNCTION = 'VI'
AND EI.TRANSACTION_SOURCE ='AP INVOICE'
AND EI.BILLABLE_FLAG = 'N'
AND NVL(NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y'
AND PROJ.PROJECT_ID = TASK.PROJECT_ID
AND TASK.PROJECT_ID = EI.PROJECT_ID
AND TASK.TASK_ID = EI.TASK_ID
AND TASK.TOP_TASK_ID = TOPTASK.TASK_ID
AND TOPTASK.PROJECT_ID = EI.PROJECT_ID
AND DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'WORK'
AND NOT EXISTS
(SELECT /*+ INDEX(EI2 PA_EXPENDITURE_ITEMS_N27)*/
1
FROM PA_EXPENDITURE_ITEMS EI2
,PA_PROJECTS PROJ
,PA_TASKS TASK
,PA_TASKS TOPTASK
WHERE EI2.DOCUMENT_HEADER_ID =EI.DOCUMENT_HEADER_ID
AND EI2.PROJECT_ID = PROJ.PROJECT_ID
AND PROJ.PROJECT_ID = TASK.PROJECT_ID
AND TASK.PROJECT_ID = EI2.PROJECT_ID
AND TASK.TASK_ID = EI2.TASK_ID
AND TASK.TOP_TASK_ID = TOPTASK.TASK_ID
AND TOPTASK.PROJECT_ID = EI2.PROJECT_ID
AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y'
AND EI2.DOCUMENT_HEADER_ID IS NOT NULL
AND EI2.SYSTEM_LINKAGE_FUNCTION = 'VI'
AND EI2.TRANSACTION_SOURCE ='AP INVOICE'
AND
(( proj.AUTO_RELEASE_PWP_INV = 'Y' AND
(
EI2.BILLABLE_FLAG = 'Y'
AND DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'WORK'
)
OR DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'COST'
OR DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'EVENT'
)
OR nvl(proj.AUTO_RELEASE_PWP_INV,'N') = 'N'
)
)
AND NOT EXISTS -- unpaid manually linked invoices exist
(SELECT 1
FROM
PA_PWP_LINKED_INVOICES PWP
WHERE
PWP.AP_INVOICE_ID = EI.DOCUMENT_HEADER_ID
)
/* AND EXISTS -- ONLY THOSE INVOICE WHERE A PAY WHEN PAID HOLD EXISTS .
(SELECT 1
FROM AP_HOLDS_ALL
WHERE INVOICE_ID = EI.DOCUMENT_HEADER_ID --- Commented the exists clause for bug#12404156
AND HOLD_LOOKUP_CODE = 'Pay When Paid'
AND RELEASE_REASON IS NULL
)*/
;
SELECT org_id
INTO G_ORG_ID
FROM pa_implementations;
select fnd_global.user_id into l_user_id from dual;
select sysdate into l_date from dual;
select min(pap.segment1) into start_project_num from pa_projects pap where project_type = nvl(p_project_type,pap.project_type) ;
select max(pap.segment1) into end_project_num from pa_projects pap where project_type = nvl(p_project_type,pap.project_type) ;
INSERT INTO PA_PWP_RELEASE_REPORT
(
ORG_ID,
REQUEST_ID,
PROJECT_ID,
DRAFT_INVOICE_NUM,
AP_INVOICE_ID,
LINK_TYPE,
RELEASE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)values
(
G_ORG_ID,
G_REQUEST_ID,
invrec.project_id,
invrec.DRAFT_INVOICE_NUM,
invrec.AP_INVOICE_ID,
invrec.link_type,
'Y',
l_user_id,
l_date,
l_user_id,
l_date
);
INSERT INTO PA_PWP_RELEASE_REPORT
(
ORG_ID,
REQUEST_ID,
PROJECT_ID,
DRAFT_INVOICE_NUM,
AP_INVOICE_ID,
LINK_TYPE,
RELEASE_FLAG,
EXCEPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
values
(
G_ORG_ID,
G_REQUEST_ID,
invrec.project_id,
invrec.DRAFT_INVOICE_NUM,
invrec.AP_INVOICE_ID,
invrec.link_type,
'N',
'PA_INV_UNREL_UNBILL',
l_user_id,
l_date,
l_user_id,
l_date
);
INSERT INTO PA_PWP_RELEASE_REPORT
(
ORG_ID,
REQUEST_ID,
PROJECT_ID,
DRAFT_INVOICE_NUM,
AP_INVOICE_ID,
LINK_TYPE,
RELEASE_FLAG,
EXCEPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
values
(
G_ORG_ID,
G_REQUEST_ID,
invrec.project_id,
invrec.DRAFT_INVOICE_NUM,
invrec.AP_INVOICE_ID,
invrec.link_type,
'N',
'PA_INV_UNREL_FLAG',
l_user_id,
l_date,
l_user_id,
l_date
);
INSERT INTO PA_PWP_RELEASE_REPORT
(
ORG_ID,
REQUEST_ID,
PROJECT_ID,
DRAFT_INVOICE_NUM,
LINK_TYPE,
RELEASE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)values
(G_ORG_ID,
G_REQUEST_ID,
invrec.project_id,
invrec.DRAFT_INVOICE_NUM,
invrec.link_type,
'X',
l_user_id,
l_date,
l_user_id,
l_date
);
INSERT INTO PA_PWP_RELEASE_REPORT
(
ORG_ID,
REQUEST_ID,
AP_INVOICE_ID,
LINK_TYPE,
RELEASE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)values
(
G_ORG_ID,
G_REQUEST_ID,
l_unbill_invids(i),
'AUTOLINK',
'Y',
l_user_id,
l_date,
l_user_id,
l_date
);
select ap_invoice_id bulk collect into l_inv_tab
from PA_PWP_RELEASE_REPORT
where nvl(CUSTOM_RELEASE_FLAG,RELEASE_FLAG) = 'Y' and request_id = G_REQUEST_ID ;
select hold_lookup_code from ap_holds_all
where invoice_id= p_invoice_id
and hold_lookup_code = 'Pay When Paid'
and release_reason IS NULL;
SELECT 'N' into v_tmp
FROM dual
WHERE EXISTS
(
-- revnue generated but unbilled transactions exist for the AP Invoice
SELECT 1
FROM pa_projects_all proj,
pa_tasks task,
pa_tasks toptask,
pa_expenditure_items ei,
pa_cust_rev_dist_lines crdl
WHERE proj.project_id = task.project_id
and task.top_task_id = toptask.task_id
and toptask.project_id = ei.project_id
and task.project_id = ei.project_id
and task.task_id = ei.task_id
and ei.DOCUMENT_HEADER_ID = p_invoice_id
AND crdl.expenditure_item_id = ei.expenditure_item_id
AND ei.system_linkage_function = 'VI'
AND ei.transaction_source in ('AP INVOICE' ,'AP NRTAX' , 'AP VARIANCE') -- bug 8208422
AND nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
AND ei.billable_flag = 'Y'
and nvl(crdl.REVERSED_FLAG,'N') <> 'Y'
AND crdl.LINE_NUM_REVERSED is null
AND crdl.draft_invoice_num IS NULL
AND nvl(crdl.ADDITIONAL_REVENUE_FLAG,'N') <> 'Y'
AND Decode(PROJ.Enable_Top_Task_Inv_Mth_Flag, 'Y', TOPTASK.Invoice_Method,PROJ.INVOICE_METHOD) = 'WORK'
)
OR exists
(
-- Pending revenue generation
SELECT 1
FROM
pa_expenditure_items ei ,
pa_projects_all proj,
pa_tasks task,
pa_tasks toptask
WHERE
ei.DOCUMENT_HEADER_ID = p_invoice_id
AND nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
AND ei.system_linkage_function = 'VI'
AND EI.TRANSACTION_SOURCE in ('AP INVOICE' ,'AP NRTAX' , 'AP VARIANCE') -- bug 8208422
AND ei.revenue_distributed_flag = 'N'
AND ei.billable_flag = 'Y'
AND proj.project_id = task.project_id
and task.top_task_id = toptask.task_id
and toptask.project_id = ei.project_id
and task.project_id = ei.project_id
and task.task_id = ei.task_id
AND Decode(PROJ.Enable_Top_Task_Inv_Mth_Flag, 'Y', TOPTASK.Invoice_Method,PROJ.INVOICE_METHOD) = 'WORK'
)
OR exists
--Any invoices that are not interfaced to AR or interfaced but unpaid in AR
(
SELECT 1
FROM PA_EXPENDITURE_ITEMS EI
, PA_CUST_REV_DIST_LINES CRDL
, PA_DRAFT_INVOICE_ITEMS PDII
, PA_DRAFT_INVOICES PDI
, PA_PROJECTS PRJ
, PA_TASKS TASK
, PA_TASKS TOPTASK
WHERE EI.PROJECT_ID = TASK.PROJECT_ID
AND EI.TASK_ID = TASK.TASK_ID
AND PRJ.PROJECT_ID = TASK.PROJECT_ID
AND TASK.TOP_TASK_ID = TOPTASK.TASK_ID
AND TOPTASK.PROJECT_ID = EI.PROJECT_ID
AND TASK.PROJECT_ID = EI.PROJECT_ID
AND EI.DOCUMENT_HEADER_ID = P_INVOICE_ID
AND EI.SYSTEM_LINKAGE_FUNCTION = 'VI'
AND EI.BILLABLE_FLAG = 'Y'
AND EI.TRANSACTION_SOURCE in ('AP INVOICE' ,'AP NRTAX' , 'AP VARIANCE') -- bug 8208422
AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND PDII.DRAFT_INVOICE_NUM = CRDL.DRAFT_INVOICE_NUM
AND PDII.LINE_NUM = CRDL.DRAFT_INVOICE_ITEM_LINE_NUM
AND PDII.PROJECT_ID = CRDL.PROJECT_ID
AND DECODE(PRJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PRJ.INVOICE_METHOD) = 'WORK'
AND PDI.PROJECT_ID = CRDL.PROJECT_ID --BUG 7704332 missing join conditions added.
AND PDI.DRAFT_INVOICE_NUM = CRDL.DRAFT_INVOICE_NUM
AND (PDI.TRANSFER_STATUS_CODE IN ('P' ,'R','X')-- INVOICE NOT YET TRANSFERED TO AR
OR EXISTS
(SELECT 1
FROM AR_PAYMENT_SCHEDULES_ALL ARP
,RA_CUSTOMER_TRX RAC
WHERE /* ARP.STATUS = 'OP' Bug 8284969 */
ARP.AMOUNT_DUE_REMAINING <> 0
AND Sign(ARP.AMOUNT_DUE_ORIGINAL) = Sign(ARP.AMOUNT_DUE_REMAINING )
AND RAC.CUSTOMER_TRX_ID = ARP.CUSTOMER_TRX_ID
AND RAC.INTERFACE_HEADER_ATTRIBUTE1 = PRJ.SEGMENT1
AND RAC.INTERFACE_HEADER_ATTRIBUTE2 = TO_CHAR(PDII.DRAFT_INVOICE_NUM)
AND RAC.INTERFACE_HEADER_CONTEXT = ( SELECT NAME
FROM
RA_BATCH_SOURCES RBS
,PA_IMPLEMENTATIONS PI
WHERE
PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
) --Bug 8204634
) )
)
OR exists
-- unpaid manually linked invoices exist
(SELECT 1
FROM PA_PROJECTS PRJ
, PA_DRAFT_INVOICES PDI
, PA_PWP_LINKED_INVOICES PWP
WHERE PRJ.PROJECT_ID = PDI.PROJECT_ID
AND PDI.PROJECT_ID = PWP.PROJECT_ID -- Bug 7720228
AND PDI.DRAFT_INVOICE_NUM = PWP.DRAFT_INVOICE_NUM
AND PWP.AP_INVOICE_ID = P_INVOICE_ID
AND (PDI.TRANSFER_STATUS_CODE IN ('P' ,'R','X')
OR EXISTS
(SELECT 1
FROM AR_PAYMENT_SCHEDULES_ALL ARP
, RA_CUSTOMER_TRX RAC
WHERE /*ARP.STATUS = 'OP' Bug 8284969 */
ARP.AMOUNT_DUE_REMAINING <> 0
AND Sign(ARP.AMOUNT_DUE_ORIGINAL) = Sign(ARP.AMOUNT_DUE_REMAINING )
AND RAC.CUSTOMER_TRX_ID = ARP.CUSTOMER_TRX_ID
AND RAC.INTERFACE_HEADER_ATTRIBUTE1 = PRJ.SEGMENT1
AND RAC.INTERFACE_HEADER_ATTRIBUTE2 = TO_CHAR(PDI.DRAFT_INVOICE_NUM)
AND RAC.INTERFACE_HEADER_CONTEXT = ( SELECT NAME
FROM
RA_BATCH_SOURCES RBS
,PA_IMPLEMENTATIONS PI
WHERE
PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
)--Bug 8204634
) ) )
;
SELECT 'Y' into v_tmp
FROM dual
WHERE EXISTS
( select 1 from PA_PWP_RELEASE_REPORT
where request_id = G_REQUEST_ID and AP_INVOICE_ID = p_invoice_id );
SELECT 'N' into v_tmp
FROM dual
WHERE EXISTS
( select 1 from
pa_projects proj,
pa_expenditure_items_all ei
where
ei.project_id = proj.project_id and
ei.document_header_id = p_invoice_id and
proj.AUTO_RELEASE_PWP_INV = 'N' );