eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.OZF_SD_BATCH_INFO_V

Source

View Text - Preformatted


SELECT lines.batch_id batch_id, lines.agreement_number ship_frm_sd_authorization_id, frm.frm_business_name dist_by_business_name, frm.frm_global_business_id dist_by_gbl_business_id, frm.frm_global_supply_chain_cd dist_by_gbl_supply_chain_code, frm.frm_prop_business_id dist_by_prop_business_id, frm.frm_prop_domain_id1 dist_by_prop_domain_id1, frm.frm_prop_auth_id1 dist_by_prop_auth_id1, frm.frm_global_partner_class_code dist_by_gbl_partner_class_code, frm.frm_contact1_name dist_by_contact1_name, frm.frm_contact1_email dist_by_contact1_email, frm.frm_contact1_phone dist_by_contact1_phone, frm.frm_contact1_fax dist_by_contact1_fax, frm.frm_global_location_id dist_by_gbl_location_id, frm.frm_prop_domain_id2 dist_by_prop_domain_id2, frm.frm_prop_auth_id2 dist_by_prop_idauthority2, frm.frm_prop_loc_id dist_by_prop_location_id, frm.frm_add_line1 dist_by_address_line1, frm.frm_add_line2 dist_by_address_line2, frm.frm_add_line3 dist_by_address_line3, frm.frm_city dist_by_city_name, frm.frm_country dist_by_gbl_country_code, frm.frm_postal_code dist_by_national_postal_code, frm.frm_box_identifier dist_by_pobox_id, frm.frm_state_region dist_by_region_name, ship_to_party.party_name ship_to_business_name, ship_to_party.duns_number ship_to_gbl_business_id, ship_to_party.category_code ship_to_gbl_supply_chain_code, NULL ship_to_prop_business_id, NULL ship_to_prop_domain_id1, NULL ship_to_prop_id_authority1, NULL ship_to_prop_domain_id2, NULL ship_to_prop_id_authority2, NULL ship_to_prop_loc_id, NULL ship_to_gbl_loc_id, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PTNR_CLASS_CODE') ship_to_gbl_partner_class_code, ship_to_loc.address1 ship_to_address_line1, ship_to_loc.address2 ship_to_address_line2, decode(ship_to_loc.address3, NULL, ship_to_loc.address4, ship_to_loc.address3 || decode(ship_to_loc.address4, NULL, NULL, ',' || ship_to_loc.address4)) ship_to_address_line3, ship_to_loc.city ship_to_city_name, ship_to_loc.state ship_to_region_name, ship_to_loc.postal_code ship_to_national_postal_code, ship_to_loc.country ship_to_gbl_country_code, ship_to_loc.po_box_number ship_to_pobox_id, ship_to_contact_v.name ship_to_contact1_name, ship_to_contact_v.email_address ship_to_contact1_email, ship_to_contact_v.phone_number ship_to_contact1_phone, NULL ship_to_contact1_fax, sold_to_party.party_name sold_to_business_name, sold_to_party.duns_number sold_to_gbl_business_id, sold_to_party.category_code sold_to_gbl_supply_chain_code, NULL sold_to_prop_business_id, NULL sold_to_prop_domain_id1, NULL sold_to_prop_id_authority1, NULL sold_to_prop_domain_id2, NULL sold_to_prop_id_authority2, NULL sold_to_prop_loc_id, NULL sold_to_gbl_loc_id, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PTNR_CLASS_CODE') sold_to_gbl_partner_class_code, sold_to_loc.address1 sold_to_address_line1, sold_to_loc.address2 sold_to_address_line2, decode(sold_to_loc.address3, NULL, sold_to_loc.address4, sold_to_loc.address3 || decode(sold_to_loc.address4, NULL, NULL, ',' || sold_to_loc.address4)) sold_to_address_line3, sold_to_loc.city sold_to_city_name, sold_to_loc.state sold_to_region_name, sold_to_loc.postal_code sold_to_national_postal_code, sold_to_loc.country sold_to_gbl_country_code, sold_to_loc.po_box_number sold_to_pobox_id, sold_to_contact_v.name sold_to_contact1_name, sold_to_contact_v.email_address sold_to_contact1_email, sold_to_contact_v.phone_number sold_to_contact1_phone, NULL sold_to_contact1_fax, nvl(used_by_party.party_name, end_cust_party.party_name) used_by_business_name, used_by_party.duns_number used_by_gbl_business_id, used_by_party.category_code used_by_gbl_supply_chain_code, NULL used_by_prop_business_id, NULL used_by_prop_domain_id1, NULL used_by_prop_id_authority1, NULL used_by_prop_domain_id2, NULL used_by_prop_id_authority2, NULL used_by_prop_loc_id, NULL used_by_gbl_loc_id, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PTNR_CLASS_CODE') used_by_gbl_partner_class_code, used_by_party.address1 used_by_address_line1, used_by_party.address2 used_by_address_line2, decode(used_by_party.address3, NULL, used_by_party.address4, used_by_party.address3 || decode(used_by_party.address4, NULL, NULL, ',' || used_by_party.address4)) used_by_address_line3, used_by_party.city used_by_city_name, used_by_party.state used_by_region_name, used_by_party.postal_code used_by_national_postal_code, used_by_party.country used_by_gbl_country_code, NULL used_by_pobox_id, used_by_contact_v.name used_by_contact1_name, used_by_contact_v.email_address used_by_contact1_email, used_by_contact_v.phone_number used_by_contact1_phone, NULL used_by_contact1_fax, sysdate ship_frm_sd_claim_request_date, hdr.batch_number ship_frm_sd_claim_request_id, NULL credit_reference_id, hdr.claim_number debit_reference_id, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_DOC_REF_CODE_BATCH') gbl_doc_ref_type_code_batch, lines.batch_line_number batch_line_number, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_DOC_REF_CODE_ORDER') gbl_doc_ref_type_code_order, lines.order_date order_date, oel.line_number order_line_number, oeh.order_number order_number, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_DOC_REF_CODE_INV') gbl_doc_ref_type_code_invoice, sysdate invoice_date, lines.invoice_line_number invoice_line_number, lines.invoice_number invoice_number, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PRICE_CODE_LIST') gbl_price_type_code_list, lines.list_price_currency_code cost_currencycode, lines.list_price cost_monetary_amount, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PRICE_CODE_AGR') gbl_price_type_code_agreement, lines.agreement_currency_code auth_cost_currency_code, lines.agreement_price auth_cost_monetary_amount, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PRICE_CODE_RESALE') gbl_price_type_code_resale, lines.resale_price_currency_code resale_currency_code, lines.resales_price resale_monetary_amount, lines.shipped_quantity_uom gbl_uom, lines.status_code gbl_claim_disposition_code, NULL dispute_code1, NULL dispute_code2, NULL dispute_code3, NULL dispute_code4, NULL dispute_code5, NULL dispute_code6, NULL dispute_code7, NULL dispute_code8, NULL dispute_code9, NULL dispute_code10, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PTNR_CODE_DIST') gbl_ptnr_class_code_dist, mtl.concatenated_segments dist_product_id, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_GBL_PTNR_CODE_MANU') gbl_ptnr_class_code_manu, lines.vendor_item_id vendor_product_id, oel.actual_shipment_date ship_date, lines.quantity_shipped shipped_quantity, lines.claim_amount_currency_code claim_amount_currency_code, lines.last_sub_claim_amount last_sub_claim_amount, NULL vendor_auth_lineitem_line_nbr, NULL vendor_auth_cost_currency_code, NULL vendor_auth_cost_monetary_amt, hdr.batch_submission_date batch_submission_date, lines.batch_line_id batch_line_id, hdr.batch_id this_doc_id FROM ozf_sd_batch_headers_all hdr, ozf_sd_batch_lines_all lines, ozf_sd_batch_from_role_v frm, oe_order_lines_all oel, oe_order_headers_all oeh, mtl_system_items_b_kfv mtl, hz_parties end_cust_party, hz_cust_acct_sites_all sold_to_acct_site, hz_cust_site_uses_all sold_to_acct_site_use, hz_cust_accounts sold_to_acct, hz_parties sold_to_party, hz_party_sites sold_to_party_sites, hz_locations sold_to_loc, ozf_sd_contact_v sold_to_contact_v, hz_cust_acct_sites_all ship_to_acct_site, hz_cust_site_uses_all ship_to_acct_site_use, hz_cust_accounts ship_to_acct, hz_parties ship_to_party, hz_party_sites ship_to_party_sites, hz_locations ship_to_loc, ozf_sd_contact_v ship_to_contact_v, hz_parties used_by_party, ozf_sd_contact_v used_by_contact_v WHERE hdr.batch_id = lines.batch_id AND hdr.vendor_id = frm.vendor_id AND hdr.vendor_site_id = frm.vendor_site_id AND hdr.batch_number = frm.batch_number AND oel.line_id = lines.order_line_id AND oel.header_id = lines.order_header_id AND sysdate BETWEEN nvl(sold_to_loc.address_effective_date, sysdate) AND nvl(sold_to_loc.address_expiration_date, sysdate) AND sold_to_loc.location_id(+) = sold_to_party_sites.location_id AND sold_to_party_sites.party_site_id(+) = sold_to_acct_site.party_site_id AND sold_to_party.party_id(+) = sold_to_acct.party_id AND sold_to_acct.cust_account_id(+) = sold_to_acct_site.cust_account_id AND sold_to_acct_site.cust_acct_site_id(+) = sold_to_acct_site_use.cust_acct_site_id AND sold_to_acct_site_use.site_use_id(+) = lines.sold_to_site_use_id AND sold_to_contact_v.cust_account_role_id(+) = lines.sold_to_contact_id AND sysdate BETWEEN nvl(ship_to_loc.address_effective_date, sysdate) AND nvl(ship_to_loc.address_expiration_date, sysdate) AND ship_to_loc.location_id(+) = ship_to_party_sites.location_id AND ship_to_party_sites.party_site_id(+) = ship_to_acct_site.party_site_id AND ship_to_party.party_id(+) = ship_to_acct.party_id AND ship_to_acct.cust_account_id(+) = ship_to_acct_site.cust_account_id AND ship_to_acct_site.cust_acct_site_id(+) = ship_to_acct_site_use.cust_acct_site_id AND ship_to_acct_site_use.site_use_id(+) = lines.ship_to_org_id AND ship_to_contact_v.cust_account_role_id(+) = lines.ship_to_contact_id AND used_by_party.party_id(+) = lines.end_customer_id AND used_by_contact_v.cust_account_role_id(+) = lines.end_customer_contact_id AND lines.end_customer_id = end_cust_party.party_id(+) AND lines.purge_flag <> 'Y' AND lines.process_feed_flag = 'Y' AND oeh.header_id = lines.order_header_id AND mtl.organization_id = lines.org_id AND mtl.inventory_item_id = lines.item_id

View Text - HTML Formatted

SELECT LINES.BATCH_ID BATCH_ID
, LINES.AGREEMENT_NUMBER SHIP_FRM_SD_AUTHORIZATION_ID
, FRM.FRM_BUSINESS_NAME DIST_BY_BUSINESS_NAME
, FRM.FRM_GLOBAL_BUSINESS_ID DIST_BY_GBL_BUSINESS_ID
, FRM.FRM_GLOBAL_SUPPLY_CHAIN_CD DIST_BY_GBL_SUPPLY_CHAIN_CODE
, FRM.FRM_PROP_BUSINESS_ID DIST_BY_PROP_BUSINESS_ID
, FRM.FRM_PROP_DOMAIN_ID1 DIST_BY_PROP_DOMAIN_ID1
, FRM.FRM_PROP_AUTH_ID1 DIST_BY_PROP_AUTH_ID1
, FRM.FRM_GLOBAL_PARTNER_CLASS_CODE DIST_BY_GBL_PARTNER_CLASS_CODE
, FRM.FRM_CONTACT1_NAME DIST_BY_CONTACT1_NAME
, FRM.FRM_CONTACT1_EMAIL DIST_BY_CONTACT1_EMAIL
, FRM.FRM_CONTACT1_PHONE DIST_BY_CONTACT1_PHONE
, FRM.FRM_CONTACT1_FAX DIST_BY_CONTACT1_FAX
, FRM.FRM_GLOBAL_LOCATION_ID DIST_BY_GBL_LOCATION_ID
, FRM.FRM_PROP_DOMAIN_ID2 DIST_BY_PROP_DOMAIN_ID2
, FRM.FRM_PROP_AUTH_ID2 DIST_BY_PROP_IDAUTHORITY2
, FRM.FRM_PROP_LOC_ID DIST_BY_PROP_LOCATION_ID
, FRM.FRM_ADD_LINE1 DIST_BY_ADDRESS_LINE1
, FRM.FRM_ADD_LINE2 DIST_BY_ADDRESS_LINE2
, FRM.FRM_ADD_LINE3 DIST_BY_ADDRESS_LINE3
, FRM.FRM_CITY DIST_BY_CITY_NAME
, FRM.FRM_COUNTRY DIST_BY_GBL_COUNTRY_CODE
, FRM.FRM_POSTAL_CODE DIST_BY_NATIONAL_POSTAL_CODE
, FRM.FRM_BOX_IDENTIFIER DIST_BY_POBOX_ID
, FRM.FRM_STATE_REGION DIST_BY_REGION_NAME
, SHIP_TO_PARTY.PARTY_NAME SHIP_TO_BUSINESS_NAME
, SHIP_TO_PARTY.DUNS_NUMBER SHIP_TO_GBL_BUSINESS_ID
, SHIP_TO_PARTY.CATEGORY_CODE SHIP_TO_GBL_SUPPLY_CHAIN_CODE
, NULL SHIP_TO_PROP_BUSINESS_ID
, NULL SHIP_TO_PROP_DOMAIN_ID1
, NULL SHIP_TO_PROP_ID_AUTHORITY1
, NULL SHIP_TO_PROP_DOMAIN_ID2
, NULL SHIP_TO_PROP_ID_AUTHORITY2
, NULL SHIP_TO_PROP_LOC_ID
, NULL SHIP_TO_GBL_LOC_ID
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PTNR_CLASS_CODE') SHIP_TO_GBL_PARTNER_CLASS_CODE
, SHIP_TO_LOC.ADDRESS1 SHIP_TO_ADDRESS_LINE1
, SHIP_TO_LOC.ADDRESS2 SHIP_TO_ADDRESS_LINE2
, DECODE(SHIP_TO_LOC.ADDRESS3
, NULL
, SHIP_TO_LOC.ADDRESS4
, SHIP_TO_LOC.ADDRESS3 || DECODE(SHIP_TO_LOC.ADDRESS4
, NULL
, NULL
, '
, ' || SHIP_TO_LOC.ADDRESS4)) SHIP_TO_ADDRESS_LINE3
, SHIP_TO_LOC.CITY SHIP_TO_CITY_NAME
, SHIP_TO_LOC.STATE SHIP_TO_REGION_NAME
, SHIP_TO_LOC.POSTAL_CODE SHIP_TO_NATIONAL_POSTAL_CODE
, SHIP_TO_LOC.COUNTRY SHIP_TO_GBL_COUNTRY_CODE
, SHIP_TO_LOC.PO_BOX_NUMBER SHIP_TO_POBOX_ID
, SHIP_TO_CONTACT_V.NAME SHIP_TO_CONTACT1_NAME
, SHIP_TO_CONTACT_V.EMAIL_ADDRESS SHIP_TO_CONTACT1_EMAIL
, SHIP_TO_CONTACT_V.PHONE_NUMBER SHIP_TO_CONTACT1_PHONE
, NULL SHIP_TO_CONTACT1_FAX
, SOLD_TO_PARTY.PARTY_NAME SOLD_TO_BUSINESS_NAME
, SOLD_TO_PARTY.DUNS_NUMBER SOLD_TO_GBL_BUSINESS_ID
, SOLD_TO_PARTY.CATEGORY_CODE SOLD_TO_GBL_SUPPLY_CHAIN_CODE
, NULL SOLD_TO_PROP_BUSINESS_ID
, NULL SOLD_TO_PROP_DOMAIN_ID1
, NULL SOLD_TO_PROP_ID_AUTHORITY1
, NULL SOLD_TO_PROP_DOMAIN_ID2
, NULL SOLD_TO_PROP_ID_AUTHORITY2
, NULL SOLD_TO_PROP_LOC_ID
, NULL SOLD_TO_GBL_LOC_ID
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PTNR_CLASS_CODE') SOLD_TO_GBL_PARTNER_CLASS_CODE
, SOLD_TO_LOC.ADDRESS1 SOLD_TO_ADDRESS_LINE1
, SOLD_TO_LOC.ADDRESS2 SOLD_TO_ADDRESS_LINE2
, DECODE(SOLD_TO_LOC.ADDRESS3
, NULL
, SOLD_TO_LOC.ADDRESS4
, SOLD_TO_LOC.ADDRESS3 || DECODE(SOLD_TO_LOC.ADDRESS4
, NULL
, NULL
, '
, ' || SOLD_TO_LOC.ADDRESS4)) SOLD_TO_ADDRESS_LINE3
, SOLD_TO_LOC.CITY SOLD_TO_CITY_NAME
, SOLD_TO_LOC.STATE SOLD_TO_REGION_NAME
, SOLD_TO_LOC.POSTAL_CODE SOLD_TO_NATIONAL_POSTAL_CODE
, SOLD_TO_LOC.COUNTRY SOLD_TO_GBL_COUNTRY_CODE
, SOLD_TO_LOC.PO_BOX_NUMBER SOLD_TO_POBOX_ID
, SOLD_TO_CONTACT_V.NAME SOLD_TO_CONTACT1_NAME
, SOLD_TO_CONTACT_V.EMAIL_ADDRESS SOLD_TO_CONTACT1_EMAIL
, SOLD_TO_CONTACT_V.PHONE_NUMBER SOLD_TO_CONTACT1_PHONE
, NULL SOLD_TO_CONTACT1_FAX
, NVL(USED_BY_PARTY.PARTY_NAME
, END_CUST_PARTY.PARTY_NAME) USED_BY_BUSINESS_NAME
, USED_BY_PARTY.DUNS_NUMBER USED_BY_GBL_BUSINESS_ID
, USED_BY_PARTY.CATEGORY_CODE USED_BY_GBL_SUPPLY_CHAIN_CODE
, NULL USED_BY_PROP_BUSINESS_ID
, NULL USED_BY_PROP_DOMAIN_ID1
, NULL USED_BY_PROP_ID_AUTHORITY1
, NULL USED_BY_PROP_DOMAIN_ID2
, NULL USED_BY_PROP_ID_AUTHORITY2
, NULL USED_BY_PROP_LOC_ID
, NULL USED_BY_GBL_LOC_ID
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PTNR_CLASS_CODE') USED_BY_GBL_PARTNER_CLASS_CODE
, USED_BY_PARTY.ADDRESS1 USED_BY_ADDRESS_LINE1
, USED_BY_PARTY.ADDRESS2 USED_BY_ADDRESS_LINE2
, DECODE(USED_BY_PARTY.ADDRESS3
, NULL
, USED_BY_PARTY.ADDRESS4
, USED_BY_PARTY.ADDRESS3 || DECODE(USED_BY_PARTY.ADDRESS4
, NULL
, NULL
, '
, ' || USED_BY_PARTY.ADDRESS4)) USED_BY_ADDRESS_LINE3
, USED_BY_PARTY.CITY USED_BY_CITY_NAME
, USED_BY_PARTY.STATE USED_BY_REGION_NAME
, USED_BY_PARTY.POSTAL_CODE USED_BY_NATIONAL_POSTAL_CODE
, USED_BY_PARTY.COUNTRY USED_BY_GBL_COUNTRY_CODE
, NULL USED_BY_POBOX_ID
, USED_BY_CONTACT_V.NAME USED_BY_CONTACT1_NAME
, USED_BY_CONTACT_V.EMAIL_ADDRESS USED_BY_CONTACT1_EMAIL
, USED_BY_CONTACT_V.PHONE_NUMBER USED_BY_CONTACT1_PHONE
, NULL USED_BY_CONTACT1_FAX
, SYSDATE SHIP_FRM_SD_CLAIM_REQUEST_DATE
, HDR.BATCH_NUMBER SHIP_FRM_SD_CLAIM_REQUEST_ID
, NULL CREDIT_REFERENCE_ID
, HDR.CLAIM_NUMBER DEBIT_REFERENCE_ID
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_DOC_REF_CODE_BATCH') GBL_DOC_REF_TYPE_CODE_BATCH
, LINES.BATCH_LINE_NUMBER BATCH_LINE_NUMBER
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_DOC_REF_CODE_ORDER') GBL_DOC_REF_TYPE_CODE_ORDER
, LINES.ORDER_DATE ORDER_DATE
, OEL.LINE_NUMBER ORDER_LINE_NUMBER
, OEH.ORDER_NUMBER ORDER_NUMBER
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_DOC_REF_CODE_INV') GBL_DOC_REF_TYPE_CODE_INVOICE
, SYSDATE INVOICE_DATE
, LINES.INVOICE_LINE_NUMBER INVOICE_LINE_NUMBER
, LINES.INVOICE_NUMBER INVOICE_NUMBER
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PRICE_CODE_LIST') GBL_PRICE_TYPE_CODE_LIST
, LINES.LIST_PRICE_CURRENCY_CODE COST_CURRENCYCODE
, LINES.LIST_PRICE COST_MONETARY_AMOUNT
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PRICE_CODE_AGR') GBL_PRICE_TYPE_CODE_AGREEMENT
, LINES.AGREEMENT_CURRENCY_CODE AUTH_COST_CURRENCY_CODE
, LINES.AGREEMENT_PRICE AUTH_COST_MONETARY_AMOUNT
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PRICE_CODE_RESALE') GBL_PRICE_TYPE_CODE_RESALE
, LINES.RESALE_PRICE_CURRENCY_CODE RESALE_CURRENCY_CODE
, LINES.RESALES_PRICE RESALE_MONETARY_AMOUNT
, LINES.SHIPPED_QUANTITY_UOM GBL_UOM
, LINES.STATUS_CODE GBL_CLAIM_DISPOSITION_CODE
, NULL DISPUTE_CODE1
, NULL DISPUTE_CODE2
, NULL DISPUTE_CODE3
, NULL DISPUTE_CODE4
, NULL DISPUTE_CODE5
, NULL DISPUTE_CODE6
, NULL DISPUTE_CODE7
, NULL DISPUTE_CODE8
, NULL DISPUTE_CODE9
, NULL DISPUTE_CODE10
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PTNR_CODE_DIST') GBL_PTNR_CLASS_CODE_DIST
, MTL.CONCATENATED_SEGMENTS DIST_PRODUCT_ID
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_GBL_PTNR_CODE_MANU') GBL_PTNR_CLASS_CODE_MANU
, LINES.VENDOR_ITEM_ID VENDOR_PRODUCT_ID
, OEL.ACTUAL_SHIPMENT_DATE SHIP_DATE
, LINES.QUANTITY_SHIPPED SHIPPED_QUANTITY
, LINES.CLAIM_AMOUNT_CURRENCY_CODE CLAIM_AMOUNT_CURRENCY_CODE
, LINES.LAST_SUB_CLAIM_AMOUNT LAST_SUB_CLAIM_AMOUNT
, NULL VENDOR_AUTH_LINEITEM_LINE_NBR
, NULL VENDOR_AUTH_COST_CURRENCY_CODE
, NULL VENDOR_AUTH_COST_MONETARY_AMT
, HDR.BATCH_SUBMISSION_DATE BATCH_SUBMISSION_DATE
, LINES.BATCH_LINE_ID BATCH_LINE_ID
, HDR.BATCH_ID THIS_DOC_ID
FROM OZF_SD_BATCH_HEADERS_ALL HDR
, OZF_SD_BATCH_LINES_ALL LINES
, OZF_SD_BATCH_FROM_ROLE_V FRM
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, MTL_SYSTEM_ITEMS_B_KFV MTL
, HZ_PARTIES END_CUST_PARTY
, HZ_CUST_ACCT_SITES_ALL SOLD_TO_ACCT_SITE
, HZ_CUST_SITE_USES_ALL SOLD_TO_ACCT_SITE_USE
, HZ_CUST_ACCOUNTS SOLD_TO_ACCT
, HZ_PARTIES SOLD_TO_PARTY
, HZ_PARTY_SITES SOLD_TO_PARTY_SITES
, HZ_LOCATIONS SOLD_TO_LOC
, OZF_SD_CONTACT_V SOLD_TO_CONTACT_V
, HZ_CUST_ACCT_SITES_ALL SHIP_TO_ACCT_SITE
, HZ_CUST_SITE_USES_ALL SHIP_TO_ACCT_SITE_USE
, HZ_CUST_ACCOUNTS SHIP_TO_ACCT
, HZ_PARTIES SHIP_TO_PARTY
, HZ_PARTY_SITES SHIP_TO_PARTY_SITES
, HZ_LOCATIONS SHIP_TO_LOC
, OZF_SD_CONTACT_V SHIP_TO_CONTACT_V
, HZ_PARTIES USED_BY_PARTY
, OZF_SD_CONTACT_V USED_BY_CONTACT_V
WHERE HDR.BATCH_ID = LINES.BATCH_ID
AND HDR.VENDOR_ID = FRM.VENDOR_ID
AND HDR.VENDOR_SITE_ID = FRM.VENDOR_SITE_ID
AND HDR.BATCH_NUMBER = FRM.BATCH_NUMBER
AND OEL.LINE_ID = LINES.ORDER_LINE_ID
AND OEL.HEADER_ID = LINES.ORDER_HEADER_ID
AND SYSDATE BETWEEN NVL(SOLD_TO_LOC.ADDRESS_EFFECTIVE_DATE
, SYSDATE)
AND NVL(SOLD_TO_LOC.ADDRESS_EXPIRATION_DATE
, SYSDATE)
AND SOLD_TO_LOC.LOCATION_ID(+) = SOLD_TO_PARTY_SITES.LOCATION_ID
AND SOLD_TO_PARTY_SITES.PARTY_SITE_ID(+) = SOLD_TO_ACCT_SITE.PARTY_SITE_ID
AND SOLD_TO_PARTY.PARTY_ID(+) = SOLD_TO_ACCT.PARTY_ID
AND SOLD_TO_ACCT.CUST_ACCOUNT_ID(+) = SOLD_TO_ACCT_SITE.CUST_ACCOUNT_ID
AND SOLD_TO_ACCT_SITE.CUST_ACCT_SITE_ID(+) = SOLD_TO_ACCT_SITE_USE.CUST_ACCT_SITE_ID
AND SOLD_TO_ACCT_SITE_USE.SITE_USE_ID(+) = LINES.SOLD_TO_SITE_USE_ID
AND SOLD_TO_CONTACT_V.CUST_ACCOUNT_ROLE_ID(+) = LINES.SOLD_TO_CONTACT_ID
AND SYSDATE BETWEEN NVL(SHIP_TO_LOC.ADDRESS_EFFECTIVE_DATE
, SYSDATE)
AND NVL(SHIP_TO_LOC.ADDRESS_EXPIRATION_DATE
, SYSDATE)
AND SHIP_TO_LOC.LOCATION_ID(+) = SHIP_TO_PARTY_SITES.LOCATION_ID
AND SHIP_TO_PARTY_SITES.PARTY_SITE_ID(+) = SHIP_TO_ACCT_SITE.PARTY_SITE_ID
AND SHIP_TO_PARTY.PARTY_ID(+) = SHIP_TO_ACCT.PARTY_ID
AND SHIP_TO_ACCT.CUST_ACCOUNT_ID(+) = SHIP_TO_ACCT_SITE.CUST_ACCOUNT_ID
AND SHIP_TO_ACCT_SITE.CUST_ACCT_SITE_ID(+) = SHIP_TO_ACCT_SITE_USE.CUST_ACCT_SITE_ID
AND SHIP_TO_ACCT_SITE_USE.SITE_USE_ID(+) = LINES.SHIP_TO_ORG_ID
AND SHIP_TO_CONTACT_V.CUST_ACCOUNT_ROLE_ID(+) = LINES.SHIP_TO_CONTACT_ID
AND USED_BY_PARTY.PARTY_ID(+) = LINES.END_CUSTOMER_ID
AND USED_BY_CONTACT_V.CUST_ACCOUNT_ROLE_ID(+) = LINES.END_CUSTOMER_CONTACT_ID
AND LINES.END_CUSTOMER_ID = END_CUST_PARTY.PARTY_ID(+)
AND LINES.PURGE_FLAG <> 'Y'
AND LINES.PROCESS_FEED_FLAG = 'Y'
AND OEH.HEADER_ID = LINES.ORDER_HEADER_ID
AND MTL.ORGANIZATION_ID = LINES.ORG_ID
AND MTL.INVENTORY_ITEM_ID = LINES.ITEM_ID