[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
P.BASE_CURRENCY_CODE,
C.PRECISION,
C.MINIMUM_ACCOUNTABLE_UNIT,
C.DESCRIPTION
INTO BASE_CURR,PREC,MIN_AU,DESCR
FROM
AP_SYSTEM_PARAMETERS P,
FND_CURRENCIES_VL C
WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
P_SELECT_TAX_AUTHORITY := FSELECTTAXAUTHORITY;
P_SELECTED_SUPPLIERS := FSELECTEDSUPPLIERS;
' || 'and exists (select ''Invoice Posting Date Ok''' || '
' || ' from ap_invoice_distributions sub ' || '
' || ' where sub.invoice_id = d.invoice_id' || '
' || ' and sub.awt_group_id is not null' || '
' || ' and sub.line_type_lookup_code = ' || '''ITEM''' || '
' || ' and sub.accounting_date <= ' || '
' || ' to_date(''' || DATE_TO || '''' || ',''dd/mm/yyyy'')' || '
' || ' )';
SELECT
DISTINCT
I.INVOICE_ID INVOICE_ID,
I.INVOICE_AMOUNT - NVL(I.AMOUNT_PAID
,0) AMOUNT
FROM
AP_INVOICES I,
AP_INVOICE_DISTRIBUTIONS D
WHERE I.INVOICE_ID = D.INVOICE_ID
AND I.INVOICE_AMOUNT - NVL(I.AMOUNT_PAID
,0) > 0
AND I.VENDOR_ID = NVL(P_SUPPLIER_ID
,I.VENDOR_ID)
AND D.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND D.AWT_GROUP_ID is not null
AND D.ACCOUNTING_DATE <= TO_DATE(DATE_TO
,'dd/mm/yyyy')
AND D.ACCRUAL_POSTED_FLAG = DECODE(P_SYSTEM_ACCT_METHOD
,'ACCRUAL'
,'Y'
,'BOTH'
,'Y'
,D.ACCRUAL_POSTED_FLAG)
AND D.CASH_POSTED_FLAG = DECODE(P_SYSTEM_ACCT_METHOD
,'CASH'
,'Y'
,'BOTH'
,'Y'
,D.CASH_POSTED_FLAG);
,P_LAST_UPDATED_BY => -1
,P_LAST_UPDATE_LOGIN => -1
,P_PROGRAM_APPLICATION_ID => NULL
,P_PROGRAM_ID => NULL
,P_REQUEST_ID => NULL
,P_AWT_SUCCESS => DO_WITHHOLDING_SUCCESS
,P_INVOICE_PAYMENT_ID => NULL);
SELECT
LY.MEANING,
LN.MEANING,
L1.DISPLAYED_FIELD,
L2.DISPLAYED_FIELD,
L3.DISPLAYED_FIELD
INTO NLS_YES,NLS_NO,NLS_ALL,NLS_VOID,NLS_NA
FROM
FND_LOOKUPS LY,
FND_LOOKUPS LN,
AP_LOOKUP_CODES L1,
AP_LOOKUP_CODES L2,
AP_LOOKUP_CODES L3
WHERE LY.LOOKUP_TYPE = 'YES_NO'
AND LY.LOOKUP_CODE = 'Y'
AND LN.LOOKUP_TYPE = 'YES_NO'
AND LN.LOOKUP_CODE = 'N'
AND L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
AND L1.LOOKUP_CODE = 'ALL'
AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
AND L2.LOOKUP_CODE = 'VOID'
AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
AND L3.LOOKUP_CODE = 'NA';
SELECT
V.VENDOR_NAME
INTO L_VENDOR_REALNAME_LOW
FROM
PO_VENDORS V
WHERE V.VENDOR_ID = TO_NUMBER(P_SUPPLIER_FROM_V);
SELECT
V.VENDOR_NAME
INTO L_VENDOR_REALNAME_HIGH
FROM
PO_VENDORS V
WHERE V.VENDOR_ID = TO_NUMBER(P_SUPPLIER_TO_V);
SELECT
NAME,
CHART_OF_ACCOUNTS_ID
INTO L_NAME,L_CHART_OF_ACCOUNTS_ID
FROM
GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = L_SOB_ID;
SELECT
NAME
FROM
FND_CURRENCIES_VL
WHERE ( CURRENCY_CODE = C_BASE_CURRENCY_CODE );
SELECT
'One Withholding Tax Distribution Exists'
FROM
AP_INVOICE_DISTRIBUTIONS
WHERE ACCOUNTING_DATE between NVL(P_DATE_FROM
,ACCOUNTING_DATE)
AND NVL(P_DATE_TO
,ACCOUNTING_DATE);
SELECT
'Checkrun_Name exists'
FROM
AP_CHECKS
WHERE ( CHECKRUN_NAME = P_CHECKRUN_NAME );
SELECT
SET_OF_BOOKS_ID
FROM
AP_SYSTEM_PARAMETERS;
SELECT
SUM(AMOUNT) AMOUNT,
SUM(BASE_AMOUNT) BASE_AMOUNT
FROM
AP_INVOICE_DISTRIBUTIONS
WHERE ( INVOICE_ID = CINVOICEAMOUNTEXEMPT.INVOICE_ID )
AND ( LINE_TYPE_LOOKUP_CODE <> 'AWT' )
AND ( AWT_GROUP_ID is null );
SELECT
SUM(AIP.AMOUNT / AI.PAYMENT_CROSS_RATE) AMOUNT,
SUM(AIP.PAYMENT_BASE_AMOUNT) BASE_AMOUNT
FROM
AP_INVOICE_PAYMENTS AIP,
AP_INVOICES AI
WHERE ( AI.INVOICE_ID = INVOICE_ID_V )
AND ( AI.INVOICE_ID = AIP.INVOICE_ID )
AND ( AIP.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
,AIP.INVOICE_PAYMENT_ID) );
SELECT
SUM(AIP.DISCOUNT_TAKEN / AI.PAYMENT_CROSS_RATE) DISCOUNT
FROM
AP_INVOICE_PAYMENTS AIP,
AP_INVOICES AI
WHERE ( AI.INVOICE_ID = INVOICE_ID_V )
AND ( AI.INVOICE_ID = AIP.INVOICE_ID )
AND ( AIP.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
,AIP.INVOICE_PAYMENT_ID) );
SELECT
C.CHECK_DATE PAYMENT_DATE
FROM
AP_CHECKS C,
AP_INVOICE_PAYMENTS P
WHERE ( C.CHECK_ID = P.CHECK_ID )
AND ( P.INVOICE_ID = CLASTPAYMENTDATE.INVOICE_ID )
AND ( P.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
,P.INVOICE_PAYMENT_ID) )
ORDER BY
C.CHECK_DATE;
SELECT
NVL(SLA_LEDGER_CASH_BASIS_FLAG
,'N')
FROM
AP_SYSTEM_PARAMETERS ASP,
GL_SETS_OF_BOOKS GLSOB
WHERE ASP.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
RET_TEXT := 'and not exists (select ''Unposted Item''' || '
' || ' from ap_invoice_distributions sub ' || '
' || ' where sub.invoice_id = d.invoice_id' || '
' || ' and sub.line_type_lookup_code = ''ITEM''
' || ' and sub.posted_flag <>''Y'')';
RET_TEXT := 'and not exists (select ''Unposted Item''' || '
' || ' from ap_invoice_payments aip, ' || '
' || ' ap_payment_history aph ' || '
' || ' where aip.invoice_id = d.invoice_id' || '
' || ' and aip.check_id = aph.check_id' || '
' || ' and (aip.posted_flag <> ''Y''' || '
' || ' or aph.posted_flag <> ''Y'')' || '
' || ' union ' || '
' || ' select ''Unposted Item''' || '
' || ' from ap_prepay_history apph
' || ' where apph.invoice_id = d.invoice_id' || '
' || ' and apph.posted_flag <> ''Y'')';
RET_TEXT := 'and exists (select ''Posted Item''' || '
' || ' from ap_invoice_distributions sub ' || '
' || ' where sub.invoice_id = d.invoice_id' || '
' || ' and sub.line_type_lookup_code = ''ITEM''' || '
' || ' and sub.posted_flag =''Y''' || '
' || ' )' || '
' || ' and exists (select ''Unposted Item''' || '
' || ' from ap_invoice_distributions sub ' || '
' || ' where sub.invoice_id = d.invoice_id' || '
' || ' and sub.line_type_lookup_code = ''ITEM''' || '
' || ' and sub.posted_flag <>''Y''' || '
' || ' )';
RET_TEXT := 'and exists (select ''Posted Payment''' || '
' || ' from ap_invoice_payments aip,' || '
' || ' ap_payment_history aph' || '
' || ' where aip.invoice_id = d.invoice_id' || '
' || ' and aip.check_id = aph.check_id' || '
' || ' and aip.posted_flag = ''Y''' || '
' || ' and aph.posted_flag = ''Y''' || '
' || ' union ' || '
' || ' select ''Posted Item''' || '
' || ' from ap_prepay_history aph' || '
' || ' where aph.invoice_id = d.invoice_id' || '
' || ' and aph.posted_flag = ''Y'')' || '
' || ' and exists (select ''Unposted Payment''' || '
' || ' from ap_invoice_payments aip,' || '
' || ' ap_payment_history aph' || '
' || ' where aip.invoice_id = d.invoice_id' || '
' || ' and aip.check_id = aph.check_id' || '
' || ' and (aip.posted_flag<>''Y''' || '
' || ' or aph.posted_flag<>''Y'')' || '
' || ' union ' || '
' || ' select ''Unposted Prepayment''' || '
' || ' from ap_prepay_history aph' || '
' || ' where aph.invoice_id = d.invoice_id' || '
' || ' and aph.posted_flag <>''Y'')';
RET_TEXT := 'and not exists (select ''Posted Item''' || '
' || ' from ap_invoice_distributions sub ' || '
' || ' where sub.invoice_id = d.invoice_id' || '
' || ' and sub.line_type_lookup_code = ''ITEM''' || '
' || ' and sub.posted_flag = ''Y''' || '
' || ' )';
RET_TEXT := 'and not exists (select ''Posted Payment''' || '
' || ' from ap_invoice_payments aip,' || '
' || ' ap_payment_history aph' || '
' || ' where aip.invoice_id = d.invoice_id' || '
' || ' and aip.check_id = aph.check_id' || '
' || ' and (aip.posted_flag=''Y''' || '
' || ' or aph.posted_flag=''Y'')' || '
' || ' union
' || ' select ''posted Prepayment''' || '
' || ' from ap_prepay_history aph' || '
' || ' where aph.invoice_id = d.invoice_id' || '
' || ' and aph.posted_flag =''Y'')';
FUNCTION FSELECTEDSUPPLIERS RETURN VARCHAR2 IS
RET_TEXT VARCHAR2(1000);
SELECT
SUPPLIER_NUM_TYPE
FROM
AP_PRODUCT_SETUP;
END FSELECTEDSUPPLIERS;
' || ' select ''Distribution already paid to ' || 'the tax authority''' || '
' || ' from ap_invoice_payments' || ' t_auth_payments' || '
' || ' , ap_checks ' || ' t_auth_checks' || '
' || ' where t_auth_checks.checkrun_name = ' || ':P_Checkrun_Name' || '
' || ' and t_auth_checks.check_id = ' || 't_auth_payments.check_id' || '
' || ' and d.awt_invoice_id = ' || 't_auth_payments.invoice_id' || '
' || ' )';
FUNCTION FSELECTTAXAUTHORITY RETURN VARCHAR2 IS
RET_TEXT VARCHAR2(1000);
END FSELECTTAXAUTHORITY;
SELECT
CREATE_AWT_DISTS_TYPE
FROM
AP_SYSTEM_PARAMETERS;
SELECT
MIN(ACCOUNTING_DATE)
FROM
AP_INVOICE_DISTRIBUTIONS
WHERE INVOICE_ID = INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = 'ITEM';
SELECT
C.CHECK_NUMBER PAYMENT_DOC
FROM
AP_CHECKS C,
AP_INVOICE_PAYMENTS P
WHERE ( C.CHECK_ID = P.CHECK_ID )
AND ( P.INVOICE_ID = INVOICE_ID )
AND ( P.INVOICE_PAYMENT_ID = NVL(BREAK_AWT_PAYMENT_ID
,P.INVOICE_PAYMENT_ID) )
ORDER BY
C.CHECK_DATE;
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_PROGRAM_APPLICATION_ID IN NUMBER
,P_PROGRAM_ID IN NUMBER
,P_REQUEST_ID IN NUMBER
,P_AWT_SUCCESS OUT NOCOPY VARCHAR2
,P_INVOICE_PAYMENT_ID IN NUMBER) IS
BEGIN
/*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_DO_WITHHOLDING(:P_INVOICE_ID, :P_AWT_DATE, :P_CALLING_MODULE, :P_AMOUNT, :P_PAYMENT_NUM,
:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID, :P_AWT_SUCCESS,
:P_INVOICE_PAYMENT_ID); end;');
STPROC.BIND_I(P_LAST_UPDATED_BY);
STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
PROCEDURE AP_WITHHOLD_AUTOSELECT(P_CHECKRUN_NAME IN VARCHAR2
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_PROGRAM_APPLICATION_ID IN NUMBER
,P_PROGRAM_ID IN NUMBER
,P_REQUEST_ID IN NUMBER) IS
BEGIN
/*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
:P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
STPROC.BIND_I(P_LAST_UPDATED_BY);
STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
END AP_WITHHOLD_AUTOSELECT;
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_PROGRAM_APPLICATION_ID IN NUMBER
,P_PROGRAM_ID IN NUMBER
,P_REQUEST_ID IN NUMBER) IS
BEGIN
/* STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_CONFIRM(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
:P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
STPROC.BIND_I(P_LAST_UPDATED_BY);
STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_PROGRAM_APPLICATION_ID IN NUMBER
,P_PROGRAM_ID IN NUMBER
,P_REQUEST_ID IN NUMBER) IS
BEGIN
/*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL(:P_CHECKRUN_NAME, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN,
:P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID); end;');
STPROC.BIND_I(P_LAST_UPDATED_BY);
STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_PROGRAM_APPLICATION_ID IN NUMBER
,P_PROGRAM_ID IN NUMBER
,P_REQUEST_ID IN NUMBER
,P_AWT_SUCCESS OUT NOCOPY VARCHAR2) IS
BEGIN
/*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_UNDO_TEMP_WITHHOLDING(:P_INVOICE_ID, :P_VENDOR_ID, :P_PAYMENT_NUM, :P_CHECKRUN_NAME,
:P_UNDO_AWT_DATE, :P_CALLING_MODULE, :P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID,
:P_AWT_SUCCESS); end;');
STPROC.BIND_I(P_LAST_UPDATED_BY);
STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_PROGRAM_APPLICATION_ID IN NUMBER
,P_PROGRAM_ID IN NUMBER
,P_REQUEST_ID IN NUMBER
,P_AWT_SUCCESS OUT NOCOPY VARCHAR2
,P_DIST_LINE_NO IN NUMBER
,P_NEW_INVOICE_ID IN NUMBER
,P_NEW_DIST_LINE_NO IN NUMBER) IS
BEGIN
/*STPROC.INIT('begin AP_WITHHOLDING_PKG.AP_UNDO_WITHHOLDING(:P_PARENT_ID, :P_CALLING_MODULE, :P_AWT_DATE, :P_NEW_INVOICE_PAYMENT_ID,
:P_LAST_UPDATED_BY, :P_LAST_UPDATE_LOGIN, :P_PROGRAM_APPLICATION_ID, :P_PROGRAM_ID, :P_REQUEST_ID, :P_AWT_SUCCESS, :P_DIST_LINE_NO,
:P_NEW_INVOICE_ID, :P_NEW_DIST_LINE_NO); end;');
STPROC.BIND_I(P_LAST_UPDATED_BY);
STPROC.BIND_I(P_LAST_UPDATE_LOGIN);
Select CONCURRENT_PROGRAM_NAME into CONC_PRO_NAME from fnd_concurrent_programs fn
where fn.CONCURRENT_PROGRAM_ID = CONC_PRO_ID;