The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE
AP_DUPLICATE_VENDORS
SET
PROCESS_FLAG = 'S'
WHERE PROCESS_FLAG = 'N';
SELECT
LY.MEANING,
LN.MEANING,
LA.DISPLAYED_FIELD
INTO NLS_YES,NLS_NO,NLS_ALL
FROM
FND_LOOKUPS LY,
FND_LOOKUPS LN,
AP_LOOKUP_CODES LA
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';
SELECT
SUBSTR(ARGUMENT1,INSTR(ARGUMENT1,'"',1,1)+1,(INSTR(ARGUMENT1,'"',1,2)-INSTR(ARGUMENT1,'"',1,1) -1)),
SUBSTR(ARGUMENT2,INSTR(ARGUMENT2,'"',1,1)+1,INSTR(ARGUMENT2,'"',1,2)-INSTR(ARGUMENT2,'"',1,1) -1),
SUBSTR(ARGUMENT3,INSTR(ARGUMENT3,'"',1,1)+1,INSTR(ARGUMENT3,'"',1,2)-INSTR(ARGUMENT3,'"',1,1) -1),
SUBSTR(ARGUMENT4,INSTR(ARGUMENT4,'"',1,1)+1,INSTR(ARGUMENT4,'"',1,2)-INSTR(ARGUMENT4,'"',1,1) -1),
SUBSTR(ARGUMENT5,INSTR(ARGUMENT5,'"',1,1)+1,INSTR(ARGUMENT5,'"',1,2)-INSTR(ARGUMENT5,'"',1,1) -1),
SUBSTR(ARGUMENT6,INSTR(ARGUMENT6,'"',1,1)+1,INSTR(ARGUMENT6,'"',1,2)-INSTR(ARGUMENT6,'"',1,1) -1),
SUBSTR(ARGUMENT7,INSTR(ARGUMENT7,'"',1,1)+1,INSTR(ARGUMENT7,'"',1,2)-INSTR(ARGUMENT7,'"',1,1) -1)
into P_LAST_UPDATED_BY_T,P_SET_OF_BOOKS_ID_T,P_FA_INSTALLED_FLAG_T,P_PO_INSTALLED_FLAG_T,
P_PA_INSTALLED_FLAG_T,P_INV_INSTALLED_FLAG_T,P_PN_INSTALLED_FLAG_T
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_CONC_REQUEST_ID;
UPDATE
AP_DUPLICATE_VENDORS
SET
PROCESS_FLAG = 'Y'
WHERE PROCESS_FLAG = 'S';
UPDATE
AP_DUPLICATE_VENDORS
SET
PROCESS_FLAG = 'N'
WHERE PROCESS_FLAG = 'D';
SELECT
VENDOR_SITE_ID
INTO L_VENDOR_SITE_ID
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C_VENDOR_ID
AND VENDOR_SITE_CODE = C_VENDOR_SITE_CODE
AND ORG_ID = C_ORG_ID;
SELECT
AI.INVOICE_ID INVOICE_ID
FROM
AP_INVOICES_ALL AI
WHERE AI.INVOICE_ID = C_INVOICE_ID
AND EXISTS (
SELECT
'Y'
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.POSTED_FLAG = 'Y'
AND AID.INVOICE_ID = AI.INVOICE_ID );
SELECT
AC.CHECK_ID CHECK_ID
FROM
AP_CHECKS_ALL AC
WHERE EXISTS (
SELECT
'Y'
FROM
AP_INVOICE_PAYMENTS_ALL AIP
WHERE AIP.CHECK_ID = AC.CHECK_ID
AND AIP.POSTED_FLAG = 'Y'
AND AIP.INVOICE_ID = C_INVOICE_ID );
SELECT
'Y'
INTO L_DUPLICATES
FROM
DUAL
WHERE EXISTS (
SELECT
'Duplicates exist in ap_invoices'
FROM
AP_INVOICES_ALL
WHERE INVOICE_NUM = C_INVOICE_NUM
AND VENDOR_ID = C_VENDOR_ID
AND INVOICE_ID <> C_INVOICE_ID )
OR EXISTS (
SELECT
'Duplicates exist in ap_history_invoices'
FROM
AP_HISTORY_INVOICES_ALL
WHERE INVOICE_NUM = C_INVOICE_NUM
AND VENDOR_ID = C_VENDOR_ID
AND INVOICE_ID <> C_INVOICE_ID );
UPDATE
AP_INVOICES_ALL
SET
VENDOR_ID = C_VENDOR_ID
,VENDOR_SITE_ID = C_VENDOR_SITE_ID
,PARTY_ID = C_PARTY_ID
,PARTY_SITE_ID = C_PARTY_SITE_ID
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,LAST_UPDATE_DATE = SYSDATE
WHERE INVOICE_ID = C_INVOICE_ID;
UPDATE
AP_CHECKS_ALL
SET
VENDOR_ID = C_VENDOR_ID
,VENDOR_SITE_ID = C_VENDOR_SITE_ID
,PARTY_ID = C_PARTY_ID
,PARTY_SITE_ID = C_PARTY_SITE_ID
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,LAST_UPDATE_DATE = SYSDATE
WHERE VENDOR_ID = C_DUP_VENDOR_ID
AND CHECK_ID in (
SELECT
CHECK_ID
FROM
AP_INVOICE_PAYMENTS
WHERE INVOICE_ID = C_INVOICE_ID );
,P_LAST_UPDATED_BY => P_LAST_UPDATED_BY_T);
SELECT
COUNT(*)
INTO L_NUM_DUP_RECURRING
FROM
AP_RECURRING_PAYMENTS_ALL ARP1
WHERE VENDOR_ID = C_DUP_VENDOR_ID
AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID
AND EXISTS (
SELECT
'this would be a duplicate'
FROM
AP_RECURRING_PAYMENTS_ALL ARP2
WHERE ARP2.VENDOR_ID = C_VENDOR_ID
AND ARP2.VENDOR_SITE_ID = C_VENDOR_SITE_ID
AND ARP2.RECURRING_PAY_NUM = ARP1.RECURRING_PAY_NUM );
UPDATE
AP_RECURRING_PAYMENTS arp1
SET
VENDOR_ID = C_VENDOR_ID
,VENDOR_SITE_ID = C_VENDOR_SITE_ID
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,LAST_UPDATE_DATE = SYSDATE
WHERE VENDOR_ID = C_DUP_VENDOR_ID
AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID
AND NOT EXISTS (
SELECT
'this would be a duplicate'
FROM
AP_RECURRING_PAYMENTS ARP2
WHERE ARP2.VENDOR_ID = C_VENDOR_ID
AND ARP2.VENDOR_SITE_ID = C_VENDOR_SITE_ID
AND ARP2.RECURRING_PAY_NUM = ARP1.RECURRING_PAY_NUM );
FUNCTION C_UPDATE_DUP_VENDORFORMULA(C_DUP_INVOICE_COUNT IN NUMBER
,C_DUP_RECURRING_COUNT IN NUMBER
,C_KEEP_SITE_FLAG IN VARCHAR2
,C_VENDOR_SITE_ID IN NUMBER
,C_ENTRY_ID IN NUMBER
,C_PAID_INVOICE_COUNT_RESET IN NUMBER
,C_UNPAID_INVOICE_COUNT_RESET IN NUMBER
,C_DUP_VENDOR_ID IN NUMBER
,C_DUP_VENDOR_SITE_ID IN NUMBER
,C_VENDOR_ID IN NUMBER
,C_PARTY_SITE_ID IN NUMBER
,C_DUP_PARTY_SITE_ID IN NUMBER) RETURN NUMBER IS
L_MERGING_PPSITE VARCHAR2(1);
SELECT
AHI1.INVOICE_NUM
FROM
AP_HISTORY_INVOICES_ALL AHI1
WHERE VENDOR_ID = V_OLD_VENDOR_ID
AND EXISTS (
SELECT
'The merge-to supplier already has this invoice num'
FROM
AP_HISTORY_INVOICES_ALL AHI2
WHERE AHI2.VENDOR_ID = V_NEW_VENDOR_ID
AND AHI2.INVOICE_NUM = AHI1.INVOICE_NUM );
UPDATE
AP_DUPLICATE_VENDORS_ALL
SET
VENDOR_SITE_ID = C_VENDOR_SITE_ID
,KEEP_SITE_FLAG = 'N'
WHERE ENTRY_ID = C_ENTRY_ID;
UPDATE
AP_DUPLICATE_VENDORS_ALL
SET
PROCESS_FLAG = 'D'
WHERE ENTRY_ID = C_ENTRY_ID;
UPDATE
AP_DUPLICATE_VENDORS_ALL
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,NUMBER_PAID_INVOICES = NVL(NUMBER_PAID_INVOICES
,0) + C_PAID_INVOICE_COUNT_RESET
,NUMBER_UNPAID_INVOICES = NVL(NUMBER_UNPAID_INVOICES
,0) + C_UNPAID_INVOICE_COUNT_RESET
WHERE ENTRY_ID = C_ENTRY_ID;
SELECT
NVL(PRIMARY_PAY_SITE_FLAG
,'N')
INTO L_MERGING_PPSITE
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C_DUP_VENDOR_ID
AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID;
UPDATE
AP_SUPPLIER_SITES_ALL
SET
PRIMARY_PAY_SITE_FLAG = 'Y'
WHERE VENDOR_ID = C_VENDOR_ID
AND VENDOR_SITE_ID = C_VENDOR_SITE_ID;
UPDATE
AP_SUPPLIER_CONTACTS
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,ORG_PARTY_SITE_ID = C_PARTY_SITE_ID
WHERE ORG_PARTY_SITE_ID = C_DUP_PARTY_SITE_ID;
SELECT
PV1.VENDOR_NAME,
PV1.SEGMENT1,
LENGTH(PV1.SEGMENT1),
PV2.VENDOR_NAME,
PV2.SEGMENT1,
LENGTH(PV2.SEGMENT1)
INTO V_OLD_VENDOR_NAME,V_OLD_VENDOR_NUM,V_OLD_VENDOR_NUM_LENGTH,
V_NEW_VENDOR_NAME,V_NEW_VENDOR_NUM,V_NEW_VENDOR_NUM_LENGTH
FROM
AP_SUPPLIERS PV1,
AP_SUPPLIERS PV2
WHERE PV1.VENDOR_ID = V_OLD_VENDOR_ID
AND PV2.VENDOR_ID = V_NEW_VENDOR_ID;
UPDATE
AP_HISTORY_INVOICES_ALL
SET
INVOICE_NUM = V_DUP_HIST_INVOICE || '*' || V_OLD_VENDOR_NUM
WHERE VENDOR_ID = V_OLD_VENDOR_ID
AND INVOICE_NUM = V_DUP_HIST_INVOICE;
UPDATE
AP_HISTORY_INVOICES_ALL
SET
INVOICE_NUM = V_DUP_HIST_INVOICE || '*' || V_OLD_VENDOR_NUM
WHERE VENDOR_ID = V_OLD_VENDOR_ID
AND INVOICE_NUM = V_DUP_HIST_INVOICE;
UPDATE
AP_HISTORY_INVOICES_ALL
SET
INVOICE_NUM = V_DUP_HIST_INVOICE || '-' || V_NEW_VENDOR_NUM
WHERE VENDOR_ID = V_NEW_VENDOR_ID
AND INVOICE_NUM = V_DUP_HIST_INVOICE;
UPDATE
AP_HISTORY_INVOICES_ALL
SET
INVOICE_NUM = V_DUP_HIST_INVOICE || '-' || V_NEW_VENDOR_NUM
WHERE VENDOR_ID = V_NEW_VENDOR_ID
AND INVOICE_NUM = V_DUP_HIST_INVOICE;
UPDATE
AP_HISTORY_INVOICES_ALL ahi
SET
VENDOR_ID = V_NEW_VENDOR_ID
WHERE VENDOR_ID = V_OLD_VENDOR_ID
AND NOT EXISTS (
SELECT
'Invoice Num exists as an invoice'
FROM
AP_INVOICES_ALL AI
WHERE AI.INVOICE_NUM = AHI.INVOICE_NUM
AND AI.VENDOR_ID = V_NEW_VENDOR_ID );
END C_UPDATE_DUP_VENDORFORMULA;
FOR c1 IN (SELECT
DV.DUPLICATE_VENDOR_SITE_ID,
DV.KEEP_SITE_FLAG,
DV.VENDOR_ID,
DV.DUPLICATE_VENDOR_ID,
DV.ORG_ID,
VS.VENDOR_SITE_CODE,
VS.PARTY_SITE_ID
FROM
AP_DUPLICATE_VENDORS_ALL DV,
AP_SUPPLIER_SITES_ALL VS
WHERE PROCESS_FLAG = 'S'
AND VS.VENDOR_SITE_ID = DV.DUPLICATE_VENDOR_SITE_ID
AND VS.ORG_ID = DV.ORG_ID) LOOP
SELECT
COUNT(VENDOR_SITE_ID)
INTO L_NUMBER_OF_OTHER_SITES
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID
AND VENDOR_SITE_ID <> C1.DUPLICATE_VENDOR_SITE_ID
AND NVL(INACTIVE_DATE
,SYSDATE + 1) > sysdate;
UPDATE
AP_SUPPLIERS
SET
END_DATE_ACTIVE = SYSDATE
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID;
SELECT
PO_VENDOR_SITES_S.NEXTVAL
INTO NEW_VENDOR_SITE_ID
FROM
SYS.DUAL;
SELECT
COUNT(*)
INTO L_TARGET_PRIMARY_PAY_SITES
FROM
AP_SUPPLIER_SITES_ALL PVS
WHERE PVS.VENDOR_ID = C1.VENDOR_ID
AND PVS.ORG_ID = C1.ORG_ID
AND NVL(PRIMARY_PAY_SITE_FLAG
,'N') = 'Y'
AND NVL(INACTIVE_DATE
,SYSDATE + 1) > sysdate;
SELECT
PVS.PRIMARY_PAY_SITE_FLAG
INTO L_DUP_PAY_SITE_FLAG
FROM
AP_SUPPLIER_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
SELECT
COUNT(*)
INTO L_DESTINATION_VENDOR_TAX_SITES
FROM
AP_SUPPLIER_SITES_ALL PVS
WHERE PVS.VENDOR_ID = C1.VENDOR_ID
AND PVS.ORG_ID = C1.ORG_ID
AND NVL(TAX_REPORTING_SITE_FLAG
,'N') = 'Y'
AND NVL(INACTIVE_DATE
,SYSDATE + 1) > sysdate;
SELECT
PVS.TAX_REPORTING_SITE_FLAG
INTO L_DUPLICATE_SITE_FLAG
FROM
AP_SUPPLIER_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
SELECT
AREA_CODE,
PHONE,
CUSTOMER_NUM,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
SHIP_VIA_LOOKUP_CODE,
FREIGHT_TERMS_LOOKUP_CODE,
FOB_LOOKUP_CODE,
INACTIVE_DATE,
FAX,
FAX_AREA_CODE,
TELEX,
TERMS_DATE_BASIS,
DISTRIBUTION_SET_ID,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID,
PAY_GROUP_LOOKUP_CODE,
PAYMENT_PRIORITY,
TERMS_ID,
INVOICE_AMOUNT_LIMIT,
PAY_DATE_BASIS_LOOKUP_CODE,
ALWAYS_TAKE_DISC_FLAG,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
VENDOR_SITE_ID,
SYSDATE,
P_LAST_UPDATED_BY_T,
C1.VENDOR_ID,
VENDOR_SITE_CODE,
VENDOR_SITE_CODE_ALT,
PURCHASING_SITE_FLAG,
RFQ_ONLY_SITE_FLAG,
PAY_SITE_FLAG,
ATTENTION_AR_FLAG,
HOLD_ALL_PAYMENTS_FLAG,
HOLD_FUTURE_PAYMENTS_FLAG,
HOLD_REASON,
HOLD_UNMATCHED_INVOICES_FLAG,
DECODE(L_DUPLICATE_TAX_SITES
,'Y'
,'N'
,TAX_REPORTING_SITE_FLAG),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
VALIDATION_NUMBER,
EXCLUDE_FREIGHT_FROM_DISCOUNT,
BANK_CHARGE_BEARER,
ORG_ID,
CHECK_DIGITS,
ALLOW_AWT_FLAG,
AWT_GROUP_ID,
DEFAULT_PAY_SITE_ID,
PAY_ON_CODE,
PAY_ON_RECEIPT_SUMMARY_CODE,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
TP_HEADER_ID,
ECE_TP_LOCATION_CODE,
PCARD_SITE_FLAG,
MATCH_OPTION,
COUNTRY_OF_ORIGIN_CODE,
FUTURE_DATED_PAYMENT_CCID,
CREATE_DEBIT_MEMO_FLAG,
SUPPLIER_NOTIF_METHOD,
EMAIL_ADDRESS,
DECODE(L_DUP_PRIMARY_PAY_SITES
,'Y'
,'N'
,PRIMARY_PAY_SITE_FLAG),
SHIPPING_CONTROL,
SELLING_COMPANY_IDENTIFIER,
GAPLESS_INV_NUM_FLAG,
LOCATION_ID,
NULL,
DUNS_NUMBER,
ADDRESS_STYLE,
LANGUAGE,
PROVINCE,
COUNTRY,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
ADDRESS_LINES_ALT,
COUNTY,
CITY,
STATE,
ZIP,
TOLERANCE_ID
INTO L_VENDOR_SITE_REC.AREA_CODE,L_VENDOR_SITE_REC.PHONE,L_VENDOR_SITE_REC.CUSTOMER_NUM,
L_VENDOR_SITE_REC.SHIP_TO_LOCATION_ID,L_VENDOR_SITE_REC.BILL_TO_LOCATION_ID,
L_VENDOR_SITE_REC.SHIP_VIA_LOOKUP_CODE,L_VENDOR_SITE_REC.FREIGHT_TERMS_LOOKUP_CODE,
L_VENDOR_SITE_REC.FOB_LOOKUP_CODE,L_VENDOR_SITE_REC.INACTIVE_DATE,L_VENDOR_SITE_REC.FAX,
L_VENDOR_SITE_REC.FAX_AREA_CODE,L_VENDOR_SITE_REC.TELEX,L_VENDOR_SITE_REC.TERMS_DATE_BASIS,
L_VENDOR_SITE_REC.DISTRIBUTION_SET_ID,L_VENDOR_SITE_REC.ACCTS_PAY_CODE_COMBINATION_ID,
L_VENDOR_SITE_REC.PREPAY_CODE_COMBINATION_ID,L_VENDOR_SITE_REC.PAY_GROUP_LOOKUP_CODE,
L_VENDOR_SITE_REC.PAYMENT_PRIORITY,L_VENDOR_SITE_REC.TERMS_ID,L_VENDOR_SITE_REC.INVOICE_AMOUNT_LIMIT,
L_VENDOR_SITE_REC.PAY_DATE_BASIS_LOOKUP_CODE,L_VENDOR_SITE_REC.ALWAYS_TAKE_DISC_FLAG,
L_VENDOR_SITE_REC.INVOICE_CURRENCY_CODE,L_VENDOR_SITE_REC.PAYMENT_CURRENCY_CODE,L_VENDOR_SITE_REC.VENDOR_SITE_ID,
L_VENDOR_SITE_REC.LAST_UPDATE_DATE,L_VENDOR_SITE_REC.LAST_UPDATED_BY,L_VENDOR_SITE_REC.VENDOR_ID,
L_VENDOR_SITE_REC.VENDOR_SITE_CODE,L_VENDOR_SITE_REC.VENDOR_SITE_CODE_ALT,L_VENDOR_SITE_REC.PURCHASING_SITE_FLAG,
L_VENDOR_SITE_REC.RFQ_ONLY_SITE_FLAG,L_VENDOR_SITE_REC.PAY_SITE_FLAG,L_VENDOR_SITE_REC.ATTENTION_AR_FLAG,
L_VENDOR_SITE_REC.HOLD_ALL_PAYMENTS_FLAG,L_VENDOR_SITE_REC.HOLD_FUTURE_PAYMENTS_FLAG,L_VENDOR_SITE_REC.HOLD_REASON,
L_VENDOR_SITE_REC.HOLD_UNMATCHED_INVOICES_FLAG,L_VENDOR_SITE_REC.TAX_REPORTING_SITE_FLAG,
L_VENDOR_SITE_REC.ATTRIBUTE_CATEGORY,L_VENDOR_SITE_REC.ATTRIBUTE1,L_VENDOR_SITE_REC.ATTRIBUTE2,
L_VENDOR_SITE_REC.ATTRIBUTE3,L_VENDOR_SITE_REC.ATTRIBUTE4,L_VENDOR_SITE_REC.ATTRIBUTE5,L_VENDOR_SITE_REC.ATTRIBUTE6,
L_VENDOR_SITE_REC.ATTRIBUTE7,L_VENDOR_SITE_REC.ATTRIBUTE8,L_VENDOR_SITE_REC.ATTRIBUTE9,L_VENDOR_SITE_REC.ATTRIBUTE10,
L_VENDOR_SITE_REC.ATTRIBUTE11,L_VENDOR_SITE_REC.ATTRIBUTE12,L_VENDOR_SITE_REC.ATTRIBUTE13,
L_VENDOR_SITE_REC.ATTRIBUTE14,L_VENDOR_SITE_REC.ATTRIBUTE15,L_VENDOR_SITE_REC.VALIDATION_NUMBER,
L_VENDOR_SITE_REC.EXCLUDE_FREIGHT_FROM_DISCOUNT,L_VENDOR_SITE_REC.BANK_CHARGE_BEARER,L_VENDOR_SITE_REC.ORG_ID,
L_VENDOR_SITE_REC.CHECK_DIGITS,L_VENDOR_SITE_REC.ALLOW_AWT_FLAG,L_VENDOR_SITE_REC.AWT_GROUP_ID,
L_VENDOR_SITE_REC.DEFAULT_PAY_SITE_ID,L_VENDOR_SITE_REC.PAY_ON_CODE,L_VENDOR_SITE_REC.PAY_ON_RECEIPT_SUMMARY_CODE,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE_CATEGORY
,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE1,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE2,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE3,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE4,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE5,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE6,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE7,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE8,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE9,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE10,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE11,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE12,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE13,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE14,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE15,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE16,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE17,
L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE18,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE19,L_VENDOR_SITE_REC.GLOBAL_ATTRIBUTE20,
L_VENDOR_SITE_REC.TP_HEADER_ID,L_VENDOR_SITE_REC.ECE_TP_LOCATION_CODE,L_VENDOR_SITE_REC.PCARD_SITE_FLAG,
L_VENDOR_SITE_REC.MATCH_OPTION,L_VENDOR_SITE_REC.COUNTRY_OF_ORIGIN_CODE,L_VENDOR_SITE_REC.FUTURE_DATED_PAYMENT_CCID,
L_VENDOR_SITE_REC.CREATE_DEBIT_MEMO_FLAG,L_VENDOR_SITE_REC.SUPPLIER_NOTIF_METHOD,L_VENDOR_SITE_REC.EMAIL_ADDRESS,
L_VENDOR_SITE_REC.PRIMARY_PAY_SITE_FLAG,L_VENDOR_SITE_REC.SHIPPING_CONTROL,L_VENDOR_SITE_REC.SELLING_COMPANY_IDENTIFIER,
L_VENDOR_SITE_REC.GAPLESS_INV_NUM_FLAG,L_VENDOR_SITE_REC.LOCATION_ID,L_VENDOR_SITE_REC.PARTY_SITE_ID,
L_VENDOR_SITE_REC.DUNS_NUMBER,L_VENDOR_SITE_REC.ADDRESS_STYLE,L_VENDOR_SITE_REC.LANGUAGE,L_VENDOR_SITE_REC.PROVINCE,
L_VENDOR_SITE_REC.COUNTRY,L_VENDOR_SITE_REC.ADDRESS_LINE1,L_VENDOR_SITE_REC.ADDRESS_LINE2,
L_VENDOR_SITE_REC.ADDRESS_LINE3,L_VENDOR_SITE_REC.ADDRESS_LINE4,L_VENDOR_SITE_REC.ADDRESS_LINES_ALT,
L_VENDOR_SITE_REC.COUNTY,L_VENDOR_SITE_REC.CITY,L_VENDOR_SITE_REC.STATE,L_VENDOR_SITE_REC.ZIP,
L_VENDOR_SITE_REC.TOLERANCE_ID
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID
AND VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID
AND NOT EXISTS (
SELECT
null
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C1.VENDOR_ID
AND VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID );
UPDATE
AP_SUPPLIER_CONTACTS
SET
ORG_PARTY_SITE_ID = L_PARTY_SITE_ID
WHERE ORG_PARTY_SITE_ID = C1.PARTY_SITE_ID;
UPDATE
AP_SUPPLIER_SITES_ALL
SET
INACTIVE_DATE = SYSDATE
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,LAST_UPDATE_DATE = SYSDATE
WHERE VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
SELECT
VENDOR_SITE_ID
INTO L_VENDOR_SITE_ID
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C_VENDOR_ID_PO
AND VENDOR_SITE_CODE = C_VENDOR_SITE_CODE_PO
AND ORG_ID = C_ORG_ID_PO;
SELECT
DISTINCT
PAD.AUTOSOURCE_RULE_ID,
PAD.SEQUENCE_NUM,
PAD.DOCUMENT_LINE_ID
FROM
PO_AUTOSOURCE_DOCUMENTS PAD
WHERE PAD.VENDOR_ID = C_DUP_VENDOR_ID_PO;
UPDATE
AP_SUPPLIER_CONTACTS
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,ORG_PARTY_SITE_ID = C_PARTY_SITE_ID_PO
WHERE ORG_PARTY_SITE_ID = C_DUP_PARTY_SITE_ID_PO;
'UPDATE ap_history_invoices_all ahi
SET vendor_id = :C_VENDOR_ID_PO
WHERE vendor_id = :C_DUP_VENDOR_ID_PO
and not exists (select null
from ap_invoices ai
where ai.invoice_num = ahi.invoice_num
and ai.vendor_id = :C_VENDOR_ID)';
PO_AP_MERGE_GRP.UPDATE_ORG_ASSIGNMENTS(P_API_VERSION => 1.0
,X_RETURN_STATUS => L_RETURN_STATUS
,P_FROM_VENDOR_ID => C_DUP_VENDOR_ID_PO
,P_FROM_SITE_ID => C_DUP_VENDOR_SITE_ID_PO
,P_TO_VENDOR_ID => C_VENDOR_ID_PO
,P_TO_SITE_ID => C_VENDOR_SITE_ID_PO);
UPDATE
AP_DUPLICATE_VENDORS_ALL
SET
LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,NUMBER_PO_HEADERS_CHANGED = NVL(NUMBER_PO_HEADERS_CHANGED
,0) + C_PO_COUNT
WHERE ENTRY_ID = C_ENTRY_ID_PO;
SELECT
NVL(PRIMARY_PAY_SITE_FLAG
,'N')
INTO L_MERGING_PPSITE
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C_DUP_VENDOR_ID_PO
AND VENDOR_SITE_ID = C_DUP_VENDOR_SITE_ID_PO;
UPDATE
AP_SUPPLIER_SITES_ALL
SET
PRIMARY_PAY_SITE_FLAG = 'Y'
WHERE VENDOR_ID = C_VENDOR_ID_PO
AND VENDOR_SITE_ID = C_VENDOR_SITE_ID_PO;
UPDATE
PO_HEADERS_ALL
SET
VENDOR_ID = C_VENDOR_ID_PO
,VENDOR_SITE_ID = C_VENDOR_SITE_ID_PO
,LAST_UPDATED_BY = P_LAST_UPDATED_BY_T
,LAST_UPDATE_DATE = SYSDATE
WHERE PO_HEADER_ID = C_PO_HEADER_ID;
'UPDATE po_rfq_vendors
SET vendor_id = :C_VENDOR_ID_PO,
vendor_site_id = :C_VENDOR_SITE_ID_PO
WHERE vendor_id = :C_DUP_VENDOR_ID_PO
AND po_header_id = :C_PO_HEADER_ID
AND not exists
(select vendor_id
from po_rfq_vendors
where vendor_id = :C_VENDOR_ID_PO
and vendor_site_id = :C_VENDOR_SITE_ID_PO
and po_header_id = :C_PO_HEADER_ID)';
'DELETE from po_rfq_vendors prv
WHERE vendor_id = :C_DUP_VENDOR_ID_PO
AND vendor_site_id = :C_DUP_VENDOR_SITE_ID_PO
AND po_header_id = :C_PO_HEADER_ID';
FOR c1 IN (SELECT
VENDOR_ID,
DUPLICATE_VENDOR_ID,
DUPLICATE_VENDOR_SITE_ID,
ORG_ID
FROM
AP_DUPLICATE_VENDORS
WHERE PROCESS_FLAG = 'S') LOOP
SELECT
TAX_REPORTING_SITE_FLAG,
INACTIVE_DATE
INTO L_DUP_TAX_FLAG,L_DUP_INACTIVE_DATE
FROM
AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = C1.DUPLICATE_VENDOR_ID
AND VENDOR_SITE_ID = C1.DUPLICATE_VENDOR_SITE_ID;
SELECT
count(*)
INTO L_COUNT
FROM
AP_SUPPLIER_SITES_ALL
WHERE TAX_REPORTING_SITE_FLAG = 'Y'
AND VENDOR_ID = C1.VENDOR_ID
AND ORG_ID = C1.ORG_ID
AND INACTIVE_DATE is not null;
UPDATE
AP_SUPPLIER_SITES_ALL
SET
TAX_REPORTING_SITE_FLAG = 'N'
WHERE VENDOR_ID = C1.VENDOR_ID
AND ORG_ID = C1.ORG_ID
AND INACTIVE_DATE is not null;