FROM (SELECT hrou.name operating_unit,
pvsa.attribute12 supplier_number_legacy,
pv.vendor_id supplier_id,
pv.segment1 supplier_number,
pv.vendor_name supplier_name,
pv.vendor_name_alt alternate_name,
pv.vendor_type_lookup_code supplier_type,
pv.one_time_flag one_time_identifier,
atth.name payment_terms,
pv.pay_group_lookup_code pay_group,
pv.payment_priority payment_priority,
pv.terms_date_basis terms_date_basis,
pv.pay_date_basis_lookup_code pay_date_basis,
pv.payment_method_lookup_code payment_method,
null allow_calc_level_override_h,
DECODE (pv.ap_tax_rounding_rule, 'N', 'Nearest') Rounding_Rule,
null dist_amount_inc_tax_h,
null ship_to_location_code_h,
null bill_to_location_code_h,
null enforce_ship_to_location,
null receipt_routing,
null match_approval_level,
null qty_received_tolerance,
null qty_received_exception,
null days_early_receipt_allowed,
null days_late_receipt_allowed,
null receipt_date_exception,
null allow_substitute_receipts,
null allow_unordered_receipts,
pvsa.vendor_site_id,
pvsa.vendor_site_code site_name_to_be,
pvsa.vendor_site_code_alt alternate_site_name,
pvsa.pay_site_flag site_use_pay,
pvsa.purchasing_site_flag site_use_purchasing,
pvsa.address_line1 supplier_address_line_1,
pvsa.address_line2 supplier_address_line_2,
pvsa.address_line3 supplier_address_line_3,
pvsa.address_line4 supplier_address_line_4,
NULL ADDRESS_LINE_ALTERNATE,
pvsa.CITY Supplier_City,
pvsa.STATE Supplier_State,
pvsa.COUNTRY Supplier_Country,
pvsa.ZIP Supplier_ZIP,
NULL COUNTY,
pvsa.PROVINCE Supplier_PROVINCE,
NULL VOICE_AREA_CODE,
NULL VOICE_NUMBER,
pvsa.attribute4 Payment_Mode_DFF,
attl.NAME Payment_Terms_Site,
pvsa.payment_method_Lookup_code Payment_Method_Site,
pvsa.EXCLUSIVE_PAYMENT_FLAG Pay_Alone,
pvsa.INVOICE_CURRENCY_CODE Invoice_Currency_Code,
pvsa.PAYMENT_CURRENCY_CODE Payment_Currency_Code,
pvsa.PAY_GROUP_LOOKUP_CODE Pay_Group_Lookup_Code,
pvsa.PAYMENT_PRIORITY Payment_Priority_Site,
pvsa.TERMS_DATE_BASIS Terms_Date_basis_Site,
pvsa.PAY_DATE_BASIS_LOOKUP_CODE Pay_Date_Basis_Site,
pvsa.ALWAYS_TAKE_DISC_FLAG Always_Take_Discount,
(SELECT gl.segment4
|| '.'
|| gl.segment1
|| '.'
|| gl.segment2
|| '.'
|| gl.segment3
|| '.'
|| gl.segment5
|| '.'
|| gl.segment6
|| '.'
|| gl.segment7
FROM gl_code_combinations gl
WHERE code_combination_id =
pvsa.accts_pay_code_combination_id)
Liability_account,
(SELECT gl.segment4
|| '.'
|| gl.segment1
|| '.'
|| gl.segment2
|| '.'
|| gl.segment3
|| '.'
|| gl.segment5
|| '.'
|| gl.segment6
|| '.'
|| gl.segment7
FROM gl_code_combinations gl
WHERE code_combination_id = pvsa.prepay_code_combination_id)
Prepay_account,
NULL invoice_tax_auto_calc_level,
NULL allow_calc_level_override_l,
pvsa.ap_tax_rounding_rule Rounding_Rule_L,
NULL dist_amount_inc_tax_l,
hlals.location_code Ship_to_location_code_Site,
hlalb.location_code Bill_to_location_code_Site,
pvsa.pay_on_code Pay_on_Code,
pvsa.create_debit_memo_flag cd_memo_on_rts_trans,
NULL first_name_contact,
NULL last_name_contact,
NULL middle_initial_contact,
NULL telephone_area_code_contact,
NULL telephone_number_contact,
NULL title_contact,
pv.match_option match_option_h,
pv.attribute1 supplier_sec_class_dff,
pv.attribute2 supplier_buisiness_size,
pv.small_business_flag small_flag,
pvsa.primary_pay_site_flag site_use_pay_primary,
pvsa.rfq_only_site_flag site_use_rfq_only,
pvsa.match_option match_option_site,
pvsa.attribute11 supplier_ter_class_dff,
NULL error_flag,
pvsa.org_id org_id,
pv.employee_id employee_num,
NULL error_vendor,
NULL error_site,
pv.start_date_active,
pv.end_date_active,
pvsa.attribute6 ifsc_code,
pvsa.email_address,
pvsa.attribute5 rtgs,
pvsa.attribute6 eft_code
FROM ap_suppliers pv,
ap_supplier_sites_all pvsa,
hr_organization_units hrou,
ap_terms_tl atth,
ap_terms_tl attl,
hr_locations_all hlahb,
hr_locations_all hlahs,
hr_locations_all hlalb,
hr_locations_all hlals
WHERE pv.vendor_id = pvsa.vendor_id
AND pvsa.org_id = hrou.organization_id
AND atth.term_id(+) = pv.terms_id
AND attl.term_id(+) = pv.terms_id
AND pv.bill_to_location_id = hlahb.location_id(+)
AND pv.bill_to_location_id = hlahs.location_id(+)
AND pvsa.bill_to_location_id = hlalb.location_id(+)
AND pvsa.bill_to_location_id = hlals.location_id(+)
AND pvsa.org_id = :p_org_id
) a,
(SELECT DISTINCT
abu.vendor_id,
abu.vendor_site_id,
abb.bank_name supplier_bank_name,
abb.bank_branch_name supplier_bank_branch_name,
abb.address_line1
|| ' '
|| abb.address_line2
|| ' '
|| abb.address_line3
|| ' '
|| abb.city
|| ' '
|| abb.state
|| ' '
|| abb.zip
|| ' '
|| abb.country
supplier_bank_address,
aba.bank_account_name supplier_bank_account_name,
aba.bank_account_num supplier_benificiary_num,
aba.bank_account_type supplier_bank_account_type,
abu.start_date supplier_bank_start_date,
abu.end_date supplier_bank_end_date,
abu.primary_flag
FROM ap_bank_account_uses_all abu,
ap_bank_accounts_all aba,
ap_bank_branches abb
WHERE abu.external_bank_account_id = aba.bank_account_id
AND abb.bank_branch_id(+) = aba.bank_branch_id
AND aba.org_id = abu.org_id
AND aba.org_id = :p_org_id
) SUPPLIER_BANK_DET
WHERE a.supplier_id = supplier_bank_det.vendor_id(+)
AND a.vendor_site_id = supplier_bank_det.vendor_site_id(+)
No comments:
Post a Comment