[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;
IF (DELETE_AP_1099_TAPE_DATA <> TRUE) THEN
RETURN (FALSE);
,'After delete_ap_1099_tape_data')*/NULL;
IF (INSERT_AP_1099_TAPE_DATA <> TRUE) THEN
RETURN (FALSE);
,'After insert_ap_1099_tape_data')*/NULL;
IF (PERFORM_FEDERAL_LIMIT_UPDATES <> TRUE) THEN
RETURN (FALSE);
,'After perform_federal_reporting_updates')*/NULL;
IF (UPDATE_STATE_TOTALS <> TRUE) THEN
RETURN (FALSE);
,'After update_state_totals')*/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
DECODE(COMBINED_FILING_FLAG
,'Y'
,'1'
,' ')
INTO L_COMBINED_FLAG
FROM
AP_SYSTEM_PARAMETERS
WHERE SET_OF_BOOKS_ID = L_SOB_ID;
SELECT
REPLACE(REPLACE(TAX_IDENTIFICATION_NUM
,'#'
,'\#')
,'.'
,'\.')
INTO L_TIN
FROM
AP_REPORTING_ENTITIES
WHERE TAX_ENTITY_ID = L_TAX_ENTITY_ID;
FUNCTION DELETE_AP_1099_TAPE_DATA RETURN BOOLEAN IS
BEGIN
DELETE FROM AP_1099_TAPE_DATA;
END DELETE_AP_1099_TAPE_DATA;
FUNCTION INSERT_AP_1099_TAPE_DATA RETURN BOOLEAN IS
L_SOB_ID NUMBER;
AP_1099_UTILITIES_PKG.INSERT_1099_DATA(P_CALLING_MODULE => 'ELECTRONIC MEDIA'
,P_SOB_ID => L_SOB_ID
,P_TAX_ENTITY_ID => L_TAX_ENTITY_ID
,P_COMBINED_FLAG => C_COMBINED_FLAG
,P_START_DATE => P_START_YEAR_DATE
,P_END_DATE => P_END_YEAR_DATE
,P_VENDOR_ID => NULL
,P_QUERY_DRIVER => P_QUERY_DRIVER
,P_MIN_REPORTABLE_FLAG => NULL
,P_FEDERAL_REPORTABLE_FLAG => NULL
,P_REGION => NULL);
SELECT
count(*)
INTO C_NUMBER_OF_B_RECS
FROM
AP_1099_TAPE_DATA;
END INSERT_AP_1099_TAPE_DATA;
UPDATE
AP_1099_TAPE_DATA
SET
REGION_CODE = ''
WHERE ROWID not in (
SELECT
TD.ROWID
FROM
AP_1099_TAPE_DATA TD,
AP_INCOME_TAX_REGIONS ITR
WHERE ITR.REGION_CODE = TD.REGION_CODE
AND NVL(ITR.INACTIVE_DATE
,TO_DATE(P_END_YEAR_DATE
,'DD-MON-RR') + 1) > TO_DATE(P_END_YEAR_DATE
,'DD-MON-RR')
AND ( ( ITR.REPORTING_LIMIT_METHOD_CODE = 'FEDERAL'
AND ( NVL(MISC1
,0) + NVL(MISC3
,0) + NVL(MISC6
,0) + NVL(MISC7
,0) + NVL(MISC9
,0) + NVL(MISC10
,0) >= P_FEDERAL_REPORTING_LIMIT
OR NVL(MISC2
,0) >= 10
OR NVL(MISC8
,0) >= 10
OR ( NVL(MISC15AT
,0) + NVL(MISC15ANT
,0) ) >= P_FEDERAL_REPORTING_LIMIT
OR NVL(MISC13
,0) + NVL(MISC14
,0) + NVL(MISC5
,0) > 0
OR NVL(MISC15B
,0) > 0 ) )
OR ( ITR.REPORTING_LIMIT_METHOD_CODE = 'SUM'
AND ( NVL(MISC1
,0) + NVL(MISC2
,0) + NVL(MISC3
,0) + NVL(MISC5
,0) + NVL(MISC6
,0) + NVL(MISC7
,0) + NVL(MISC8
,0) + NVL(MISC9
,0) + NVL(MISC10
,0) + NVL(MISC13
,0) + NVL(MISC14
,0) + NVL(MISC15AT
,0) + NVL(MISC15ANT
,0) + NVL(MISC15B
,0) ) >= NVL(ITR.REPORTING_LIMIT
,0) )
OR ( ITR.REPORTING_LIMIT_METHOD_CODE = 'INDIVIDUAL'
AND ( NVL(MISC1
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC2
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC3
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC5
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC6
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC7
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC8
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC9
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC13
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC14
,0) >= ITR.REPORTING_LIMIT
OR ( NVL(MISC15AT
,0) + NVL(MISC15ANT
,0) >= ITR.REPORTING_LIMIT )
OR NVL(MISC15B
,0) >= ITR.REPORTING_LIMIT
OR NVL(MISC10
,0) >= NVL(ITR.REPORTING_LIMIT
,0) ) ) ) );
UPDATE
AP_INCOME_TAX_REGIONS
SET
CONTROL_TOTAL1 = 0
,CONTROL_TOTAL2 = 0
,CONTROL_TOTAL3 = 0
,CONTROL_TOTAL4 = 0
,CONTROL_TOTAL5 = 0
,CONTROL_TOTAL6 = 0
,CONTROL_TOTAL7 = 0
,CONTROL_TOTAL8 = 0
,CONTROL_TOTAL9 = 0
,CONTROL_TOTAL10 = 0
,CONTROL_TOTAL13 = 0
,CONTROL_TOTAL14 = 0
,CONTROL_TOTAL15A = 0
,CONTROL_TOTAL15B = 0
,NUM_OF_PAYEES = 0;
FUNCTION UPDATE_STATE_TOTALS RETURN BOOLEAN IS
L_STATE_TOTAL1 NUMBER;
SELECT
SUM(MISC1),
SUM(MISC2),
SUM(MISC3),
SUM(MISC4),
SUM(MISC5),
SUM(MISC6),
( SUM(MISC7) + SUM(MISC15B) + SUM(MISC15AT) ),
SUM(MISC8),
SUM(MISC9),
SUM(MISC10),
SUM(MISC13),
SUM(MISC14),
( SUM(MISC15AT) + SUM(MISC15ANT) ),
( SUM(MISC15B) + SUM(MISC15AT) ),
count(*),
REGION_CODE
FROM
AP_1099_TAPE_DATA
GROUP BY
REGION_CODE;
UPDATE
AP_INCOME_TAX_REGIONS
SET
CONTROL_TOTAL1 = L_STATE_TOTAL1
,CONTROL_TOTAL2 = L_STATE_TOTAL2
,CONTROL_TOTAL3 = L_STATE_TOTAL3
,CONTROL_TOTAL4 = L_STATE_TOTAL4
,CONTROL_TOTAL5 = L_STATE_TOTAL5
,CONTROL_TOTAL6 = L_STATE_TOTAL6
,CONTROL_TOTAL7 = L_STATE_TOTAL7
,CONTROL_TOTAL8 = L_STATE_TOTAL8
,CONTROL_TOTAL9 = L_STATE_TOTAL9
,CONTROL_TOTAL10 = L_STATE_TOTAL10
,CONTROL_TOTAL13 = L_STATE_TOTAL13
,CONTROL_TOTAL14 = L_STATE_TOTAL14
,CONTROL_TOTAL15A = L_STATE_TOTAL15A
,CONTROL_TOTAL15B = L_STATE_TOTAL15B
,NUM_OF_PAYEES = L_STATE_NUM_OF_PAYEES
WHERE REGION_CODE = L_REGION_CODE_NUM;
END UPDATE_STATE_TOTALS;
SELECT
RPAD(' '
,80),
RPAD(' '
,40),
RPAD(' '
,40)
INTO C_TRANSMITTER_NAME,C_TRANSMITTER_ADDRESS,C_TRANSMITTER_CSZ
FROM
SYS.DUAL;
SELECT
RPAD(' '
,40),
'0',
SUBSTR(REPLACE(REPLACE(TAX_IDENTIFICATION_NUM
,'-'
,'')
,' '
,'')
,1
,9),
TO_CHAR(TO_DATE(P_START_YEAR_DATE
,'DD-MON-RR')
,'YYYY')
INTO C_SECOND_NAME,C_TRANSFER_FLAG,C_EIN,C_PAYMENT_YEAR
FROM
AP_REPORTING_ENTITIES
WHERE TAX_ENTITY_ID = P_TAX_ENTITY_ID;
SELECT
DECODE(P_ADDRESS_CHOICE
,'ADDRESS1'
,RPAD(HR.ADDRESS_LINE_1
,40)
,'LOCATION'
,HR.LOCATION_CODE
,'REP_ENTITY'
,RE.ENTITY_NAME
,RPAD(HR.ADDRESS_LINE_1
,40)),
DECODE(P_ADDRESS_CHOICE
,'ADDRESS1'
,RPAD(HR.ADDRESS_LINE_2 || ' ' || HR.ADDRESS_LINE_3
,40)
,'LOCATION'
,RPAD(HR.ADDRESS_LINE_1 || ' ' || HR.ADDRESS_LINE_2
,40)
,'REP_ENTITY'
,RPAD(HR.ADDRESS_LINE_1 || ' ' || HR.ADDRESS_LINE_2
,40)
,RPAD(HR.ADDRESS_LINE_2 || ' ' || HR.ADDRESS_LINE_3
,40)),
RPAD(HR.TOWN_OR_CITY
,40),
RPAD(HR.REGION_2
,2),
RPAD(SUBSTR(REPLACE(REPLACE(HR.POSTAL_CODE
,'-'
,'')
,' '
,'')
,1
,9)
,9)
INTO C_FIRST_NAME,C_ADDRESS,C_CITY,C_STATE,C_ZIP
FROM
HR_LOCATIONS HR,
AP_REPORTING_ENTITIES RE
WHERE HR.LOCATION_ID = RE.LOCATION_ID
AND RE.TAX_ENTITY_ID = P_TAX_ENTITY_ID;
CURSOR VENDOR_SELECT IS
SELECT
DECODE(SUM(MISC1)
,0
,0
,1),
DECODE(SUM(MISC2)
,0
,0
,2),
DECODE(SUM(MISC3)
,0
,0
,3),
DECODE(SUM(MISC4)
,0
,0
,4),
DECODE(SUM(MISC5)
,0
,0
,5),
DECODE(SUM(MISC6)
,0
,0
,6),
DECODE(SUM(MISC7 + MISC15AT + MISC15B)
,0
,0
,7),
DECODE(SUM(MISC8)
,0
,0
,8),
DECODE(SUM(MISC9)
,0
,0
,9),
DECODE(SUM(MISC10)
,0
,0
,10),
DECODE(SUM(MISC13)
,0
,0
,13),
DECODE(SUM(MISC14)
,0
,0
,14),
DECODE(SUM(MISC15AT + MISC15ANT)
,0
,0
,151),
DECODE(SUM(MISC15B + MISC15AT)
,0
,0
,152)
FROM
AP_1099_TAPE_DATA
GROUP BY
VENDOR_ID
HAVING SUM(NVL(MISC1
,0)) + SUM(NVL(MISC3
,0)) + SUM(NVL(MISC6
,0)) + SUM(NVL(MISC7
,0)) + SUM(NVL(MISC9
,0)) + SUM(NVL(MISC10
,0)) >= P_FEDERAL_REPORTING_LIMIT
OR SUM(NVL(MISC2
,0)) >= 10
OR SUM(NVL(MISC8
,0)) >= 10
OR SUM(NVL(MISC15AT
,0)) + SUM(NVL(MISC15ANT
,0)) >= P_FEDERAL_REPORTING_LIMIT
OR SUM(NVL(MISC13
,0)) + SUM(NVL(MISC14
,0)) + SUM(NVL(MISC5
,0)) > 0
OR SUM(NVL(MISC15B
,0)) > 0
UNION
SELECT
DECODE(SUM(MISC1)
,0
,0
,1),
DECODE(SUM(MISC2)
,0
,0
,2),
DECODE(SUM(MISC3)
,0
,0
,3),
DECODE(SUM(MISC4)
,0
,0
,4),
DECODE(SUM(MISC5)
,0
,0
,5),
DECODE(SUM(MISC6)
,0
,0
,6),
DECODE(SUM(MISC7 + MISC15AT + MISC15B)
,0
,0
,7),
DECODE(SUM(MISC8)
,0
,0
,8),
DECODE(SUM(MISC9)
,0
,0
,9),
DECODE(SUM(MISC10)
,0
,0
,10),
DECODE(SUM(MISC13)
,0
,0
,13),
DECODE(SUM(MISC14)
,0
,0
,14),
DECODE(SUM(MISC15AT + MISC15ANT)
,0
,0
,151),
DECODE(SUM(MISC15B + MISC15AT)
,0
,0
,152)
FROM
AP_1099_TAPE_DATA
WHERE REGION_CODE is not null
GROUP BY
VENDOR_ID;
OPEN VENDOR_SELECT;
FETCH VENDOR_SELECT
INTO L_INC1,L_INC2,L_INC3,L_INC4,L_INC5,L_INC6,L_INC7,L_INC8,L_INC9,L_INC10,L_INC13,L_INC14,L_INC15A,L_INC15B;
EXIT WHEN VENDOR_SELECT%NOTFOUND;
CLOSE VENDOR_SELECT;
SELECT
DECODE(L_MAIN1
,1
,'1'
,'') || DECODE(L_MAIN2
,2
,'2'
,'') || DECODE(L_MAIN3
,3
,'3'
,'') || DECODE(L_MAIN4
,4
,'4'
,'') || DECODE(L_MAIN5
,5
,'5'
,'') || DECODE(L_MAIN6
,6
,'6'
,'') || DECODE(L_MAIN7
,7
,'7'
,'') || DECODE(L_MAIN8
,8
,'8'
,'') || DECODE(L_MAIN10
,'A'
,'A'
,'') || DECODE(L_MAIN13
,'B'
,'B'
,'') || DECODE(L_MAIN14
,'C'
,'C'
,'') || DECODE(L_MAIN15A
,'D'
,'D'
,'') || DECODE(L_MAIN15B
,'E'
,'E'
,'')
INTO C_AMOUNT_INDICATOR
FROM
SYS.DUAL;
FUNCTION PERFORM_FEDERAL_LIMIT_UPDATES RETURN BOOLEAN IS
BEGIN
UPDATE
AP_1099_TAPE_DATA
SET
MISC2 = 0
WHERE VENDOR_ID in (
SELECT
VENDOR_ID
FROM
AP_1099_TAPE_DATA
GROUP BY
VENDOR_ID
HAVING SUM(NVL(MISC2
,0)) < 10 );
UPDATE
AP_1099_TAPE_DATA
SET
MISC8 = 0
WHERE VENDOR_ID in (
SELECT
VENDOR_ID
FROM
AP_1099_TAPE_DATA
GROUP BY
VENDOR_ID
HAVING SUM(NVL(MISC8
,0)) < 10 );
UPDATE
AP_1099_TAPE_DATA
SET
MISC15ANT = 0
,MISC15AT = 0
WHERE VENDOR_ID in (
SELECT
VENDOR_ID
FROM
AP_1099_TAPE_DATA
GROUP BY
VENDOR_ID
HAVING SUM(NVL(MISC15ANT
,0) + NVL(MISC15AT
,0)) < P_FEDERAL_REPORTING_LIMIT );
UPDATE
AP_1099_TAPE_DATA
SET
MISC7 = 0
WHERE VENDOR_ID in (
SELECT
VENDOR_ID
FROM
AP_1099_TAPE_DATA
GROUP BY
VENDOR_ID
HAVING SUM(NVL(MISC7
,0)) < P_FEDERAL_REPORTING_LIMIT );
UPDATE
AP_1099_TAPE_DATA
SET
MISC1 = 0
,MISC3 = 0
,MISC6 = 0
,MISC7 = 0
,MISC9 = 0
,MISC10 = 0
WHERE VENDOR_ID in (
SELECT
VENDOR_ID
FROM
AP_1099_TAPE_DATA
GROUP BY
VENDOR_ID
HAVING SUM(NVL(MISC1
,0)) + SUM(NVL(MISC3
,0)) + SUM(NVL(MISC6
,0)) + SUM(NVL(MISC7
,0)) + SUM(NVL(MISC9
,0)) + SUM(NVL(MISC10
,0)) < P_FEDERAL_REPORTING_LIMIT
AND SUM(NVL(MISC15B
,0)) > 0 );
END PERFORM_FEDERAL_LIMIT_UPDATES;