The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
FROM hr_all_organization_units
WHERE organization_id = c_org_id;
SELECT Use_Le_As_Subscriber_Flag,
party_tax_profile_id
INTO l_use_Le_As_Subscriber_Flag,
l_party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_org_id
AND Party_Type_Code = 'OU';
PROCEDURE ship_to_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
l_geography_id number;
IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE1(i) := NULL;
PROCEDURE ship_from_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
l_geography_id number;
IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE1(i) := NULL;
PROCEDURE bill_from_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
l_geography_id number;
IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE1(i) := NULL;
PROCEDURE bill_to_geo_names_derive(p_trx_copy_for_tax_update IN VARCHAR2) IS
l_geography_id number;
IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR
ZX_API_PUB.G_PUB_SRVC = 'GLOBAL_DOCUMENT_UPDATE' THEN -- Bug 5200373
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE1(i) := NULL;
As per Desh's update in the bug, If a eBiz customer wants to pass first party inv org id in
Rel 12 with new partner integration, he must do legal to business association using LE form.
That would create a PTP for the first party inv org.
Hence, there will be a record in HZ_PARTIES and is correct to derive info from HZ_PARTIES.
SHIP/BILL FROM N NULL (PTP is always expected)
-------------------------------------------------------------------------------------------
For P2P products, the information can be derived as follows.
Party Type PTP known Other Info Derivation Logic
--------------- ----- ----------- ----------------
SHIP/BILL TO Y Via ZX_PARTY_TAX_PROFILE, HZ_PARTIES
As per Desh's update in the bug, If a eBiz customer wants to pass first party inv org id in
Rel 12 with new partner integration, he must do legal to business association using LE form.
That would create a PTP for the first party inv org.
Hence, there will definitely be record in HZ_PARTIES.
SHIP/BILL TO N NULL (PTP is always expected)
SHIP/BILL FROM Y Via ZX_PARTY_TAX_PROFILE, HZ_PARTIES
SHIP/BILL FROM N SHIP_THIRD_PTY_ACCT_ID Via PO_VENDORS
In this case, SHIP_THIRD_PTY_ACCT_ID holds value of VENDOR_ID.
*/
PROCEDURE bill_to_party_name_derive IS
l_api_name CONSTANT VARCHAR2(80) := 'BILL_TO_PARTY_NAME_DERIVE';
SELECT pty.party_name,
pty.party_number
INTO ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i)
AND ptp.party_id = pty.party_id;
SELECT hzp.party_name,
hzp.party_number
INTO ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i)
FROM hz_cust_accounts hzca,
hz_parties hzp
WHERE hzp.party_id = hzca.party_id
AND hzca.cust_account_id = ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i);
SELECT hzp.party_name,
hzp.party_number
INTO ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i)
FROM hz_cust_accounts hzca,
hz_parties hzp
WHERE hzp.party_id = hzca.party_id
AND hzca.cust_account_id = ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i);
SELECT pty.party_name,
pty.party_number
INTO ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i)
AND ptp.party_id = pty.party_id;
SELECT pty.party_name,
pty.party_number
INTO ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID(i)
AND ptp.party_id = pty.party_id;
SELECT pty.party_name,
pty.party_number
INTO ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i)
AND ptp.party_id = pty.party_id;
SELECT ps.location_id
INTO x_location_id
FROM hz_cust_site_uses_all siteuse, hz_cust_acct_sites_all site, hz_party_sites ps
WHERE siteuse.site_use_id = p_site_use_id and
siteuse.cust_acct_site_id = site.cust_acct_site_id and
site.party_site_id = ps.party_site_id ;
SELECT ptp.party_tax_profile_id
INTO x_party_tax_profile_id
FROM hz_cust_site_uses_all siteuse, hz_cust_acct_sites_all site, hz_cust_accounts acct, zx_party_tax_profile ptp
WHERE siteuse.site_use_id = p_site_use_id and
siteuse.cust_acct_site_id = site.cust_acct_site_id and
site.cust_account_id = acct.cust_account_id and
acct.party_id = ptp.party_id and
ptp.party_type_code = 'THIRD_PARTY' ;
zx_ptnr_neg_lines_tab.INTERNAL_ORGANIZATION_ID.DELETE;
zx_ptnr_neg_lines_tab.EVENT_CLASS_MAPPING_ID.DELETE;
zx_ptnr_neg_lines_tab.TRX_ID.DELETE;
zx_ptnr_neg_lines_tab.ENTITY_CODE.DELETE;
zx_ptnr_neg_lines_tab.EVENT_CLASS_CODE.DELETE;
zx_ptnr_neg_lines_tab.APPLICATION_ID.DELETE;
zx_ptnr_neg_lines_tab.TRX_DATE.DELETE;
zx_ptnr_neg_lines_tab.TRX_CURRENCY_CODE.DELETE;
zx_ptnr_neg_lines_tab.TRX_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.RECORD_TYPE_CODE.DELETE;
zx_ptnr_neg_lines_tab.TRX_LINE_ID.DELETE;
zx_ptnr_neg_lines_tab.TRX_LEVEL_TYPE.DELETE;
zx_ptnr_neg_lines_tab.LINE_LEVEL_ACTION.DELETE;
zx_ptnr_neg_lines_tab.TRX_LINE_DATE.DELETE;
zx_ptnr_neg_lines_tab.LINE_AMT.DELETE;
zx_ptnr_neg_lines_tab.TRX_LINE_QUANTITY.DELETE;
zx_ptnr_neg_lines_tab.UNIT_PRICE.DELETE;
zx_ptnr_neg_lines_tab.PRODUCT_ID.DELETE;
zx_ptnr_neg_lines_tab.PRODUCT_ORG_ID.DELETE;
zx_ptnr_neg_lines_tab.UOM_CODE.DELETE;
zx_ptnr_neg_lines_tab.PRODUCT_TYPE.DELETE;
zx_ptnr_neg_lines_tab.PRODUCT_CODE.DELETE;
zx_ptnr_neg_lines_tab.FOB_POINT.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE1.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE1.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE2.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE2.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE3.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE3.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE4.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE4.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE5.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE5.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE6.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE6.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE7.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE7.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE8.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE8.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE9.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE9.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_TYPE10.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_GEOGRAPHY_VALUE10.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE1.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE1.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE2.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE2.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE3.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE3.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE4.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE4.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE5.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE5.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE6.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE6.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE7.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE7.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE8.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE8.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE9.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE9.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_TYPE10.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_GEOGRAPHY_VALUE10.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE1.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE1.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE2.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE2.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE3.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE3.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE4.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE4.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE5.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE5.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE6.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE6.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE7.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE7.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE8.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE8.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE9.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE9.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_TYPE10.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_GEOGRAPHY_VALUE10.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE1.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE1.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE2.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE2.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE3.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE3.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE4.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE4.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE5.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE5.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE6.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE6.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE7.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE7.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE8.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE8.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE9.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE9.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_TYPE10.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_GEOGRAPHY_VALUE10.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_LOCATION_ID.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_LOCATION_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_LOCATION_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_LOCATION_ID.DELETE;
zx_ptnr_neg_lines_tab.ACCOUNT_CCID.DELETE;
zx_ptnr_neg_lines_tab.APPL_FROM_TRX_ID.DELETE;
zx_ptnr_neg_lines_tab.APPL_FROM_LINE_ID.DELETE;
zx_ptnr_neg_lines_tab.APPL_FROM_TRX_LEVEL_TYPE.DELETE;
zx_ptnr_neg_lines_tab.APPL_FROM_TRX_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_TRX_ID.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_LINE_ID.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_TRX_LEVEL_TYPE.DELETE;
zx_ptnr_neg_lines_tab.EXEMPT_CERTIFICATE_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.EXEMPT_REASON.DELETE;
zx_ptnr_neg_lines_tab.EXEMPTION_CONTROL_FLAG.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_PARTY_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_PARTY_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_SITE_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_PARTY_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_CUST_ACCT_SITE_USE_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_SITE_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_PARTY_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.TRADING_HQ_SITE_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.TRADING_HQ_PARTY_TAX_PROF_ID.DELETE;
zx_ptnr_neg_lines_tab.SHIP_THIRD_PTY_ACCT_ID.DELETE; -- Bug 4939819
zx_ptnr_neg_lines_tab.BILL_THIRD_PTY_ACCT_ID.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_APPLICATION_ID.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_ENTITY_CODE.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_EVENT_CLASS_CODE.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_PARTY_NAME.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_PARTY_NAME.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_PARTY_NAME.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_PARTY_NAME.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_PARTY_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.SHIP_FROM_PARTY_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.BILL_FROM_PARTY_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.BILL_TO_PARTY_NUMBER.DELETE;
zx_ptnr_neg_lines_tab.TAX_PROVIDER_ID.DELETE;
zx_ptnr_neg_lines_tab.TAX_REGIME_CODE.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE1.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE2.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE3.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE4.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE5.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE6.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE7.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE8.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE9.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE10.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE11.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE12.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE13.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE14.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE15.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE16.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE17.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE18.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE19.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE20.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE21.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE22.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE23.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE24.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE25.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE26.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE27.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE28.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE29.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_VARCHAR_ATTRIBUTE30.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE1.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE2.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE3.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE4.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE5.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE6.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE7.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE8.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE9.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_NUMBER_ATTRIBUTE10.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE1.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE2.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE3.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE4.DELETE;
zx_ptnr_neg_lines_tab.LINE_EXT_DATE_ATTRIBUTE5.DELETE;
zx_ptnr_neg_lines_tab.SHIP_THIRD_PTY_ACCT_SITE_ID.DELETE;
zx_ptnr_neg_lines_tab.BILL_THIRD_PTY_ACCT_SITE_ID.DELETE;
zx_ptnr_neg_lines_tab.SHIP_TO_CUST_ACCT_SITE_USE_ID.DELETE;
zx_ptnr_neg_lines_tab.RECEIVABLES_TRX_TYPE_ID.DELETE;
zx_ptnr_neg_lines_tab.ADJUSTED_DOC_DATE.DELETE; --Bug14756523
When multiple item lines are updated, "COPY_PTNR_TAX_LINE_BEF_UPD" is called
individually for each item line updated. Hence, subsequent calls delete the
earlier records inserted in zx_ptnr_neg_line_gt.
As far as updating multiple item lines is concerned, records are deleted from
zx_ptnr_neg_line_gt after each line update in zxvtxsrvcpkgb.pls */
-- delete from zx_ptnr_neg_line_gt;
select application_id
into l_application_id
from zx_lines
where tax_line_id = p_tax_line_id;
INSERT INTO
ZX_PTNR_NEG_TAX_LINE_GT
(
tax_line_id ,
document_type_id ,
application_id ,
entity_code ,
event_class_code ,
trx_id ,
trx_line_id ,
trx_level_type ,
country_code ,
tax ,
situs ,
tax_jurisdiction ,
tax_currency_code ,
tax_curr_tax_amount ,
tax_amount ,
tax_rate_percentage ,
taxable_amount ,
exempt_certificate_number ,
exempt_rate_modifier ,
exempt_reason ,
tax_only_line_flag ,
inclusive_tax_line_flag ,
use_tax_flag ,
ebiz_override_flag ,
user_override_flag ,
last_manual_entry ,
manually_entered_flag ,
cancel_flag ,
delete_flag
)
SELECT
p_tax_line_id,
zxevnt.EVENT_CLASS_MAPPING_ID,
zxlines.application_id,
zxlines.entity_code,
zxlines.event_class_code,
zxlines.trx_id,
zxlines.trx_line_id,
zxlines.trx_level_type,
zxlines.tax_regime_code,
zxlines.tax,
zxlines.place_of_supply_type_code,
zxlines.tax_jurisdiction_code,
zxlines.tax_currency_code,
zxlines.tax_amt_tax_curr,
zxlines. TAX_AMT ,
zxlines.tax_rate,
zxlines.TAXABLE_AMT,
zxlines.exempt_certificate_number,
zxlines.exempt_rate_modifier,
zxlines.exempt_reason,
zxlines.tax_only_line_flag,
zxlines.tax_amt_included_flag,
zxlines.self_assessed_flag,
decode(zxlines.overridden_flag, 'N', 'Y', 'N') ,
zxlines.overridden_flag,
zxlines.last_manual_entry,
zxlines.manually_entered_flag,
zxlines.cancel_flag,
zxlines.delete_flag
FROM zx_lines zxlines, zx_evnt_cls_mappings zxevnt
WHERE zxlines.tax_line_id = p_tax_line_id
AND zxevnt.event_class_code = zxlines.event_class_code
AND zxevnt.application_id = zxlines.application_id
AND zxevnt.entity_code = zxlines.entity_code;
p_update_index IN NUMBER,
p_trx_copy_for_tax_update IN VARCHAR2,
p_regime_code IN VARCHAR2,
p_tax_provider_id IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) is
l_geography_id number;
SELECT
INTERNAL_ORGANIZATION_ID,
EVENT_CLASS_MAPPING_ID,
TRX_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
APPLICATION_ID,
TRX_DATE,
TRX_CURRENCY_CODE,
TRX_NUMBER,
RECORD_TYPE_CODE,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
LINE_LEVEL_ACTION,
TRX_LINE_DATE,
LINE_AMT,
TRX_LINE_QUANTITY,
UNIT_PRICE,
PRODUCT_ID,
PRODUCT_ORG_ID,
UOM_CODE,
PRODUCT_TYPE,
PRODUCT_CODE,
FOB_POINT,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE, --Bug 6434040
EXEMPTION_CONTROL_FLAG,
SHIP_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_PARTY_TAX_PROF_ID,
SHIP_TO_SITE_TAX_PROF_ID,
SHIP_TO_PARTY_TAX_PROF_ID,
SHIP_TO_LOCATION_ID,
BILL_TO_CUST_ACCT_SITE_USE_ID,
BILL_TO_SITE_TAX_PROF_ID,
BILL_TO_PARTY_TAX_PROF_ID,
BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID,
--POA_LOCATION_ID,
--POO_LOCATION_ID,
SHIP_FROM_LOCATION_ID,
TRADING_HQ_SITE_TAX_PROF_ID,
TRADING_HQ_PARTY_TAX_PROF_ID,
SHIP_THIRD_PTY_ACCT_ID, -- Bug 4939819
BILL_THIRD_PTY_ACCT_ID,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_ENTITY_CODE,
ADJUSTED_DOC_EVENT_CLASS_CODE,
ADJUSTED_DOC_TRX_ID,
ADJUSTED_DOC_LINE_ID,
ADJUSTED_DOC_TRX_LEVEL_TYPE,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
SHIP_THIRD_PTY_ACCT_SITE_ID,
BILL_THIRD_PTY_ACCT_SITE_ID,
RECEIVABLES_TRX_TYPE_ID,
ADJUSTED_DOC_DATE --Bug14756523
FROM ZX_LINES_DET_FACTORS
WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID AND
ENTITY_CODE = p_event_class_rec.ENTITY_CODE AND
EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE AND
TRX_ID = p_event_class_rec.TRX_ID;
/* TSRM calls the ZX_SRVC_TYP_PKG.db_update_line_det_factors after the tax calculation
is successfully completed to update some flags on ZX_LINE_DET_FACTORS. In this scenario,
we need not take the snapshot of the ZX_LINE_DET_FACTORS as these updates may not be
relevant for the partners.
If the call is made during the post tax calculation, the ZX_API_PUB.G_PUB_SRVC is set to
'CALCULATE_TAX'. Hence, we will aviod the snapshot capturing during this scenario.
We should not call this API for products other than AR. */
IF p_event_class_rec.application_id = 222 THEN
IF ZX_API_PUB.G_PUB_SRVC = 'CALCULATE_TAX' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' Skipping this procedure as no synchronization needed.');
IF nvl(p_trx_copy_for_tax_update, 'N') = 'Y' OR p_tax_provider_id is NULL THEN
begin
select tax_regime_code,
tax_provider_id
into l_regime_code,
l_tax_provider_id
from zx_lines
WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
AND TRX_ID = p_event_class_rec.TRX_ID
AND TAX_PROVIDER_ID in (1,2)
AND rownum = 1;
' p_trx_copy_for_tax_update: '|| p_trx_copy_for_tax_update);
FOR ptr IN p_trx_line_dist_tbl.application_id.FIRST .. p_update_index loop
I:=ptr;
Select
INTERNAL_ORGANIZATION_ID,
EVENT_CLASS_MAPPING_ID,
TRX_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
APPLICATION_ID,
TRX_DATE,
TRX_CURRENCY_CODE,
TRX_NUMBER,
RECORD_TYPE_CODE,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
LINE_LEVEL_ACTION,
TRX_LINE_DATE,
LINE_AMT,
TRX_LINE_QUANTITY,
UNIT_PRICE,
PRODUCT_ID,
PRODUCT_ORG_ID,
UOM_CODE,
PRODUCT_TYPE,
PRODUCT_CODE,
FOB_POINT,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE, --Bug 6434040
EXEMPTION_CONTROL_FLAG,
SHIP_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_PARTY_TAX_PROF_ID,
SHIP_TO_SITE_TAX_PROF_ID,
SHIP_TO_PARTY_TAX_PROF_ID,
SHIP_TO_LOCATION_ID,
BILL_TO_CUST_ACCT_SITE_USE_ID,
BILL_TO_SITE_TAX_PROF_ID,
BILL_TO_PARTY_TAX_PROF_ID,
BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID,
--POA_LOCATION_ID,
--POO_LOCATION_ID,
SHIP_FROM_LOCATION_ID,
TRADING_HQ_SITE_TAX_PROF_ID,
TRADING_HQ_PARTY_TAX_PROF_ID,
SHIP_THIRD_PTY_ACCT_ID, -- Bug 4939819
BILL_THIRD_PTY_ACCT_ID,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_ENTITY_CODE,
ADJUSTED_DOC_EVENT_CLASS_CODE,
ADJUSTED_DOC_TRX_ID,
ADJUSTED_DOC_LINE_ID,
ADJUSTED_DOC_TRX_LEVEL_TYPE,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
SHIP_THIRD_PTY_ACCT_SITE_ID,
BILL_THIRD_PTY_ACCT_SITE_ID,
RECEIVABLES_TRX_TYPE_ID,
ADJUSTED_DOC_DATE --Bug14756523
INTO
ZX_PTNR_NEG_LINES_TAB.INTERNAL_ORGANIZATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_MAPPING_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRX_ID(i),
ZX_PTNR_NEG_LINES_TAB.ENTITY_CODE(i),
ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_CODE(i),
ZX_PTNR_NEG_LINES_TAB.APPLICATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRX_DATE(i),
ZX_PTNR_NEG_LINES_TAB.TRX_CURRENCY_CODE(i),
ZX_PTNR_NEG_LINES_TAB.TRX_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.RECORD_TYPE_CODE(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LINE_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LEVEL_TYPE(i),
ZX_PTNR_NEG_LINES_TAB.LINE_LEVEL_ACTION(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LINE_DATE(i),
ZX_PTNR_NEG_LINES_TAB.LINE_AMT(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LINE_QUANTITY(i),
ZX_PTNR_NEG_LINES_TAB.UNIT_PRICE(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_ID(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_ORG_ID(i),
ZX_PTNR_NEG_LINES_TAB.UOM_CODE(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_TYPE(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_CODE(i),
ZX_PTNR_NEG_LINES_TAB.FOB_POINT(i),
ZX_PTNR_NEG_LINES_TAB.EXEMPT_CERTIFICATE_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.EXEMPT_REASON(i),
ZX_PTNR_NEG_LINES_TAB.EXEMPTION_CONTROL_FLAG(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_SITE_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_LOCATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_SITE_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_LOCATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_LOCATION_ID(i),
--ZX_PTNR_NEG_LINES_TAB.POA_LOCATION_ID(i),
--ZX_PTNR_NEG_LINES_TAB.POO_LOCATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_LOCATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_SITE_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_ID(i), -- Bug 4939819
ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_APPLICATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_ENTITY_CODE(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_EVENT_CLASS_CODE(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_LINE_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_LEVEL_TYPE(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_SITE_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_SITE_ID(i),
ZX_PTNR_NEG_LINES_TAB.RECEIVABLES_TRX_TYPE_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_DATE(i) --Bug14756523
FROM ZX_LINES_DET_FACTORS
WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID AND
ENTITY_CODE = p_event_class_rec.ENTITY_CODE AND
EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE AND
TRX_ID = p_event_class_rec.TRX_ID AND
TRX_LINE_ID = p_trx_line_dist_tbl.TRX_LINE_ID(i) AND
TRX_LEVEL_TYPE = p_trx_line_dist_tbl.TRX_LEVEL_TYPE(i);
ship_to_geo_names_derive(p_trx_copy_for_tax_update);
ship_from_geo_names_derive(p_trx_copy_for_tax_update);
bill_to_geo_names_derive(p_trx_copy_for_tax_update);
bill_from_geo_names_derive(p_trx_copy_for_tax_update);
ship_to_geo_names_derive(p_trx_copy_for_tax_update);
ship_from_geo_names_derive(p_trx_copy_for_tax_update);
bill_to_geo_names_derive(p_trx_copy_for_tax_update);
bill_from_geo_names_derive(p_trx_copy_for_tax_update);
' Inserting into zx_ptnr_neg_line_gt');
Insert into zx_ptnr_neg_line_gt(
INTERNAL_ORGANIZATION_ID,
EVENT_CLASS_MAPPING_ID,
TRX_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
APPLICATION_ID,
TRX_DATE,
TRX_CURRENCY_CODE,
TRX_NUMBER,
RECORD_TYPE_CODE,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
LINE_LEVEL_ACTION,
TRX_LINE_DATE,
LINE_AMT,
TRX_LINE_QUANTITY,
UNIT_PRICE,
PRODUCT_ID,
PRODUCT_ORG_ID,
UOM_CODE,
PRODUCT_TYPE,
PRODUCT_CODE,
FOB_POINT,
TAX_REGIME_CODE,
TAX_PROVIDER_ID,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON,
EXEMPTION_CONTROL_FLAG,
SHIP_TO_PARTY_TAX_PROF_ID,
SHIP_FROM_PARTY_TAX_PROF_ID,
SHIP_TO_PARTY_NUMBER,
SHIP_TO_PARTY_NAME,
SHIP_FROM_PARTY_NUMBER,
SHIP_FROM_PARTY_NAME,
BILL_TO_PARTY_NUMBER,
BILL_TO_PARTY_NAME,
BILL_FROM_PARTY_NUMBER,
BILL_FROM_PARTY_NAME,
SHIP_TO_SITE_TAX_PROF_ID,
SHIP_TO_LOCATION_ID,
BILL_TO_CUST_ACCT_SITE_USE_ID,
BILL_TO_SITE_TAX_PROF_ID,
BILL_TO_PARTY_TAX_PROF_ID,
BILL_TO_LOCATION_ID,
TRADING_HQ_SITE_TAX_PROF_ID,
TRADING_HQ_PARTY_TAX_PROF_ID,
BILL_THIRD_PTY_ACCT_ID,
ADJUSTED_DOC_EVENT_CLASS_CODE,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_ENTITY_CODE,
ADJUSTED_DOC_TRX_ID,
ADJUSTED_DOC_LINE_ID,
ADJUSTED_DOC_TRX_LEVEL_TYPE,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
SHIP_THIRD_PTY_ACCT_SITE_ID,
BILL_THIRD_PTY_ACCT_SITE_ID,
SHIP_TO_GEOGRAPHY_TYPE1,
SHIP_TO_GEOGRAPHY_VALUE1,
SHIP_TO_GEOGRAPHY_TYPE2,
SHIP_TO_GEOGRAPHY_VALUE2,
SHIP_TO_GEOGRAPHY_TYPE3,
SHIP_TO_GEOGRAPHY_VALUE3,
SHIP_TO_GEOGRAPHY_TYPE4,
SHIP_TO_GEOGRAPHY_VALUE4,
SHIP_FROM_GEOGRAPHY_TYPE1,
SHIP_FROM_GEOGRAPHY_VALUE1,
SHIP_FROM_GEOGRAPHY_TYPE2,
SHIP_FROM_GEOGRAPHY_VALUE2,
SHIP_FROM_GEOGRAPHY_TYPE3,
SHIP_FROM_GEOGRAPHY_VALUE3,
SHIP_FROM_GEOGRAPHY_TYPE4,
SHIP_FROM_GEOGRAPHY_VALUE4,
BILL_TO_GEOGRAPHY_TYPE1,
BILL_TO_GEOGRAPHY_VALUE1,
BILL_TO_GEOGRAPHY_TYPE2,
BILL_TO_GEOGRAPHY_VALUE2,
BILL_TO_GEOGRAPHY_TYPE3,
BILL_TO_GEOGRAPHY_VALUE3,
BILL_TO_GEOGRAPHY_TYPE4,
BILL_TO_GEOGRAPHY_VALUE4,
BILL_FROM_GEOGRAPHY_TYPE1,
BILL_FROM_GEOGRAPHY_VALUE1,
BILL_FROM_GEOGRAPHY_TYPE2,
BILL_FROM_GEOGRAPHY_VALUE2,
BILL_FROM_GEOGRAPHY_TYPE3,
BILL_FROM_GEOGRAPHY_VALUE3,
BILL_FROM_GEOGRAPHY_TYPE4,
BILL_FROM_GEOGRAPHY_VALUE4,
RECEIVABLES_TRX_TYPE_ID,
ADJUSTED_DOC_DATE --Bug14756523
)
SELECT
ZX_PTNR_NEG_LINES_TAB.INTERNAL_ORGANIZATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_MAPPING_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRX_ID(i),
ZX_PTNR_NEG_LINES_TAB.ENTITY_CODE(i),
ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_CODE(i),
ZX_PTNR_NEG_LINES_TAB.APPLICATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRX_DATE(i),
ZX_PTNR_NEG_LINES_TAB.TRX_CURRENCY_CODE(i),
ZX_PTNR_NEG_LINES_TAB.TRX_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.RECORD_TYPE_CODE(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LINE_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LEVEL_TYPE(i),
ZX_PTNR_NEG_LINES_TAB.LINE_LEVEL_ACTION(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LINE_DATE(i),
ZX_PTNR_NEG_LINES_TAB.LINE_AMT(i),
ZX_PTNR_NEG_LINES_TAB.TRX_LINE_QUANTITY(i),
ZX_PTNR_NEG_LINES_TAB.UNIT_PRICE(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_ID(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_ORG_ID(i),
ZX_PTNR_NEG_LINES_TAB.UOM_CODE(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_TYPE(i),
ZX_PTNR_NEG_LINES_TAB.PRODUCT_CODE(i),
ZX_PTNR_NEG_LINES_TAB.FOB_POINT(i),
ZX_PTNR_NEG_LINES_TAB.TAX_REGIME_CODE(i),
ZX_PTNR_NEG_LINES_TAB.TAX_PROVIDER_ID(i),
ZX_PTNR_NEG_LINES_TAB.EXEMPT_CERTIFICATE_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.EXEMPT_REASON(i),
ZX_PTNR_NEG_LINES_TAB.EXEMPTION_CONTROL_FLAG(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NUMBER(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_PARTY_NAME(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_SITE_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_LOCATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_CUST_ACCT_SITE_USE_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_SITE_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_LOCATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_SITE_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.TRADING_HQ_PARTY_TAX_PROF_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_EVENT_CLASS_CODE(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_APPLICATION_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_ENTITY_CODE(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_LINE_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_TRX_LEVEL_TYPE(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_CUST_ACCT_SITE_USE_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_THIRD_PTY_ACCT_SITE_ID(i),
ZX_PTNR_NEG_LINES_TAB.BILL_THIRD_PTY_ACCT_SITE_ID(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE1(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE1(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE2(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE2(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE3(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE3(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_TYPE4(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_TO_GEOGRAPHY_VALUE4(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE1(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE1(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE2(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE2(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE3(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE3(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_TYPE4(i),
ZX_PTNR_NEG_LINES_TAB.SHIP_FROM_GEOGRAPHY_VALUE4(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE1(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE1(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE2(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE2(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE3(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_VALUE3(i),
ZX_PTNR_NEG_LINES_TAB.BILL_TO_GEOGRAPHY_TYPE4(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE4(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE1(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE1(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE2(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE2(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE3(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE3(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_TYPE4(i),
ZX_PTNR_NEG_LINES_TAB.BILL_FROM_GEOGRAPHY_VALUE4(i),
ZX_PTNR_NEG_LINES_TAB.RECEIVABLES_TRX_TYPE_ID(i),
ZX_PTNR_NEG_LINES_TAB.ADJUSTED_DOC_DATE(i) --Bug14756523
FROM DUAL
WHERE NOT EXISTS (SELECT 'Y'
FROM zx_ptnr_neg_line_gt
WHERE application_id = ZX_PTNR_NEG_LINES_TAB.APPLICATION_ID(i)
AND entity_code = ZX_PTNR_NEG_LINES_TAB.ENTITY_CODE(i)
AND event_class_code = ZX_PTNR_NEG_LINES_TAB.EVENT_CLASS_CODE(i)
AND trx_id = ZX_PTNR_NEG_LINES_TAB.TRX_ID(i)
AND trx_line_id = ZX_PTNR_NEG_LINES_TAB.TRX_LINE_ID(i)
);
/*Bug 4950953 , 4950901 making default value as NULL as it causing issue in update operation subsequently*/
FOR i IN zx_ptnr_neg_lines_tab.application_id.FIRST .. zx_ptnr_neg_lines_tab.application_id.LAST
LOOP
ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) := NULL;
/*Bug 4950953 , 4950901 During Invoice update,complete scenario's need to populate the
ZX_TRX_PRE_PROC_OPTIONS_GT as it used in user procedures.*/
Begin
select count(*)
into l_count
from ZX_TRX_PRE_PROC_OPTIONS_GT
where APPLICATION_ID = p_event_class_rec.application_id
AND ENTITY_CODE = p_event_class_rec.entity_code
AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
AND TRX_ID = p_event_class_rec.trx_id;
INSERT into ZX_TRX_PRE_PROC_OPTIONS_GT (INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVNT_CLS_MAPPING_ID,
TAX_EVENT_TYPE_CODE,
PROD_FAMILY_GRP_CODE,
TRX_ID,
TAX_REGIME_CODE,
PARTNER_PROCESSING_FLAG,
TAX_PROVIDER_ID,
EVENT_ID,
QUOTE_FLAG,
RECORD_FLAG,
RECORD_FOR_PARTNERS_FLAG,
APPLICATION_SHORT_NAME,
LEGAL_ENTITY_NUMBER,
ESTABLISHMENT_NUMBER, -- Bug 5139731
ALLOW_TAX_CALCULATION_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (p_event_class_rec.internal_organization_id,
p_event_class_rec.application_id,
p_event_class_rec.entity_code,
p_event_class_rec.event_class_code,
p_event_class_rec.event_class_mapping_id,
p_event_class_rec.tax_event_type_code,
p_event_class_rec.prod_family_grp_code,
p_event_class_rec.trx_id,
l_regime_code,
NULL, --p_ptnr_processing_flag,
l_tax_provider_id,
p_event_class_rec.event_id,
p_event_class_rec.quote_flag,
p_event_class_rec.record_flag,
p_event_class_rec.record_for_partners_flag,
NULL,--l_application_short_name,
NULL,--l_legal_entity_number,
NULL,--l_establishment_number,
p_event_class_rec.process_for_applicability_flag,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id);
IF ZX_API_PUB.G_PUB_SRVC NOT IN ('GLOBAL_DOCUMENT_UPDATE', 'OVERRIDE_TAX') THEN -- Bug 5200373
DELETE FROM ZX_TRX_PRE_PROC_OPTIONS_GT
WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
AND TRX_ID = p_event_class_rec.TRX_ID;
Update ZX_PTNR_NEG_LINE_GT SET
LINE_EXT_VARCHAR_ATTRIBUTE1 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE1(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE2 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE2(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE3 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE3(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE4 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE4(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE5 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE5(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE6 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE6(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE7 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE7(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE8 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE8(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE9 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE9(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE10 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE10(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE11 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE11(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE12 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE12(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE13 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE13(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE14 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE14(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE15 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE15(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE16 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE16(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE17 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE17(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE18 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE18(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE19 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE19(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE20 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE20(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE21 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE21(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE22 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE22(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE23 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE23(i) ,
LINE_EXT_VARCHAR_ATTRIBUTE24 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_VARCHAR_ATTRIBUTE24(i) ,
LINE_EXT_NUMBER_ATTRIBUTE1 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE1(i) ,
LINE_EXT_NUMBER_ATTRIBUTE2 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE2(i) ,
LINE_EXT_NUMBER_ATTRIBUTE3 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE3(i) ,
LINE_EXT_NUMBER_ATTRIBUTE4 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE4(i) ,
LINE_EXT_NUMBER_ATTRIBUTE5 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE5(i) ,
LINE_EXT_NUMBER_ATTRIBUTE6 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_NUMBER_ATTRIBUTE6(i) ,
LINE_EXT_DATE_ATTRIBUTE1 = ZX_PTNR_NEG_LINES_TAB.LINE_EXT_DATE_ATTRIBUTE1(i)
WHERE
APPLICATION_ID = p_event_class_rec.APPLICATION_ID AND
ENTITY_CODE = p_event_class_rec.ENTITY_CODE AND
EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE AND
TRX_ID = p_event_class_rec.TRX_ID AND
TRX_LINE_ID = zx_ptnr_neg_lines_tab.TRX_LINE_ID(i) AND
TRX_LEVEL_TYPE = zx_ptnr_neg_lines_tab.TRX_LEVEL_TYPE(i);
select tax_regime_code
into p_country_code
from zx_regimes_usages
where regime_usage_id=p_regime_usage_id;
SELECT pty.party_name
INTO l_srvc_prvdr_name
FROM HZ_PARTIES pty,
ZX_PARTY_TAX_PROFILE ptp
WHERE ptp.party_tax_profile_id =p_srvc_prvdr_id
AND pty.party_id = ptp.party_id
AND ptp.provider_type_code in ('BOTH', 'SERVICE');
'GLOBAL_DOCUMENT_UPDATE');
'GLOBAL_DOCUMENT_UPDATE');
SELECT context_flex_structure_id
INTO l_context_flex_structure_id
FROM ZX_SERVICE_TYPES
WHERE service_category_code = 'USER_EXT'
AND rownum=1;
SELECT distinct ru.tax_regime_code
into l_tax_regime_code
FROM zx_regimes_usages ru,
zx_srvc_subscriptions srvc
WHERE srvc.regime_usage_id=p_regime_usage_id
and srvc.regime_usage_id=ru.regime_usage_id
AND NOT EXISTS (SELECT 1
FROM ZX_API_CODE_COMBINATIONS comb
WHERE comb.segment_attribute1 = ru.tax_regime_code
AND comb.segment_attribute2 is null
);
INSERT INTO ZX_API_CODE_COMBINATIONS (
CONTEXT_FLEX_STRUCTURE_ID,
CODE_COMBINATION_ID,
SEGMENT_ATTRIBUTE1,
SUMMARY_FLAG,
ENABLED_FLAG,
RECORD_TYPE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (l_context_flex_structure_id,
ZX_API_CODE_COMBINATIONS_S.nextval,
l_tax_regime_code,
'N',
'Y',
'EBTAX_CREATED',
sysdate, --creation_date
fnd_global.user_Id, --created_by
sysdate, --last_update_date
fnd_global.user_id, --last_updated_by
fnd_global.conc_login_id --last_update_login
);
INSERT INTO ZX_API_REGISTRATIONS (
API_REGISTRATION_ID,
API_OWNER_ID,
CONTEXT_CCID,
PACKAGE_NAME,
PROCEDURE_NAME,
SERVICE_TYPE_ID,
RECORD_TYPE_CODE,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT ZX_API_REGISTRATIONS_S.nextval,
ru.first_pty_org_id,
comb.code_combination_id,
decode(srvc.srvc_provider_id,1,'ZX_VTX_USER_PKG','ZX_TAXWARE_USER_PKG'),
decode(srvctypes.service_type_code,'DERIVE_HDR_ATTRS','DERIVE_HDR_EXT_ATTR',
'DERIVE_LINE_ATTRS','DERIVE_LINE_EXT_ATTR'),
srvctypes.service_type_id,
'EBTAX_CREATED',
1,
sysdate, --creation_date
fnd_global.user_Id, --created_by
sysdate, --last_update_date
fnd_global.user_id, --last_updated_by
fnd_global.conc_login_id --last_update_login;
AND NOT EXISTS (SELECT 1
FROM ZX_API_REGISTRATIONS api
WHERE api.service_type_id = srvctypes.service_type_id
AND api.context_ccid = comb.code_combination_id
AND api.api_owner_id = ru.first_pty_org_id
);
INSERT INTO zx_api_owner_statuses (
API_OWNER_ID,
SERVICE_CATEGORY_CODE,
STATUS_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT ru.first_pty_org_id,
'USER_EXT',
'NEW',
sysdate, --creation_date
fnd_global.user_Id, --created_by
sysdate, --last_update_date
fnd_global.user_id, --last_updated_by
fnd_global.conc_login_id --last_update_login;
AND NOT EXISTS (SELECT 1
FROM ZX_API_OWNER_STATUSES sts
WHERE sts.api_owner_id = ru.first_pty_org_id
);
select ru.first_pty_org_id,
comb.code_combination_id
INTO l_api_owner_id,
l_context_cc_id
FROM ZX_REGIMES_USAGES ru,
ZX_API_CODE_COMBINATIONS comb,
ZX_SRVC_SUBSCRIPTIONS srvc,
ZX_SERVICE_TYPES srvctypes
WHERE ru.regime_usage_id = p_regime_usage_id
AND srvc.srvc_provider_id = p_srvc_prvdr_id
AND srvctypes.context_flex_structure_id= l_context_flex_structure_id
AND srvc.regime_usage_id = ru.regime_usage_id
AND ru.tax_regime_code = comb.segment_attribute1
AND comb.segment_attribute2 is null
AND comb.context_flex_structure_id = srvctypes.context_flex_structure_id
AND srvctypes.service_type_code in ('DERIVE_HDR_ATTRS','DERIVE_LINE_ATTRS');
SELECT pty.party_name
INTO l_srvc_prvdr_name
FROM HZ_PARTIES pty,
ZX_PARTY_TAX_PROFILE ptp
WHERE ptp.party_tax_profile_id =p_srvc_prvdr_id
AND pty.party_id = ptp.party_id
AND ptp.provider_type_code in ('BOTH', 'SERVICE');
Select
distinct srvc.srvc_provider_id
into l_srvc_provider_id
from zx_api_code_combinations comb,
zx_regimes_usages usage,
zx_srvc_subscriptions srvc
where comb.code_combination_id= p_context_cc_id
and comb.segment_attribute1= usage.tax_regime_code
and comb.segment_attribute2= 'O2C'
and usage.first_pty_org_id= p_api_owner_id
and srvc.regime_usage_id=usage.regime_usage_id
and not exists (select 1 from zx_api_registrations
where API_OWNER_ID = srvc.srvc_provider_id
and CONTEXT_CCID = p_context_cc_id);