[Home] [Help]
SELECT original_copy, cust_account_id, party_id, party_site_id,
cust_acct_site_id, account_number, account_name,
customer_party_name, customer_tax_reference, customer_tax_payer_id,
customer_tax_ref_number, rep_reg_number,receipt_method_name, payment_method_name,
customer_address, customer_city, customer_state, customer_county,
customer_postal_code, customer_address|| ' '|| customer_city|| ' '
|| customer_state|| ' '|| customer_county|| ' '
|| customer_postal_code customer_complete_address,
NVL(TRIM(BOTH ',' FROM customer_name_attr||','||customer_address_attr),'The Same') customer_attr_complete_address,
customer_name_attr, customer_address_attr,
rct_global_attribute3,rct_global_attribute4,rct_global_attribute5,
rct_global_attribute6,rct_global_attribute7,rct_global_attribute8,
rct_global_attribute9,rct_global_attribute10,rct_global_attribute11,
rct_global_attribute12,rct_global_attribute13,rct_global_attribute14,
rct_global_attribute15,transaction_id,
transaction_number, transaction_date, transaction_term_due_date,
transaction_currency_code, document_sequence_number, waybill_number,
transaction_type, shipping_bill, shipping_date, vendor_party_name,
vendor_address, vendor_city, vendor_state, vendor_county,
vendor_postal_code, vendor_address|| ' '|| vendor_city|| ' '
|| vendor_state|| ' '|| vendor_county|| ' '
|| vendor_postal_code vendor_complete_address,
vendor_tax_reference, sales_person_name, term_name, term_desc,
vendor_tax_reg_number, customer_tax_reg_number,
customer_tax_authority, user_name, line_total_amount,
tax_total_amount, ((tax_total_amount * 100) / line_total_amount) tax_rate,
VAT_tax_total_amount, ((VAT_tax_total_amount * 100) / line_total_amount) VAT_tax_rate,
excise_tax_total_amount, ((excise_tax_total_amount * 100) / line_total_amount) excise_tax_rate,
exempt_tax_total_amount, ((exempt_tax_total_amount * 100) / line_total_amount) exempt_tax_rate,
(line_total_amount + tax_total_amount) trx_total_amount,
INITCAP
(TO_CHAR (TO_DATE ((NVL(line_total_amount,1) + NVL(tax_total_amount,1)),
'J'
),
'JSP'
)
) trx_total_in_words,
TRIM(BOTH '-' FROM phone_number) phone_number, vendor_contact_name,
TRIM (BOTH ',' FROM customer_contact_name) customer_contact_name,
NULL null_value, chief_accountant_name, general_director_name,
vendor_tax_kpp, vendor_tax_payer_id,legal_entity_address
FROM (SELECT DECODE (rct.printing_pending,'Y', 'Original','Copy') original_copy,
hca.cust_account_id cust_account_id, hca.party_id party_id,
hcas.party_site_id party_site_id,
hcas.cust_acct_site_id cust_acct_site_id,
hca.account_number account_number,
hca.account_name account_name,
cust_hpar.party_name customer_party_name,
cust_hpar.tax_reference customer_tax_reference,
hop.jgzz_fiscal_code customer_tax_payer_id,
hop.tax_reference customer_tax_ref_number,
cust_zptp.rep_registration_number rep_reg_number,
arm.name receipt_method_name,
ifpc.payment_channel_name payment_method_name,
TRIM ( cust_hpar.address1|| ' '
|| cust_hpar.address2|| ' '
|| cust_hpar.address3|| ' '
|| cust_hpar.address4) customer_address,
cust_hpar.city customer_city,
cust_hpar.state customer_state,
cust_hpar.county customer_county,
cust_hpar.postal_code customer_postal_code,
rct.global_attribute1 customer_name_attr,
rct.global_attribute2 customer_address_attr,
rct.global_attribute3 rct_global_attribute3,
rct.global_attribute4 rct_global_attribute4,
rct.global_attribute5 rct_global_attribute5,
rct.global_attribute6 rct_global_attribute6,
rct.global_attribute7 rct_global_attribute7,
rct.global_attribute8 rct_global_attribute8,
rct.global_attribute9 rct_global_attribute9,
rct.global_attribute10 rct_global_attribute10,
rct.global_attribute11 rct_global_attribute11,
rct.global_attribute12 rct_global_attribute12,
rct.global_attribute13 rct_global_attribute13,
rct.global_attribute14 rct_global_attribute14,
rct.global_attribute15 rct_global_attribute15,
rct.customer_trx_id transaction_id,
rct.trx_number transaction_number,
rct.trx_date transaction_date,
rct.term_due_date transaction_term_due_date,
rct.invoice_currency_code transaction_currency_code,
rct.doc_sequence_value document_sequence_number,
rct.waybill_number waybill_number,
rctt.NAME transaction_type, rct.attribute15 shipping_bill,
rct.ship_date_actual shipping_date,
vndr_hpar.party_name vendor_party_name,
vndr_hpar.address1 vendor_address,
vndr_hpar.city vendor_city, vndr_hpar.state vendor_state,
vndr_hpar.county vendor_county,
vndr_hpar.postal_code vendor_postal_code,
vndr_hpar.tax_reference vendor_tax_reference,
rs.NAME sales_person_name, rt.NAME term_name,
rt.description term_desc,
vndr_zr.registration_number vendor_tax_reg_number,
cust_zr.registration_number customer_tax_reg_number,
tax_auth_hpar.party_name customer_tax_authority,
fnd_global.user_name user_name,
NVL((SELECT SUM (rctl.extended_amount)
FROM ra_customer_trx_lines_all rctl
WHERE rctl.customer_trx_id = rct.customer_trx_id
AND rctl.line_type = 'LINE'),0) line_total_amount,
NVL((SELECT SUM (rctl.extended_amount)
FROM ra_customer_trx_lines_all rctl
WHERE rctl.customer_trx_id = rct.customer_trx_id
AND rctl.line_type = 'TAX'),0) tax_total_amount,
-- NVL ((SELECT SUM (rctl.tax_rate)
-- FROM ra_customer_trx_lines_all rctl
-- WHERE rctl.customer_trx_id = rct.customer_trx_id
-- AND rctl.line_type = 'TAX'),0) tax_rate,
NVL((SELECT SUM (rctl.extended_amount)
FROM ra_customer_trx_lines_all rctl
,zx_lines zl
WHERE rctl.customer_trx_id = rct.customer_trx_id
AND rctl.line_type = 'TAX'
AND rctl.tax_line_id = zl.tax_line_id
AND zl.tax_type_code='VAT'),0) VAT_tax_total_amount,
-- NVL ((SELECT SUM (rctl.tax_rate)
-- FROM ra_customer_trx_lines_all rctl
-- ,zx_lines zl
-- WHERE rctl.customer_trx_id = rct.customer_trx_id
-- AND rctl.line_type = 'TAX'
-- AND rctl.tax_line_id = zl.tax_line_id
-- AND zl.tax_type_code='VAT'),0) VAT_tax_rate,
NVL((SELECT SUM (rctl.extended_amount)
FROM ra_customer_trx_lines_all rctl
,zx_lines zl
WHERE rctl.customer_trx_id = rct.customer_trx_id
AND rctl.line_type = 'TAX'
AND rctl.tax_line_id = zl.tax_line_id
AND zl.tax_type_code='EXCISE'),0) excise_tax_total_amount,
-- NVL ((SELECT SUM (rctl.tax_rate)
-- FROM ra_customer_trx_lines_all rctl
-- ,zx_lines zl
-- WHERE rctl.customer_trx_id = rct.customer_trx_id
-- AND rctl.line_type = 'TAX'
-- AND rctl.tax_line_id = zl.tax_line_id
-- AND zl.tax_type_code='EXCISE'),0) excise_tax_rate,
NVL((SELECT SUM (rctl.extended_amount)
FROM ra_customer_trx_lines_all rctl
,zx_lines zl
WHERE rctl.customer_trx_id = rct.customer_trx_id
AND rctl.line_type = 'TAX'
AND rctl.tax_line_id = zl.tax_line_id
AND zl.tax_type_code='Exempt Tax'),0) exempt_tax_total_amount,
-- NVL ((SELECT SUM (rctl.tax_rate)
-- FROM ra_customer_trx_lines_all rctl
-- ,zx_lines zl
-- WHERE rctl.customer_trx_id = rct.customer_trx_id
-- AND rctl.line_type = 'TAX'
-- AND rctl.tax_line_id = zl.tax_line_id
-- AND zl.tax_type_code='Exempt Tax'),0) exempt_tax_rate,
cust_hpar.primary_phone_country_code|| '-'
|| cust_hpar.primary_phone_area_code|| '-'
|| cust_hpar.primary_phone_number phone_number,
NVL(vndr_hpar.person_title,vndr_hpar.person_pre_name_adjunct)|| ' '
|| vndr_hpar.person_first_name|| ' '
|| vndr_hpar.person_middle_name|| ' '
|| vndr_hpar.person_last_name vendor_contact_name,
SUBSTRB(cont_hpar.person_last_name,1,50)|| ','|| ' '
|| SUBSTRB (cont_hpar.person_first_name, 1, 50) customer_contact_name,
(SELECT per_hpar.party_name
FROM hz_parties per_hpar,
hz_relationships vndr_hr
WHERE vndr_hpar.party_id = vndr_hr.object_id
AND vndr_hr.relationship_code = 'CONTACT_OF'
AND vndr_hr.relationship_type = 'CONTACT'
AND vndr_hr.directional_flag = 'F'
AND vndr_hr.subject_table_name = 'HZ_PARTIES'
AND vndr_hr.object_table_name = 'HZ_PARTIES'
AND vndr_hr.subject_type = 'PERSON'
AND TRUNC (NVL (vndr_hr.end_date, SYSDATE)) > TRUNC (SYSDATE)
AND vndr_hr.subject_id = per_hpar.party_id
AND xle_contact_grp.concat_contact_roles(vndr_hr.subject_id,vndr_hr.object_id) = 'Chief Accountant') chief_accountant_name,
(SELECT per_hpar.party_name
FROM hz_parties per_hpar,
hz_relationships vndr_hr
WHERE vndr_hpar.party_id = vndr_hr.object_id
AND vndr_hr.relationship_code = 'CONTACT_OF'
AND vndr_hr.relationship_type = 'CONTACT'
AND vndr_hr.directional_flag = 'F'
AND vndr_hr.subject_table_name = 'HZ_PARTIES'
AND vndr_hr.object_table_name = 'HZ_PARTIES'
AND vndr_hr.subject_type = 'PERSON'
AND TRUNC (NVL (vndr_hr.end_date, SYSDATE)) > TRUNC (SYSDATE)
AND vndr_hr.subject_id = per_hpar.party_id
AND xle_contact_grp.concat_contact_roles(vndr_hr.subject_id,vndr_hr.object_id) ='General Director') general_director_name,
(SELECT registrationeo.registration_number
FROM xle_registrations registrationeo,
xle_jurisdictions_vl jurisdictions
WHERE jurisdictions.jurisdiction_id =registrationeo.jurisdiction_id
AND registrationeo.source_table = 'XLE_ETB_PROFILES'
AND registrationeo.source_id = xev.establishment_id
AND NVL (jurisdictions.registration_code_etb, 'RN') ='CRR')vendor_tax_kpp,
(SELECT registrationeo.registration_number
FROM xle_registrations registrationeo,
xle_jurisdictions_vl jurisdictions
WHERE jurisdictions.jurisdiction_id =registrationeo.jurisdiction_id
AND registrationeo.source_table = 'XLE_ENTITY_PROFILES'
AND registrationeo.source_id = xep.legal_entity_id
AND NVL (jurisdictions.registration_code_le, 'RN') ='INT')vendor_tax_payer_id,
(SELECT LTRIM ( loc.address_line_1|| ' '
|| loc.address_line_2|| ' '
|| loc.address_line_3|| ' '
|| loc.town_or_city|| ' '
|| loc.region_2|| ' '
|| loc.postal_code) address
FROM xle_registrations xlr,
hr_locations loc
WHERE xlr.source_table = 'XLE_ENTITY_PROFILES'
AND xlr.source_id = xep.legal_entity_id
AND xlr.location_id = loc.location_id(+)) legal_entity_address
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all rctt,
ra_terms rt,
ra_salesreps_all rs,
hz_cust_accounts hca,
hz_parties cust_hpar,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
xle_entity_profiles xep,
hz_parties vndr_hpar,
xle_etb_profiles xev,
hz_parties estab_hpar,
zx_party_tax_profile vndr_zptp,
zx_registrations vndr_zr,
zx_party_tax_profile cust_zptp,
zx_registrations cust_zr,
hz_locations hl,
fnd_territories_tl ftt,
zx_party_tax_profile tax_auth_zptp,
xle_legalauth_v xlv,
hz_parties tax_auth_hpar,
hz_cust_account_roles cont_hcar,
hz_relationships cont_hr,
hz_parties cont_hpar,
ar_receipt_methods arm,
iby_fndcpt_pmt_chnnls_vl ifpc,
hz_organization_profiles hop
WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.term_id = rt.term_id(+)
AND rct.primary_salesrep_id = rs.salesrep_id(+)
AND rct.org_id = rs.org_id(+)
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = cust_hpar.party_id
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND rct.legal_entity_id = xep.legal_entity_id
AND xep.party_id = vndr_hpar.party_id
AND xev.legal_entity_id = xep.legal_entity_id
AND xev.party_id = estab_hpar.party_id
AND xev.main_establishment_flag = 'Y'
AND estab_hpar.party_id = vndr_zptp.party_id
AND vndr_zptp.party_tax_profile_id = vndr_zr.party_tax_profile_id(+)
AND vndr_zr.default_registration_flag(+) = 'Y'
AND cust_hpar.party_id = cust_zptp.party_id
AND cust_zptp.party_tax_profile_id = cust_zr.party_tax_profile_id(+)
AND cust_zr.default_registration_flag(+) = 'Y'
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code(+)
AND NVL (ftt.LANGUAGE, USERENV ('LANG')) = USERENV ('LANG')
AND vndr_zr.tax_authority_id = tax_auth_zptp.party_tax_profile_id(+)
AND tax_auth_zptp.party_type_code(+) = 'TAX_AUTHORITY'
AND tax_auth_zptp.party_id = xlv.legalauth_id(+)
AND xlv.party_id = tax_auth_hpar.party_id(+)
AND rct.bill_to_contact_id = cont_hcar.cust_account_role_id(+)
AND cont_hcar.role_type(+) = 'CONTACT'
AND cont_hcar.party_id = cont_hr.party_id(+)
AND cont_hr.subject_table_name(+) = 'HZ_PARTIES'
AND cont_hr.object_table_name(+) = 'HZ_PARTIES'
AND cont_hr.directional_flag(+) = 'F'
AND cont_hr.subject_id = cont_hpar.party_id(+)
AND rct.receipt_method_id = arm.receipt_method_id(+)
AND arm.payment_channel_code = ifpc.payment_channel_code(+)
AND cust_hpar.party_id = hop.party_id(+)
AND hop.effective_end_date IS NULL)
SELECT ORIGINAL_COPY
, CUST_ACCOUNT_ID
, PARTY_ID
, PARTY_SITE_ID
,
CUST_ACCT_SITE_ID
, ACCOUNT_NUMBER
, ACCOUNT_NAME
,
CUSTOMER_PARTY_NAME
, CUSTOMER_TAX_REFERENCE
, CUSTOMER_TAX_PAYER_ID
,
CUSTOMER_TAX_REF_NUMBER
, REP_REG_NUMBER
, RECEIPT_METHOD_NAME
, PAYMENT_METHOD_NAME
,
CUSTOMER_ADDRESS
, CUSTOMER_CITY
, CUSTOMER_STATE
, CUSTOMER_COUNTY
,
CUSTOMER_POSTAL_CODE
, CUSTOMER_ADDRESS|| ' '|| CUSTOMER_CITY|| ' '
|| CUSTOMER_STATE|| ' '|| CUSTOMER_COUNTY|| ' '
|| CUSTOMER_POSTAL_CODE CUSTOMER_COMPLETE_ADDRESS
,
NVL(TRIM(BOTH '
, '
FROM CUSTOMER_NAME_ATTR||'
, '||CUSTOMER_ADDRESS_ATTR)
, 'THE SAME') CUSTOMER_ATTR_COMPLETE_ADDRESS
,
CUSTOMER_NAME_ATTR
, CUSTOMER_ADDRESS_ATTR
,
RCT_GLOBAL_ATTRIBUTE3
, RCT_GLOBAL_ATTRIBUTE4
, RCT_GLOBAL_ATTRIBUTE5
,
RCT_GLOBAL_ATTRIBUTE6
, RCT_GLOBAL_ATTRIBUTE7
, RCT_GLOBAL_ATTRIBUTE8
,
RCT_GLOBAL_ATTRIBUTE9
, RCT_GLOBAL_ATTRIBUTE10
, RCT_GLOBAL_ATTRIBUTE11
,
RCT_GLOBAL_ATTRIBUTE12
, RCT_GLOBAL_ATTRIBUTE13
, RCT_GLOBAL_ATTRIBUTE14
,
RCT_GLOBAL_ATTRIBUTE15
, TRANSACTION_ID
,
TRANSACTION_NUMBER
, TRANSACTION_DATE
, TRANSACTION_TERM_DUE_DATE
,
TRANSACTION_CURRENCY_CODE
, DOCUMENT_SEQUENCE_NUMBER
, WAYBILL_NUMBER
,
TRANSACTION_TYPE
, SHIPPING_BILL
, SHIPPING_DATE
, VENDOR_PARTY_NAME
,
VENDOR_ADDRESS
, VENDOR_CITY
, VENDOR_STATE
, VENDOR_COUNTY
,
VENDOR_POSTAL_CODE
, VENDOR_ADDRESS|| ' '|| VENDOR_CITY|| ' '
|| VENDOR_STATE|| ' '|| VENDOR_COUNTY|| ' '
|| VENDOR_POSTAL_CODE VENDOR_COMPLETE_ADDRESS
,
VENDOR_TAX_REFERENCE
, SALES_PERSON_NAME
, TERM_NAME
, TERM_DESC
,
VENDOR_TAX_REG_NUMBER
, CUSTOMER_TAX_REG_NUMBER
,
CUSTOMER_TAX_AUTHORITY
, USER_NAME
, LINE_TOTAL_AMOUNT
,
TAX_TOTAL_AMOUNT
, ((TAX_TOTAL_AMOUNT * 100) / LINE_TOTAL_AMOUNT) TAX_RATE
,
VAT_TAX_TOTAL_AMOUNT
, ((VAT_TAX_TOTAL_AMOUNT * 100) / LINE_TOTAL_AMOUNT) VAT_TAX_RATE
,
EXCISE_TAX_TOTAL_AMOUNT
, ((EXCISE_TAX_TOTAL_AMOUNT * 100) / LINE_TOTAL_AMOUNT) EXCISE_TAX_RATE
,
EXEMPT_TAX_TOTAL_AMOUNT
, ((EXEMPT_TAX_TOTAL_AMOUNT * 100) / LINE_TOTAL_AMOUNT) EXEMPT_TAX_RATE
,
(LINE_TOTAL_AMOUNT + TAX_TOTAL_AMOUNT) TRX_TOTAL_AMOUNT
,
INITCAP
(TO_CHAR (TO_DATE ((NVL(LINE_TOTAL_AMOUNT
, 1) + NVL(TAX_TOTAL_AMOUNT
, 1))
,
'J'
)
,
'JSP'
)
) TRX_TOTAL_IN_WORDS
,
TRIM(BOTH '-'
FROM PHONE_NUMBER) PHONE_NUMBER
, VENDOR_CONTACT_NAME
,
TRIM (BOTH '
, '
FROM CUSTOMER_CONTACT_NAME) CUSTOMER_CONTACT_NAME
,
NULL NULL_VALUE
, CHIEF_ACCOUNTANT_NAME
, GENERAL_DIRECTOR_NAME
,
VENDOR_TAX_KPP
, VENDOR_TAX_PAYER_ID
, LEGAL_ENTITY_ADDRESS
FROM (SELECT DECODE (RCT.PRINTING_PENDING
, 'Y'
, 'ORIGINAL'
, 'COPY') ORIGINAL_COPY
,
HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HCA.PARTY_ID PARTY_ID
,
HCAS.PARTY_SITE_ID PARTY_SITE_ID
,
HCAS.CUST_ACCT_SITE_ID CUST_ACCT_SITE_ID
,
HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
,
HCA.ACCOUNT_NAME ACCOUNT_NAME
,
CUST_HPAR.PARTY_NAME CUSTOMER_PARTY_NAME
,
CUST_HPAR.TAX_REFERENCE CUSTOMER_TAX_REFERENCE
,
HOP.JGZZ_FISCAL_CODE CUSTOMER_TAX_PAYER_ID
,
HOP.TAX_REFERENCE CUSTOMER_TAX_REF_NUMBER
,
CUST_ZPTP.REP_REGISTRATION_NUMBER REP_REG_NUMBER
,
ARM.NAME RECEIPT_METHOD_NAME
,
IFPC.PAYMENT_CHANNEL_NAME PAYMENT_METHOD_NAME
,
TRIM ( CUST_HPAR.ADDRESS1|| ' '
|| CUST_HPAR.ADDRESS2|| ' '
|| CUST_HPAR.ADDRESS3|| ' '
|| CUST_HPAR.ADDRESS4) CUSTOMER_ADDRESS
,
CUST_HPAR.CITY CUSTOMER_CITY
,
CUST_HPAR.STATE CUSTOMER_STATE
,
CUST_HPAR.COUNTY CUSTOMER_COUNTY
,
CUST_HPAR.POSTAL_CODE CUSTOMER_POSTAL_CODE
,
RCT.GLOBAL_ATTRIBUTE1 CUSTOMER_NAME_ATTR
,
RCT.GLOBAL_ATTRIBUTE2 CUSTOMER_ADDRESS_ATTR
,
RCT.GLOBAL_ATTRIBUTE3 RCT_GLOBAL_ATTRIBUTE3
,
RCT.GLOBAL_ATTRIBUTE4 RCT_GLOBAL_ATTRIBUTE4
,
RCT.GLOBAL_ATTRIBUTE5 RCT_GLOBAL_ATTRIBUTE5
,
RCT.GLOBAL_ATTRIBUTE6 RCT_GLOBAL_ATTRIBUTE6
,
RCT.GLOBAL_ATTRIBUTE7 RCT_GLOBAL_ATTRIBUTE7
,
RCT.GLOBAL_ATTRIBUTE8 RCT_GLOBAL_ATTRIBUTE8
,
RCT.GLOBAL_ATTRIBUTE9 RCT_GLOBAL_ATTRIBUTE9
,
RCT.GLOBAL_ATTRIBUTE10 RCT_GLOBAL_ATTRIBUTE10
,
RCT.GLOBAL_ATTRIBUTE11 RCT_GLOBAL_ATTRIBUTE11
,
RCT.GLOBAL_ATTRIBUTE12 RCT_GLOBAL_ATTRIBUTE12
,
RCT.GLOBAL_ATTRIBUTE13 RCT_GLOBAL_ATTRIBUTE13
,
RCT.GLOBAL_ATTRIBUTE14 RCT_GLOBAL_ATTRIBUTE14
,
RCT.GLOBAL_ATTRIBUTE15 RCT_GLOBAL_ATTRIBUTE15
,
RCT.CUSTOMER_TRX_ID TRANSACTION_ID
,
RCT.TRX_NUMBER TRANSACTION_NUMBER
,
RCT.TRX_DATE TRANSACTION_DATE
,
RCT.TERM_DUE_DATE TRANSACTION_TERM_DUE_DATE
,
RCT.INVOICE_CURRENCY_CODE TRANSACTION_CURRENCY_CODE
,
RCT.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER
,
RCT.WAYBILL_NUMBER WAYBILL_NUMBER
,
RCTT.NAME TRANSACTION_TYPE
, RCT.ATTRIBUTE15 SHIPPING_BILL
,
RCT.SHIP_DATE_ACTUAL SHIPPING_DATE
,
VNDR_HPAR.PARTY_NAME VENDOR_PARTY_NAME
,
VNDR_HPAR.ADDRESS1 VENDOR_ADDRESS
,
VNDR_HPAR.CITY VENDOR_CITY
, VNDR_HPAR.STATE VENDOR_STATE
,
VNDR_HPAR.COUNTY VENDOR_COUNTY
,
VNDR_HPAR.POSTAL_CODE VENDOR_POSTAL_CODE
,
VNDR_HPAR.TAX_REFERENCE VENDOR_TAX_REFERENCE
,
RS.NAME SALES_PERSON_NAME
, RT.NAME TERM_NAME
,
RT.DESCRIPTION TERM_DESC
,
VNDR_ZR.REGISTRATION_NUMBER VENDOR_TAX_REG_NUMBER
,
CUST_ZR.REGISTRATION_NUMBER CUSTOMER_TAX_REG_NUMBER
,
TAX_AUTH_HPAR.PARTY_NAME CUSTOMER_TAX_AUTHORITY
,
FND_GLOBAL.USER_NAME USER_NAME
,
NVL((SELECT SUM (RCTL.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.LINE_TYPE = 'LINE')
, 0) LINE_TOTAL_AMOUNT
,
NVL((SELECT SUM (RCTL.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.LINE_TYPE = 'TAX')
, 0) TAX_TOTAL_AMOUNT
,
-- NVL ((SELECT SUM (RCTL.TAX_RATE)
--
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
--
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
--
AND RCTL.LINE_TYPE = 'TAX')
, 0) TAX_RATE
,
NVL((SELECT SUM (RCTL.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
, ZX_LINES ZL
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.LINE_TYPE = 'TAX'
AND RCTL.TAX_LINE_ID = ZL.TAX_LINE_ID
AND ZL.TAX_TYPE_CODE='VAT')
, 0) VAT_TAX_TOTAL_AMOUNT
,
-- NVL ((SELECT SUM (RCTL.TAX_RATE)
--
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
--
, ZX_LINES ZL
--
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
--
AND RCTL.LINE_TYPE = 'TAX'
--
AND RCTL.TAX_LINE_ID = ZL.TAX_LINE_ID
--
AND ZL.TAX_TYPE_CODE='VAT')
, 0) VAT_TAX_RATE
,
NVL((SELECT SUM (RCTL.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
, ZX_LINES ZL
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.LINE_TYPE = 'TAX'
AND RCTL.TAX_LINE_ID = ZL.TAX_LINE_ID
AND ZL.TAX_TYPE_CODE='EXCISE')
, 0) EXCISE_TAX_TOTAL_AMOUNT
,
-- NVL ((SELECT SUM (RCTL.TAX_RATE)
--
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
--
, ZX_LINES ZL
--
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
--
AND RCTL.LINE_TYPE = 'TAX'
--
AND RCTL.TAX_LINE_ID = ZL.TAX_LINE_ID
--
AND ZL.TAX_TYPE_CODE='EXCISE')
, 0) EXCISE_TAX_RATE
,
NVL((SELECT SUM (RCTL.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
, ZX_LINES ZL
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.LINE_TYPE = 'TAX'
AND RCTL.TAX_LINE_ID = ZL.TAX_LINE_ID
AND ZL.TAX_TYPE_CODE='EXEMPT TAX')
, 0) EXEMPT_TAX_TOTAL_AMOUNT
,
-- NVL ((SELECT SUM (RCTL.TAX_RATE)
--
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
--
, ZX_LINES ZL
--
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
--
AND RCTL.LINE_TYPE = 'TAX'
--
AND RCTL.TAX_LINE_ID = ZL.TAX_LINE_ID
--
AND ZL.TAX_TYPE_CODE='EXEMPT TAX')
, 0) EXEMPT_TAX_RATE
,
CUST_HPAR.PRIMARY_PHONE_COUNTRY_CODE|| '-'
|| CUST_HPAR.PRIMARY_PHONE_AREA_CODE|| '-'
|| CUST_HPAR.PRIMARY_PHONE_NUMBER PHONE_NUMBER
,
NVL(VNDR_HPAR.PERSON_TITLE
, VNDR_HPAR.PERSON_PRE_NAME_ADJUNCT)|| ' '
|| VNDR_HPAR.PERSON_FIRST_NAME|| ' '
|| VNDR_HPAR.PERSON_MIDDLE_NAME|| ' '
|| VNDR_HPAR.PERSON_LAST_NAME VENDOR_CONTACT_NAME
,
SUBSTRB(CONT_HPAR.PERSON_LAST_NAME
, 1
, 50)|| '
, '|| ' '
|| SUBSTRB (CONT_HPAR.PERSON_FIRST_NAME
, 1
, 50) CUSTOMER_CONTACT_NAME
,
(SELECT PER_HPAR.PARTY_NAME
FROM HZ_PARTIES PER_HPAR
,
HZ_RELATIONSHIPS VNDR_HR
WHERE VNDR_HPAR.PARTY_ID = VNDR_HR.OBJECT_ID
AND VNDR_HR.RELATIONSHIP_CODE = 'CONTACT_OF'
AND VNDR_HR.RELATIONSHIP_TYPE = 'CONTACT'
AND VNDR_HR.DIRECTIONAL_FLAG = 'F'
AND VNDR_HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND VNDR_HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND VNDR_HR.SUBJECT_TYPE = 'PERSON'
AND TRUNC (NVL (VNDR_HR.END_DATE
, SYSDATE)) > TRUNC (SYSDATE)
AND VNDR_HR.SUBJECT_ID = PER_HPAR.PARTY_ID
AND XLE_CONTACT_GRP.CONCAT_CONTACT_ROLES(VNDR_HR.SUBJECT_ID
, VNDR_HR.OBJECT_ID) = 'CHIEF ACCOUNTANT') CHIEF_ACCOUNTANT_NAME
,
(SELECT PER_HPAR.PARTY_NAME
FROM HZ_PARTIES PER_HPAR
,
HZ_RELATIONSHIPS VNDR_HR
WHERE VNDR_HPAR.PARTY_ID = VNDR_HR.OBJECT_ID
AND VNDR_HR.RELATIONSHIP_CODE = 'CONTACT_OF'
AND VNDR_HR.RELATIONSHIP_TYPE = 'CONTACT'
AND VNDR_HR.DIRECTIONAL_FLAG = 'F'
AND VNDR_HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND VNDR_HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND VNDR_HR.SUBJECT_TYPE = 'PERSON'
AND TRUNC (NVL (VNDR_HR.END_DATE
, SYSDATE)) > TRUNC (SYSDATE)
AND VNDR_HR.SUBJECT_ID = PER_HPAR.PARTY_ID
AND XLE_CONTACT_GRP.CONCAT_CONTACT_ROLES(VNDR_HR.SUBJECT_ID
, VNDR_HR.OBJECT_ID) ='GENERAL DIRECTOR') GENERAL_DIRECTOR_NAME
,
(SELECT REGISTRATIONEO.REGISTRATION_NUMBER
FROM XLE_REGISTRATIONS REGISTRATIONEO
,
XLE_JURISDICTIONS_VL JURISDICTIONS
WHERE JURISDICTIONS.JURISDICTION_ID =REGISTRATIONEO.JURISDICTION_ID
AND REGISTRATIONEO.SOURCE_TABLE = 'XLE_ETB_PROFILES'
AND REGISTRATIONEO.SOURCE_ID = XEV.ESTABLISHMENT_ID
AND NVL (JURISDICTIONS.REGISTRATION_CODE_ETB
, 'RN') ='CRR')VENDOR_TAX_KPP
,
(SELECT REGISTRATIONEO.REGISTRATION_NUMBER
FROM XLE_REGISTRATIONS REGISTRATIONEO
,
XLE_JURISDICTIONS_VL JURISDICTIONS
WHERE JURISDICTIONS.JURISDICTION_ID =REGISTRATIONEO.JURISDICTION_ID
AND REGISTRATIONEO.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND REGISTRATIONEO.SOURCE_ID = XEP.LEGAL_ENTITY_ID
AND NVL (JURISDICTIONS.REGISTRATION_CODE_LE
, 'RN') ='INT')VENDOR_TAX_PAYER_ID
,
(SELECT LTRIM ( LOC.ADDRESS_LINE_1|| ' '
|| LOC.ADDRESS_LINE_2|| ' '
|| LOC.ADDRESS_LINE_3|| ' '
|| LOC.TOWN_OR_CITY|| ' '
|| LOC.REGION_2|| ' '
|| LOC.POSTAL_CODE) ADDRESS
FROM XLE_REGISTRATIONS XLR
,
HR_LOCATIONS LOC
WHERE XLR.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND XLR.SOURCE_ID = XEP.LEGAL_ENTITY_ID
AND XLR.LOCATION_ID = LOC.LOCATION_ID(+)) LEGAL_ENTITY_ADDRESS
FROM RA_CUSTOMER_TRX_ALL RCT
,
RA_CUST_TRX_TYPES_ALL RCTT
,
RA_TERMS RT
,
RA_SALESREPS_ALL RS
,
HZ_CUST_ACCOUNTS HCA
,
HZ_PARTIES CUST_HPAR
,
HZ_CUST_SITE_USES_ALL HCSU
,
HZ_CUST_ACCT_SITES_ALL HCAS
,
HZ_PARTY_SITES HPS
,
XLE_ENTITY_PROFILES XEP
,
HZ_PARTIES VNDR_HPAR
,
XLE_ETB_PROFILES XEV
,
HZ_PARTIES ESTAB_HPAR
,
ZX_PARTY_TAX_PROFILE VNDR_ZPTP
,
ZX_REGISTRATIONS VNDR_ZR
,
ZX_PARTY_TAX_PROFILE CUST_ZPTP
,
ZX_REGISTRATIONS CUST_ZR
,
HZ_LOCATIONS HL
,
FND_TERRITORIES_TL FTT
,
ZX_PARTY_TAX_PROFILE TAX_AUTH_ZPTP
,
XLE_LEGALAUTH_V XLV
,
HZ_PARTIES TAX_AUTH_HPAR
,
HZ_CUST_ACCOUNT_ROLES CONT_HCAR
,
HZ_RELATIONSHIPS CONT_HR
,
HZ_PARTIES CONT_HPAR
,
AR_RECEIPT_METHODS ARM
,
IBY_FNDCPT_PMT_CHNNLS_VL IFPC
,
HZ_ORGANIZATION_PROFILES HOP
WHERE RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCT.TERM_ID = RT.TERM_ID(+)
AND RCT.PRIMARY_SALESREP_ID = RS.SALESREP_ID(+)
AND RCT.ORG_ID = RS.ORG_ID(+)
AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = CUST_HPAR.PARTY_ID
AND RCT.BILL_TO_SITE_USE_ID = HCSU.SITE_USE_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND RCT.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
AND XEP.PARTY_ID = VNDR_HPAR.PARTY_ID
AND XEV.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
AND XEV.PARTY_ID = ESTAB_HPAR.PARTY_ID
AND XEV.MAIN_ESTABLISHMENT_FLAG = 'Y'
AND ESTAB_HPAR.PARTY_ID = VNDR_ZPTP.PARTY_ID
AND VNDR_ZPTP.PARTY_TAX_PROFILE_ID = VNDR_ZR.PARTY_TAX_PROFILE_ID(+)
AND VNDR_ZR.DEFAULT_REGISTRATION_FLAG(+) = 'Y'
AND CUST_HPAR.PARTY_ID = CUST_ZPTP.PARTY_ID
AND CUST_ZPTP.PARTY_TAX_PROFILE_ID = CUST_ZR.PARTY_TAX_PROFILE_ID(+)
AND CUST_ZR.DEFAULT_REGISTRATION_FLAG(+) = 'Y'
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HL.COUNTRY = FTT.TERRITORY_CODE(+)
AND NVL (FTT.LANGUAGE
, USERENV ('LANG')) = USERENV ('LANG')
AND VNDR_ZR.TAX_AUTHORITY_ID = TAX_AUTH_ZPTP.PARTY_TAX_PROFILE_ID(+)
AND TAX_AUTH_ZPTP.PARTY_TYPE_CODE(+) = 'TAX_AUTHORITY'
AND TAX_AUTH_ZPTP.PARTY_ID = XLV.LEGALAUTH_ID(+)
AND XLV.PARTY_ID = TAX_AUTH_HPAR.PARTY_ID(+)
AND RCT.BILL_TO_CONTACT_ID = CONT_HCAR.CUST_ACCOUNT_ROLE_ID(+)
AND CONT_HCAR.ROLE_TYPE(+) = 'CONTACT'
AND CONT_HCAR.PARTY_ID = CONT_HR.PARTY_ID(+)
AND CONT_HR.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_HR.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_HR.DIRECTIONAL_FLAG(+) = 'F'
AND CONT_HR.SUBJECT_ID = CONT_HPAR.PARTY_ID(+)
AND RCT.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
AND ARM.PAYMENT_CHANNEL_CODE = IFPC.PAYMENT_CHANNEL_CODE(+)
AND CUST_HPAR.PARTY_ID = HOP.PARTY_ID(+)
AND HOP.EFFECTIVE_END_DATE IS NULL)
|
|
|
|