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)
No comments:
Post a Comment