[Home] [Help]
SELECT D.DOCUMENT_TRANSFER_ID DOCUMENT_TRANSFER_ID, H.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, H.TERM_ID TERM_ID, (select sum(extended_amount) from ra_customer_trx_lines l WHERE l.customer_trx_id = h.customer_trx_id) AMOUNT, H.INVOICE_CURRENCY_CODE CURRENCY_CODE, H.TRX_DATE TRX_DATE, H.TRX_NUMBER TRX_NUMBER, nvl(H.PURCHASE_ORDER,'N/A') PO_NUMBER, T.type DOCUMENT_TYPE, RM.payment_type_code PAYMENT_METHOD, org.name SUPPLIER_NAME, loc.ece_tp_location_code SUPPLIER_CODE, sr.name SALESREP, sr.email_address SALESREP_EMAIL, to_char(d.document_transfer_id) ||':'|| to_char(h.customer_trx_id) REFERENCE_NUMBER, H.SHIP_DATE_ACTUAL SHIP_DATE, (select sum(nvl(extended_amount,0)) from ra_customer_trx_lines l where l.line_type = 'TAX' and l.customer_trx_id = h.customer_trx_id) TAX_AMOUNT, H.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID, H.SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID, H.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID, H.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID, H.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID, H.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID, H.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID, ar_xml_view_functions.header_function1(h.customer_trx_id) USER1, ar_xml_view_functions.header_function2(h.customer_trx_id) USER2, ar_xml_view_functions.header_function3(h.customer_trx_id) USER3, ar_xml_view_functions.header_function4(h.customer_trx_id) USER4, ar_xml_view_functions.header_function5(h.customer_trx_id) USER5, ar_xml_view_functions.header_function6(h.customer_trx_id) USER6, ar_xml_view_functions.header_function7(h.customer_trx_id) USER7, ar_xml_view_functions.header_function8(h.customer_trx_id) USER8, ar_xml_view_functions.header_function9(h.customer_trx_id) USER9, ar_xml_view_functions.header_function10(h.customer_trx_id) USER10, loc.description SUPPLIER_DESCRIPTION, loc.address_line_1 SUPPLIER_ADDRESS_LINE_1, loc.address_line_2 SUPPLIER_ADDRESS_LINE_2, loc.address_line_3 SUPPLIER_ADDRESS_LINE_3, loc.town_or_city SUPPLIER_TOWN_OR_CITY, loc.country SUPPLIER_COUNTRY, loc.region_1 SUPPLIER_REGION1, loc.region_2 SUPPLIER_REGION2, loc.region_3 SUPPLIER_REGION3, loc.postal_code SUPPLIER_POSTAL_CODE, loc.telephone_number_1 SUPPLIER_TELEPHONE_NUMBER_1, loc.telephone_number_2 SUPPLIER_TELEPHONE_NUMBER_2, loc.telephone_number_3 SUPPLIER_TELEPHONE_NUMBER_3, OF_SHIP_VIA.freight_code FREIGHT_CODE, OF_SHIP_VIA.description FREIGHT_DESCRIPTION, h.waybill_number WAYBILL_NUMBER from AR_DOCUMENT_TRANSFERS D, RA_CUSTOMER_TRX H, RA_CUST_TRX_TYPES T, AR_RECEIPT_METHODS RM, HR_ALL_ORGANIZATION_UNITS ORG, HR_LOCATIONS LOC, RA_SALESREPS SR, ORG_FREIGHT OF_SHIP_VIA, OE_SYSTEM_PARAMETERS OS where d.source_id = h.customer_trx_id and h.cust_trx_type_id = t.cust_trx_type_id and h.receipt_method_id = rm.receipt_method_id(+) and org.organization_id = h.org_id and org.location_id = loc.location_id(+) and h.PRIMARY_SALESREP_ID = sr.salesrep_id(+) and H.SHIP_VIA = OF_SHIP_VIA.FREIGHT_CODE(+) and NVL(OF_SHIP_VIA.ORGANIZATION_ID, OS.MASTER_ORGANIZATION_ID) = OS.MASTER_ORGANIZATION_ID
SELECT D.DOCUMENT_TRANSFER_ID DOCUMENT_TRANSFER_ID
, H.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, H.TERM_ID TERM_ID
, (SELECT SUM(EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES L
WHERE L.CUSTOMER_TRX_ID = H.CUSTOMER_TRX_ID) AMOUNT
, H.INVOICE_CURRENCY_CODE CURRENCY_CODE
, H.TRX_DATE TRX_DATE
, H.TRX_NUMBER TRX_NUMBER
, NVL(H.PURCHASE_ORDER
, 'N/A') PO_NUMBER
, T.TYPE DOCUMENT_TYPE
, RM.PAYMENT_TYPE_CODE PAYMENT_METHOD
, ORG.NAME SUPPLIER_NAME
, LOC.ECE_TP_LOCATION_CODE SUPPLIER_CODE
, SR.NAME SALESREP
, SR.EMAIL_ADDRESS SALESREP_EMAIL
, TO_CHAR(D.DOCUMENT_TRANSFER_ID) ||':'|| TO_CHAR(H.CUSTOMER_TRX_ID) REFERENCE_NUMBER
, H.SHIP_DATE_ACTUAL SHIP_DATE
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES L
WHERE L.LINE_TYPE = 'TAX'
AND L.CUSTOMER_TRX_ID = H.CUSTOMER_TRX_ID) TAX_AMOUNT
, H.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, H.SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID
, H.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, H.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, H.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, H.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
, H.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION1(H.CUSTOMER_TRX_ID) USER1
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION2(H.CUSTOMER_TRX_ID) USER2
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION3(H.CUSTOMER_TRX_ID) USER3
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION4(H.CUSTOMER_TRX_ID) USER4
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION5(H.CUSTOMER_TRX_ID) USER5
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION6(H.CUSTOMER_TRX_ID) USER6
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION7(H.CUSTOMER_TRX_ID) USER7
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION8(H.CUSTOMER_TRX_ID) USER8
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION9(H.CUSTOMER_TRX_ID) USER9
, AR_XML_VIEW_FUNCTIONS.HEADER_FUNCTION10(H.CUSTOMER_TRX_ID) USER10
, LOC.DESCRIPTION SUPPLIER_DESCRIPTION
, LOC.ADDRESS_LINE_1 SUPPLIER_ADDRESS_LINE_1
, LOC.ADDRESS_LINE_2 SUPPLIER_ADDRESS_LINE_2
, LOC.ADDRESS_LINE_3 SUPPLIER_ADDRESS_LINE_3
, LOC.TOWN_OR_CITY SUPPLIER_TOWN_OR_CITY
, LOC.COUNTRY SUPPLIER_COUNTRY
, LOC.REGION_1 SUPPLIER_REGION1
, LOC.REGION_2 SUPPLIER_REGION2
, LOC.REGION_3 SUPPLIER_REGION3
, LOC.POSTAL_CODE SUPPLIER_POSTAL_CODE
, LOC.TELEPHONE_NUMBER_1 SUPPLIER_TELEPHONE_NUMBER_1
, LOC.TELEPHONE_NUMBER_2 SUPPLIER_TELEPHONE_NUMBER_2
, LOC.TELEPHONE_NUMBER_3 SUPPLIER_TELEPHONE_NUMBER_3
, OF_SHIP_VIA.FREIGHT_CODE FREIGHT_CODE
, OF_SHIP_VIA.DESCRIPTION FREIGHT_DESCRIPTION
, H.WAYBILL_NUMBER WAYBILL_NUMBER
FROM AR_DOCUMENT_TRANSFERS D
, RA_CUSTOMER_TRX H
, RA_CUST_TRX_TYPES T
, AR_RECEIPT_METHODS RM
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_LOCATIONS LOC
, RA_SALESREPS SR
, ORG_FREIGHT OF_SHIP_VIA
, OE_SYSTEM_PARAMETERS OS
WHERE D.SOURCE_ID = H.CUSTOMER_TRX_ID
AND H.CUST_TRX_TYPE_ID = T.CUST_TRX_TYPE_ID
AND H.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID(+)
AND ORG.ORGANIZATION_ID = H.ORG_ID
AND ORG.LOCATION_ID = LOC.LOCATION_ID(+)
AND H.PRIMARY_SALESREP_ID = SR.SALESREP_ID(+)
AND H.SHIP_VIA = OF_SHIP_VIA.FREIGHT_CODE(+)
AND NVL(OF_SHIP_VIA.ORGANIZATION_ID
, OS.MASTER_ORGANIZATION_ID) = OS.MASTER_ORGANIZATION_ID
|
|
|
|