[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,
P.ORG_ID
INTO BASE_CURR,PREC,MIN_AU,DESCR,ORG_ID
FROM
AP_SYSTEM_PARAMETERS P,
FND_CURRENCIES_VL C
WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
UPDATE
AP_OTHER_PERIODS
SET
STATUS = 'S'
WHERE PERIOD_NAME = P_PERIOD_NAME
AND MODULE = C_MODULE;
,'insert_key_ind: Inserting into key_ind tables...')*/NULL;
IF (INSERT_KEY_IND <> TRUE) THEN
RETURN (FALSE);
,'update_key_ind: Updating key_ind tables...')*/NULL;
IF (UPDATE_KEY_IND <> TRUE) THEN
RETURN (FALSE);
UPDATE
AP_OTHER_PERIODS
SET
STATUS = 'C'
WHERE PERIOD_NAME = P_PERIOD_NAME
AND MODULE = C_MODULE;
SELECT
LY.MEANING,
LN.MEANING,
LA.DISPLAYED_FIELD,
LNA.DISPLAYED_FIELD
INTO NLS_YES,NLS_NO,NLS_ALL,NLS_NA
FROM
FND_LOOKUPS LY,
FND_LOOKUPS LN,
AP_LOOKUP_CODES LA,
AP_LOOKUP_CODES LNA
WHERE LY.LOOKUP_TYPE = 'YES_NO'
AND LY.LOOKUP_CODE = 'Y'
AND LN.LOOKUP_TYPE = 'YES_NO'
AND LN.LOOKUP_CODE = 'N'
AND LA.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
AND LA.LOOKUP_CODE = 'ALL'
AND LNA.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
AND LNA.LOOKUP_CODE = 'NA';
UPDATE
AP_OTHER_PERIODS
SET
STATUS = 'Y'
WHERE PERIOD_NAME = P_PERIOD_NAME
AND PERIOD_TYPE = C_PERIOD_TYPE
AND MODULE = 'KEY INDICATORS'
AND APPLICATION_ID = 200;
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
PERIOD_YEAR,
PERIOD_NUM,
PERIOD_TYPE,
START_DATE,
END_DATE,
STATUS
INTO L_PERIOD_YEAR,L_PERIOD_NUM,L_PERIOD_TYPE,L_START_DATE,L_END_DATE,L_STATUS
FROM
AP_OTHER_PERIODS
WHERE ROWID = P_PERIOD_ROWID
AND MODULE = C_MODULE;
SELECT
MAX(PERIOD_NUM)
INTO L_PRIOR_PERIOD_NUM
FROM
AP_OTHER_PERIODS
WHERE PERIOD_TYPE = L_PERIOD_TYPE
AND PERIOD_YEAR = L_PRIOR_PERIOD_YEAR
AND MODULE = C_MODULE;
SELECT
PERIOD_NAME,
START_DATE,
END_DATE
INTO L_PRIOR_PERIOD_NAME,L_PRIOR_START_DATE,L_PRIOR_END_DATE
FROM
AP_OTHER_PERIODS
WHERE PERIOD_TYPE = L_PERIOD_TYPE
AND PERIOD_YEAR = L_PRIOR_PERIOD_YEAR
AND PERIOD_NUM = L_PRIOR_PERIOD_NUM
AND MODULE = C_MODULE;
SELECT
count(*),
NVL(SUM(DECODE(SIGN(NVL(END_DATE_ACTIVE
,SYSDATE + 1) - SYSDATE)
,1
,0
,1))
,0),
NVL(SUM(DECODE(ONE_TIME_FLAG
,'Y'
,1
,0))
,0),
NVL(SUM(DECODE(TYPE_1099
,NULL
,0
,1))
,0)
INTO L_TOTAL_VENDORS,L_TOTAL_INACTIVE,L_TOTAL_ONE_TIME,L_TOTAL_1099
FROM
PO_VENDORS
WHERE TRUNC(CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_TOTAL_VOIDED
FROM
AP_CHECKS
WHERE VOID_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_TOTAL_DISTS
FROM
AP_INVOICE_DISTRIBUTIONS DIS
WHERE TRUNC(DIS.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_TOTAL_LINES
FROM
AP_INVOICE_LINES LINES
WHERE TRUNC(LINES.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(( BATCH_ID )),
nvl(count(*),
0),
SUM(NVL(BASE_AMOUNT
,NVL(INVOICE_AMOUNT
,0)))
INTO L_TOTAL_BATCHES,L_TOTAL_INVOICES,L_TOTAL_INVOICES_DLR
FROM
AP_INVOICES
WHERE TRUNC(CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
NVL(SUM(DECODE(AP_HOLDS.HOLD_LOOKUP_CODE
,NULL
,0
,1))
,0),
NVL(SUM(DECODE(AP_HOLDS.HOLD_LOOKUP_CODE
,NULL
,0
,NVL(AP_INVOICES.BASE_AMOUNT
,AP_INVOICES.INVOICE_AMOUNT)))
,0)
INTO L_TOTAL_INVOICE_HOLDS,L_TOTAL_INVOICE_HOLDS_DLR
FROM
AP_INVOICES,
AP_HOLDS,
AP_HOLD_CODES
WHERE AP_INVOICES.INVOICE_ID = AP_HOLDS.INVOICE_ID
AND AP_HOLDS.HOLD_LOOKUP_CODE = AP_HOLD_CODES.HOLD_LOOKUP_CODE
AND AP_HOLD_CODES.HOLD_TYPE = 'INVOICE HOLD REASON'
AND AP_HOLDS.RELEASE_LOOKUP_CODE IS NULL
AND TRUNC(AP_INVOICES.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*),
NVL(SUM(NVL(CLEARED_BASE_AMOUNT
,NVL(CLEARED_AMOUNT
,AMOUNT)))
,0)
INTO L_TOTAL_CLEARED,L_TOTAL_CLEARED_DLR
FROM
AP_CHECKS
WHERE CLEARED_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_TOTAL_STOPPED
FROM
AP_CHECKS
WHERE STOPPED_BY is not null
AND VOID_DATE is null
AND RELEASED_DATE is null;
SELECT
NVL(SUM(DECODE(PAYMENT_TYPE_FLAG
,'R'
,0
,(DECODE(CHECKRUN_NAME
,NULL
,1
,0))))
,0),
NVL(SUM(DECODE(PAYMENT_TYPE_FLAG
,'R'
,0
,(DECODE(CHECKRUN_NAME
,NULL
,NVL(BASE_AMOUNT
,AMOUNT)
,0))))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,0
,1))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,0
,NVL(BASE_AMOUNT
,AMOUNT)))
,0),
NVL(SUM(DECODE(STATUS_LOOKUP_CODE
,'SPOILED'
,1
,0))
,0),
SUM(DECODE(CLEARED_DATE
,NULL
,DECODE(STATUS_LOOKUP_CODE
,'NEGOTIABLE'
,1
,'ISSUED'
,1
,'VOIDED'
,1
,'STOP INITIATED'
,1
,0)
,0)),
SUM(DECODE(CLEARED_DATE
,NULL
,DECODE(STATUS_LOOKUP_CODE
,'NEGOTIABLE'
,NVL(BASE_AMOUNT
,AMOUNT)
,'ISSUED'
,NVL(BASE_AMOUNT
,AMOUNT)
,'VOIDED'
,NVL(BASE_AMOUNT
,AMOUNT)
,'STOP INITIATED'
,NVL(BASE_AMOUNT
,AMOUNT)
,0)
,0))
INTO L_TOTAL_MAN_CHECKS,L_TOTAL_MAN_CHECKS_DLR,L_TOTAL_AUTO_CHECKS,L_TOTAL_AUTO_CHECKS_DLR,L_TOTAL_SPOILED,L_TOTAL_OUTSTANDING,L_TOTAL_OUTSTANDING_DLR
FROM
AP_CHECKS
WHERE CHECK_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND NVL(VOID_DATE
,SYSDATE + 1) >= sysdate;
SELECT
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,1
,0))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,NVL(BASE_AMOUNT
,AMOUNT)
,0))
,0)
INTO L_TOTAL_REFUND_CHECKS,L_TOTAL_REFUND_CHECKS_DLR
FROM
AP_CHECKS
WHERE CHECK_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND NVL(VOID_DATE
,SYSDATE + 1) >= sysdate
AND PAYMENT_TYPE_FLAG = 'R';
SELECT
count(( INVOICE_ID )),
NVL(SUM(NVL(AP_UTILITIES_PKG.AP_ROUND_CURRENCY((DISCOUNT_TAKEN * NVL(PAY.EXCHANGE_RATE
,1))
,C_BASE_CURRENCY_CODE)
,0))
,0),
NVL(SUM(DECODE(DISCOUNT_TAKEN
,NULL
,0
,0
,0
,1))
,0)
INTO L_TOTAL_PAID_INV,L_TOTAL_DISCS_DLR,L_TOTAL_DISCS
FROM
AP_CHECKS CHK,
AP_INVOICE_PAYMENTS PAY
WHERE CHECK_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND PAY.CHECK_ID = CHK.CHECK_ID
AND NVL(VOID_DATE
,SYSDATE + 1) >= sysdate;
SELECT
count(*)
INTO L_TOTAL_SCHEDULED
FROM
AP_PAYMENT_SCHEDULES PAY,
AP_INVOICES INV
WHERE PAY.INVOICE_ID = INV.INVOICE_ID
AND TRUNC(INV.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_TOTAL_SITES
FROM
PO_VENDORS PV,
PO_VENDOR_SITES PVS
WHERE TRUNC(PVS.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND TRUNC(PV.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
FOR c1 IN (SELECT
H.INVOICE_ID
FROM
AP_HOLDS H,
AP_HOLD_CODES HC
WHERE RELEASE_LOOKUP_CODE is null
AND H.HOLD_LOOKUP_CODE = HC.HOLD_LOOKUP_CODE
AND HC.HOLD_TYPE = 'MATCHING HOLD REASON') LOOP
BEGIN
SELECT
NVL(BASE_AMOUNT
,NVL(INVOICE_AMOUNT
,0)),
TRUNC(CREATION_DATE)
INTO L_INVOICE_AMOUNT,L_CREATION_DATE
FROM
AP_INVOICES
WHERE INVOICE_ID = C1.INVOICE_ID;
FOR c1 IN (SELECT
DISTINCT
( INVOICE_ID ) MATCHED_INVOICE_ID
FROM
AP_INVOICE_DISTRIBUTIONS
WHERE PO_DISTRIBUTION_ID is not null) LOOP
BEGIN
SELECT
NVL(BASE_AMOUNT
,NVL(INVOICE_AMOUNT
,0)),
TRUNC(CREATION_DATE)
INTO L_INVOICE_AMOUNT,L_CREATION_DATE
FROM
AP_INVOICES
WHERE INVOICE_ID = C1.MATCHED_INVOICE_ID;
FOR c1 IN (SELECT
SUM(NVL(DIS.BASE_AMOUNT
,DIS.AMOUNT)) DIS_SUM,
TRUNC(INV.CREATION_DATE) INV_CREATION_DATE,
NVL(INV.BASE_AMOUNT
,NVL(INVOICE_AMOUNT
,0)) INV_AMOUNT
FROM
AP_INVOICE_DISTRIBUTIONS DIS,
AP_INVOICES INV
WHERE PAYMENT_STATUS_FLAG in ( 'P' , 'N' )
AND DIS.INVOICE_ID = INV.INVOICE_ID
GROUP BY
DIS.INVOICE_ID,
INV.CREATION_DATE,
INVOICE_AMOUNT,
INV.BASE_AMOUNT -- Added
HAVING INVOICE_AMOUNT <> SUM(DIS.AMOUNT)) LOOP
IF C1.INV_CREATION_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY') THEN
L_DIST_TOTAL_VARS := L_DIST_TOTAL_VARS + 1;
FOR c2 IN (SELECT
SUM(LINES.AMOUNT) LINES_SUM,
TRUNC(INV.CREATION_DATE) INV_CREATION_DATE,
NVL(INVOICE_AMOUNT
,0) INV_AMOUNT
FROM
AP_INVOICE_LINES LINES,
AP_INVOICES INV,
AP_INVOICE_DISTRIBUTIONS DIST
WHERE INV.INVOICE_ID = LINES.INVOICE_ID
AND LINES.INVOICE_ID = DIST.INVOICE_ID
AND LINES.LINE_NUMBER = DIST.INVOICE_LINE_NUMBER
AND PAYMENT_STATUS_FLAG in ( 'P' , 'N' )
GROUP BY
LINES.INVOICE_ID,
INV.CREATION_DATE,
INVOICE_AMOUNT
HAVING INVOICE_AMOUNT <> SUM(LINES.AMOUNT)) LOOP
IF C2.INV_CREATION_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY') THEN
L_LINE_TOTAL_VARS := L_LINE_TOTAL_VARS + 1;
L_UPDATED_VENDORS NUMBER := 0;
L_UPDATED_SITES NUMBER := 0;
SELECT
count(*)
INTO L_VOID
FROM
AP_CHECKS
WHERE VOID_DATE >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND VOID_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*),
NVL(SUM(NVL(CLEARED_BASE_AMOUNT
,NVL(CLEARED_AMOUNT
,AMOUNT)))
,0)
INTO L_CLEARED,L_CLEARED_DLR
FROM
AP_CHECKS
WHERE CLEARED_DATE >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND CLEARED_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_STOPPED
FROM
AP_CHECKS
WHERE TRUNC(STOPPED_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(STOPPED_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND VOID_DATE is null
AND RELEASED_DATE is null;
SELECT
NVL(SUM(DECODE(PAYMENT_TYPE_FLAG
,'R'
,0
,(DECODE(CHECKRUN_NAME
,NULL
,1
,0))))
,0),
NVL(SUM(DECODE(PAYMENT_TYPE_FLAG
,'R'
,0
,(DECODE(CHECKRUN_NAME
,NULL
,NVL(BASE_AMOUNT
,AMOUNT)
,0))))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,0
,1))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,0
,NVL(BASE_AMOUNT
,AMOUNT)))
,0),
NVL(SUM(DECODE(STATUS_LOOKUP_CODE
,'SPOILED'
,1
,0))
,0),
SUM(DECODE(CLEARED_DATE
,NULL
,DECODE(STATUS_LOOKUP_CODE
,'NEGOTIABLE'
,1
,'ISSUED'
,1
,'VOIDED'
,1
,'STOP INITIATED'
,1
,0)
,0)),
SUM(DECODE(CLEARED_DATE
,NULL
,DECODE(STATUS_LOOKUP_CODE
,'NEGOTIABLE'
,NVL(BASE_AMOUNT
,AMOUNT)
,'ISSUED'
,NVL(BASE_AMOUNT
,AMOUNT)
,'VOIDED'
,NVL(BASE_AMOUNT
,AMOUNT)
,'STOP INITIATED'
,NVL(BASE_AMOUNT
,AMOUNT)
,0)
,0))
INTO L_MANUAL_CHECKS,L_MANUAL_CHECKS_DLR,L_AUTO_CHECKS,L_AUTO_CHECKS_DLR,L_NEW_SPOILED,L_NEW_OUTSTANDING,L_NEW_OUTSTANDING_DLR
FROM
AP_CHECKS
WHERE CHECK_DATE >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND CHECK_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND NVL(VOID_DATE
,SYSDATE + 1) >= sysdate;
SELECT
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,1
,0))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,NVL(BASE_AMOUNT
,AMOUNT)
,0))
,0)
INTO L_NEW_REFUND_PAYMENTS,L_NEW_REFUND_PAYMENTS_DLR
FROM
AP_CHECKS
WHERE CHECK_DATE >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND CHECK_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND NVL(VOID_DATE
,SYSDATE + 1) >= sysdate
AND PAYMENT_TYPE_FLAG = 'R';
SELECT
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,1
,0))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,NVL(PAY.PAYMENT_BASE_AMOUNT
,PAY.AMOUNT)
,0))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,0
,1))
,0),
NVL(SUM(DECODE(CHECKRUN_NAME
,NULL
,0
,NVL(PAY.PAYMENT_BASE_AMOUNT
,PAY.AMOUNT)))
,0),
nvl(count(( INVOICE_ID )),
0),
NVL(SUM(AP_UTILITIES_PKG.AP_ROUND_CURRENCY((DISCOUNT_TAKEN * NVL(PAY.EXCHANGE_RATE
,1))
,C_BASE_CURRENCY_CODE))
,0),
NVL(SUM(DECODE(DISCOUNT_TAKEN
,NULL
,0
,0
,0
,1))
,0)
INTO L_MANUAL_PAYMENTS,L_MANUAL_PAYMENTS_DLR,L_AUTO_PAYMENTS,L_AUTO_PAYMENTS_DLR,L_INVOICES,L_DISCOUNT_DLR,L_DISCOUNTS
FROM
AP_CHECKS CHK,
AP_INVOICE_PAYMENTS PAY
WHERE CHECK_DATE >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND CHECK_DATE <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND PAY.CHECK_ID = CHK.CHECK_ID
AND NVL(VOID_DATE
,SYSDATE + 1) >= sysdate;
SELECT
count(*),
SUM(NVL(BASE_AMOUNT
,NVL(INVOICE_AMOUNT
,0))),
nvl(count(( BATCH_ID )),
0)
INTO L_NEW_INVOICES,L_TOTAL_DLR,L_BATCHES
FROM
AP_INVOICES
WHERE TRUNC(CREATION_DATE) >= TRUNC(TO_DATE(C_START_DATE
,'DD/MM/YYYY'))
AND TRUNC(CREATION_DATE) <= TRUNC(TO_DATE(C_END_DATE
,'DD/MM/YYYY'));
SELECT
NVL(SUM(DECODE(H.HOLD_LOOKUP_CODE
,NULL
,0
,1))
,0),
NVL(SUM(DECODE(H.HOLD_LOOKUP_CODE
,NULL
,0
,NVL(I.BASE_AMOUNT
,I.INVOICE_AMOUNT)))
,0)
INTO L_NEW_ON_HOLD,L_NEW_HOLD_DLR
FROM
AP_INVOICES I,
AP_HOLDS H,
AP_HOLD_CODES HC
WHERE I.INVOICE_ID = H.INVOICE_ID
AND H.HOLD_LOOKUP_CODE = HC.HOLD_LOOKUP_CODE
AND TRUNC(I.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(I.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND HC.HOLD_TYPE = 'INVOICE HOLD REASON'
AND H.RELEASE_LOOKUP_CODE IS NULL;
SELECT
count(*)
INTO L_PAYMENT_SCHEDULES
FROM
AP_PAYMENT_SCHEDULES PAY,
AP_INVOICES INV
WHERE PAY.INVOICE_ID = INV.INVOICE_ID
AND TRUNC(INV.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(INV.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_NEW_LINES
FROM
AP_INVOICE_LINES LINES
WHERE TRUNC(LINES.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(LINES.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_NEW_DISTS
FROM
AP_INVOICE_DISTRIBUTIONS DIS
WHERE TRUNC(DIS.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(DIS.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*),
NVL(SUM(DECODE(SIGN(NVL(END_DATE_ACTIVE
,SYSDATE + 1) - SYSDATE)
,1
,0
,1))
,0),
NVL(SUM(DECODE(ONE_TIME_FLAG
,'Y'
,1
,0))
,0),
NVL(SUM(DECODE(TYPE_1099
,NULL
,0
,1))
,0)
INTO L_NEW_VENDORS,L_NEW_INACTIVE,L_NEW_ONE_TIME,L_NEW_TYPE_1099_VENDORS
FROM
PO_VENDORS
WHERE TRUNC(CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*),
NVL(SUM(DECODE(PVS.HOLD_ALL_PAYMENTS_FLAG
,'Y'
,1
,DECODE(PVS.HOLD_FUTURE_PAYMENTS_FLAG
,'Y'
,1
,0)))
,0)
INTO L_OLD_VENDOR_SITES,L_TOTAL_VENDORS_HELD
FROM
PO_VENDORS PV,
PO_VENDOR_SITES PVS
WHERE TRUNC(PVS.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(PVS.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND TRUNC(PV.CREATION_DATE) < TO_DATE(C_START_DATE
,'DD/MM/YYYY');
SELECT
count(*),
NVL(SUM(DECODE(PVS.HOLD_ALL_PAYMENTS_FLAG
,'Y'
,1
,DECODE(PVS.HOLD_FUTURE_PAYMENTS_FLAG
,'Y'
,1
,0)))
,0)
INTO L_NEW_VENDOR_SITES,L_NEW_VENDORS_HELD
FROM
PO_VENDORS PV,
PO_VENDOR_SITES PVS
WHERE TRUNC(PVS.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(PVS.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY')
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND TRUNC(PV.CREATION_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(PV.CREATION_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_UPDATED_VENDORS
FROM
PO_VENDORS
WHERE TRUNC(LAST_UPDATE_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(LAST_UPDATE_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
SELECT
count(*)
INTO L_UPDATED_SITES
FROM
PO_VENDOR_SITES
WHERE TRUNC(LAST_UPDATE_DATE) >= TO_DATE(C_START_DATE
,'DD/MM/YYYY')
AND TRUNC(LAST_UPDATE_DATE) <= TO_DATE(C_END_DATE
,'DD/MM/YYYY');
C_UPDATED_VENDORS := L_UPDATED_VENDORS;
C_UPDATED_SITES := L_UPDATED_SITES;
FUNCTION INSERT_KEY_IND RETURN BOOLEAN IS
BEGIN
INSERT INTO AP_INVOICE_KEY_IND
(PERIOD_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,NUM_INVOICES
,AMOUNT_INVOICES
,NUM_INVOICE_LINES
,NUM_DISTRIBUTIONS
,NUM_BATCHES
,NUM_INVOICE_LINE_VARIANCES
,NUM_INVOICE_DIST_VARIANCES
,AMOUNT_INVOICE_LINE_VARIANCES
,AMOUNT_INVOICE_DIST_VARIANCES
,NUM_PAYMENT_SCHEDULES
,NUM_INVOICES_HELD
,AMOUNT_INVOICES_HELD
,NUM_INVOICES_MATCHED
,AMOUNT_INVOICES_MATCHED
,NUM_INVOICES_MATCH_HOLD
,AMOUNT_INVOICES_MATCH_HOLD
,ORG_ID)
VALUES (P_PERIOD_NAME
,SYSDATE
,0
,C_NEW_INVOICES
,C_TOTAL_DLR
,C_NEW_LINES
,C_NEW_DISTS
,C_BATCHES
,C_LINE_VAR_COUNT
,C_DIST_VAR_COUNT
,C_LINE_VAR_AMOUNT
,C_DIST_VAR_AMOUNT
,C_PAYMENT_SCHEDULES
,C_NEW_ON_HOLD
,C_NEW_HOLD_DLR
,C_NEW_COUNT
,C_NEW_AMOUNT
,C_MH_COUNT
,C_MH_AMOUNT
,C_ORG_ID);
INSERT INTO AP_VENDOR_KEY_IND
(PERIOD_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,NUM_VENDORS
,NUM_VENDOR_SITES
,NUM_SITES
,NUM_INACTIVE_VENDORS
,NUM_ONE_TIME_VENDORS
,NUM_1099_VENDORS
,NUM_VENDORS_HELD
,NUM_VENDORS_UPDATED
,NUM_SITES_UPDATED
,ORG_ID)
VALUES (P_PERIOD_NAME
,SYSDATE
,0
,C_NEW_VENDORS
,C_NEW_VENDOR_SITES
,C_OLD_VENDOR_SITES
,C_NEW_INACTIVE
,C_NEW_ONE_TIME
,C_NEW_TYPE_1099_VENDORS
,C_NEW_VENDORS_HELD
,C_UPDATED_VENDORS
,C_UPDATED_SITES
,C_ORG_ID);
INSERT INTO AP_PAYMENT_KEY_IND
(PERIOD_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,NUM_AUTO_PAYMENTS
,NUM_MANUAL_PAYMENTS
,AMOUNT_AUTO_PAYMENTS
,AMOUNT_MANUAL_PAYMENTS
,NUM_INVOICES_PAID
,NUM_AUTO_CHECKS
,NUM_MANUAL_CHECKS
,AMOUNT_AUTO_CHECKS
,AMOUNT_MANUAL_CHECKS
,NUM_DISCOUNTS_TAKEN
,AMOUNT_DISCOUNTS_TAKEN
,NUM_CHECKS_VOIDED
,NUM_STOP_PAYMENTS
,NUM_SPOILED
,NUM_OUTSTANDING
,NUM_CLEARED_CHECKS
,AMOUNT_CLEARED_CHECKS
,AMOUNT_OUTSTANDING
,NUM_REFUND_PAYMENTS
,AMOUNT_REFUND_PAYMENTS
,ORG_ID)
VALUES (P_PERIOD_NAME
,SYSDATE
,0
,C_AUTO_PAYMENTS
,C_MANUAL_PAYMENTS
,C_AUTO_PAYMENTS_DLR
,C_MANUAL_PAYMENTS_DLR
,C_INVOICES
,C_AUTO_CHECKS
,C_MANUAL_CHECKS
,C_AUTO_CHECKS_DLR
,C_MANUAL_CHECKS_DLR
,C_DISCOUNTS
,C_DISCOUNT_DLR
,C_VOID
,C_STOPPED
,C_NEW_SPOILED
,C_NEW_OUTSTANDING
,C_CLEARED
,C_CLEARED_DLR
,C_NEW_OUTSTANDING_DLR
,C_NEW_REFUND_PAYMENTS
,C_NEW_REFUND_PAYMENTS_DLR
,C_ORG_ID);
END INSERT_KEY_IND;
FUNCTION UPDATE_KEY_IND RETURN BOOLEAN IS
BEGIN
UPDATE
AP_INVOICE_KEY_IND
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = 0
,NUM_INVOICES = C_NEW_INVOICES
,AMOUNT_INVOICES = C_TOTAL_DLR
,NUM_DISTRIBUTIONS = C_NEW_DISTS
,NUM_INVOICE_LINES = C_NEW_LINES
,NUM_BATCHES = C_BATCHES
,NUM_INVOICE_LINE_VARIANCES = C_LINE_VAR_COUNT
,NUM_INVOICE_DIST_VARIANCES = C_DIST_VAR_COUNT
,AMOUNT_INVOICE_LINE_VARIANCES = C_LINE_VAR_AMOUNT
,AMOUNT_INVOICE_DIST_VARIANCES = C_DIST_VAR_AMOUNT
,NUM_PAYMENT_SCHEDULES = C_PAYMENT_SCHEDULES
,NUM_INVOICES_HELD = C_NEW_ON_HOLD
,AMOUNT_INVOICES_HELD = C_NEW_HOLD_DLR
,NUM_INVOICES_MATCHED = C_NEW_COUNT
,AMOUNT_INVOICES_MATCHED = C_NEW_AMOUNT
,NUM_INVOICES_MATCH_HOLD = C_MH_COUNT
,AMOUNT_INVOICES_MATCH_HOLD = C_MH_AMOUNT
WHERE PERIOD_NAME = P_PERIOD_NAME;
UPDATE
AP_VENDOR_KEY_IND
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = 0
,NUM_VENDORS = C_NEW_VENDORS
,NUM_VENDOR_SITES = C_NEW_VENDOR_SITES
,NUM_SITES = C_OLD_VENDOR_SITES
,NUM_INACTIVE_VENDORS = C_NEW_INACTIVE
,NUM_ONE_TIME_VENDORS = C_NEW_ONE_TIME
,NUM_1099_VENDORS = C_NEW_TYPE_1099_VENDORS
,NUM_VENDORS_HELD = C_NEW_VENDORS_HELD
,NUM_VENDORS_UPDATED = C_UPDATED_VENDORS
,NUM_SITES_UPDATED = C_UPDATED_SITES
WHERE PERIOD_NAME = P_PERIOD_NAME;
UPDATE
AP_PAYMENT_KEY_IND
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = 0
,NUM_AUTO_PAYMENTS = C_AUTO_PAYMENTS
,NUM_MANUAL_PAYMENTS = C_MANUAL_PAYMENTS
,AMOUNT_AUTO_PAYMENTS = C_AUTO_PAYMENTS_DLR
,AMOUNT_MANUAL_PAYMENTS = C_MANUAL_PAYMENTS_DLR
,NUM_INVOICES_PAID = C_INVOICES
,NUM_AUTO_CHECKS = C_AUTO_CHECKS
,NUM_MANUAL_CHECKS = C_MANUAL_CHECKS
,AMOUNT_AUTO_CHECKS = C_AUTO_CHECKS_DLR
,AMOUNT_MANUAL_CHECKS = C_MANUAL_CHECKS_DLR
,NUM_DISCOUNTS_TAKEN = C_DISCOUNTS
,AMOUNT_DISCOUNTS_TAKEN = C_DISCOUNT_DLR
,NUM_CHECKS_VOIDED = C_VOID
,NUM_STOP_PAYMENTS = C_STOPPED
,NUM_SPOILED = C_NEW_SPOILED
,NUM_OUTSTANDING = C_NEW_OUTSTANDING
,NUM_CLEARED_CHECKS = C_CLEARED
,AMOUNT_CLEARED_CHECKS = C_CLEARED_DLR
,AMOUNT_OUTSTANDING = C_NEW_OUTSTANDING_DLR
,NUM_REFUND_PAYMENTS = C_NEW_REFUND_PAYMENTS
,AMOUNT_REFUND_PAYMENTS = C_NEW_REFUND_PAYMENTS_DLR
WHERE PERIOD_NAME = P_PERIOD_NAME;
END UPDATE_KEY_IND;
L_PRIOR_VENDORS_UPDATED NUMBER := 0;
L_PRIOR_SITES_UPDATED NUMBER := 0;
SELECT
NUM_INVOICES,
AMOUNT_INVOICES,
NUM_INVOICE_LINES,
NUM_DISTRIBUTIONS,
NUM_BATCHES,
NUM_INVOICE_LINE_VARIANCES,
NUM_INVOICE_DIST_VARIANCES,
AMOUNT_INVOICE_LINE_VARIANCES,
AMOUNT_INVOICE_DIST_VARIANCES,
NUM_PAYMENT_SCHEDULES,
NUM_INVOICES_HELD,
AMOUNT_INVOICES_HELD,
NUM_INVOICES_MATCHED,
AMOUNT_INVOICES_MATCHED,
NUM_INVOICES_MATCH_HOLD,
AMOUNT_INVOICES_MATCH_HOLD,
NUM_VENDORS,
NUM_VENDOR_SITES,
NUM_SITES,
NUM_INACTIVE_VENDORS,
NUM_ONE_TIME_VENDORS,
NUM_1099_VENDORS,
NUM_VENDORS_HELD,
NUM_VENDORS_UPDATED,
NUM_SITES_UPDATED,
NUM_INVOICES_PAID,
NUM_AUTO_CHECKS,
NUM_MANUAL_CHECKS,
AMOUNT_AUTO_CHECKS,
AMOUNT_MANUAL_CHECKS,
NUM_DISCOUNTS_TAKEN,
AMOUNT_DISCOUNTS_TAKEN,
NUM_CHECKS_VOIDED,
NUM_STOP_PAYMENTS,
NUM_SPOILED,
NUM_OUTSTANDING,
NUM_CLEARED_CHECKS,
AMOUNT_CLEARED_CHECKS,
AMOUNT_OUTSTANDING,
NUM_REFUND_PAYMENTS,
AMOUNT_REFUND_PAYMENTS
INTO L_OLD_INVOICES,L_OLD_TOTAL_DLR,L_OLD_LINES,L_OLD_DISTS,L_OLD_BATCHES,L_OLD_VAR_LINES_COUNT,
L_OLD_VAR_DISTS_COUNT,L_OLD_VAR_LINES_AMOUNT,L_OLD_VAR_DISTS_AMOUNT,L_OLD_PAYMENT_SCHEDULES,
L_OLD_ON_HOLD,L_OLD_HOLD_DLR,L_OLD_COUNT,L_OLD_AMOUNT,L_OLD_MH_COUNT,L_OLD_MH_AMOUNT,L_OLD_VENDORS,
L_OLD_SITES,L_PRIOR_OLD_VENDOR_SITES,L_INACTIVE,L_ONE_TIME,L_TYPE_1099_VENDORS,L_VENDORS_HELD,
L_PRIOR_VENDORS_UPDATED,L_PRIOR_SITES_UPDATED,L_OLD_INVOICES_PAID,L_OLD_AUTO_CHECKS,L_OLD_MANUAL_CHECKS,
L_OLD_AUTO_CHECKS_DLR,L_OLD_MANUAL_CHECKS_DLR,L_OLD_DISCOUNTS,L_OLD_DISCOUNT_DLR,L_OLD_VOID,L_OLD_STOPPED,
L_OLD_SPOILED,L_OLD_OUTSTANDING,L_OLD_CLEARED,L_OLD_CLEARED_DLR,L_OLD_OUTSTANDING_DLR,L_OLD_REFUND_PAYMENTS,L_OLD_REFUND_PAYMENTS_DLR
FROM
AP_INVOICE_KEY_IND INV,
AP_VENDOR_KEY_IND VEN,
AP_PAYMENT_KEY_IND PAY
WHERE INV.PERIOD_NAME = VEN.PERIOD_NAME
AND INV.PERIOD_NAME = PAY.PERIOD_NAME
AND INV.PERIOD_NAME = C_PRIOR_PERIOD_NAME;
C_PRIOR_VENDORS_UPDATED := L_PRIOR_VENDORS_UPDATED;
C_PRIOR_SITES_UPDATED := L_PRIOR_SITES_UPDATED;
L_TOTAL_VENDORS_UPDATED NUMBER := 0;
L_TOTAL_SITES_UPDATED NUMBER := 0;
L_PERCENT_VENDORS_UPDATED NUMBER(38,2);
L_PERCENT_SITES_UPDATED NUMBER(38,2);
/*SRW.REFERENCE(C_UPDATED_VENDORS)*/NULL;
/*SRW.REFERENCE(C_UPDATED_SITES)*/NULL;
/*SRW.REFERENCE(C_PRIOR_VENDORS_UPDATED)*/NULL;
/*SRW.REFERENCE(C_PRIOR_SITES_UPDATED)*/NULL;
L_TOTAL_VENDORS_UPDATED := C_UPDATED_VENDORS + C_PRIOR_VENDORS_UPDATED;
L_TOTAL_SITES_UPDATED := C_UPDATED_SITES + C_PRIOR_SITES_UPDATED;
IF C_PRIOR_VENDORS_UPDATED <> 0 THEN
L_PERCENT_VENDORS_UPDATED := ((C_UPDATED_VENDORS - C_PRIOR_VENDORS_UPDATED) / C_PRIOR_VENDORS_UPDATED) * 100;
IF C_PRIOR_SITES_UPDATED <> 0 THEN
L_PERCENT_SITES_UPDATED := ((C_UPDATED_SITES - C_PRIOR_SITES_UPDATED) / C_PRIOR_SITES_UPDATED) * 100;
SELECT
count(( INVOICE_ID ))
INTO L_INVOICE_COUNT
FROM
AP_HOLDS H,
AP_HOLD_CODES HC
WHERE H.RELEASE_LOOKUP_CODE is null
AND H.HOLD_LOOKUP_CODE = HC.HOLD_LOOKUP_CODE
AND HC.HOLD_TYPE = 'MATCHING HOLD REASON';
C_TOTAL_VENDORS_UPDATED := L_TOTAL_VENDORS_UPDATED;
C_TOTAL_SITES_UPDATED := L_TOTAL_SITES_UPDATED;
C_PERCENT_VENDORS_UPDATED := L_PERCENT_VENDORS_UPDATED;
C_PERCENT_SITES_UPDATED := L_PERCENT_SITES_UPDATED;
SELECT
DISPLAYED_FIELD
INTO L_DISPLAY_FIELD
FROM
AP_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'SOURCE'
AND LOOKUP_CODE = C_INVOICE_SOURCE;
SELECT
PERIOD_NAME
INTO P_PERIOD_NAME
FROM
AP_OTHER_PERIODS
WHERE ROWID = P_PERIOD_ROWID;
P_WHERE_CREATED_BY := 'AND DECODE (ai.source, ''SelfService'', fnd_user_ap_pkg.get_user_name(ai.last_updated_by),
fnd_user_ap_pkg.get_user_name(ai.created_by))
= fnd_user_ap_pkg.get_user_name(' || P_ENTERED_BY || ')';
P_WHERE_CREATED_BY_AERH := 'AND fnd_user_ap_pkg.get_user_name(aerh.last_updated_by)
= fnd_user_ap_pkg.get_user_name(' || P_ENTERED_BY || ')';
FUNCTION C_UPDATED_VENDORS_P RETURN NUMBER IS
BEGIN
RETURN C_UPDATED_VENDORS;
END C_UPDATED_VENDORS_P;
FUNCTION C_UPDATED_SITES_P RETURN NUMBER IS
BEGIN
RETURN C_UPDATED_SITES;
END C_UPDATED_SITES_P;
FUNCTION C_PRIOR_VENDORS_UPDATED_P RETURN NUMBER IS
BEGIN
RETURN C_PRIOR_VENDORS_UPDATED;
END C_PRIOR_VENDORS_UPDATED_P;
FUNCTION C_PRIOR_SITES_UPDATED_P RETURN NUMBER IS
BEGIN
RETURN C_PRIOR_SITES_UPDATED;
END C_PRIOR_SITES_UPDATED_P;
FUNCTION C_PERCENT_VENDORS_UPDATED_P RETURN NUMBER IS
BEGIN
RETURN C_PERCENT_VENDORS_UPDATED;
END C_PERCENT_VENDORS_UPDATED_P;
FUNCTION C_PERCENT_SITES_UPDATED_P RETURN NUMBER IS
BEGIN
RETURN C_PERCENT_SITES_UPDATED;
END C_PERCENT_SITES_UPDATED_P;
FUNCTION C_TOTAL_VENDORS_UPDATED_P RETURN NUMBER IS
BEGIN
RETURN C_TOTAL_VENDORS_UPDATED;
END C_TOTAL_VENDORS_UPDATED_P;
FUNCTION C_TOTAL_SITES_UPDATED_P RETURN NUMBER IS
BEGIN
RETURN C_TOTAL_SITES_UPDATED;
END C_TOTAL_SITES_UPDATED_P;