Provisioning and Administering Oracle Integration 3
Tuesday, 23 June 2026
Monday, 22 June 2026
Delta Data or Incremental in Oracle Fusion
About Incremental Data logic In Oracle Fusion
Delta Data (often referred to as Incremental Data)
refers exclusively to the data that has changed.
meaning when it was created, updated, or deleted
since a specific point in time,
usually the last successful integration or extraction run.
Instead of processing the entire database data every time (a
"Full Load"), integrations use delta extracts to pull only the fresh
updates. This is crucial for maintaining high-performing, real-time, or daily
synchronized systems.
How it works with practical example
Consider a bank statement, a full load is the entire transaction history since the account
was opened. A delta load is just the transactions that happened yesterday.
|
Feature |
Full Data Load |
Delta / Incremental Data |
|
Volume |
High
(Processes all records) |
Low
(Processes only changed records) |
|
Performance |
Slower,
resource-intensive |
Fast,
lightweight |
|
Frequency |
Typically
one-time, weekly, or monthly |
Frequent
(Hourly, daily, or near real-time) |
|
Primary
Use |
Initial
system cut-over or data reconciliation |
Regular,
automated downstream integrations |
SQL Query:
select to_char(sysdate
,'mm/dd/yyyy hh:mi:ss') systimestamp,
to_char(dha.creation_date
,'mm/dd/yyyy hh:mi:ss') ord_Cre_Date,
dha.creation_date crfea_date,
dha.order_number
FROM
doo_headers_all dha
WHERE 1=1
AND
To_date(To_char(dha.creation_date,
'DD-MM-YYYY HH24:MI:SS'
)
,
'DD-MM-YYYY HH24:MI:SS')
BETWEEN
To_date(:p_from_date,
'DD-MM-YYYY HH24:MI:SS') AND
To_date(:p_to_date,
'DD-MM-YYYY HH24:MI:SS'
)
--and submitted_flag='Y'
ORDER BY
dha.order_number,dha.creation_date DESC
Execution Part
Important Tables in Oracle Financials
Important Tables in Oracle Financials
Account Receivables (AR) Transactions Tables
RA_CUSTOMER_TRX_ALL
It is a Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL
It is a Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL
Distribution table for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL
Salesrep information table for Transaction Lines
Transaction Interface Tables
RA_INTERFACE_LINES_ALL Transaction
It stores Lines interface
RA_INTERFACE_SALESCREDITS_ALL
It stores Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL
It stores Transaction Distribution information
RA_INTERFACE_ERRORS_ALL
It stores Transaction errors details.
AR_PAYMENTS_INTERFACE_ALL
Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL
Lockbox transfers the receipts that pass validation to the interim tables.
AR_INTERIM_CASH_RCPT_LINES_ALL
Lockbox transfers the receipts that pass validation to the interim tables.
Account Receivable(AR) Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt
It is a Receitp Header tables
AR_RECEIVABLE_APPLICATIONS_ALL
It stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL
This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL
This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.
Customer Tables
HZ_PARTIES
A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS
This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES
This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL
This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL
This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS
A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
Setup tables
RA_CUST_TRX_TYPES_ALL
This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES
This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS
This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications
Integration Versioning in Oracle Integration Cloud
Integration Versioning in Oracle Integration Cloud(OIC)
When we create any integration, the version number will be
defaulted to 01.00.0000 for all the styles of integration(AppDriven, Schedule and Publisher/Subscriber Integrations)
Detailed Structure of Version in OIC:
01Ã Major(two
characters long)
00Ã Minor(two
characters long)
0000Ã Patch(patch
characters long)
With two ‘.’ In between, a total of 10 characters long.
Note: Version Number cannot be more than 10 characters.
[Major: 2 chars].
[Minor: 2 chars].
[Patch: 4 chars]
(e.g., 01.00.0000).=10 characters including ‘.’
Q:
Why does activating a Minor or
Patch version (like 01.01.0000) automatically deactivate my previous version,
while activating a Major version (2.00.0000) allows both to run side-by-side?
For the incremental changes in the Integration, instead of
deactivating the existing integration, we can create a new version
I want to make major/minor/patch changes in the integration
I can create it as patch(01.00.0001)
à 1.0.1;1.0.2;1.0.3…etc
I can create it as minor(01.01.0000)Ã 1.1.0;1.2.0;1.3.0—etc
I can create it as a major(2.00.0000)Ã 1.0.0;2.0.0;3.0.0..etc
When we try to activate the new version with a patch/minor,
it will force us to deactivate the older version. Because in the Gate Way URL
or endpoint URL we can see (1.0) at the end
When we try to activate minor or patch version, we will get
prompt to deactivate the previous version of Integration as the as URL ends
with (1.0) for both
Example URL looks like:
Default Integration endpoint URL(.01.00.0000)
https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/
integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO
WORLD REST_API/1.0/
sayHello/{firstName}
For Patch/Min Version Endpoint URL(01.01.0000/01.00.0001)
https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/
integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO
WORLD REST_API/1.0/
sayHello/{firstName}
When we activate new version 1.1.0/1.0.1 it will deactivate
1.0.0 to avoid collisions.
-------------------------
Now, for the major version
When I try to activate new version with major changes(2.0), it will not ask or force us to deactivate the old version
Default Integration endpoint URL(.01.00.0000)
https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/
integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO
WORLD REST_API/1.0/
sayHello/{firstName}
For Major versions(02.00.0000)
https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/
integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO
WORLD REST_API/2.0/
sayHello/{firstName}
https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/
integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO
WORLD REST_API/3.0/
sayHello/{firstName}
Friday, 19 June 2026
Supplier Sync Report from Cloud to EBS - Delta Data using parameters Date range with timestamp
Supplier Sync Report from Cloud to EBS - Delta Data using parameters Date range with timestamp
Supplier Master Data
SELECT DISTINCT *
FROM (SELECT 'SupplierMaster',
suppMaster.party_id,
parties.party_name,
suppMaster.segment1,
suppMaster.vendor_type_lookup_code,
suppMaster.vendor_id,
suppMaster.one_time_flag,
(SELECT DISTINCT apTerms.name
FROM poz_supplier_sites_all_m termNameSuppSitesAll,
ap_terms apTerms
WHERE suppMaster.vendor_id = termNameSuppSitesAll.vendor_id
AND termNameSuppSitesAll.terms_id = apTerms.term_id
AND ROWNUM = 1)
TermName,
(SELECT DISTINCT invoiceSuppSitesAll.pay_date_basis_lookup_code
FROM poz_supplier_sites_all_m invoiceSuppSitesAll
WHERE suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
AND ROWNUM = 1)
PayDate,
(SELECT DISTINCT invoiceSuppSitesAll.payment_priority
FROM poz_supplier_sites_all_m invoiceSuppSitesAll
WHERE suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
AND ROWNUM = 1)
PaymentPriority1,
' ' AS
PaymentCurrency,
suppMaster.minority_group_lookup_code,
suppMaster.women_owned_flag,
(SELECT DISTINCT invoiceSuppSitesAll.terms_date_basis
FROM poz_supplier_sites_all_m invoiceSuppSitesAll
WHERE suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
AND ROWNUM = 1)
TERMS_DATE_BASIS,
''
"_INSPECTION_REQUIRED_FLAG_",
''
"_RECEIPT_REQUIRED_FLAG_",
(SELECT DISTINCT
invoiceSuppSitesAll.allow_substitute_receipts_flag
FROM poz_supplier_sites_all_m invoiceSuppSitesAll
WHERE suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
AND ROWNUM = 1)
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
(SELECT DISTINCT
invoiceSuppSitesAll.allow_unordered_receipts_flag
FROM poz_supplier_sites_all_m invoiceSuppSitesAll
WHERE suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
AND ROWNUM = 1)
ALLOW_UNORDERED_RECEIPTS_FLAG,
suppMaster.federal_reportable_flag,
suppMaster.vat_registration_num
VAT_REGISTRATION_NUM,
suppMaster.tax_reporting_name
TaxReportingCode,
''
"_MATCH_OPTION_",
''
"_EMPLOYEE_ID_",
''
"_EMPLOYEE_NAME_",
To_char(suppMaster.last_update_date, 'DD-MM-YYYY HH24:MI:SS')
LAST_UPDATE_DATE,
regist.registration_status,
suppMasterPII.income_tax_id
TaxPayerID
,
suppMaster.taxpayer_country
Tax_Payer_Country,
suppMaster.organization_type_lookup_code
Tax_Organization_Type,
To_char(suppMaster.end_date_active, 'DD-MM-YYYY HH24:MI:SS')
Inactive_Date,
suppMaster.customer_num
Customer_Number,
suppMaster.corporate_website
CORPORATE_WEBSITE,
suppMaster.vat_code
VAT_CODE,
suppMaster.attribute1
ATTRIBUTE1
,
suppMaster.bc_not_applicable_flag
BUS_CLASS_NOT_APPLICABLE,
suppMaster.auto_tax_calc_override
AUTO_TAX_CALC_OVERRIDE,
suppOrg.ceo_name
Chief_Executive_Name,
suppOrg.ceo_title
Chief_Executive_Title,
suppMaster.allow_awt_flag
Use_Withholding_Tax,
(SELECT person_first_name
FROM hz_parties
WHERE busClass.provided_by_contact_id = party_id)
Provided_By_First_Name,
(SELECT person_last_name
FROM hz_parties
WHERE busClass.provided_by_contact_id = party_id)
Provided_By_Last_Name,
(SELECT email_address
FROM hz_parties
WHERE busClass.provided_by_contact_id = party_id)
Provided_By_Email,
suppOrg.year_established
YearEstablished,
suppOrg.mission_statement
MissionStatement,
suppOrg.incorp_year
YearIncorporated,
suppOrg.principal_title
PrincipalTitle,
suppOrg.principal_name
PrincipalName,
suppOrg.party_number
PartyCode,
(SELECT party_name
FROM hz_addtnl_party_names
WHERE party_id = suppMaster.party_id
AND status_flag = 'A')
Alias,
suppOrg.duns_number_c
DUNSNumber
,
suppOrg.sic_code
SIC,
suppOrg.fiscal_yearend_month
FiscalYearEndM,
suppOrg.curr_fy_potential_revenue
CurrFYPotRevenue,
suppOrg.pref_functional_currency
PrefFuncCurrency,
suppMaster.business_relationship
Relationships
/*,suppOrg.PARTY_ID CustomerNumber
,suppOrg.
,suppOrg.
National Insurance Number
Regional Information
*/
,
suppMaster.attribute2,
suppMaster.attribute3,
suppMaster.attribute4,
suppMaster.attribute5,
suppMaster.attribute6,
suppMaster.attribute7,
suppMaster.attribute8,
suppMaster.attribute9,
suppMaster.attribute10,
suppMaster.attribute11,
suppMaster.attribute12,
suppMaster.attribute13,
suppMaster.attribute14,
suppMaster.attribute15,
suppMaster.attribute16
GLOBAL_ATTRIBUTE1,
suppMaster.attribute17
GLOBAL_ATTRIBUTE2,
suppMaster.attribute18
GLOBAL_ATTRIBUTE3,
suppMaster.attribute19
GLOBAL_ATTRIBUTE4,
suppMaster.attribute20
GLOBAL_ATTRIBUTE5,
(SELECT party_name
FROM hz_parties
WHERE party_id = suppMaster.parent_party_id)
PARENT_SUPPLIER,
(SELECT vendor_name_alt
FROM poz_suppliers_v
WHERE party_id = suppMaster.party_id)
ALTERNATE_NAME,
Nvl((SELECT Listagg(
Concat(Concat(suppMaster.vendor_id, '_'), Concat(file_name,
Concat('#', fdt.description))), ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd
WHERE fad.document_id = fdt.document_id
AND fad.pk1_value = To_char(suppMaster.vendor_id)
AND fad.entity_name = 'POZ_SUPPLIERS'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE')
||Decode((SELECT prr.response_repository_id
FROM poq_response_repository prr,
poq_questions pq
WHERE supplier_id = To_char(suppMaster.vendor_id)
AND prr.question_id = pq.question_id
AND pq.question_name = 'Cancelled cheque'),
NULL,
NULL,
';')
|| (SELECT Listagg(
Concat(Concat(suppMaster.vendor_id, '_'),
Concat(file_name,
Concat('#', Nvl(fdt.description, 'Cancelled cheque')))), ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd
WHERE fad.document_id = fdt.document_id
AND fad.pk1_value IN
(SELECT prrv.resp_repository_value_id
FROM poq_response_repository prr,
poq_questions pq,
poq_resp_repository_values prrv
WHERE supplier_id = To_char(
suppMaster.vendor_id)
AND prr.response_repository_id =
prrv.response_repository_id
AND prr.question_id = pq.question_id
AND pq.question_name =
'Cancelled cheque')
AND fad.entity_name = 'POQ_RESP_REPOSITORY_VALUES'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'), 'XXX')
Header_AttachmentsFileName,
Nvl((SELECT Listagg(fdc.user_name, ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd,
fnd_document_categories_vl fdc
WHERE fad.document_id = fdt.document_id
AND pk1_value = To_char(suppMaster.vendor_id)
AND entity_name = 'POZ_SUPPLIERS'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'
AND fad.category_name = fdc.category_name)
|| Decode((SELECT prr.response_repository_id
FROM poq_response_repository prr,
poq_questions pq
WHERE supplier_id = To_char(suppMaster.vendor_id)
AND prr.question_id = pq.question_id
AND pq.question_name = 'Cancelled cheque'),
NULL,
NULL,
';')
|| (SELECT Listagg(fdc.user_name, ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd,
fnd_document_categories_vl fdc
WHERE fad.document_id = fdt.document_id
AND pk1_value IN
(SELECT prrv.resp_repository_value_id
FROM poq_response_repository prr,
poq_questions pq,
poq_resp_repository_values prrv
WHERE supplier_id = To_char(
suppMaster.vendor_id)
AND prr.response_repository_id =
prrv.response_repository_id
AND prr.question_id =
pq.question_id
AND pq.question_name =
'Cancelled cheque')
AND entity_name = 'POQ_RESP_REPOSITORY_VALUES'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'
AND fad.category_name = fdc.category_name), 'XXX')
HEADERSCATEGORY,
Nvl((SELECT prr.response_repository_id
FROM poq_response_repository prr,
poq_questions pq
WHERE supplier_id = To_char(suppMaster.vendor_id)
AND prr.question_id = pq.question_id
AND pq.question_name = 'Cancelled cheque'), '0')
RESPONSE_REPOSITORY_ID,
Nvl((SELECT prrv.resp_repository_value_id
FROM poq_response_repository prr,
poq_questions pq,
poq_resp_repository_values prrv
WHERE supplier_id = To_char(suppMaster.vendor_id)
AND prr.response_repository_id =
prrv.response_repository_id
AND prr.question_id = pq.question_id
AND pq.question_name = 'Cancelled cheque'), '0')
RESP_REPOSITORY_VALUE_ID
FROM poz_suppliers suppMaster,
hz_parties parties,
hz_party_sites partySite,
poz_supplier_registrations regist,
poz_suppliers_pii suppMasterPII,
hz_organization_profiles suppOrg,
/*poz_sup_products_services suppProdServ,*/
poz_bus_classifications busClass
WHERE suppMaster.party_id = parties.party_id (+)
AND suppMaster.party_id = partySite.party_id (+)
AND suppMaster.party_id = suppOrg.party_id (+)
AND suppMaster.segment1 = regist.supplier_number (+)
AND suppMaster.vendor_id = suppMasterPII.vendor_id(+)
/* AND suppMaster.vendor_id = suppProdServ.vendor_id(+) */
AND suppMaster.party_id = busClass.party_id(+)
--AND regist.registration_status = 'APPROVED'
AND suppMaster.business_relationship = 'SPEND_AUTHORIZED'
AND ( partySite.attribute2 = 'ACTIVE'
OR partySite.attribute2 = 'GSTIN NOT AVAILABLE' )
AND ( partySite.attribute4 = 'ACTIVE'
OR partySite.attribute4 = 'PAN NOT AVAILABLE' )
AND ( partySite.attribute17 IS NOT NULL
AND partySite.attribute18 IS NOT NULL )
-- and parties.party_name='AKASH TECHNO ENGINEERS'
--AND parties.country='US'
AND To_date(To_char(suppMaster.last_update_date,
'DD-MM-YYYY HH24:MI:SS'
),
'DD-MM-YYYY HH24:MI:SS') BETWEEN
To_date(:p_fromdatetime, 'DD-MM-YYYY HH24:MI:SS') AND
To_date(:p_todatetime, 'DD-MM-YYYY HH24:MI:SS'
)
AND suppMaster.attribute1 != 'TO BE VALIDATED'
/* AND suppMaster.ATTRIBUTE3 = 'Y' */
AND suppMaster.segment1 ! = '10026999'
AND suppMaster.segment1 NOT IN ( '30006135', '30007587' )
/*,'12300001','10022482','10005676','10008799', '10028530')*/
--AND suppMaster.segment1 NOT IN ('30006151')
/*AND suppMaster.segment1 IN ('10004572', '10000788')*/
/*and (SELECT DISTINCT
invoiceSuppSitesAll.PRC_BU_ID
FROM poz_supplier_sites_all_m invoiceSuppSitesAll
WHERE suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
AND ROWNUM = 1) not in (300000002166391,300000002166404)*/
/*and suppMaster.segment1 IN ('10001026')
and parties.party_name = 'A1 CLAMPS INDIA PVT LTD.'*/
)b
------------------------------------------
SupplierSiteData
SELECT 'SupplierSite',
suppMaster.party_id,
suppMaster.vendor_id,
suppMaster.segment1,
partySite.party_site_id,
partySite.party_site_name,
parties.party_name,
supplierSite.vendor_site_id,
supplierSite.vendor_site_code,
partySite.attribute2 GST_STATUS
,
partySite.attribute4,
partySite.attribute3
STATE_JURISDICTION_CODE,
partySite.attribute9
CLASSIFICATION,
partySite.attribute7 GSTN_Reg,
partySite.attribute10
ATTRIBUTE10,
partySite.attribute11 VAT,
partySite.attribute12 CST,
partySite.attribute13 TIN,
partySite.attribute14
ATTRIBUTE14,
partySite.attribute15 EMAIL,
partySite.attribute16
ATTRIBUTE16,
(SELECT operatingUnit.name
FROM poz_site_assignments_all_m assALL,
hr_operating_units operatingUnit
WHERE suppMaster.party_id = partySite.party_id
AND partySite.party_site_id = supplierSite.party_site_id
AND supplierSite.vendor_site_id = assALL.vendor_site_id
AND assALL.bu_id = operatingUnit.organization_id)
Operating_Unit,
addressDetail.address1,
addressDetail.address2,
addressDetail.address3,
addressDetail.address4,
addressDetail.city,
addressDetail.state,
addressDetail.postal_code,
addressDetail.province,
addressDetail.country,
(SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = addressDetail.country)
CountryName,
''
"_SITE_USE_TYPE_",
(SELECT loc.location_name
FROM poz_site_assignments_all_m assALL,
hr_locations_all loc
WHERE assALL.vendor_site_id = supplierSite.vendor_site_id
AND assALL.ship_to_location_id = loc.location_id)
SHIP_TO_LOCATION_ID,
(SELECT loc1.location_name
FROM poz_site_assignments_all_m assALL,
hr_locations_all loc1
WHERE assALL.vendor_site_id = supplierSite.vendor_site_id
AND assALL.bill_to_location_id = loc1.location_id)
BILL_TO_LOCATION_ID
/*,(SELECT APPOZ1.FREIGHT_TERMS_LOOKUP_CODE
FROM AP_POZ_SITE_ADDR_ASSIGNMENT_V APPOZ1
WHERE APPOZ1.VENDOR_SITE_ID = supplierSite.VENDOR_SITE_ID) FREIGHT_TERMS*/
,
supplierSite.freight_terms_lookup_code
FREIGHT_TERMS,
(SELECT APPOZ2.fob_lookup_code
FROM ap_poz_site_addr_assignment_v APPOZ2
WHERE APPOZ2.vendor_site_id = supplierSite.vendor_site_id)
FOB_LOOKUP_CODE,
(SELECT ( GCC1.segment1
||'.'
||GCC1.segment2
||'.'
||GCC1.segment3
||'.'
||GCC1.segment4
||'.'
||GCC1.segment5
||'.'
||GCC1.segment6
||'.'
||GCC1.segment7
||'.'
||GCC1.segment8
||'.'
||GCC1.segment9 )
FROM poz_site_assignments_all_m billpayable,
gl_code_combinations GCC1
WHERE supplierSite.vendor_site_id = billpayable.vendor_site_id
AND billpayable.accts_pay_code_combination_id =
GCC1.code_combination_id
)
ACCT_PAY_CODE_COMBINATION_ID,
(SELECT ( GCC1.segment1
||'.'
||GCC1.segment2
||'.'
||GCC1.segment3
||'.'
||GCC1.segment4
||'.'
||GCC1.segment5
||'.'
||GCC1.segment6
||'.'
||GCC1.segment7
||'.'
||GCC1.segment8
||'.'
||GCC1.segment9 )
FROM poz_site_assignments_all_m billpayable,
gl_code_combinations GCC1
WHERE supplierSite.vendor_site_id = billpayable.vendor_site_id
AND billpayable.prepay_code_combination_id =
GCC1.code_combination_id)
PREPAY_CODE_COMBINATION_ID,
(SELECT term.name
FROM ap_terms_tl term
WHERE term.term_id = supplierSite.terms_id) TERMS_ID,
supplierSite.payment_currency_code,
supplierSite.hold_all_payments_flag,
supplierSite.hold_reason,
supplierSite.tax_reporting_site_flag
TAX_REPORTING_SITE_FLAG,
supplierSite.match_option,
''
"_TOLERANCE_NAME_",
(SELECT APPOZ2.allow_awt_flag
FROM ap_poz_site_addr_assignment_v APPOZ2
WHERE APPOZ2.vendor_site_id = supplierSite.vendor_site_id)
USE_WITHHOLDING_TAX
/*,(SELECT psp.income_tax_id
FROM poz_suppliers_pii psp
WHERE psp.vendor_id = suppMaster.vendor_id) PAN_NUMBER*/
,
partySite.attribute18 PAN_NUMBER
,
partySite.attribute17
GSTIN_NO,
partySite.attribute8
TDS_SECTION,
supplierSite.purchasing_site_flag,
supplierSite.pcard_site_flag,
supplierSite.pay_site_flag,
supplierSite.primary_pay_site_flag,
supplierSite.phone_country_code
PHONE_COUNTRY_CODE,
supplierSite.area_code
PHONE_AREA_CODE,
supplierSite.phone
PHONE_NUMBER,
supplierSite.phone_extension
PHONE_EXTENSION,
supplierSite.fax_country_code
FAX_COUNTRY_CODE,
supplierSite.fax FAX,
supplierSite.fax_area_code
FAX_AREA_CODE,
addressDetail.addr_element_attribute2,
To_char(supplierSite.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
INACTIVE_DATE,
supplierSite.rfq_only_site_flag
SOURCING_ONLY,
supplierSite.customer_num
CUSTOMER_NUM,
supplierSite.supplier_notif_method
Communication_Method,
supplierSite.email_address
EMAIL_ADDRESS,
supplierSite.hold_flag HOLD_FLAG,
supplierSite.purchasing_hold_reason
PURCHASING_HOLD_REASON,
supplierSite.pay_on_code
PAY_ON_RECEIPT,
supplierSite.pay_on_use_flag PAY_ON_USE
,
supplierSite.po_ack_reqd_days
PO_ACK_REQD_DAYS,
supplierSite.pay_on_receipt_summary_code
Invoice_Summary_Level,
supplierSite.gapless_inv_num_flag
GAPLESS_INV_NUMBERING,
supplierSite.selling_company_identifier
Selling_Company_Identifier,
supplierSite.create_debit_memo_flag
CREATE_DEBIT_MEMO_FROM_RETURN,
supplierSite.enforce_ship_to_location_code
Ship_To_Exception_Action,
supplierSite.receiving_routing_id
Receipt_Routing,
supplierSite.qty_rcv_tolerance
Over_Receipt_Tolerance,
supplierSite.qty_rcv_exception_code
Over_Receipt_Action,
supplierSite.days_early_receipt_allowed
Early_Receipt_Tolerance_Days,
supplierSite.days_late_receipt_allowed
Late_Receipt_Tolerance_Days,
supplierSite.receipt_days_exception_code
Receipt_Date_Exception,
supplierSite.payment_priority
Payment_Priority,
supplierSite.pay_group_lookup_code Pay_Group,
supplierSite.hold_unmatched_invoices_flag
Hold_Unmatched_Invoices,
supplierSite.hold_future_payments_flag
Hold_Unvalidated_Invoices,
supplierSite.hold_by
Payment_Hold_By,
To_char(supplierSite.hold_date, 'DD-MM-YYYY HH24:MI:SS')
Payment_Hold_Date,
supplierSite.bank_charge_deduction_type
Bank_Charge_Deduction_Type,
supplierSite.always_take_disc_flag
Always_Take_Discount,
supplierSite.exclude_freight_from_discount
Exclude_Freight_From_Discoun,
supplierSite.exclude_tax_from_discount
Exclude_Tax_From_Discount,
supplierSite.auto_calculate_interest_flag
Create_Interest_Invoices,
supplierSite.attribute_category
Attribute_Category,
supplierSite.pay_group_lookup_code,
(SELECT ( GCC1.segment1
||'.'
||GCC1.segment2
||'.'
||GCC1.segment3
||'.'
||GCC1.segment4
||'.'
||GCC1.segment5
||'.'
||GCC1.segment6
||'.'
||GCC1.segment7
||'.'
||GCC1.segment8
||'.'
||GCC1.segment9 )
FROM poz_site_assignments_all_m billpayable,
gl_code_combinations GCC1
WHERE supplierSite.vendor_site_id = billpayable.vendor_site_id
AND billpayable.accts_pay_code_combination_id =
GCC1.code_combination_id
)
LIABILITY_DISTRIBUTION,
(SELECT ( GCC1.segment1
||'.'
||GCC1.segment2
||'.'
||GCC1.segment3
||'.'
||GCC1.segment4
||'.'
||GCC1.segment5
||'.'
||GCC1.segment6
||'.'
||GCC1.segment7
||'.'
||GCC1.segment8
||'.'
||GCC1.segment9 )
FROM poz_site_assignments_all_m billpayable,
gl_code_combinations GCC1
WHERE supplierSite.vendor_site_id = billpayable.vendor_site_id
AND billpayable.future_dated_payment_ccid =
GCC1.code_combination_id)
BILLS_PAYABLE_DISTRIBUTION,
(SELECT default_relationship_flag
FROM poz_sup_thirdparty_payment_rel
WHERE vendor_site_id = supplierSite.vendor_site_id)
Default_Relationship_Flag,
supplierSite.invoice_amount_limit
INVOICE_AMOUNT_LIMIT,
supplierSite.invoice_currency_code
INVOICE_CURRENCY,
supplierSite.terms_date_basis
TERMS_DATE_BASIS,
supplierSite.pay_date_basis_lookup_code
PAY_DATE_BASIS,
supplierSite.qty_rcv_tolerance
QTY_TOLERANCE,
supplierSite.exclude_freight_from_discount
EXCLUDE_FREIGHT_FROM_DISCOUNT,
supplierSite.always_take_disc_flag
ALWAYS_TAKE_DISC,
(SELECT assALL.awt_group_id
FROM poz_site_assignments_all_m assALL
WHERE suppMaster.party_id = partySite.party_id
AND partySite.party_site_id = supplierSite.party_site_id
AND supplierSite.vendor_site_id = assALL.vendor_site_id)
WITHHOLDING_TAX_GROUP,
(SELECT distSet.distribution_set_name
FROM poz_site_assignments_all_m assALL,
ap_distribution_sets_all distSet
WHERE suppMaster.party_id = partySite.party_id
AND partySite.party_site_id = supplierSite.party_site_id
AND supplierSite.vendor_site_id = assALL.vendor_site_id
AND assALL.distribution_set_id = distSet.distribution_set_id)
DISTRIBUTION_SET,
(SELECT location_language
FROM poz_supplier_address_v
WHERE suppMaster.party_id = partySite.party_id
AND partySite.party_site_id = party_site_id
AND ROWNUM = 1)
LOCATION_LANGUAGE,
partySite.attribute1,
partySite.attribute5,
partySite.attribute17
GLOBAL_ATTRIBUTE2,
partySite.attribute18
GLOBAL_ATTRIBUTE3,
partySite.attribute19
GLOBAL_ATTRIBUTE4,
partySite.attribute20
GLOBAL_ATTRIBUTE5,
To_char(partySite.attribute_date1, 'DD-MM-YYYY HH24:MI:SS')
GLOBAL_ATTRIBUTE6,
To_char(partySite.attribute_date2, 'DD-MM-YYYY HH24:MI:SS')
GLOBAL_ATTRIBUTE7,
To_char(partySite.attribute_date3, 'DD-MM-YYYY HH24:MI:SS')
GLOBAL_ATTRIBUTE8,
To_char(partySite.end_date_active, 'DD-MM-YYYY HH24:MI:SS')
ADDRESS_INACTIVE_DATE,
partySite.status
ADDRESS_STATUS,
supplierSite.attribute1
TRANSPORTER,
Nvl((SELECT Listagg(
Concat(Concat(suppMaster.vendor_id, '_'), Concat(file_name,
Concat('#', fdt.description))), ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd
WHERE fad.document_id = fdt.document_id
AND fad.pk1_value = To_char(supplierSite.vendor_site_id)
AND fad.entity_name = 'POZ_SUPPLIER_SITES_ALL_M'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'), 'XXX')
Site_AttachmentsFileName,
Nvl((SELECT Listagg(fdc.user_name, ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd,
fnd_document_categories_vl fdc
WHERE fad.document_id = fdt.document_id
AND pk1_value = To_char(supplierSite.vendor_site_id)
AND entity_name = 'POZ_SUPPLIER_SITES_ALL_M'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'
AND fad.category_name = fdc.category_name), 'XXX')
SITESCATEGORY,
(SELECT address_purpose_ordering
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_ORDERING,
(SELECT address_purpose_remit_to
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_REMITTO,
(SELECT address_purpose_rfq_or_bidding
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_RFQ_BIDDING,
(SELECT status
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_STATUS,
(SELECT email_address
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_EMAIL,
(SELECT phone_area_code
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_PHONE_AREACODE,
(SELECT phone_number
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_PHONE,
(SELECT fax_phone_area_code
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_FAX_AREACODE,
(SELECT fax_phone_number
FROM poz_supplier_address_v
WHERE partySite.party_site_id = party_site_id
AND ROWNUM = 1)
ADDRESSPUR_FAX
FROM poz_suppliers suppMaster,
hz_party_sites partySite,
poz_supplier_sites_all_m supplierSite,
hz_parties parties,
hz_locations addressDetail
WHERE suppMaster.party_id = parties.party_id
AND partySite.location_id = addressDetail.location_id
AND ( partySite.attribute2 = 'ACTIVE'
OR partySite.attribute2 = 'GSTIN NOT AVAILABLE' )
AND ( partySite.attribute4 = 'ACTIVE'
OR partySite.attribute4 = 'PAN NOT AVAILABLE' )
AND ( partySite.attribute17 IS NOT NULL
AND partySite.attribute18 IS NOT NULL )
AND partySite.party_site_id = supplierSite.party_site_id
AND suppMaster.party_id = partySite.party_id
---------------------------------
SupplierContactData
SELECT 'SupplierContact',
suppMaster.segment1 SUPPLIER_NUMBER
,
suppMaster.party_id
PARTY_ID,
partySite.party_site_id PARTY_SITE_ID,
suppMaster.vendor_id VENDOR_ID,
supplierSite.vendor_site_id VENDOR_SITE_ID,
suppliersite.vendor_site_code
VENDOR_SITE_CODE,
parties.person_title NAME_PREFIX,
parties.person_first_name
PERSON_FIRST_NAME,
parties.person_middle_name
PERSON_MIDDLE_NAME,
parties.person_last_name
PERSON_LAST_NAME,
orgContact.job_title JOB_TITLE,
parties.phone_country_code
PHONE_COUNTRY_CODE,
parties.phone_area_code PHONE_AREA_CODE
,
parties.phone_number PHONE,
parties.phone_extension PHONE_EXTENSION
,
parties.mobile_country_code
MOBILE_COUNTRY_CODE,
parties.mobile_area_code
MOBILE_AREA_CODE,
parties.mobile_number MOBILE,
parties.email_address EMAIL,
parties.fax_number FAX,
parties.status STATUS,
(SELECT primary_flag
FROM hz_contact_points
WHERE suppContacts.per_party_id = owner_table_id
AND contact_point_type = 'EMAIL'
AND parties.relationship_id = relationship_id
AND rownum = 1)
Administrative_Contact,
(SELECT operatingUnit.NAME
FROM poz_site_assignments_all_m assALL,
hr_operating_units operatingUnit
WHERE suppMaster.party_id = partySite.party_id
AND partySite.party_site_id = supplierSite.party_site_id
AND supplierSite.vendor_site_id = assALL.vendor_site_id
AND assALL.bu_id = operatingUnit.organization_id) OPERATING_UNIT,
(SELECT Decode(user_guid, NULL, 'N',
'Y')
FROM per_users
WHERE parties.per_party_id = party_id)
HAS_USER_ACCOUNT,
CASE
WHEN Upper(parties.status) = 'ACTIVE' THEN
To_char(suppContacts.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
ELSE To_char(parties.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
END INACTIVE_DATE
FROM poz_all_supplier_contacts_v parties,
poz_supplier_contacts suppContacts,
poz_suppliers suppMaster,
hz_party_sites partySite,
poz_supplier_sites_all_m supplierSite,
hz_org_contacts orgContact
WHERE suppMaster.party_id = parties.sup_party_id
AND suppMaster.party_id = partySite.party_id
AND partySite.party_site_id = supplierSite.party_site_id
AND partySite.party_site_id = suppContacts.party_site_id
AND parties.per_party_id = suppContacts.per_party_id
AND suppContacts.org_contact_id = orgContact.org_contact_id
-----------------------------
Supplier Payment
SELECT 'SupplierPayments',
suppMaster.party_id,
suppMaster.segment1,
externalPayeeBank.party_site_id
PARTY_SITE_ID,
(SELECT vendor_site_code
FROM poz_supplier_sites_all_m
WHERE externalPayeeBank.supplier_site_id = vendor_site_id)
VENDOR_SITE_CODE,
Decode (externalPayeeBank.party_site_id, NULL, NULL,
(SELECT DISTINCT vendor_site_code
FROM poz_supplier_sites_all_m
WHERE
party_site_id = externalPayeeBank.party_site_id
AND rownum = 1
AND externalPayeeBank.supplier_site_id IS NULL))
PARTY_SITE_CODE
/*,(SELECT operatingUnit.NAME
FROM POZ_SITE_ASSIGNMENTS_ALL_M assALL
,HR_OPERATING_UNITS operatingUnit
,HZ_PARTY_SITES partySite
,POZ_SUPPLIER_SITES_ALL_M supplierSite
WHERE suppMaster.PARTY_ID = partySite.PARTY_ID
AND partySite.PARTY_SITE_ID = supplierSite.PARTY_SITE_ID
AND supplierSite.VENDOR_SITE_ID = assALL.VENDOR_SITE_ID
AND assALL.BU_ID = operatingUnit.ORGANIZATION_ID ) OPERATING_UNIT*/
,
(SELECT NAME
FROM hr_operating_units operUnit,
poz_supplier_sites_all_m suppSites
WHERE externalPayeeBank.supplier_site_id = suppSites.vendor_site_id
AND suppSites.prc_bu_id = organization_id)
OPERATING_UNIT,
externalPayeeBank.ext_payee_id
PAYEE_IDENTIFIER,
externalPayeeBank.delivery_channel_code
DELIVERY_CHANNEL_CODE,
externalPayeeBank.bank_instruction1_code
BANK_INSTRUCTION1_CODE,
externalPayeeBank.bank_instruction2_code
BANK_INSTRUCTION2_CODE,
externalPayeeBank.bank_instruction_details
BANK_INSTRUCTION_DETAILS,
externalPayeeBank.settlement_priority
SETTLEMENT_PRIORITY,
externalPayeeBank.payment_text_message1
PAYMENT_TEXT_MESSAGE1,
externalPayeeBank.payment_text_message2
PAYMENT_TEXT_MESSAGE2,
externalPayeeBank.payment_text_message3
PAYMENT_TEXT_MESSAGE3,
externalPayeeBank.service_level_code
SERVICE_LEVEL_CODE,
externalPayeeBank.exclusive_payment_flag
PAY_EACH_DOCUMENT_ALONE,
externalPayeeBank.bank_charge_bearer
BANK_CHARGE_BEARER,
externalPayeeBank.payment_reason_code
PAYMENT_REASON_CODE,
externalPayeeBank.payment_reason_comments
PAYMENT_REASON_COMMENTS,
externalPayeeBank.remit_advice_delivery_method
DELIVERY_METHOD,
externalPayeeBank.remit_advice_email
REMITTANCE_MAIL,
externalPayeeBank.remit_advice_fax
REMITTANCE_FAX,
(SELECT payment_method_code
FROM iby_ext_party_pmt_mthds
WHERE externalPayeeBank.ext_payee_id = ext_pmt_party_id
AND primary_flag = 'Y')
PAYMENT_METHOD_CODE,
To_char(externalPayeeBank.end_date, 'DD-MM-YYYY HH24:MI:SS') END_DATE
FROM poz_suppliers suppMaster,
iby_external_payees_all externalPayeeBank
WHERE suppMaster.party_id = externalPayeeBank.payee_party_id
-------------------------------------
SupplierProductServices
SELECT 'SupplierProductServices',
suppMaster.party_id,
suppMaster.segment1,
(SELECT UNIQUE(category_name)
FROM poz_prod_serv_category_v
WHERE suppProdServ.category_id = category_id
AND category_name IS NOT NULL)Category_Name,
suppProdServ.status Status
FROM poz_suppliers suppMaster,
poz_sup_products_services suppProdServ
WHERE suppMaster.vendor_id = suppProdServ.vendor_id
----------------------------
SupplierBusinessClassification
SELECT 'SupplierBusinessClassification',
suppMaster.segment1,
suppMaster.party_id,
busClass.lookup_code
Classification_Lookup_Code,
busClass.ext_attr_1
SubClassification,
(SELECT name
FROM poz_certifying_agencies
WHERE busClass.certifying_agency_id = agency_id)
Certifying_Agency,
busClass.certificate_number
Certificate_Number,
To_char(busClass.start_date, 'DD-MM-YYYY HH24:MI:SS') Start_Date
,
To_char(busClass.expiration_date, 'DD-MM-YYYY HH24:MI:SS')
Expiration_Date
/*,To_char(busClass.confirmed_on, 'DD-MM-YYYY HH24:MI:SS')
Confirmed_On*/
,
busClass.notes Notes,
busClass.status Status,
busClass.classification_id
CLASSIFICATION_ID,
Nvl((SELECT Listagg(
Concat(Concat(suppMaster.vendor_id, '_'), Concat(file_name,
Concat('#', fdt.description))), ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd
WHERE fad.document_id = fdt.document_id
AND fad.pk1_value = To_char(busClass.classification_id)
AND fad.entity_name = 'POZ_BUS_CLASSIFICATIONS'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'), 'XXX')
BUSCLASS_ATTACHMENTSFILENAME,
Nvl((SELECT Listagg(fdc.user_name, ';')
within GROUP(ORDER BY url) AS the_list
FROM fnd_documents_tl fdt,
fnd_attached_documents fad,
fnd_documents fd,
fnd_document_categories_vl fdc
WHERE fad.document_id = fdt.document_id
AND pk1_value = To_char(busClass.classification_id)
AND entity_name = 'POZ_BUS_CLASSIFICATIONS'
AND fd.document_id = fdt.document_id
AND Upper(fd.datatype_code) = 'FILE'
AND fad.category_name = fdc.category_name), 'XXX')
BUSCLASSCATEGORY
FROM poz_suppliers suppMaster,
poz_bus_classifications busClass
WHERE suppMaster.party_id = busClass.party_id
AND busClass.deleted = 'N'
-------------------------------------
SupplierBanks
SELECT 'SupplierBank',
suppMaster.party_id,
suppMaster.segment1
SUPPLIER_NUMBER,
externalPayeeBank.party_site_id
PARTY_SITE_ID,
(SELECT vendor_site_code
FROM poz_supplier_sites_all_m
WHERE externalPayeeBank.supplier_site_id = vendor_site_id)
VENDOR_SITE_CODE,
Decode (externalPayeeBank.party_site_id, NULL, NULL,
(SELECT DISTINCT vendor_site_code
FROM poz_supplier_sites_all_m
WHERE
party_site_id = externalPayeeBank.party_site_id
AND rownum = 1
AND externalPayeeBank.supplier_site_id IS NULL))
PARTY_SITE_CODE,
externalPayeeBank.ext_payee_id
PAYEE_IDENTIFIER,
bankAccount.country_code COUNTRY_CODE
,
banks.bank_name
BANK_NAME,
branch.bank_branch_name BRANCH_NAME,
branch.branch_number
BRANCH_NUMBER,
(SELECT NAME
FROM hr_operating_units operUnit,
poz_supplier_sites_all_m suppSites
WHERE externalPayeeBank.supplier_site_id = suppSites.vendor_site_id
AND suppSites.prc_bu_id = organization_id)
BUSINESS_UNIT_NAME,
branch.eft_swift_code
/*,account.MASKED_IBAN*/
,
To_char(bankAccount.start_date, 'DD-MM-YYYY HH24:MI:SS')
ACCOUNT_START_DATE,
To_char(bankAccount.end_date, 'DD-MM-YYYY HH24:MI:SS')
ACCOUNT_END_DATE,
bankAccount.bank_account_name ACCOUNT_NAME
,
bankAccount.bank_account_name_alt
ACCOUNT_ALTERNATE_NAME,
bankAccount.bank_account_num ACCOUNT_NUM,
bankAccount.currency_code
CURRENCY_CODE,
bankAccount.iban IBAN,
bankAccount.bank_account_type
ACCOUNT_TYPE_CODE,
bankAccount.ext_bank_account_id
Payee_Bank_Account_Identifier,
bankAccount.description
Account_Description,
pmtInstrBank.instrument_payment_use_id
PAYEE_BANK_ACCOUNT_ASSI_ID,
pmtInstrBank.primary_flag PRIMARY_FLAG
,
To_char(pmtInstrBank.start_date, 'DD-MM-YYYY HH24:MI:SS')
ACCOUNT_ASSIGNMENT_START_DATE,
To_char(pmtInstrBank.end_date, 'DD-MM-YYYY HH24:MI:SS')
ACCOUNT_ASSIGNMENT_END_DATE,
bankAccount.agency_location_code
AGENCY_LOCATION_CODE
FROM poz_suppliers suppMaster,
iby_external_payees_all externalPayeeBank,
iby_pmt_instr_uses_all pmtInstrBank,
iby_ext_bank_accounts bankAccount,
ce_index_banks banks,
ce_index_bank_branches branch
WHERE externalPayeeBank.ext_payee_id = pmtInstrBank.ext_pmt_party_id
AND pmtInstrBank.instrument_id = bankAccount.ext_bank_account_id
AND bankAccount.bank_id = banks.bank_party_id
AND bankAccount.branch_id = branch.branch_party_id
----------------------------------------------------
SupplierContacts
SELECT 'SupplierContacts',
suppMaster.segment1
SUPPLIER_NUMBER,
suppMaster.party_id
PARTY_ID,
suppMaster.vendor_id
VENDOR_ID,
parties.person_title
NAME_PREFIX,
parties.person_first_name
PERSON_FIRST_NAME,
parties.person_middle_name
PERSON_MIDDLE_NAME,
parties.person_last_name
PERSON_LAST_NAME,
(SELECT orgContact.job_title
FROM hz_org_contacts orgContact
WHERE parties.relationship_id = orgContact.party_relationship_id)
JOB_TITLE,
parties.phone_country_code
PHONE_COUNTRY_CODE,
parties.phone_area_code
PHONE_AREA_CODE,
parties.phone_number PHONE
,
parties.phone_extension
PHONE_EXTENSION,
parties.mobile_country_code
MOBILE_COUNTRY_CODE,
parties.mobile_area_code
MOBILE_AREA_CODE,
parties.mobile_number
MOBILE,
parties.email_address EMAIL
,
parties.fax_number
FAX,
parties.status
STATUS,
(SELECT contcPoints.primary_flag
FROM hz_contact_points contcPoints
WHERE parties.per_party_id = contcPoints.owner_table_id
AND contcPoints.contact_point_type = 'EMAIL'
AND parties.relationship_id = relationship_id)
Administrative_Contact,
(SELECT Decode(user_guid, NULL, 'N',
'Y')
FROM per_users
WHERE parties.per_party_id = party_id)
HAS_USER_ACCOUNT,
'Y'
VENDOR_CONTACT_FLAG,
To_char(parties.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
INACTIVE_DATE
FROM poz_suppliers suppMaster,
poz_all_supplier_contacts_v parties
WHERE suppMaster.party_id = parties.sup_party_id
AND parties.per_party_id NOT IN (SELECT per_party_id
FROM poz_supplier_contacts
WHERE party_id = parties.sup_party_id)
Subscribe to:
Posts (Atom)