ERP SQL

SCS:- Non-SCS AR Invoices did not create as AP Invoices in SCS OU.

SELECT ar.customer_trx_id,

ar.creation_date,

ar.last_update_date,

ar.trx_number,

ar.attribute11,

ar.payment_trxn_extension_id

FROM apps.RA_CUSTOMER_TRX_ALL ar, apps.ar_payment_schedules_all apsa

WHERE ar.customer_trx_id = apsa.customer_trx_id

AND ar.attribute15 = 'INTER / INTRA OU'

AND apsa.class = 'INV'

-- AND ar.payment_trxn_extension_id IS NOT NULL - (Creidt Card Invoices from WDI)

AND NOT EXISTS

(SELECT 'X'

FROM apps.xxar_edi810_ar_inv_header_stg arstg

WHERE arstg.invoice_number = ar.trx_number)

ORDER BY creation_date;

Fulfilled vs Invoiced Qty

SELECT ooh.creation_date "ORDER_CREATION_DATE",

ooh.order_number,

ool.line_id,

ool.creation_Date "ORDER_LINE_CREATION_DATE",

ool.line_number,

hca.account_name,

ooh.attribute11 selling_branch,

msib.segment1,

ool.ordered_quantity,

ool.fulfilled_quantity,

ool.shipped_quantity,

rctl.quantity_invoiced,

rctl.unit_selling_price,

rct.trx_number,

rct.TRX_DATE

FROM apps.oe_order_lines_all ool,

apps.oe_order_headers_all ooh,

apps.ra_customer_trx_all rct,

apps.ra_customer_trx_lines_all rctl,

apps.hz_cust_accounts hca,

apps.mtl_system_items_b msib

WHERE ool.header_id = ooh.header_id

AND ooh.sold_to_org_id = hca.cust_account_id

AND ool.line_id = rctl.interface_line_attribute6

AND ooh.order_number = rctl.sales_order

AND rct.customer_trx_id = rctl.customer_trx_id

AND ool.inventory_item_id = msib.inventory_item_id

AND ool.ship_from_org_id = msib.organization_id

AND ool.fulfilled_quantity <> rctl.quantity_invoiced

ORDER BY ooh.order_number;

RCV Transactions accounted more than once

SELECT rt.transaction_id,

rt.transaction_date,

pha.segment1,

pla.line_num,

plla.shipment_num,

rt.organization_id,

pla.attribute1,

rt.shipment_line_id,

rt.transaction_type,

rt.quantity,

rt.po_unit_price,

rt.quantity * rt.po_unit_price AS sum1

FROM apps.rcv_transactions rt,

apps.po_headers_all pha,

apps.po_lines_all pla,

apps.po_line_locations_all plla

WHERE pha.po_header_id = pla.po_header_id

AND pha.po_header_id = plla.po_header_id

AND pla.PO_LINE_ID = plla.PO_LINE_ID

AND rt.po_header_id = pha.po_header_id

AND rt.po_line_id = pla.PO_LINE_ID

AND rt.po_line_location_id = plla.line_location_id

AND rt.transaction_id IN ( SELECT rcv_transaction_id

FROM apps.rcv_accounting_events

WHERE rcv_transaction_id IS NOT NULL

GROUP BY rcv_transaction_id

HAVING COUNT (rcv_transaction_id) > 1);

RCV Transactions not accounted

SELECT rt.transaction_date,

pha.segment1,

pla.line_num,

plla.shipment_num,

pla.attribute1,

pla.attribute2,

rt.organization_id,

rt.quantity,

rt.po_unit_price,

rt.quantity * rt.po_unit_price AS sum1

FROM apps.rcv_transactions rt,

apps.po_headers_all pha,

apps.po_lines_all pla,

apps.po_line_locations_all plla,

apps.po_distributions_all pda

WHERE rt.transaction_type = 'RECEIVE'

AND rt.po_distribution_id IS NOT NULL

AND pha.po_header_id = pla.po_header_id

AND pha.po_header_id = plla.po_header_id

AND pla.PO_LINE_ID = plla.PO_LINE_ID

AND pha.po_header_id = pda.po_header_id

AND pla.po_line_id = pda.po_line_id

AND plla.LINE_LOCATION_ID = pda.line_location_id

AND rt.po_distribution_id = pda.po_distribution_id

AND rt.po_header_id = pha.po_header_id

AND rt.po_line_id = pla.PO_LINE_ID

AND rt.po_distribution_id NOT IN (SELECT DISTINCT po_distribution_id

FROM apps.rcv_accounting_events)

ORDER BY rt.transaction_id;

MTL Transactions accounted more than once

(Current Query gives both original and duplicate transaction data. Need to remove the additional records manually)

SELECT organization_id,

transaction_quantity,

transaction_cost,

transaction_quantity * transaction_cost AS sum1,

rcv_transaction_id

FROM apps.mtl_material_transactions

WHERE rcv_transaction_id IN ( SELECT rcv_transaction_id

FROM apps.mtl_material_transactions

WHERE rcv_transaction_id IS NOT NULL

GROUP BY rcv_transaction_id

HAVING COUNT (rcv_transaction_id) > 1)

ORDER BY transaction_source_id, rcv_transaction_id, transaction_id;

RCV Transactions received, but not delivered

SELECT rt.transaction_date,

pha.segment1,

pla.line_num,

plla.shipment_num,

rt.organization_id,

pla.attribute1,

rt.shipment_line_id,

rt.transaction_type,

rt.quantity,

rt.po_unit_price,

rt.quantity * rt.po_unit_price AS sum1

FROM apps.rcv_transactions rt,

apps.po_headers_all pha,

apps.po_lines_all pla,

apps.po_line_locations_all plla

WHERE pha.po_header_id = pla.po_header_id

AND pha.po_header_id = plla.po_header_id

AND pla.PO_LINE_ID = plla.PO_LINE_ID

AND rt.po_header_id = pha.po_header_id

AND rt.po_line_id = pla.PO_LINE_ID

AND rt.shipment_line_id IN

(SELECT rsl.shipment_line_id

FROM rcv_supply rsup,

rcv_shipment_lines rsl,

rcv_transactions rt,

rcv_shipment_headers rsh,

apps.po_lookup_codes plc,

apps.po_lookup_codes plc1,

apps.po_lookup_codes plc2,

apps.po_lookup_codes plc3,

apps.mtl_units_of_measure mum,

apps.mtl_units_of_measure mum1,

apps.mtl_units_of_measure mum2,

apps.mtl_units_of_measure mum3,

apps.mtl_units_of_measure mum4,

mtl_system_items_b msi,

mtl_system_items_b msi1,

mtl_transaction_reasons mtr,

hz_cust_accounts hzca,

hz_parties hzp,

oe_order_lines_all oel,

oe_order_headers_all oeh,

oe_transaction_types_tl oetl,

oe_transaction_types_all oeot,

mtl_customer_items mci,

po_lines_all pol,

po_headers_all poh,

po_line_locations_all pll,

apps.po_releases pr,

po_vendors pov,

rcv_routing_headers rrh,

hr_locations_all_tl hrl,

gl_daily_conversion_types dct,

hr_all_organization_units_tl ood,

hr_all_organization_units_tl ood1,

mtl_parameters mp_to,

mtl_parameters mp_from

WHERE rsup.supply_type_code = 'RECEIVING'

AND rsl.shipment_line_id = rsup.shipment_line_id

AND rt.transaction_id = rsup.rcv_transaction_id

AND rt.transaction_type != 'UNORDERED'

AND rrh.routing_header_id(+) = rt.routing_header_id

AND rsh.shipment_header_id = rsup.shipment_header_id

AND rt.transaction_type = plc.lookup_code

AND plc.lookup_type = 'RCV TRANSACTION TYPE'

AND plc1.lookup_type(+) = 'INSPECTION STATUS'

AND plc1.lookup_code(+) = rt.inspection_status_code

AND plc2.lookup_type(+) = 'RCV DESTINATION TYPE'

AND plc2.lookup_code(+) = rsup.destination_type_code

AND oel.line_id(+) = rsup.oe_order_line_id

AND oeh.header_id(+) = rsup.oe_order_header_id

AND oel.line_type_id = oeot.transaction_type_id(+)

AND oeot.transaction_type_id =

oetl.transaction_type_id(+)

AND oetl.LANGUAGE(+) = USERENV ('LANG')

AND oeot.transaction_type_code(+) = 'LINE'

AND hzca.cust_account_id(+) = rsh.customer_id

AND hzca.party_id = hzp.party_id(+)

AND oel.ordered_item_id = mci.customer_item_id(+)

AND pol.po_line_id(+) = rsup.po_line_id

AND poh.po_header_id(+) = rsup.po_header_id

AND pov.vendor_id(+) = rsh.vendor_id

AND pll.line_location_id(+) = rsup.po_line_location_id

AND mum.unit_of_measure = rsup.unit_of_measure

AND mum2.unit_of_measure(+) =

pol.unit_meas_lookup_code

AND (mum4.unit_of_measure(+) =

pll.secondary_unit_of_measure)

AND (mum3.uom_code(+) = oel.ordered_quantity_uom2)

AND msi.organization_id(+) = rsup.to_organization_id

AND msi.inventory_item_id(+) = rsup.item_id

AND mum1.unit_of_measure(+) =

msi.primary_unit_of_measure

AND msi1.organization_id(+) =

rsup.from_organization_id

AND msi1.inventory_item_id(+) = rsup.item_id

AND pr.po_release_id(+) = rsup.po_release_id

AND mtr.reason_id(+) = rt.reason_id

AND hrl.location_id(+) = rt.location_id

AND hrl.LANGUAGE(+) = USERENV ('LANG')

AND ood.organization_id(+) = rsl.from_organization_id

AND ood.LANGUAGE(+) = USERENV ('LANG')

AND ood1.organization_id(+) = rt.location_id

AND ood1.LANGUAGE(+) = USERENV ('LANG')

AND DECODE (rt.source_document_code,

'PO', 'PO TYPE',

'SHIPMENT SOURCE TYPE') =

plc3.lookup_type

AND DECODE (rt.source_document_code,

'PO', poh.type_lookup_code,

rsh.receipt_source_code) =

plc3.lookup_code

AND dct.conversion_type(+) =

rt.currency_conversion_type

AND rsl.TO_ORGANIZATION_ID = mp_to.ORGANIZATION_ID

AND rsl.FROM_ORGANIZATION_ID =

mp_from.ORGANIZATION_ID(+))

ORDER BY rt.shipment_line_id, rt.transaction_type;

Channel Revenue Management

• • • Open Claims

SELECT ooha.order_number,

ofuab.order_line_id,

ofuab.amount,

ofuab.org_id,

asl.vendor_name

FROM apps.ozf_funds_utilized_all_b ofuab,

apps.oe_order_lines_all oola,

apps.oe_order_headers_all ooha,

apps.ozf_sd_request_headers_all_b osrhab,

apps.ozf_Sd_batch_lines_all osbla,

apps.ap_suppliers asl,

apps.ozf_claims_all oca

WHERE ofuab.order_line_id = oola.line_id

AND osbla.batch_id = oca.batch_id

AND osbla.utilization_id = ofuab.utilization_id

AND oola.header_id = ooha.header_id

AND osrhab.offer_id = ofuab.plan_id

AND ofuab.org_id = osrhab.ORG_ID

AND osrhab.supplier_id = asl.vendor_id

AND oca.status_code = 'OPEN'

AND ofuab.creation_date < '01-AUG-2018';

• • • • Pending Accruals (No Batch)

SELECT ooha.order_number,

ofuab.order_line_id,

ofuab.amount,

ofuab.org_id,

asl.vendor_name

FROM apps.ozf_funds_utilized_all_b ofuab,

apps.oe_order_lines_all oola,

apps.oe_order_headers_all ooha,

apps.ozf_sd_request_headers_all_b osrhab,

apps.ap_suppliers asl

WHERE ofuab.order_line_id = oola.line_id

AND oola.header_id = ooha.header_id

AND osrhab.offer_id = ofuab.plan_id

AND ofuab.org_id = osrhab.ORG_ID

AND osrhab.supplier_id = asl.vendor_id

AND ofuab.utilization_id NOT IN

(SELECT utilization_id FROM apps.ozf_sd_batch_lines_all)

AND ofuab.creation_date < '01-AUG-2018'

ORDER BY oola.line_id ASC;

Claims & Payments with UDA

SELECT oeh.attribute11

selling_Branch,

oos.name,

oeh.order_number,

oel.line_number || '.' || oel.shipment_number

order_line,

otth.name

order_type,

ott.name

line_type,

Msi.Segment1

Part_Number,

(SELECT Mcb.Segment1

FROM Mtl_Item_Categories Mic,

Mtl_Categories_B Mcb,

Mtl_Category_Sets_Tl Mcs

WHERE 1 = 1

AND Mcs.Category_Set_Name = 'WESCO TRADE SIM'

AND Mcs.Language = 'US'

AND Mic.Category_Set_Id = Mcs.Category_Set_Id

AND Mcb.Category_Id = Mic.Category_Id

AND Mic.Inventory_Item_Id = oel.inventory_item_id

AND Mic.Organization_Id = 84)

SIM,

(SELECT Mcb.Segment1

FROM Mtl_Item_Categories Mic,

Mtl_Categories_B Mcb,

Mtl_Category_Sets_Tl Mcs

WHERE 1 = 1

AND Mcs.Category_Set_Name = 'WESCO MFG Prod Catalog'

AND Mcs.Language = 'US'

AND Mic.Category_Set_Id = Mcs.Category_Set_Id

AND Mcb.Category_Id = Mic.Category_Id

AND Mic.Inventory_Item_Id = oel.inventory_item_id

AND Mic.Organization_Id = 84)

Catalog_Number,

Msi.Description,

xcsua.cust_account_number

customer_number,

xcsua.party_name

customer_name,

xcsua.location

"Customer Site Use Id ",

xcsua.site_use_id

customer_bill_To,

DECODE (LENGTH (SUBSTR (hcasa.orig_system_reference, 5, 5)),

5, SUBSTR (hcasa.orig_system_reference, 5, 5),

NULL)

DPC,

DECODE (LENGTH (SUBSTR (hcsua.attribute7, 1, 4)),

4, hcsua.attribute7,

NULL)

"Global_account ",

(SELECT INTERNAL_SALES_REP

FROM APPS.XXONT_SWB_BRANCH_SLSREP_TBL

WHERE SITE_USE_ID = OEL.INVOICE_TO_ORG_ID

AND SELLING_BRANCH = OEH.ATTRIBUTE11)

ISR_NAME,

(SELECT EXTERNAL_SALES_REP

FROM APPS.XXONT_SWB_BRANCH_SLSREP_TBL

WHERE SITE_USE_ID = OEL.INVOICE_TO_ORG_ID

AND SELLING_BRANCH = OEH.ATTRIBUTE11)

OSR_NAME,

oel.ordered_quantity,

Obl.Quantity_Shipped,

DECODE (Obl.Shipped_Quantity_Uom,

'EA', 'Each',

Obl.Shipped_Quantity_Uom)

Uom,

aps.vendor_name

"SPA Supplier Name",

assa.vendor_site_code

"SPA Supplier Site",

CASE

WHEN Oolaeb.C_EXT_ATTR4 IS NOT NULL

THEN

oolaeb.c_ext_attr4

ELSE

(SELECT NAME

FROM qp_list_headers qlh

WHERE qlh.list_header_id = oel.price_list_id)

END

"Customer Contract",

CASE

WHEN oolaeb.c_ext_attr7 IS NOT NULL

THEN

oolaeb.c_ext_attr7

WHEN oolaeb.n_ext_attr4 IS NOT NULL

THEN

(SELECT PRODUCT_ATTR_VALUE

FROM qp_pricing_attributes qp

WHERE qp.list_line_id = TO_NUMBER (oolaeb.n_ext_attr4)

AND qp.list_header_id = oel.price_list_id)

ELSE

NULL

END

"Cust contract identifer type",

CASE

WHEN oolaeb.c_ext_attr6 IS NOT NULL

THEN

oolaeb.c_ext_attr6

WHEN oolaeb.n_ext_attr4 IS NOT NULL

THEN

(SELECT qsv.user_segment_name

FROM qp_pricing_attributes qp,

qp_prc_contexts_v qpc,

qp_segments_v qsv

WHERE qp.list_line_id = TO_NUMBER (oolaeb.n_ext_attr4)

AND qp.list_header_id = oel.price_list_id

AND qpc.prc_context_code =

qp.product_attribute_context

AND qpc.prc_context_id = qsv.prc_context_id

AND qsv.segment_mapping_column = qp.product_attribute)

ELSE

NULL

END

"Cust Contract Identifier Value",

SPA_AUTH_NAME

"SPA Auth Name",

SPA_REF_IDENTIFIER

"SPA Identifier Type",

SPA_REF_ID_VALUE

"SPA Identifer Value",

oel.unit_Selling_price

"Unit Selling Price",

oel.attribute2

replacement_cost,

spa_replacement_cost

"SPA Unit Replacement CostAmt",

spa_claim_amount

"SPA Unit Claim Amount",

pw.spa_cost

"SPA Cost",

CASE WHEN pw.source LIKE '%RETRO%' THEN 'RETRO' ELSE 'CLAIM' END

claim_source,

ROUND (Obl.Claim_Amount, 2)

Claim_Amount,

(SELECT DISTINCT h.trx_number

FROM apps.ra_customer_trx_all h, ra_customer_trx_lines_all l

WHERE h.interface_header_attribute1 = TO_CHAR (oeh.order_number)

AND NVL (h.interface_header_attribute1, -1) =

NVL (l.INTERFACE_LINE_ATTRIBUTE1, -1)

AND h.customer_trx_id = l.customer_trx_id

AND NVL (l.INTERFACE_LINE_ATTRIBUTE6, -1) = oel.line_id)

trx_number,

(SELECT DISTINCT h.trx_date

FROM apps.ra_customer_trx_all h, ra_customer_trx_lines_all l

WHERE h.interface_header_attribute1 = TO_CHAR (oeh.order_number)

AND NVL (h.interface_header_attribute1, -1) =

NVL (l.INTERFACE_LINE_ATTRIBUTE1, -1)

AND h.customer_trx_id = l.customer_trx_id

AND NVL (l.INTERFACE_LINE_ATTRIBUTE6, -1) = oel.line_id)

Invoice_Date,

obh.claim_number,

aca.check_number,

aca.status_lookup_code,

aca.check_date,

pw.COST_IDENTIFIER_ID

FROM ozf_sd_batch_headers_all obh,

ozf_sd_batch_lines_all obl,

oe_order_headers_all oeh,

oe_order_lines_all oel,

xxap_cust_site_uses_all_v xcsua,

mtl_system_items_b msi,

oe_transaction_types_tl otth,

oe_transaction_types_tl ott,

mtl_parameters mp,

ap_invoice_payments_all aip,

ap_invoices_all aia,

ap_checks_all aca,

ozf_funds_utilized_all_b ofu,

ozf_claim_types_all_vl oct,

apps.ego_mtl_sy_items_ext_b emsieb,

apps.oe_order_sources oos,

apps.ONT_WESXOEPL_AGV pw,

apps.hz_cust_acct_sites_all hcasa,

apps.hz_cust_site_uses_all hcsua,

apps.ap_suppliers aps,

apps.ap_supplier_sites_all assa,

oe_order_lines_all_ext_b oolaeb

WHERE oeh.booked_flag = 'Y'

AND Oeh.Header_Id = Oel.Header_Id

AND Obl.Order_Header_Id = Oel.Header_Id

AND Obl.Order_Line_Id = Oel.Line_Id

AND obl.batch_id = obh.batch_id

AND otth.transaction_type_id = oeh.order_type_id

AND ott.transaction_type_id = oel.line_type_id

AND Msi.Inventory_Item_Id = oel.inventory_item_id

AND Msi.Organization_Id = Oel.Ship_From_Org_Id

AND Mp.Organization_Id = Oel.Ship_From_Org_Id

AND oel.order_source_id = oos.order_Source_id

AND aia.invoice_num = obh.claim_number

AND aca.check_id(+) = aip.check_id

AND aip.invoice_id(+) = aia.invoice_id

AND UPPER (aia.SOURCE) = 'CLAIMS'

AND aca.status_lookup_code(+) <> 'VOIDED'

--AND rca.customer_trx_id(+) = obl.invoice_number

--and rca.interface_header_attribute1=to_char(oeh.order_number)

AND oeh.invoice_to_org_id = xcsua.location

AND ofu.utilization_id = obl.utilization_id

AND ofu.adjustment_type_id = oct.claim_type_id

AND Emsieb.Attr_Group_Id = 703

AND Emsieb.Inventory_Item_Id = oel.Inventory_Item_Id

AND Emsieb.Organization_Id = 84

AND pw.line_id = oel.line_id

AND pw.accrual_status = 'COMPLETELY_PROCESSED'

AND hcasa.cust_account_id = oeh.sold_To_org_id

AND hcsua.site_use_id = oeh.invoice_to_org_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND aps.vendor_id = obh.vendor_id

AND assa.vendor_site_id = obh.vendor_site_id

AND aps.vendor_id = assa.vendor_id

--and oeh.order_number='2000000998'

AND Oolaeb.Attr_Group_Id(+) = 714

AND Oolaeb.line_id(+) = oel.line_id

Customers Query for Vertex

SELECT DISTINCT hp.party_name customer_name,

hca.account_number customer_acc_number,

hps.party_site_number site_number,

hcasa.orig_system_reference orig_system_add_ref,

hou.name operating_unit,

hcsua.site_use_code,

hl.address1,

hl.address2 " Address2",

hl.address3 "Address3",

hl.city,

hl.state,

hl.postal_code,

hl.country,

hl.province,

hl.county

FROM apps.XXAR_CUST_ACCT_STG xcas,

apps.XXAR_CUST_SITE_STG xcss,

apps.HZ_PARTIES hp,

apps.HZ_PARTY_SITES hps,

apps.HZ_CUST_ACCOUNTS hca,

apps.HZ_CUST_ACCT_SITES_ALL hcasa,

apps.HZ_CUST_SITE_USES_ALL hcsua,

apps.HR_OPERATING_UNITS hou,

apps.HZ_LOCATIONS hl

WHERE xcas.orig_system_customer_ref = xcss.orig_system_customer_ref

AND xcas.wesnet_branch_number = xcss.wesnet_branch_number

AND xcas.orig_system_customer_ref = hca.orig_system_reference

AND xcss.orig_system_address_ref = hcasa.orig_system_reference

AND xcss.site_usage = hcsua.site_use_code

AND xcss.org_id = hcasa.org_id

AND hp.party_id = hps.party_id

AND hps.party_id = hca.party_id

AND hp.party_id = hca.party_id

AND hca.cust_account_id = hcasa.cust_account_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

AND hcasa.org_id = hcsua.org_id

AND hcasa.org_id = hou.organization_id

AND hl.location_id = hps.location_id

AND hcsua.site_use_code in ('BILL_TO','SHIP_TO')

AND hcasa.orig_system_reference like '1841%'

AND hou.name = 'CSC US OU'

ORDER BY OPERATING_UNIT DESC;

Account Alias Details

SELECT CID.ORGANIZATION_CODE,

HRO.NAME,

MMT.TRANSACTION_ID,

MSI.SEGMENT1 AS ITEM_NUMBER,

MSI.DESCRIPTION,

MTY.TRANSACTION_TYPE_NAME,

MGD.segment1 AS ACCOUNT_ALIAS_NAME,

TRUNC (MMT.TRANSACTION_DATE) AS TRANSACTION_DATE,

MMT.TRANSACTION_QUANTITY,

MMT.TRANSACTION_UOM,

MMT.TRANSACTION_SOURCE_NAME,

CID.UNIT_COST,

NVL (MMT.ACTUAL_COST, 0) AS ACTUAL_COST,

GCC.CONCATENATED_SEGMENTS AS ACCOUNT_CODE_COMBINATION,

CID.LINE_TYPE_NAME,

CID.BASE_TRANSACTION_VALUE,

MMT.subinventory_code,

MMT.transaction_reference

FROM apps.MTL_MATERIAL_TRANSACTIONS MMT,

apps.MTL_SYSTEM_ITEMS_B MSI,

apps.HR_ALL_ORGANIZATION_UNITS HRO,

apps.MTL_TRANSACTION_TYPES MTY,

apps.CST_INV_DISTRIBUTION_V CID,

apps.GL_CODE_COMBINATIONS_KFV GCC,

apps.MTL_GENERIC_DISPOSITIONS MGD,

apps.FND_LOOKUP_VALUES FLV

WHERE MMT.TRANSACTION_TYPE_ID IN (31, 41)

AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID

AND MMT.ORGANIZATION_ID = HRO.ORGANIZATION_ID

AND MMT.TRANSACTION_TYPE_ID = MTY.TRANSACTION_TYPE_ID

AND TRUNC (MMT.TRANSACTION_DATE) BETWEEN :FROM_DATE AND :TO_DATE

AND MMT.TRANSACTION_ID = CID.TRANSACTION_ID

AND MMT.ORGANIZATION_ID = CID.ORGANIZATION_ID

AND CID.REFERENCE_ACCOUNT = GCC.CODE_COMBINATION_ID

AND gcc.code_combination_id = MGD.distribution_account

AND MGD.segment1 = FLV.LOOKUP_CODE

AND FLV.LOOKUP_TYPE = 'XXINV_ACCT_ALIAS_DETAIL_EXT' -- (select distinct distribution_account from MTL_GENERIC_DISPOSITIONS where segment1 like '08%')

AND SYSDATE BETWEEN NVL (FLV.start_date_active, SYSDATE)

AND NVL (FLV.END_DATE_ACTIVE, SYSDATE)

AND FLV.ENABLED_FLAG = 'Y'

AND FLV.language = USERENV ('LANG')

AND MGD.ENABLED_FLAG = 'Y'

AND CID.MSI_Organization_id = MGD.organization_id

ORDER BY MMT.TRANSACTION_ID, CID.BASE_TRANSACTION_VALUE DESC;

DS Accrual Report

SELECT *

FROM ( SELECT po_number,

supplier_name,

supplier_site, --ship_to_location,

po_line_number,

po_line_type,

AP_Invoice_Number,

AP_Invoice_date,

AP_Invoice_Create_date,

AP_Invoice_Line_Amt,

AP_Invoice_Terms,

receipt_num,

po_shipment_line_num,

po_line_receipt_date,

attribute6 branch,

address1

|| ' '

|| DECODE (address2, NULL, NULL, address2 || ' ')

|| DECODE (address3, NULL, NULL, address3 || ' ')

|| DECODE (address4, NULL, NULL, address4 || ' ')

|| city

|| ', '

|| DECODE (state, NULL, NULL, state)

|| ' '

|| DECODE (postal_code, NULL, NULL, postal_code) ship_to

FROM (SELECT DISTINCT

ph.segment1

po_number,

aps.vendor_name

supplier_name,

apss.vendor_site_code

supplier_site,

hl.location_code

ship_to_location,

pl.line_num

po_line_number,

ffvv_line_sts.description

po_line_type,

aia.invoice_num

AP_Invoice_Number,

aia.invoice_date

AP_Invoice_date,

aia.creation_date

AP_Invoice_Create_date,

aila.amount

AP_Invoice_Line_Amt,

(SELECT terms.name

FROM apps.ap_terms_tl terms

WHERE terms.term_id = aia.terms_id

AND terms.LANGUAGE(+) = USERENV ('LANG')

AND terms.enabled_flag(+) = 'Y')

AP_Invoice_Terms,

rsh.receipt_num,

rsl.line_num

po_shipment_line_num,

rsl.last_update_date

po_line_receipt_date,

mp.attribute6,

hl_shipto.address1,

hl_shipto.address2,

hl_shipto.address3,

hl_shipto.address4,

hl_shipto.city,

hl_shipto.postal_code,

hl_shipto.state,

hl_shipto.province,

hl_shipto.country

FROM apps.po_line_locations_all pll,

apps.po_headers_all ph,

apps.po_lines_all pl,

apps.fnd_flex_value_sets ffvs_line_sts,

apps.fnd_flex_values_vl ffvv_line_sts,

apps.ap_suppliers aps,

apps.ap_supplier_sites_all apss,

apps.hr_locations_all hl,

apps.po_distributions_all pda,

apps.ap_invoice_distributions_all aida,

apps.ap_invoices_all aia,

apps.ap_invoice_lines_all aila,

apps.rcv_shipment_lines rsl,

apps.rcv_shipment_headers rsh,

apps.mtl_parameters mp,

hz_locations hl_shipto

WHERE pl.po_header_id = ph.po_header_id

--AND ph.segment1 like '4000000181'

AND pll.po_header_id = pl.po_header_id

AND pll.po_line_id = pl.po_line_id

AND ffvs_line_sts.flex_value_set_name =

'XXPO_Line_Type'

AND ffvv_line_sts.flex_value_set_id =

ffvs_line_sts.flex_value_set_id

AND ffvv_line_sts.flex_value = pl.attribute1

AND ffvv_line_sts.flex_value = 'DS'

AND aps.vendor_id = ph.vendor_id

AND apss.vendor_site_id = ph.vendor_site_id

AND hl.location_id = ph.ship_to_location_id

AND pda.po_header_id = pl.po_header_id

AND pda.po_line_id = pl.po_line_id

AND pda.gl_cancelled_date IS NULL

AND pda.quantity_billed > 0

AND aida.po_distribution_id(+) =

pda.po_distribution_id

AND aia.invoice_id(+) = aida.invoice_id

AND aila.invoice_id(+) = aia.invoice_id --CAB

AND aila.po_line_id = pl.po_line_id --CAB

AND aila.quantity_invoiced <> 0 --CAB

AND rsl.comments(+) =

aila.invoice_id || '.' || aila.line_number --CAB

AND rsh.shipment_header_id(+) = rsl.shipment_header_id --CAB

--AND rsl.po_header_id = pl.po_header_id

--AND rsl.po_line_id = pl.po_line_id

--AND rsh.shipment_header_id = rsl.shipment_header_id

AND mp.organization_id = hl.inventory_organization_id

AND pda.line_location_id = pll.line_location_id

AND hl_shipto.location_id = pll.ship_to_location_id

AND aia.creation_date >=

TO_DATE ('01-APR-2019', 'DD-MON-YYYY')

AND aia.creation_date <

TO_DATE ('01-JUL-2019', 'DD-MON-YYYY'))

--GROUP BY po_number, supplier_name, supplier_site, --, ship_to_location,

-- po_line_number, po_line_type,

-- receipt_num, po_shipment_line_num, po_line_receipt_date, attribute6,

-- address1, address2, address3, address4, city, state, postal_code

ORDER BY 8, 1, 4)

--WHERE po_number = '4000000103'"

Vertex Debug XML

SELECT *

FROM apps.vertex_oic_debug_xml

WHERE request_name = 'INVOICE' AND xml LIKE '%2000008376%'

ORDER BY 1 DESC;

Customer, Account, Site & Site Usage Details

SELECT DISTINCT

hp.Party_Number ERP_Party_Number,

hp.PARTY_NAME ERP_PARTY_NAME,

hp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE,

hp.DUNS_NUMBER DUNS_NUMBER,

-- hp.DUNS_NUMBER_C DUNS_NUMBER_C,

hca.account_number ERP_Account_No,

hca.account_name ERP_Account_Description,

hca.status ERP_Account_Status,

hcp1.credit_checking account_credit_check,

hcpa1.currency_code Account_Currency,

hcpa1.overall_credit_limit account_credit_limit,

hcpa1.trx_credit_limit account_order_limit,

aterm.name Account_Profile_Term,

hps.party_site_number ERP_Site_No,

hl.address1

|| ','

|| hl.address2

|| ','

|| hl.address3

|| ','

|| hl.address4

|| ','

|| hl.city

|| ','

|| hl.county

|| ','

|| hl.state

|| ','

|| hl.country

|| ','

|| hl.postal_code AS Address,

hps.addressee,

hou.name ERP_OU,

hcsua.site_use_code ERP_Site_Usage,

hcsua.location ERP_Location_No,

hcsua.bill_to_site_use_id ERP_Related_Bill_To_Location,

cp.credit_checking site_credit_check,

hcpa.currency_code Site_Currency,

hcpa.overall_credit_limit Site_Overall_Credit_Limit,

hcpa.trx_credit_limit Site_Order_Credit_Limit,

col.name Collector,

res.source_name Credit_Analyst,

term.name Site_Profile_Term,

cp.send_statements Send_Statements_Flag,

astc.name Statement_Cycle,

cp.credit_classification,

cp.percent_collectable Collectable_Percent,

hcsua.attribute1 Inv_Del_Option_Kit_Exp_Flag,

hcsua.attribute2 Inv_Del_Add_Packslip_Flag,

hcsua.attribute14 Inv_Del_Method,

hcsua.attribute3 Stm_Del_Option_Carrier_Ac,

hcsua.attribute4 Stm_Delivery_Address,

hcsua.attribute5 Attention_To,

hcsua.attribute6 Special_Instructions,

hcsua.attribute7 National_Account_No,

hcsua.attribute8 Global_Account_Flag,

hcsua.attribute9 Top_Customer_Flag,

hcsua.attribute10 Securitization_Code,

hcsua.attribute11 FS_Industry_Code,

hcsua.attribute12 Remit_To,

hcsua.ATTRIBUTE13 WESCO_Inter_Branch,

hcsua.ATTRIBUTE15 Pricing_Rounding_Method,

hcsua.ATTRIBUTE16 Pricing_Precision,

hcsua.ATTRIBUTE17 Ecommerce_Site,

cp.Interest_charges Late_Charge_Flag,

cp.payment_grace_days Receipt_Grace_Days,

hcpa.interest_type Interest_Type,

hcpa.Interest_rate Interest_Rate,

hcpa.min_fc_balance_overdue_type cust_balance_type,

hcpa.min_fc_balance_amount cust_balance_amount

FROM apps.HZ_PARTY_SITES hps,

apps.HZ_CUST_ACCOUNTS hca,

apps.HZ_CUST_ACCT_SITES_ALL hcasa,

apps.HZ_CUST_SITE_USES_ALL hcsua,

apps.hr_operating_units hou,

apps.hz_customer_profiles cp,

apps.HZ_CUST_PROFILE_AMTS hcpa,

apps.ra_terms term,

apps.ra_terms aterm,

apps.AR_STATEMENT_CYCLES astc,

apps.ar_collectors col,

apps.jtf_rs_resource_extns res,

apps.hz_locations hl,

apps.hz_parties hp,

apps.hz_customer_profiles hcp1,

apps.hz_cust_profile_amts hcpa1

WHERE hca.orig_system_reference NOT LIKE 'STP%'

AND hcp1.cust_account_id(+) = hca.cust_account_id

AND hcp1.site_use_id(+) IS NULL

AND hcpa1.cust_account_profile_id(+) = hcp1.cust_account_profile_id

AND hcp1.standard_terms = aterm.term_id(+)

AND hp.party_id = hps.party_id

AND hp.party_id = hca.party_id

AND hps.location_id = hl.location_id

AND hou.organization_id = hcsua.org_id

AND hca.cust_account_id = hcasa.cust_account_id

AND hps.party_site_id = hcasa.party_site_id

AND hps.party_id = hca.party_id

AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

AND hcasa.org_id = hcsua.org_id

AND cp.site_use_id(+) = hcsua.location

AND cp.site_use_id = hcpa.site_use_id(+)

AND cp.collector_id = col.collector_id(+)

AND cp.standard_terms = term.term_id(+)

AND cp.statement_cycle_id = astc.statement_cycle_id(+)

AND cp.credit_analyst_id = res.resource_id(+)

ORDER BY hca.account_number, hps.party_site_number, hcsua.site_use_code;

Inventory Cost Adjustment

SELECT

mmt.transaction_id,

mmt.transaction_date,

mp.organization_code inv_org,

msi.segment1 item_number,

ccg.cost_group,

mtt.transaction_type_name trx_type,

gcc.segment3 adj_account,

gcc.concatenated_segments adj_acct_comb,

mmt.transaction_source_name,

mmt.prior_cost,

mmt.new_cost,

mmt.quantity_adjusted,

(-1 * mmt.quantity_adjusted * ( nvl(mmt.new_cost,0) - nvl(mmt.prior_cost,0) ) ) adj_acct_amount

FROM

apps.mtl_material_transactions mmt,

apps.mtl_transaction_types mtt,

gl_code_combinations_kfv gcc,

mtl_parameters mp,

mtl_system_items msi,

cst_cost_groups ccg

WHERE

mtt.transaction_type_id = mmt.transaction_type_id

AND mtt.transaction_type_name = 'Average cost update'

AND gcc.code_combination_id = mmt.material_account

AND mp.organization_id = mmt.organization_id

AND msi.inventory_item_id = mmt.inventory_item_id

AND msi.organization_id = mmt.organization_id

AND ccg.cost_group_id = mmt.cost_group_id

AND gcc.segment3 = '50707'

ORDER BY

mmt.transaction_id DESC;

SWB Cost Adjustment

SELECT *

FROM XXGL_COST_ADJ_ACCTNG_STG

WHERE credit_account_comb LIKE '%50707%' AND status = 'P'

Billtrust G2 Segment Missing

SELECT *

FROM apps.ra_customer_trx_all

WHERE customer_trx_id IN

(SELECT customer_trx_id

FROM apps.ra_customer_trx_lines_all a

WHERE DESCRIPTION = 'FREIGHT'

AND NOT EXISTS

(SELECT 'x'

FROM ra_customer_trx_lines_all b

WHERE a.customer_trx_line_id <>

b.customer_trx_line_id

AND DESCRIPTION <> 'FREIGHT'

AND a.customer_trx_id = b.customer_trx_id));

Invoices with Delivery Details by Branch

WITH

deliveries

AS

----------------------------------------------------------------------------

-- Get all of the deliveries for each Invoice.

-- The first union will get lines which have actual deliveries.

----------------------------------------------------------------------------

( SELECT trxl.customer_trx_id,

TO_CHAR (wnd.delivery_id) delivery_id,

TO_CHAR (wnd.delivery_id) erp_delivery_number

FROM apps.ra_customer_trx_lines_all trxl,

apps.wsh_new_deliveries wnd

WHERE trxl.line_type = 'LINE'

AND wnd.organization_id = trxl.warehouse_id

AND TO_CHAR (wnd.delivery_id) =

trxl.interface_line_attribute3

GROUP BY trxl.customer_trx_id, wnd.delivery_id

UNION

---------------------------------------------------------------------------

-- This second union will get "Bill-Only" lines where there was no actual delivery.

---------------------------------------------------------------------------

SELECT trxl.customer_trx_id,

typ.name delivery_id,

DECODE (SUBSTR (typ.name, 1, 9),

'Drop Ship', 'DRPSHP',

'BILONL') erp_delivery_number

FROM apps.ra_customer_trx_lines_all trxl,

apps.oe_order_lines_all oel,

apps.oe_transaction_types_tl typ

WHERE trxl.line_type = 'LINE'

AND oel.line_id = trxl.interface_line_attribute6

AND typ.transaction_type_id = oel.line_type_id

------------------------------------------------------------------------

-- Exclude the lines where there was an actual delivery.

------------------------------------------------------------------------

AND trxl.customer_trx_line_id NOT IN

(SELECT trxl.customer_trx_line_id

FROM apps.ra_customer_trx_lines_all trxl,

apps.wsh_new_deliveries wnd

WHERE trxl.line_type = 'LINE'

AND wnd.organization_id = trxl.warehouse_id

AND TO_CHAR (wnd.delivery_id) =

trxl.interface_line_attribute3)),

deliv_count

AS

----------------------------------------------------------------------------

-- Get the count for the number of deliveries for each Invoice.

----------------------------------------------------------------------------

( SELECT customer_trx_id, COUNT (DISTINCT delivery_id) delivery_count

FROM (SELECT trxl.customer_trx_id customer_trx_id,

TO_CHAR (wnd.delivery_id) delivery_id

FROM apps.ra_customer_trx_lines_all trxl,

apps.wsh_new_deliveries wnd

WHERE trxl.line_type = 'LINE'

AND wnd.organization_id = trxl.warehouse_id

AND TO_CHAR (wnd.delivery_id) =

trxl.interface_line_attribute3

UNION

SELECT trxl.customer_trx_id customer_trx_id,

typ.name delivery_id

FROM apps.ra_customer_trx_lines_all trxl,

apps.oe_order_lines_all oel,

apps.oe_transaction_types_tl typ

WHERE trxl.line_type = 'LINE'

AND oel.line_id = trxl.interface_line_attribute6

AND typ.transaction_type_id = oel.line_type_id

AND trxl.customer_trx_line_id NOT IN

(SELECT trxl.customer_trx_line_id

FROM apps.ra_customer_trx_lines_all trxl,

apps.wsh_new_deliveries wnd

WHERE trxl.line_type = 'LINE'

AND wnd.organization_id =

trxl.warehouse_id

AND TO_CHAR (wnd.delivery_id) =

trxl.interface_line_attribute3))

GROUP BY customer_trx_id)

SELECT (SELECT name

FROM apps.ra_batch_sources_all

WHERE org_id = trx.org_id AND batch_source_id = trx.batch_source_id)

source,

deliv_count.delivery_count,

trx.attribute11

"ERP_SELLING_BRNACH",

psa.trx_number

ERP_INVOICE_NUMBER,

deliveries.delivery_id

ACTUAL_DELIVERY_NUMBER,

DECODE (NVL (deliv_count.delivery_count, 0),

0, NULL,

1, TO_CHAR (deliveries.erp_delivery_number),

'MULTPL')

ERP_DELIVERY_NUMBER,

trx.INTERFACE_HEADER_ATTRIBUTE1

ERP_SALES_ORDER,

hca.account_number

ERP_CUSTOMER_NUMBER,

hca.account_name

ERP_CUSTOMER_NAME,

DECODE (psa.STATUS, 'OP', 'Open', 'CL', 'Closed', 'Other')

ERP_INVOICE_STATUS,

psa.AMOUNT_DUE_ORIGINAL

ERP_INVOICE_ORIGINAL_BALANCE,

psa.AMOUNT_DUE_REMAINING

ERP_CURRENT_BALANCE,

TRUNC (psa.trx_date)

ERP_INVOICE_DATE,

trx.PURCHASE_ORDER

ERP_PO_NUMBER,

psa.AMOUNT_IN_DISPUTE

ERP_DISPUTE_AMOUNT

FROM apps.ar_payment_schedules_all psa,

apps.ra_customer_trx_all trx,

apps.hz_cust_accounts hca,

deliveries,

deliv_count

WHERE psa.class = 'INV'

AND trx.customer_trx_id = psa.customer_trx_id

AND trx.org_id = psa.org_id

AND deliveries.customer_trx_id(+) = psa.customer_trx_id

AND deliv_count.customer_trx_id(+) = psa.customer_trx_id

AND hca.cust_account_id = trx.sold_to_customer_id

-- AND trx.attribute11 = '7861'

ORDER BY psa.org_id, psa.trx_number, deliveries.delivery_id;

AP Invoice Holds

SELECT a.Supplier,

a.po_number,

a.po_type,

a.po_line_num,

a.shipment_num,

a.Item,

a.Item_desc,

a.wesco_sim,

a.mfr_part_num,

a.po_unit_price,

a.ordered_quantity,

a.quantity_received,

a.quantity_billed,

a.hold_lookup_code,

a.invoice_num,

a.invoice_line,

a.invoice_source,

a.invoice_amount,

a.invoice_date,

a.creation_date,

a.quantity_invoiced,

a.inv_unit_price,

a.line_inv_amount,

a.Buyer_name,

a.Receiving_Warehouse,

a.Receiving_branch,

NVL (so_b2b.customer_name,

NVL (so_drp.customer_name, so.customer_name))

customer_name,

NVL (so_b2b.order_number, NVL (so_drp.order_number, a.so_num))

so_num,

NVL (so_b2b.hdr_status, NVL (so_drp.hdr_status, so.hdr_status))

so_hdr_status,

(SELECT jrre.source_name

FROM apps.jtf_rs_salesreps jtf, apps.jtf_rs_resource_extns jrre

WHERE jtf.resource_id = jrre.resource_id

AND jtf.salesrep_id =

NVL (so_b2b.salesrep_id,

NVL (so_drp.salesrep_id, so.salesrep_id))

AND ROWNUM = 1)

isr_name,

NVL (so_b2b.line_number, so_drp.line_number)

so_line,

NVL (so_b2b.line_status, so_drp.line_status)

so_lne_status --,so_b2b.salesrep_id bsb_isr,so_drp.salesrep_id drp_isr,so.salesrep_id isr

FROM (SELECT sup.vendor_name Supplier,

ai.invoice_num,

ail.line_number invoice_line,

ai.source invoice_source,

ai.invoice_amount,

ai.invoice_date,

ai.creation_date,

ai.invoice_id,

ah.hold_lookup_code,

ah.hold_reason,

msib.segment1 Item,

msib.description Item_desc,

emseb.c_ext_attr4 WESCO_SIM,

emseb.c_ext_attr11 mfr_part_num,

ail.quantity_invoiced,

ail.amount line_inv_amount,

ail.unit_price inv_unit_price,

pol.unit_price po_unit_price,

poh.segment1 po_number,

pol.line_num po_line_num,

pol.attribute1 po_type,

DECODE (poh.interface_source_code,

'CONVERSION', SUBSTR (pol.attribute2, 3, 6),

pol.attribute2) so_num,

poll.shipment_num,

poll.quantity ordered_quantity,

poll.quantity_received,

poll.quantity_billed,

ppf.full_name Buyer_name,

mp.organization_code Receiving_Warehouse,

mp.attribute6 Receiving_branch,

poh.Po_Header_Id,

pol.Po_Line_Id,

poll.line_location_id

FROM apps.ap_invoices_all ai,

apps.ap_holds_all ah,

apps.ap_invoice_lines_all ail,

apps.po_headers_all poh,

apps.po_lines_all pol,

apps.po_line_locations_all poll,

apps.ap_suppliers sup,

apps.mtl_system_items_b msib,

apps.mtl_parameters mp,

apps.per_all_people_f ppf,

apps.ego_mtl_sy_items_ext_b emseb,

apps.ego_attr_groups_v eag

WHERE ai.invoice_id = ah.invoice_id

AND ah.release_lookup_code IS NULL

AND ah.line_location_id = ail.po_line_location_id

AND ail.invoice_id = ai.invoice_id

AND sup.vendor_id = ai.vendor_id

AND ail.po_header_id = poh.po_header_id

AND ail.po_line_id = pol.po_line_id

AND ail.po_line_location_id = poll.line_location_id

AND mp.organization_id = poll.ship_to_organization_id

AND msib.inventory_item_id(+) = pol.item_id

AND msib.organization_id(+) = mp.organization_id

AND poh.agent_id = ppf.person_id

AND emseb.attr_group_id = eag.attr_group_id

AND eag.attr_group_name = 'WESCO_MDM'

AND emseb.inventory_item_id(+) = msib.inventory_item_id

-- AND ai.invoice_num = '5602940806'

AND SYSDATE BETWEEN ppf.effective_start_date

AND ppf.effective_end_date) a,

(SELECT hzp.party_name customer_name,

ooh.order_number,

ool.line_number || '.' || ool.shipment_number line_number,

ool.line_id,

ooh.salesrep_id,

UPPER (ot.name) line_type,

ooh.flow_status_code hdr_status,

ool.flow_status_code line_status,

Ods.Po_Header_Id,

Ods.Po_Line_Id

FROM apps.oe_order_headers_all ooh,

apps.oe_order_lines_all ool,

apps.Oe_Transaction_Types_Tl Ot,

apps.hz_cust_accounts_all hz,

apps.hz_parties hzp,

apps.Oe_Drop_Ship_Sources Ods

WHERE ool.header_id = ooh.header_id

AND hz.cust_account_id = ooh.sold_to_org_id

AND hz.party_id = hzp.party_id

AND ot.transaction_type_id = ool.line_type_id

AND ot.language = 'US'

AND UPPER (ot.name) LIKE '%DROP%'

AND Ods.Header_Id = ool.Header_Id

AND Ods.Line_Id = ool.Line_Id) so_drp,

(SELECT hzp.party_name customer_name,

ooh.order_number,

ool.line_number || '.' || ool.shipment_number line_number,

NVL (ool.split_from_line_id, ool.line_id) line_id,

ooh.salesrep_id,

UPPER (ot.name) line_type,

ooh.flow_status_code hdr_status,

ool.flow_status_code line_status,

Pda.Line_Location_Id

FROM apps.oe_order_headers_all ooh,

apps.oe_order_lines_all ool,

apps.Oe_Transaction_Types_Tl Ot,

apps.hz_cust_accounts_all hz,

apps.hz_parties hzp,

apps.po_req_distributions_all prd,

apps.po_requisition_lines_all prl,

apps.po_requisition_headers_all prh,

apps.po_distributions_all pda

WHERE ool.header_id = ooh.header_id

AND hz.cust_account_id = ooh.sold_to_org_id

AND hz.party_id = hzp.party_id

AND ot.transaction_type_id = ool.line_type_id

AND ool.line_id = Prh.Interface_Source_Line_Id

AND Prh.Requisition_Header_Id = Prl.Requisition_Header_Id

AND Prl.Requisition_Line_Id = Prd.Requisition_Line_Id

AND Pda.Req_Distribution_Id = Prd.Distribution_Id

AND ot.language = 'US'

AND UPPER (ot.name) LIKE '%BACK TO BACK%') so_b2b,

(SELECT DISTINCT hzp.party_name Customer_name,

oeh.order_number,

oeh.salesrep_id,

oeh.flow_status_code hdr_status

FROM apps.hz_cust_site_uses_all hcsu,

apps.hz_cust_acct_sites_all hcas,

apps.hz_cust_accounts_all hcaa,

apps.oe_order_headers_all oeh,

apps.hz_parties hzp,

apps.oe_order_lines_all oel

WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id

AND hcsu.site_use_code = 'BILL_TO'

AND hcas.cust_account_id = hcaa.cust_account_id

AND hcaa.party_id = hzp.party_id

AND hcsu.org_id = oeh.org_id

AND hcsu.site_use_id = oel.invoice_to_org_id

AND oel.header_id = oeh.header_id) so

WHERE 1 = 1

AND so_drp.Po_Header_Id(+) = a.Po_Header_Id

AND so_drp.Po_Line_Id(+) = a.Po_Line_Id

AND so.order_number(+) = a.so_num

AND so_b2b.Line_Location_Id(+) = a.line_location_id;