The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select distinct draft_invoice_num,link_type From (
Select draft_invoice_num, 'M' link_type From PA_PWP_LINKED_INVOICES PWP
Where PWP.AP_INVOICE_ID = p_invoice_id
And PWP.PROJECT_ID = p_project_id
UNION ALL
Select pdii.draft_invoice_num, 'A' From PA_DRAFT_INVOICE_ITEMS PDII ,
PA_CUST_REV_DIST_LINES CRDL ,
PA_EXPENDITURE_ITEMS EI
Where 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.system_linkage_function = 'VI'
And ei.document_header_id =p_invoice_id
And ei.transaction_source like 'AP%'
And ei.project_id =p_project_id);
DELETE
FROM PA_PWP_LINKED_INVOICES
WHERE project_id = PA_LINK_TAB(i).PROJECT_ID
AND draft_invoice_num = PA_LINK_TAB(i).DRAFT_INVOICE_NUM
AND ap_invoice_id = PA_LINK_TAB(i).AP_INVOICE_ID;
UPDATE PA_PWP_AP_INV_HDR
SET LINKED_DRAFT_INVOICE_NUM = l_draft_inv_number,
LINKED_DRFAT_INV_TYPE = l_draft_inv_link_type
WHERE INVOICE_ID = PA_LINK_TAB(i).AP_INVOICE_ID
AND PROJECT_ID = PA_LINK_TAB(i).PROJECT_ID;
SELECT sysdate
INTO l_sysdate
FROM dual;
SELECT org_id
INTO l_orgid
FROM pa_projects where project_id = PA_LINK_TAB(1).PROJECT_ID ;
INSERT
INTO PA_PWP_LINKED_INVOICES
( ORG_ID,
PROJECT_ID ,
DRAFT_INVOICE_NUM ,
AP_INVOICE_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE
)
VALUES
( l_orgid,
PA_LINK_TAB(i).PROJECT_ID ,
PA_LINK_TAB(i).DRAFT_INVOICE_NUM ,
PA_LINK_TAB(i).AP_INVOICE_ID ,
l_CREATED_BY ,
l_sysdate ,
l_CREATED_BY ,
l_sysdate
);
UPDATE PA_PWP_AP_INV_HDR
SET LINKED_DRAFT_INVOICE_NUM = DECODE(LINKED_DRAFT_INVOICE_NUM, NULL,
to_char(PA_LINK_TAB(i).DRAFT_INVOICE_NUM),
LINKED_DRAFT_INVOICE_NUM|| ',' ||
to_char(PA_LINK_TAB(i).DRAFT_INVOICE_NUM)),
LINKED_DRFAT_INV_TYPE = DECODE(LINKED_DRFAT_INV_TYPE,NULL,'M',
LINKED_DRFAT_INV_TYPE||','||'M')
WHERE INVOICE_ID = PA_LINK_TAB(i).AP_INVOICE_ID
AND PROJECT_ID = PA_LINK_TAB(i).PROJECT_ID;