POS POSASNDATA

SELECT RHI.SHIPMENT_NUM, 'INTERFACE' STAGE, To_Char(RHI.SHIPPED_DATE,'dd-MM-yyyy hh24:mi:ss') SHIPPED_DATE, To_Char(RHI.EXPECTED_RECEIPT_DATE,'dd-MM-yyyy hh24:mi:ss') EXPECTED_RECEIPT_DATE, FREIGHT_TERMS, RHI.FREIGHT_CARRIER_CODE, RHI.FREIGHT_BILL_NUMBER, RHI.NUM_OF_CONTAINERS, RHI.BILL_OF_LADING, RHI.WAYBILL_AIRBILL_NUM, rhi.INVOICE_NUM, rhi.INVOICE_DATE, TO_CHAR(RHI.TOTAL_INVOICE_AMOUNT, FND_CURRENCY_CACHE.GET_FORMAT_MASK(RHI.CURRENCY_CODE, 30)) TOTAL_INVOICE_AMOUNT, RHI.FREIGHT_AMOUNT, RHI.PAYMENT_TERMS_NAME, RHI.COMMENTS, RHI.PACKING_SLIP, RHI.CARRIER_EQUIPMENT, RHI.CARRIER_METHOD, RHI.HAZARD_CLASS, RHI.HAZARD_CODE, RHI.HAZARD_DESCRIPTION, RHI.PACKAGING_CODE, RHI.RECEIPT_NUM, nvl(HR.LOCATION_CODE, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)) LOCATION_CODE, RHI.SPECIAL_HANDLING_CODE, POV.VENDOR_NAME, RHI.VENDOR_ID, RHI.VENDOR_NUM, POVS.VENDOR_SITE_CODE, RHI.VENDOR_SITE_ID, RHI.TAR_WEIGHT, RHI.TAR_WEIGHT_UOM_CODE, RHI.NET_WEIGHT, RHI.NET_WEIGHT_UOM_CODE, RHI.GROSS_WEIGHT, RHI.GROSS_WEIGHT_UOM_CODE, RHI.USGGL_TRANSACTION_CODE, RHI.CURRENCY_CODE, RHI.INVOICE_STATUS_CODE, RHI.TAX_NAME, RHI.TAX_AMOUNT, RHI.SHIP_TO_ORGANIZATION_ID, RHI.HEADER_INTERFACE_ID HEADER_ID, RHI.ASN_TYPE, RHI.REMIT_TO_SITE_ID , RHI.SHIP_FROM_LOCATION_ID, SUBSTR(PS.PARTY_SITE_NUMBER,1,INSTR(PS.PARTY_SITE_NUMBER,'|')-1) SHIP_FROM_LOCATION_CODE,
POS_CANCEL_ASN.getVendorSiteAddress(RHI.VENDOR_SITE_ID) SUPP_SITE_ADDRESS,
RHI.FREIGHT_CARRIER_CODE,
POS_CANCEL_ASN.getOperatingUnitName(RHI.SHIP_TO_ORGANIZATION_ID) OU_NAME,
POS_CANCEL_ASN.getOperatingUnitAddress(RHI.SHIP_TO_ORGANIZATION_ID) OU_ADDRESS
FROM RCV_HEADERS_INTERFACE RHI, RCV_TRANSACTIONS_INTERFACE RTI, HR_LOCATIONS_ALL_TL HR, hz_locations hz, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS ,HZ_PARTY_SITES PS
WHERE
RHI.ASN_TYPE IN ('ASN', 'ASBN') and RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID and HR.LOCATION_ID(+) = RHI.LOCATION_ID and HR.LANGUAGE(+) = USERENV('LANG') and hz.location_id(+) = RHI.LOCATION_ID AND POV.VENDOR_ID (+) = RHI.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RHI.VENDOR_SITE_ID AND RHI.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+)
AND
rhi.SHIPMENT_NUM = :asnShipmentNum and rhi.VENDOR_ID = :vendorId and rhi.VENDOR_SITE_ID = :vendorSiteId
UNION
SELECT RSH.SHIPMENT_NUM, 'SHIPMENT' STAGE, To_Char(RSH.SHIPPED_DATE,'dd-MM-yyyy hh24:mi:ss') SHIPPED_DATE,
To_Char(RSH.EXPECTED_RECEIPT_DATE,'dd-MM-yyyy hh24:mi:ss') EXPECTED_RECEIPT_DATE, RSH.FREIGHT_TERMS, RSH.FREIGHT_CARRIER_CODE, RSH.FREIGHT_BILL_NUMBER, RSH.NUM_OF_CONTAINERS, RSH.BILL_OF_LADING, RSH.WAYBILL_AIRBILL_NUM, RSH.INVOICE_NUM, RSH.INVOICE_DATE, TO_CHAR(RSH.INVOICE_AMOUNT, FND_CURRENCY_CACHE.GET_FORMAT_MASK(RSH.CURRENCY_CODE, 30)) TOTAL_INVOICE_AMOUNT, RSH.FREIGHT_AMOUNT, AP.NAME PAYMENT_TERMS_NAME, RSH.COMMENTS, RSH.PACKING_SLIP, RSH.CARRIER_EQUIPMENT, RSH.CARRIER_METHOD, RSH.HAZARD_CLASS, RSH.HAZARD_CODE, RSH.HAZARD_DESCRIPTION, RSH.PACKAGING_CODE, RSH.RECEIPT_NUM, nvl(HR.LOCATION_CODE, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)) LOCATION_CODE, RSH.SPECIAL_HANDLING_CODE, POV.VENDOR_NAME VENDOR_NAME , RSH.VENDOR_ID, POV.SEGMENT1 VENDOR_NUM, POVS.VENDOR_SITE_CODE, RSH.VENDOR_SITE_ID, RSH.TAR_WEIGHT, RSH.TAR_WEIGHT_UOM_CODE, RSH.NET_WEIGHT, RSH.NET_WEIGHT_UOM_CODE, RSH.GROSS_WEIGHT, RSH.GROSS_WEIGHT_UOM_CODE, RSH.USSGL_TRANSACTION_CODE, RSH.CURRENCY_CODE, RSH.INVOICE_STATUS_CODE, RSH.TAX_NAME, RSH.TAX_AMOUNT, RSH.SHIP_TO_ORG_ID, RSH.SHIPMENT_HEADER_ID HEADER_ID, RSH.ASN_TYPE, RSH.REMIT_TO_SITE_ID , RSH.SHIP_FROM_LOCATION_ID, SUBSTR(PS.PARTY_SITE_NUMBER,1,INSTR(PS.PARTY_SITE_NUMBER,'|')-1) SHIP_FROM_LOCATION_CODE,
POS_CANCEL_ASN.getVendorSiteAddress(RSH.VENDOR_SITE_ID) SUPP_SITE_ADDRESS,
RSH.FREIGHT_CARRIER_CODE,
POS_CANCEL_ASN.getOperatingUnitName(RSH.SHIP_TO_ORG_ID) OU_NAME,
POS_CANCEL_ASN.getOperatingUnitAddress(RSH.SHIP_TO_ORG_ID) OU_ADDRESS
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, HR_LOCATIONS_ALL_TL HR, hz_locations hz, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, AP_TERMS_VL AP ,HZ_PARTY_SITES PS WHERE RSH.ASN_TYPE IN ('ASN', 'ASBN') AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND (RECEIPT_SOURCE_CODE = 'VENDOR' AND RSH.ASN_TYPE IN ('ASN','ASBN') AND HR.LOCATION_ID(+) = RSH.SHIP_TO_LOCATION_ID AND HR.LANGUAGE (+) = USERENV('LANG') AND hz.location_id(+) = RSH.SHIP_TO_LOCATION_ID AND AP.TERM_ID (+) = RSH.PAYMENT_TERMS_ID AND POV.VENDOR_ID (+) = RSH.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID AND RSH.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+))
AND rsh.SHIPMENT_NUM = :asnShipmentNum and rsh.VENDOR_ID = :vendorId and rsh.VENDOR_SITE_ID = :vendorSiteId

SELECT RHI.SHIPMENT_NUM, 'INTERFACE' STAGE, To_Char(RHI.SHIPPED_DATE,'dd-MM-yyyy hh24:mi:ss') SHIPPED_DATE, To_Char(RHI.EXPECTED_RECEIPT_DATE,'dd-MM-yyyy hh24:mi:ss') EXPECTED_RECEIPT_DATE, FREIGHT_TERMS, RHI.FREIGHT_CARRIER_CODE, RHI.FREIGHT_BILL_NUMBER, RHI.NUM_OF_CONTAINERS, RHI.BILL_OF_LADING, RHI.WAYBILL_AIRBILL_NUM, rhi.INVOICE_NUM, rhi.INVOICE_DATE, TO_CHAR(RHI.TOTAL_INVOICE_AMOUNT, FND_CURRENCY_CACHE.GET_FORMAT_MASK(RHI.CURRENCY_CODE, 30)) TOTAL_INVOICE_AMOUNT, RHI.FREIGHT_AMOUNT, RHI.PAYMENT_TERMS_NAME, RHI.COMMENTS, RHI.PACKING_SLIP, RHI.CARRIER_EQUIPMENT, RHI.CARRIER_METHOD, RHI.HAZARD_CLASS, RHI.HAZARD_CODE, RHI.HAZARD_DESCRIPTION, RHI.PACKAGING_CODE, RHI.RECEIPT_NUM, nvl(HR.LOCATION_CODE, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)) LOCATION_CODE, RHI.SPECIAL_HANDLING_CODE, POV.VENDOR_NAME, RHI.VENDOR_ID, RHI.VENDOR_NUM, POVS.VENDOR_SITE_CODE, RHI.VENDOR_SITE_ID, RHI.TAR_WEIGHT, RHI.TAR_WEIGHT_UOM_CODE, RHI.NET_WEIGHT, RHI.NET_WEIGHT_UOM_CODE, RHI.GROSS_WEIGHT, RHI.GROSS_WEIGHT_UOM_CODE, RHI.USGGL_TRANSACTION_CODE, RHI.CURRENCY_CODE, RHI.INVOICE_STATUS_CODE, RHI.TAX_NAME, RHI.TAX_AMOUNT, RHI.SHIP_TO_ORGANIZATION_ID, RHI.HEADER_INTERFACE_ID HEADER_ID, RHI.ASN_TYPE, RHI.REMIT_TO_SITE_ID , RHI.SHIP_FROM_LOCATION_ID, SUBSTR(PS.PARTY_SITE_NUMBER,1,INSTR(PS.PARTY_SITE_NUMBER,'|')-1) SHIP_FROM_LOCATION_CODE, POS_CANCEL_ASN.getVendorSiteAddress(RHI.VENDOR_SITE_ID) SUPP_SITE_ADDRESS, RHI.FREIGHT_CARRIER_CODE, POS_CANCEL_ASN.getOperatingUnitName(RHI.SHIP_TO_ORGANIZATION_ID) OU_NAME, POS_CANCEL_ASN.getOperatingUnitAddress(RHI.SHIP_TO_ORGANIZATION_ID) OU_ADDRESS FROM RCV_HEADERS_INTERFACE RHI, RCV_TRANSACTIONS_INTERFACE RTI, HR_LOCATIONS_ALL_TL HR, hz_locations hz, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS ,HZ_PARTY_SITES PS WHERE RHI.ASN_TYPE IN ('ASN', 'ASBN') and RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID and HR.LOCATION_ID(+) = RHI.LOCATION_ID and HR.LANGUAGE(+) = USERENV('LANG') and hz.location_id(+) = RHI.LOCATION_ID AND POV.VENDOR_ID (+) = RHI.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RHI.VENDOR_SITE_ID AND RHI.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+) AND rhi.SHIPMENT_NUM = :asnShipmentNum and rhi.VENDOR_ID = :vendorId and rhi.VENDOR_SITE_ID = :vendorSiteId UNION SELECT RSH.SHIPMENT_NUM, 'SHIPMENT' STAGE, To_Char(RSH.SHIPPED_DATE,'dd-MM-yyyy hh24:mi:ss') SHIPPED_DATE, To_Char(RSH.EXPECTED_RECEIPT_DATE,'dd-MM-yyyy hh24:mi:ss') EXPECTED_RECEIPT_DATE, RSH.FREIGHT_TERMS, RSH.FREIGHT_CARRIER_CODE, RSH.FREIGHT_BILL_NUMBER, RSH.NUM_OF_CONTAINERS, RSH.BILL_OF_LADING, RSH.WAYBILL_AIRBILL_NUM, RSH.INVOICE_NUM, RSH.INVOICE_DATE, TO_CHAR(RSH.INVOICE_AMOUNT, FND_CURRENCY_CACHE.GET_FORMAT_MASK(RSH.CURRENCY_CODE, 30)) TOTAL_INVOICE_AMOUNT, RSH.FREIGHT_AMOUNT, AP.NAME PAYMENT_TERMS_NAME, RSH.COMMENTS, RSH.PACKING_SLIP, RSH.CARRIER_EQUIPMENT, RSH.CARRIER_METHOD, RSH.HAZARD_CLASS, RSH.HAZARD_CODE, RSH.HAZARD_DESCRIPTION, RSH.PACKAGING_CODE, RSH.RECEIPT_NUM, nvl(HR.LOCATION_CODE, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)) LOCATION_CODE, RSH.SPECIAL_HANDLING_CODE, POV.VENDOR_NAME VENDOR_NAME , RSH.VENDOR_ID, POV.SEGMENT1 VENDOR_NUM, POVS.VENDOR_SITE_CODE, RSH.VENDOR_SITE_ID, RSH.TAR_WEIGHT, RSH.TAR_WEIGHT_UOM_CODE, RSH.NET_WEIGHT, RSH.NET_WEIGHT_UOM_CODE, RSH.GROSS_WEIGHT, RSH.GROSS_WEIGHT_UOM_CODE, RSH.USSGL_TRANSACTION_CODE, RSH.CURRENCY_CODE, RSH.INVOICE_STATUS_CODE, RSH.TAX_NAME, RSH.TAX_AMOUNT, RSH.SHIP_TO_ORG_ID, RSH.SHIPMENT_HEADER_ID HEADER_ID, RSH.ASN_TYPE, RSH.REMIT_TO_SITE_ID , RSH.SHIP_FROM_LOCATION_ID, SUBSTR(PS.PARTY_SITE_NUMBER,1,INSTR(PS.PARTY_SITE_NUMBER,'|')-1) SHIP_FROM_LOCATION_CODE, POS_CANCEL_ASN.getVendorSiteAddress(RSH.VENDOR_SITE_ID) SUPP_SITE_ADDRESS, RSH.FREIGHT_CARRIER_CODE, POS_CANCEL_ASN.getOperatingUnitName(RSH.SHIP_TO_ORG_ID) OU_NAME, POS_CANCEL_ASN.getOperatingUnitAddress(RSH.SHIP_TO_ORG_ID) OU_ADDRESS FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, HR_LOCATIONS_ALL_TL HR, hz_locations hz, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, AP_TERMS_VL AP ,HZ_PARTY_SITES PS WHERE RSH.ASN_TYPE IN ('ASN', 'ASBN') AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND (RECEIPT_SOURCE_CODE = 'VENDOR' AND RSH.ASN_TYPE IN ('ASN','ASBN') AND HR.LOCATION_ID(+) = RSH.SHIP_TO_LOCATION_ID AND HR.LANGUAGE (+) = USERENV('LANG') AND hz.location_id(+) = RSH.SHIP_TO_LOCATION_ID AND AP.TERM_ID (+) = RSH.PAYMENT_TERMS_ID AND POV.VENDOR_ID (+) = RSH.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID AND RSH.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+)) AND rsh.SHIPMENT_NUM = :asnShipmentNum and rsh.VENDOR_ID = :vendorId and rsh.VENDOR_SITE_ID = :vendorSiteId