APPS.PA_CLIENT_EXTN_PWP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 22

/*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  ;

Line: 33

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 ;

Line: 47

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';

Line: 58

 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;

Line: 78

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;

Line: 101

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();

Line: 109

SELECT    org_id
     INTO G_ORG_ID
     FROM pa_implementations;

Line: 113

     select fnd_global.user_id into l_user_id from  dual;

Line: 114

     select sysdate  into l_date from  dual;

Line: 120

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
 );