[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
relationship_id,
remit_party_id,
remit_supplier_site_id
INTO
x_relationship_id,
x_remit_party_id,
x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND primary_flag = 'Y'
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
relationship_id,
remit_party_id,
remit_supplier_site_id
INTO
x_relationship_id,
x_remit_party_id,
x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND active = 'Y'
AND primary_flag = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
relationship_id,
remit_party_id
INTO
x_relationship_id,
x_remit_party_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND primary_flag = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT count(*)
INTO p_count
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT irel.relationship_id,irel.remit_party_id,irel.remit_supplier_site_id
INTO x_relationship_id, x_remit_party_id, x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE irel.party_id = p_party_id
AND irel.supplier_site_id = p_supplier_site_id
AND irel.remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
relationship_id,
remit_supplier_site_id
INTO
x_relationship_id,
x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_party_id = x_remit_party_id
AND active = 'Y'
AND primary_flag = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT count(*)
INTO p_count
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_party_id = x_remit_party_id
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT irel.relationship_id,irel.remit_party_id,irel.remit_supplier_site_id
INTO x_relationship_id, x_remit_party_id, x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE irel.party_id = p_party_id
AND irel.supplier_site_id = p_supplier_site_id
AND irel.remit_party_id = x_remit_party_id
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
relationship_id
INTO
x_relationship_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_party_id = x_remit_party_id
AND remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND primary_flag = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT count(*)
INTO p_count
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_party_id = x_remit_party_id
AND remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT irel.relationship_id,irel.remit_party_id,irel.remit_supplier_site_id
INTO x_relationship_id, x_remit_party_id, x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE irel.party_id = p_party_id
AND irel.supplier_site_id = p_supplier_site_id
AND irel.remit_party_id = x_remit_party_id
AND irel.remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
remit_party_id,
remit_supplier_site_id
INTO
x_remit_party_id,
x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND active = 'Y'
AND relationship_id = x_relationship_id
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
remit_party_id
INTO
x_remit_party_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND relationship_id = x_relationship_id
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
remit_supplier_site_id
INTO
x_remit_supplier_site_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_party_id = x_remit_party_id
AND active = 'Y'
AND relationship_id = x_relationship_id
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
SELECT
relationship_id
INTO
x_relationship_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_party_id
AND supplier_site_id = p_supplier_site_id
AND remit_party_id = x_remit_party_id
AND remit_supplier_site_id = x_remit_supplier_site_id
AND active = 'Y'
AND relationship_id = x_relationship_id
AND(to_char(nvl(p_date, sysdate), 'YYYY-MM-DD HH24:MI:SS') BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00')
AND(to_char(nvl(irel.to_date, nvl(p_date, sysdate)), 'YYYY-MM-DD') || ' 23:59:59'));
INSERT
INTO IBY_EXT_PAYEE_RELATIONSHIPS
(
RELATIONSHIP_ID,
PARTY_ID,
SUPPLIER_SITE_ID,
REMIT_PARTY_ID,
REMIT_SUPPLIER_SITE_ID,
FROM_DATE,
TO_DATE,
PRIMARY_FLAG,
ADDITIONAL_INFORMATION,
ACTIVE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
x_relationship_id,
p_relationship.party_id,
p_relationship.supplier_site_id,
p_relationship.remit_party_id,
p_relationship.remit_supplier_site_id,
p_relationship.from_date,
p_relationship.to_date,
NVL(p_relationship.primary_flag,'N'),
p_relationship.additional_information,
NVL(p_relationship.active,'N'),
fnd_global.user_id,
trunc(sysdate),
fnd_global.user_id,
trunc(sysdate),
fnd_global.user_id,
1,
p_relationship.attribute_category,
p_relationship.attribute1,
p_relationship.attribute2,
p_relationship.attribute3,
p_relationship.attribute4,
p_relationship.attribute5,
p_relationship.attribute6,
p_relationship.attribute7,
p_relationship.attribute8,
p_relationship.attribute9,
p_relationship.attribute10,
p_relationship.attribute11,
p_relationship.attribute12,
p_relationship.attribute13,
p_relationship.attribute14,
p_relationship.attribute15
);
PROCEDURE update_Ext_Payee_Relationship(
p_api_version IN NUMBER,
p_relationship IN Payee_Relationship_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_relationship_id IN OUT NOCOPY NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_Ext_Payee_Relationship';
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.update_Ext_Payee_Relationship';
print_debuginfo('Enter : update_Ext_Payee_Relationship ');
validate_Payee_Relationship(p_relationship,'UPDATE', x_return_status);
UPDATE IBY_EXT_PAYEE_RELATIONSHIPS
SET
party_id = p_relationship.party_id,
supplier_site_id = p_relationship.supplier_site_id,
remit_party_id = p_relationship.remit_party_id,
remit_supplier_site_id = p_relationship.remit_supplier_site_id,
from_date = p_relationship.from_date,
to_date = p_relationship.to_date,
primary_flag = NVL(p_relationship.primary_flag,'N'),
additional_information = p_relationship.additional_information,
active = NVL(p_relationship.active,'N'),
last_updated_by = fnd_global.user_id,
last_update_date = trunc(sysdate),
last_update_login = fnd_global.user_id,
object_version_number = object_version_number+1,
attribute_category = p_relationship.attribute_category,
attribute1 = p_relationship.attribute1,
attribute2 = p_relationship.attribute2,
attribute3 = p_relationship.attribute3,
attribute4 = p_relationship.attribute4,
attribute5 = p_relationship.attribute5,
attribute6 = p_relationship.attribute6,
attribute7 = p_relationship.attribute7,
attribute8 = p_relationship.attribute8,
attribute9 = p_relationship.attribute9,
attribute10 = p_relationship.attribute10,
attribute11 = p_relationship.attribute11,
attribute12 = p_relationship.attribute12,
attribute13 = p_relationship.attribute13,
attribute14 = p_relationship.attribute14,
attribute15 = p_relationship.attribute15
WHERE relationship_id = x_relationship_id;
print_debuginfo('Exit : update_Ext_Payee_Relationship ');
END update_Ext_Payee_Relationship;
SELECT count(*) l_count, XMLType.getClobVal(XMLAgg(XMLElement("ID",Relationship_id)))
INTO l_error_count, l_error_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_relationship.party_id
AND supplier_site_id = p_relationship.supplier_site_id
AND active = 'Y'
AND primary_flag = 'Y'
AND ((
p_relationship.to_date IS NULL AND to_date IS NULL
)
OR (
p_relationship.to_date IS NOT NULL AND to_date IS NULL AND
TRUNC(from_date) <= TRUNC(p_relationship.TO_DATE)
)
OR (
to_date IS NOT NULL AND p_relationship.to_date IS NULL AND
TRUNC(to_date) >= TRUNC(p_relationship.FROM_DATE)
)
OR (
p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
TRUNC(from_date) >= TRUNC(p_relationship.from_date) AND TRUNC(TO_DATE) <= TRUNC(p_relationship.TO_DATE)
)
OR (
p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
TRUNC(from_date) <= TRUNC(p_relationship.from_date) AND TRUNC(to_date) >= TRUNC(p_relationship.TO_DATE)
))
AND relationship_id <> nvl(DECODE(p_action_mode,'CREATE',NULL,p_relationship.relationship_id),-1)
;
SELECT count(*) l_count, XMLType.getClobVal(XMLAgg(XMLElement("ID",Relationship_id)))
INTO l_error_count, l_error_id
FROM iby_ext_payee_relationships irel
WHERE party_id = p_relationship.party_id
AND supplier_site_id = p_relationship.supplier_site_id
AND remit_party_id = p_relationship.remit_party_id
AND remit_supplier_site_id = p_relationship.remit_supplier_site_id
AND active = 'Y'
AND ((
p_relationship.to_date IS NULL AND to_date IS NULL
)
OR (
p_relationship.to_date IS NOT NULL AND to_date IS NULL AND
TRUNC(from_date) <= TRUNC(p_relationship.TO_DATE)
)
OR (
to_date IS NOT NULL AND p_relationship.to_date IS NULL AND
TRUNC(to_date) >= TRUNC(p_relationship.FROM_DATE)
)
OR (
p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
TRUNC(from_date) >= TRUNC(p_relationship.from_date) AND TRUNC(TO_DATE) <= TRUNC(p_relationship.TO_DATE)
)
OR (
p_relationship.to_date IS NOT NULL AND to_date IS NOT NULL AND
TRUNC(from_date) <= TRUNC(p_relationship.from_date) AND TRUNC(to_date) >= TRUNC(p_relationship.TO_DATE)
))
AND relationship_id <> nvl(DECODE(p_action_mode,'CREATE',NULL,p_relationship.relationship_id),-1)
;