The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Cursor to select the records inserted by Oracle seeded code during concurrent program processing for a run i.e request_id*/
CURSOR C_REPORTED_REC IS
SELECT REQUEST_ID,PROJECT_ID,DRAFT_INVOICE_NUM,RA_INVOICE_NUM,AP_INVOICE_ID,LINK_TYPE FROM
PA_PWP_RELEASE_REPORT_ALL WHERE REQUEST_ID = P_REQUEST_ID ;
SELECT AP_INVOICE_ID
FROM
PA_PWP_RELEASE_REPORT PWP
,AP_INVOICES APINV
WHERE
PWP.AP_INVOICE_ID = APINV.INVOICE_ID
AND PWP.REQUEST_ID = P_REQUEST_ID
AND PWP.RELEASE_FLAG = 'N'
AND APINV.INVOICE_AMOUNT < 5000 ;
SELECT REQUEST_ID,PROJECT_ID,DRAFT_INVOICE_NUM FROM
PA_PWP_RELEASE_REPORT_ALL
WHERE REQUEST_ID = P_REQUEST_ID
AND link_type = 'UNLINKED'
AND release_flag = 'X';
The client extension can update the CUSTOM_RELEASE_FLAG
to prevent or allow release of payment holds for a supplier invoice.
This will override the RELEASE_FLAG set by Oracle code.
FOR rec IN c_reported_rec LOOP
Update PA_PWP_RELEASE_REPORT_ALL
set CUSTOM_RELEASE_FLAG = 'Y'
where request_id = p_request_id
AND ........
End Loop;
The CUSTOM_RELEASE_FLAG column value updated by this package will always take precedence over the RELEASE_FLAG value updated by the standard Oracle code.
FOR rec IN C_OVERRIDE LOOP
Update PA_PWP_RELEASE_REPORT_ALL
set CUSTOM_RELEASE_FLAG = 'Y'
where request_id = p_request_id
AND AP_INVOICE_ID = rec.AP_INVOICE_ID
AND ....
End Loop;
If a user wants to associate a supplier invoice with any of these draft invoices and release the supplier invoice, they can do so by inserting a record in
the PA_PWP_RELEASE_REPORT_ALL table with CUSTOM_RELEASE_FLAG set to 'Y'.
Users should not modify the package to update the template record that has
RELEASE_FLAG value as 'X'.
G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
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;
INSERT INTO PA_PWP_RELEASE_REPORT
(
ORG_ID,
REQUEST_ID,
PROJECT_ID,
DRAFT_INVOICE_NUM,
LINK_TYPE,
CUSTOM_RELEASE_FLAG,
AP_INVOICE_ID ,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)values
( l_org_id
l_request_id
rec.project_id,
rec.DRAFT_INVOICE_NUM,
rec.link_type,
'Y',
l_user_id,
l_date,
l_user_id,
l_date
);