select cus.customer_number,
cus.customer_name,
ood.ORGANIZATION_CODE,
mmt.subinventory_code,
rcta.interface_header_attribute1 order_number,
msi.segment1,
msi.description,
mmt.transaction_date,
-mmt.transaction_quantity transaction_quantity,
apl.amount,
cux_17_cst_outstock_pkg.get_batchs(mmt.transaction_id) batchs
from ra_customer_trx_all rcta
,ra_batch_sources_all rbsa
,RA_CUST_TRX_TYPES_ALL rctt
,ra_customer_trx_lines_all rctl
,oe_order_lines_all ool
,ar_customers_all_v cus
,org_organization_definitions ood
,mtl_material_transactions mmt
, mtl_system_items_b msi
,ap_invoice_lines_all apl
where rcta.batch_source_id = rbsa.batch_source_id
and rbsa.name = 'Order Entry'
and rbsa.org_id = rcta.org_id
and rcta.cust_trx_type_id = rctt.cust_trx_type_id
and rcta.org_id = rctt.org_id
and rctt.name = '销售发票'
and rctl.customer_trx_id=rcta.customer_trx_id
and rctl.line_type='LINE'
and rctl.interface_line_attribute10 not in
(select ood.ORGANIZATION_ID from org_organization_definitions ood where ood.OPERATING_UNIT=rcta.org_id)
and ool.org_id=rctl.org_id
and ool.line_id=rctl.interface_line_attribute6
and cus.customer_id=rcta.sold_to_customer_id
and ood.ORGANIZATION_ID=rctl.interface_line_attribute10
and mmt.organization_id = ood.organization_id
and mmt.source_line_id = ool.line_id
and msi.organization_id=mmt.organization_id
and msi.inventory_item_id=mmt.inventory_item_id
and apl.org_id=rcta.org_id
and apl.reference_2=mmt.transaction_id
and rcta.org_id = fnd_profile.VALUE('ORG_ID')
cus.customer_name,
ood.ORGANIZATION_CODE,
mmt.subinventory_code,
rcta.interface_header_attribute1 order_number,
msi.segment1,
msi.description,
mmt.transaction_date,
-mmt.transaction_quantity transaction_quantity,
apl.amount,
cux_17_cst_outstock_pkg.get_batchs(mmt.transaction_id) batchs
from ra_customer_trx_all rcta
,ra_batch_sources_all rbsa
,RA_CUST_TRX_TYPES_ALL rctt
,ra_customer_trx_lines_all rctl
,oe_order_lines_all ool
,ar_customers_all_v cus
,org_organization_definitions ood
,mtl_material_transactions mmt
, mtl_system_items_b msi
,ap_invoice_lines_all apl
where rcta.batch_source_id = rbsa.batch_source_id
and rbsa.name = 'Order Entry'
and rbsa.org_id = rcta.org_id
and rcta.cust_trx_type_id = rctt.cust_trx_type_id
and rcta.org_id = rctt.org_id
and rctt.name = '销售发票'
and rctl.customer_trx_id=rcta.customer_trx_id
and rctl.line_type='LINE'
and rctl.interface_line_attribute10 not in
(select ood.ORGANIZATION_ID from org_organization_definitions ood where ood.OPERATING_UNIT=rcta.org_id)
and ool.org_id=rctl.org_id
and ool.line_id=rctl.interface_line_attribute6
and cus.customer_id=rcta.sold_to_customer_id
and ood.ORGANIZATION_ID=rctl.interface_line_attribute10
and mmt.organization_id = ood.organization_id
and mmt.source_line_id = ool.line_id
and msi.organization_id=mmt.organization_id
and msi.inventory_item_id=mmt.inventory_item_id
and apl.org_id=rcta.org_id
and apl.reference_2=mmt.transaction_id
and rcta.org_id = fnd_profile.VALUE('ORG_ID')