VIEW: APPS.PO_LINE_LOCATIONS_XML

Source

View Text - Preformatted

SELECT PLL.SHIPMENT_NUM, TO_CHAR(NVL(PLL.NEED_BY_DATE, PLL.PROMISED_DATE),'DD-MON-YYYY HH24:MI:SS') DUE_DATE, PLL.QUANTITY, PLL.PRICE_OVERRIDE, PLL.QUANTITY_CANCELLED, PLL.CANCEL_FLAG, TO_CHAR(PLL.CANCEL_DATE,'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE, PLL.CANCEL_REASON, PLL.TAXABLE_FLAG, TO_CHAR(PLL.START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE, TO_CHAR(PLL.END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE, PLL.ATTRIBUTE_CATEGORY, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, PLL.PO_HEADER_ID, PL.PO_LINE_ID, PLL.LINE_LOCATION_ID, DECODE(NVL(PLL.SHIPMENT_TYPE, 'PRICE BREAK'), 'PRICE BREAK','BLANKET', 'SCHEDULED', 'RELEASE', 'BLANKET','RELEASE', 'STANDARD','STANDARD', 'PLANNED', 'PLANNED','PREPAYMENT','PREPAYMENT') SHIPMENT_TYPE, PLL.PO_RELEASE_ID, PLL.CONSIGNED_FLAG, PLL.USSGL_TRANSACTION_CODE, PLL.GOVERNMENT_CONTEXT, PLL.RECEIVING_ROUTING_ID, PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.CLOSED_REASON, TO_CHAR(PLL.CLOSED_DATE,'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE, PLL.CLOSED_BY, PLL.ORG_ID, PLL.UNIT_OF_MEASURE_CLASS, PLL.ENCUMBER_NOW, PLL.INSPECTION_REQUIRED_FLAG, PLL.RECEIPT_REQUIRED_FLAG, PLL.QTY_RCV_TOLERANCE, PLL.QTY_RCV_EXCEPTION_CODE, PLL.ENFORCE_SHIP_TO_LOCATION_CODE, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG, PLL.DAYS_EARLY_RECEIPT_ALLOWED, PLL.DAYS_LATE_RECEIPT_ALLOWED, PLL.RECEIPT_DAYS_EXCEPTION_CODE, PLL.INVOICE_CLOSE_TOLERANCE, PLL.RECEIVE_CLOSE_TOLERANCE, PLL.SHIP_TO_ORGANIZATION_ID, PLL.SOURCE_SHIPMENT_ID, PLL.CLOSED_CODE, PLL.REQUEST_ID, PLL.PROGRAM_APPLICATION_ID, PLL.PROGRAM_ID, PLL.PROGRAM_UPDATE_DATE, TO_CHAR(PLL.LAST_ACCEPT_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_ACCEPT_DATE, PLL.ENCUMBERED_FLAG, TO_CHAR(PLL.ENCUMBERED_DATE,'DD-MON-YYYY HH24:MI:SS') ENCUMBERED_DATE, PLL.UNENCUMBERED_QUANTITY, PLL.FOB_LOOKUP_CODE, PLL.FREIGHT_TERMS_LOOKUP_CODE, TO_CHAR(PLL.ESTIMATED_TAX_AMOUNT, PGT.FORMAT_MASK) ESTIMATED_TAX_AMOUNT, PLL.FROM_HEADER_ID, PLL.FROM_LINE_ID, PLL.FROM_LINE_LOCATION_ID, PLL.LEAD_TIME, PLL.LEAD_TIME_UNIT, PLL.PRICE_DISCOUNT, PLL.TERMS_ID, PLL.APPROVED_FLAG, TO_CHAR(PLL.APPROVED_DATE,'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE, PLL.CLOSED_FLAG, PLL.CANCELLED_BY, PLL.FIRM_STATUS_LOOKUP_CODE, TO_CHAR(PLL.FIRM_DATE,'DD-MON-YYYY HH24:MI:SS') FIRM_DATE, TO_CHAR(PLL.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE, PLL.LAST_UPDATED_BY, PLL.LAST_UPDATE_LOGIN, TO_CHAR(PLL.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE, PLL.CREATED_BY, PLL.QUANTITY_RECEIVED, PLL.QUANTITY_ACCEPTED, PLL.QUANTITY_REJECTED, PLL.QUANTITY_BILLED, NVL(MUM.UNIT_OF_MEASURE_TL, PLL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE , PLL.SHIP_VIA_LOOKUP_CODE, PLL.GLOBAL_ATTRIBUTE_CATEGORY, PLL.GLOBAL_ATTRIBUTE1, PLL.GLOBAL_ATTRIBUTE2, PLL.GLOBAL_ATTRIBUTE3, PLL.GLOBAL_ATTRIBUTE4, PLL.GLOBAL_ATTRIBUTE5, PLL.GLOBAL_ATTRIBUTE6, PLL.GLOBAL_ATTRIBUTE7, PLL.GLOBAL_ATTRIBUTE8, PLL.GLOBAL_ATTRIBUTE9, PLL.GLOBAL_ATTRIBUTE10, PLL.GLOBAL_ATTRIBUTE11, PLL.GLOBAL_ATTRIBUTE12, PLL.GLOBAL_ATTRIBUTE13, PLL.GLOBAL_ATTRIBUTE14, PLL.GLOBAL_ATTRIBUTE15, PLL.GLOBAL_ATTRIBUTE16, PLL.GLOBAL_ATTRIBUTE17, PLL.GLOBAL_ATTRIBUTE18, PLL.GLOBAL_ATTRIBUTE19, PLL.GLOBAL_ATTRIBUTE20, PLL.QUANTITY_SHIPPED, PLL.COUNTRY_OF_ORIGIN_CODE, PLL.TAX_USER_OVERRIDE_FLAG, PLL.MATCH_OPTION, PLL.TAX_CODE_ID, PLL.CALCULATE_TAX_FLAG, PLL.CHANGE_PROMISED_DATE_REASON, PLL.NOTE_TO_RECEIVER, PLL.SECONDARY_UNIT_OF_MEASURE, PLL.SECONDARY_QUANTITY, PLL.PREFERRED_GRADE, PLL.SECONDARY_QUANTITY_RECEIVED, PLL.SECONDARY_QUANTITY_ACCEPTED, PLL.SECONDARY_QUANTITY_REJECTED, PLL.SECONDARY_QUANTITY_CANCELLED, PLL.VMI_FLAG, TO_CHAR(PLL.RETROACTIVE_DATE,'DD-MON-YYYY HH24:MI:SS') RETROACTIVE_DATE, PLL.SUPPLIER_ORDER_LINE_NUMBER, TO_CHAR(PO_CORE_S.GET_TOTAL('S',PLL.LINE_LOCATION_ID),PGT.FORMAT_MASK) AMOUNT, TO_CHAR(PLL.AMOUNT_RECEIVED, PGT.FORMAT_MASK) AMOUNT_RECEIVED, TO_CHAR(PLL.AMOUNT_BILLED, PGT.FORMAT_MASK) AMOUNT_BILLED, TO_CHAR(PLL.AMOUNT_CANCELLED, PGT.FORMAT_MASK) AMOUNT_CANCELLED, TO_CHAR(PLL.AMOUNT_ACCEPTED, PGT.FORMAT_MASK) AMOUNT_ACCEPTED, TO_CHAR(PLL.AMOUNT_REJECTED, PGT.FORMAT_MASK) AMOUNT_REJECTED, PLL.DROP_SHIP_FLAG, TO_CHAR(PLL.SALES_ORDER_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') SALES_ORDER_UPDATE_DATE, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PLL.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1,NULL,PO_COMMUNICATION_PVT.get_oneTime_loc(PLL.SHIP_TO_LOCATION_ID)) IS_SHIPMENT_ONE_TIME_LOC, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1,NULL,PO_COMMUNICATION_PVT.get_oneTime_address(PLL.LINE_LOCATION_ID)) ONE_TIME_ADDRESS_DETAILS, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GET_DROP_SHIP_DETAILS(PLL.LINE_LOCATION_ID),NULL) DETAILS, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCONTPHONE(),NULL) SHIP_CONT_PHONE, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCONTEMAIL(),NULL) SHIP_CONT_EMAIL, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETDELIVERCONTPHONE(),NULL) ULTIMATE_DELIVER_CONT_PHONE, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCONTEMAIL(),NULL) ULTIMATE_DELIVER_CONT_EMAIL, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETSHIPCONTNAME(),NULL) SHIP_CONT_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETDELIVERCONTNAME(),NULL) ULTIMATE_DELIVER_CONT_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETSHIPCUSTNAME(),NULL) SHIP_CUST_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCUSTLOCATION(),NULL) SHIP_CUST_LOCATION, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETDELIVERCUSTNAME(),NULL) ULTIMATE_DELIVER_CUST_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCUSTLOCATION(),NULL) ULTIMATE_DELIVER_CUST_LOCATION, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCONTACTFAX(),NULL) SHIP_TO_CONTACT_FAX, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCONTACTNAME(),NULL) ULTIMATE_DELIVER_TO_CONT_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCONTACTFAX(),NULL) ULTIMATE_DELIVER_TO_CONT_FAX, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPPINGMETHOD(),NULL) SHIPPING_METHOD, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETSHIPPINGINSTRUCTIONS(),NULL) SHIPPING_INSTRUCTIONS, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETPACKINGINSTRUCTIONS(),NULL) PACKING_INSTRUCTIONS, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETCUSTOMERPRODUCTDESC(),NULL) CUSTOMER_PRODUCT_DESC, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETCUSTOMERPONUMBER(),NULL) CUSTOMER_PO_NUM, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETCUSTOMERPOLINENUM(),NULL) CUSTOMER_PO_LINE_NUM, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETCUSTOMERPOSHIPMENTNUM(),NULL) CUSTOMER_PO_SHIPMENT_NUM, TO_CHAR(PLL.NEED_BY_DATE,'DD-MON-YYYY HH24:MI:SS') NEED_BY_DATE, TO_CHAR( PLL.PROMISED_DATE,'DD-MON-YYYY HH24:MI:SS') PROMISED_DATE, TO_CHAR(PLL.AMOUNT, PGT.FORMAT_MASK) TOTAL_SHIPMENT_AMOUNT, PLL.FINAL_MATCH_FLAG, PLL.MANUAL_PRICE_CHANGE_FLAG, PLL.TAX_NAME, PLL.TRANSACTION_FLOW_HEADER_ID, PLL.VALUE_BASIS, PLL.MATCHING_BASIS, PLL.PAYMENT_TYPE, PLL.DESCRIPTION, PLL.QUANTITY_FINANCED, TO_CHAR(PLL.AMOUNT_FINANCED, PGT.FORMAT_MASK) AMOUNT_FINANCED, PLL.QUANTITY_RECOUPED, TO_CHAR(PLL.AMOUNT_RECOUPED, PGT.FORMAT_MASK) AMOUNT_RECOUPED, TO_CHAR(PLL.RETAINAGE_WITHHELD_AMOUNT, PGT.FORMAT_MASK) RETAINAGE_WITHHELD_AMOUNT, TO_CHAR(PLL.RETAINAGE_RELEASED_AMOUNT, PGT.FORMAT_MASK) RETAINAGE_RELEASED_AMOUNT, PLL.WORK_APPROVER_ID, PLL.BID_PAYMENT_ID , TO_CHAR(PLL.AMOUNT_SHIPPED, PGT.FORMAT_MASK) AMOUNT_SHIPPED FROM PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL PL, PO_COMMUNICATION_GT PGT, MTL_UNITS_OF_MEASURE_TL MUM WHERE PLL.PO_LINE_ID (+) = PL.PO_LINE_ID AND (PLL.SHIPMENT_TYPE (+) <> 'BLANKET' AND PGT.PO_RELEASE_ID IS NULL) AND PLL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+) AND MUM.LANGUAGE(+) = USERENV('LANG') UNION SELECT PLL.SHIPMENT_NUM, TO_CHAR(NVL(PLL.NEED_BY_DATE, PLL.PROMISED_DATE),'DD-MON-YYYY HH24:MI:SS') DUE_DATE, PLL.QUANTITY, PLL.PRICE_OVERRIDE, PLL.QUANTITY_CANCELLED, PLL.CANCEL_FLAG, TO_CHAR(PLL.CANCEL_DATE,'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE, PLL.CANCEL_REASON, PLL.TAXABLE_FLAG, TO_CHAR(PLL.START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE, TO_CHAR(PLL.END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE, PLL.ATTRIBUTE_CATEGORY, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, PLL.PO_HEADER_ID, PL.PO_LINE_ID, PLL.LINE_LOCATION_ID, DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK','BLANKET', 'SCHEDULED', 'RELEASE', 'BLANKET','RELEASE', 'STANDARD','STANDARD', 'PLANNED', 'PLANNED','PREPAYMENT','PREPAYMENT') SHIPMENT_TYPE, PLL.PO_RELEASE_ID, PLL.CONSIGNED_FLAG, PLL.USSGL_TRANSACTION_CODE, PLL.GOVERNMENT_CONTEXT, PLL.RECEIVING_ROUTING_ID, PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.CLOSED_REASON, TO_CHAR(PLL.CLOSED_DATE,'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE, PLL.CLOSED_BY, PLL.ORG_ID, PLL.UNIT_OF_MEASURE_CLASS, PLL.ENCUMBER_NOW, PLL.INSPECTION_REQUIRED_FLAG, PLL.RECEIPT_REQUIRED_FLAG, PLL.QTY_RCV_TOLERANCE, PLL.QTY_RCV_EXCEPTION_CODE, PLL.ENFORCE_SHIP_TO_LOCATION_CODE, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG, PLL.DAYS_EARLY_RECEIPT_ALLOWED, PLL.DAYS_LATE_RECEIPT_ALLOWED, PLL.RECEIPT_DAYS_EXCEPTION_CODE, PLL.INVOICE_CLOSE_TOLERANCE, PLL.RECEIVE_CLOSE_TOLERANCE, PLL.SHIP_TO_ORGANIZATION_ID, PLL.SOURCE_SHIPMENT_ID, PLL.CLOSED_CODE, PLL.REQUEST_ID, PLL.PROGRAM_APPLICATION_ID, PLL.PROGRAM_ID, PLL.PROGRAM_UPDATE_DATE, TO_CHAR(PLL.LAST_ACCEPT_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_ACCEPT_DATE, PLL.ENCUMBERED_FLAG, TO_CHAR(PLL.ENCUMBERED_DATE,'DD-MON-YYYY HH24:MI:SS') ENCUMBERED_DATE, PLL.UNENCUMBERED_QUANTITY, PLL.FOB_LOOKUP_CODE, PLL.FREIGHT_TERMS_LOOKUP_CODE, TO_CHAR(PLL.ESTIMATED_TAX_AMOUNT, PGT.FORMAT_MASK) ESTIMATED_TAX_AMOUNT, PLL.FROM_HEADER_ID, PLL.FROM_LINE_ID, PLL.FROM_LINE_LOCATION_ID, PLL.LEAD_TIME, PLL.LEAD_TIME_UNIT, PLL.PRICE_DISCOUNT, PLL.TERMS_ID, PLL.APPROVED_FLAG, TO_CHAR(PLL.APPROVED_DATE,'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE, PLL.CLOSED_FLAG, PLL.CANCELLED_BY, PLL.FIRM_STATUS_LOOKUP_CODE, TO_CHAR(PLL.FIRM_DATE,'DD-MON-YYYY HH24:MI:SS') FIRM_DATE, TO_CHAR(PLL.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE, PLL.LAST_UPDATED_BY, PLL.LAST_UPDATE_LOGIN, TO_CHAR(PLL.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE, PLL.CREATED_BY, PLL.QUANTITY_RECEIVED, PLL.QUANTITY_ACCEPTED, PLL.QUANTITY_REJECTED, PLL.QUANTITY_BILLED, NVL(MUM.UNIT_OF_MEASURE_TL, PLL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE , PLL.SHIP_VIA_LOOKUP_CODE, PLL.GLOBAL_ATTRIBUTE_CATEGORY, PLL.GLOBAL_ATTRIBUTE1, PLL.GLOBAL_ATTRIBUTE2, PLL.GLOBAL_ATTRIBUTE3, PLL.GLOBAL_ATTRIBUTE4, PLL.GLOBAL_ATTRIBUTE5, PLL.GLOBAL_ATTRIBUTE6, PLL.GLOBAL_ATTRIBUTE7, PLL.GLOBAL_ATTRIBUTE8, PLL.GLOBAL_ATTRIBUTE9, PLL.GLOBAL_ATTRIBUTE10, PLL.GLOBAL_ATTRIBUTE11, PLL.GLOBAL_ATTRIBUTE12, PLL.GLOBAL_ATTRIBUTE13, PLL.GLOBAL_ATTRIBUTE14, PLL.GLOBAL_ATTRIBUTE15, PLL.GLOBAL_ATTRIBUTE16, PLL.GLOBAL_ATTRIBUTE17, PLL.GLOBAL_ATTRIBUTE18, PLL.GLOBAL_ATTRIBUTE19, PLL.GLOBAL_ATTRIBUTE20, PLL.QUANTITY_SHIPPED, PLL.COUNTRY_OF_ORIGIN_CODE, PLL.TAX_USER_OVERRIDE_FLAG, PLL.MATCH_OPTION, PLL.TAX_CODE_ID, PLL.CALCULATE_TAX_FLAG, PLL.CHANGE_PROMISED_DATE_REASON, PLL.NOTE_TO_RECEIVER, PLL.SECONDARY_UNIT_OF_MEASURE, PLL.SECONDARY_QUANTITY, PLL.PREFERRED_GRADE, PLL.SECONDARY_QUANTITY_RECEIVED, PLL.SECONDARY_QUANTITY_ACCEPTED, PLL.SECONDARY_QUANTITY_REJECTED, PLL.SECONDARY_QUANTITY_CANCELLED, PLL.VMI_FLAG, TO_CHAR(PLL.RETROACTIVE_DATE,'DD-MON-YYYY HH24:MI:SS') RETROACTIVE_DATE, PLL.SUPPLIER_ORDER_LINE_NUMBER, TO_CHAR(PO_CORE_S.GET_TOTAL('S',PLL.LINE_LOCATION_ID),PGT.FORMAT_MASK) AMOUNT, TO_CHAR(PLL.AMOUNT_RECEIVED, PGT.FORMAT_MASK) AMOUNT_RECEIVED, TO_CHAR(PLL.AMOUNT_BILLED, PGT.FORMAT_MASK) AMOUNT_BILLED, TO_CHAR(PLL.AMOUNT_CANCELLED, PGT.FORMAT_MASK) AMOUNT_CANCELLED, TO_CHAR(PLL.AMOUNT_ACCEPTED, PGT.FORMAT_MASK) AMOUNT_ACCEPTED, TO_CHAR(PLL.AMOUNT_REJECTED, PGT.FORMAT_MASK) AMOUNT_REJECTED, PLL.DROP_SHIP_FLAG, TO_CHAR(PLL.SALES_ORDER_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') SALES_ORDER_UPDATE_DATE, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PLL.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1,NULL,PO_COMMUNICATION_PVT.get_oneTime_loc(PLL.SHIP_TO_LOCATION_ID)) IS_SHIPMENT_ONE_TIME_LOC, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID,-1),-1,NULL,PO_COMMUNICATION_PVT.get_oneTime_address(PLL.LINE_LOCATION_ID)) ONE_TIME_ADDRESS_DETAILS, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GET_DROP_SHIP_DETAILS(PLL.LINE_LOCATION_ID),NULL) DETAILS, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCONTPHONE(),NULL) SHIP_CONT_PHONE, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCONTEMAIL(),NULL) SHIP_CONT_EMAIL, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETDELIVERCONTPHONE(),NULL) ULTIMATE_DELIVER_CONT_PHONE, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCONTEMAIL(),NULL) ULTIMATE_DELIVER_CONT_EMAIL, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETSHIPCONTNAME(),NULL) SHIP_CONT_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETDELIVERCONTNAME(),NULL) ULTIMATE_DELIVER_CONT_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETSHIPCUSTNAME(),NULL) SHIP_CUST_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCUSTLOCATION(),NULL) SHIP_CUST_LOCATION, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETDELIVERCUSTNAME(),NULL) ULTIMATE_DELIVER_CUST_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCUSTLOCATION(),NULL) ULTIMATE_DELIVER_CUST_LOCATION, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPCONTACTFAX(),NULL) SHIP_TO_CONTACT_FAX, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCONTACTNAME(),NULL) ULTIMATE_DELIVER_TO_CONT_NAME, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETDELIVERCONTACTFAX(),NULL) ULTIMATE_DELIVER_TO_CONT_FAX, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETSHIPPINGMETHOD(),NULL) SHIPPING_METHOD, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETSHIPPINGINSTRUCTIONS(),NULL) SHIPPING_INSTRUCTIONS, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETPACKINGINSTRUCTIONS(),NULL) PACKING_INSTRUCTIONS, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETCUSTOMERPRODUCTDESC(),NULL) CUSTOMER_PRODUCT_DESC, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETCUSTOMERPONUMBER(),NULL) CUSTOMER_PO_NUM, DECODE(PLL.DROP_SHIP_FLAG,'Y',PO_COMMUNICATION_PVT.GETCUSTOMERPOLINENUM(),NULL) CUSTOMER_PO_LINE_NUM, DECODE(PLL.DROP_SHIP_FLAG,'Y', PO_COMMUNICATION_PVT.GETCUSTOMERPOSHIPMENTNUM(),NULL) CUSTOMER_PO_SHIPMENT_NUM, TO_CHAR(PLL.NEED_BY_DATE,'DD-MON-YYYY HH24:MI:SS') NEED_BY_DATE, TO_CHAR( PLL.PROMISED_DATE,'DD-MON-YYYY HH24:MI:SS') PROMISED_DATE, TO_CHAR(PLL.AMOUNT, PGT.FORMAT_MASK) TOTAL_SHIPMENT_AMOUNT, PLL.FINAL_MATCH_FLAG, PLL.MANUAL_PRICE_CHANGE_FLAG, PLL.TAX_NAME, PLL.TRANSACTION_FLOW_HEADER_ID, PLL.VALUE_BASIS, PLL.MATCHING_BASIS, PLL.PAYMENT_TYPE, PLL.DESCRIPTION, PLL.QUANTITY_FINANCED, TO_CHAR(PLL.AMOUNT_FINANCED, PGT.FORMAT_MASK) AMOUNT_FINANCED, PLL.QUANTITY_RECOUPED, TO_CHAR(PLL.AMOUNT_RECOUPED, PGT.FORMAT_MASK) AMOUNT_RECOUPED, TO_CHAR(PLL.RETAINAGE_WITHHELD_AMOUNT, PGT.FORMAT_MASK) RETAINAGE_WITHHELD_AMOUNT, TO_CHAR(PLL.RETAINAGE_RELEASED_AMOUNT, PGT.FORMAT_MASK) RETAINAGE_RELEASED_AMOUNT, PLL.WORK_APPROVER_ID, PLL.BID_PAYMENT_ID, TO_CHAR(PLL.AMOUNT_SHIPPED, PGT.FORMAT_MASK) AMOUNT_SHIPPED FROM PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL PL, PO_COMMUNICATION_GT PGT, MTL_UNITS_OF_MEASURE_TL MUM WHERE PLL.PO_LINE_ID (+) = PL.PO_LINE_ID AND (PLL.SHIPMENT_TYPE (+) = 'BLANKET' AND PGT.PO_RELEASE_ID IS NOT NULL) AND PLL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+) AND MUM.LANGUAGE(+) = USERENV('LANG')

View Text - HTML Formatted

SELECT PLL.SHIPMENT_NUM
, TO_CHAR(NVL(PLL.NEED_BY_DATE
, PLL.PROMISED_DATE)
, 'DD-MON-YYYY HH24:MI:SS') DUE_DATE
, PLL.QUANTITY
, PLL.PRICE_OVERRIDE
, PLL.QUANTITY_CANCELLED
, PLL.CANCEL_FLAG
, TO_CHAR(PLL.CANCEL_DATE
, 'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.TAXABLE_FLAG
, TO_CHAR(PLL.START_DATE
, 'DD-MON-YYYY HH24:MI:SS') START_DATE
, TO_CHAR(PLL.END_DATE
, 'DD-MON-YYYY HH24:MI:SS') END_DATE
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, PLL.PO_HEADER_ID
, PL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, DECODE(NVL(PLL.SHIPMENT_TYPE
, 'PRICE BREAK')
, 'PRICE BREAK'
, 'BLANKET'
, 'SCHEDULED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE'
, 'STANDARD'
, 'STANDARD'
, 'PLANNED'
, 'PLANNED'
, 'PREPAYMENT'
, 'PREPAYMENT') SHIPMENT_TYPE
, PLL.PO_RELEASE_ID
, PLL.CONSIGNED_FLAG
, PLL.USSGL_TRANSACTION_CODE
, PLL.GOVERNMENT_CONTEXT
, PLL.RECEIVING_ROUTING_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.CLOSED_REASON
, TO_CHAR(PLL.CLOSED_DATE
, 'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE
, PLL.CLOSED_BY
, PLL.ORG_ID
, PLL.UNIT_OF_MEASURE_CLASS
, PLL.ENCUMBER_NOW
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, PLL.QTY_RCV_TOLERANCE
, PLL.QTY_RCV_EXCEPTION_CODE
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.SOURCE_SHIPMENT_ID
, PLL.CLOSED_CODE
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_ID
, PLL.PROGRAM_UPDATE_DATE
, TO_CHAR(PLL.LAST_ACCEPT_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_ACCEPT_DATE
, PLL.ENCUMBERED_FLAG
, TO_CHAR(PLL.ENCUMBERED_DATE
, 'DD-MON-YYYY HH24:MI:SS') ENCUMBERED_DATE
, PLL.UNENCUMBERED_QUANTITY
, PLL.FOB_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, TO_CHAR(PLL.ESTIMATED_TAX_AMOUNT
, PGT.FORMAT_MASK) ESTIMATED_TAX_AMOUNT
, PLL.FROM_HEADER_ID
, PLL.FROM_LINE_ID
, PLL.FROM_LINE_LOCATION_ID
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, PLL.TERMS_ID
, PLL.APPROVED_FLAG
, TO_CHAR(PLL.APPROVED_DATE
, 'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE
, PLL.CLOSED_FLAG
, PLL.CANCELLED_BY
, PLL.FIRM_STATUS_LOOKUP_CODE
, TO_CHAR(PLL.FIRM_DATE
, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE
, TO_CHAR(PLL.LAST_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.LAST_UPDATE_LOGIN
, TO_CHAR(PLL.CREATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE
, PLL.CREATED_BY
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_REJECTED
, PLL.QUANTITY_BILLED
, NVL(MUM.UNIT_OF_MEASURE_TL
, PLL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE
, PLL.SHIP_VIA_LOOKUP_CODE
, PLL.GLOBAL_ATTRIBUTE_CATEGORY
, PLL.GLOBAL_ATTRIBUTE1
, PLL.GLOBAL_ATTRIBUTE2
, PLL.GLOBAL_ATTRIBUTE3
, PLL.GLOBAL_ATTRIBUTE4
, PLL.GLOBAL_ATTRIBUTE5
, PLL.GLOBAL_ATTRIBUTE6
, PLL.GLOBAL_ATTRIBUTE7
, PLL.GLOBAL_ATTRIBUTE8
, PLL.GLOBAL_ATTRIBUTE9
, PLL.GLOBAL_ATTRIBUTE10
, PLL.GLOBAL_ATTRIBUTE11
, PLL.GLOBAL_ATTRIBUTE12
, PLL.GLOBAL_ATTRIBUTE13
, PLL.GLOBAL_ATTRIBUTE14
, PLL.GLOBAL_ATTRIBUTE15
, PLL.GLOBAL_ATTRIBUTE16
, PLL.GLOBAL_ATTRIBUTE17
, PLL.GLOBAL_ATTRIBUTE18
, PLL.GLOBAL_ATTRIBUTE19
, PLL.GLOBAL_ATTRIBUTE20
, PLL.QUANTITY_SHIPPED
, PLL.COUNTRY_OF_ORIGIN_CODE
, PLL.TAX_USER_OVERRIDE_FLAG
, PLL.MATCH_OPTION
, PLL.TAX_CODE_ID
, PLL.CALCULATE_TAX_FLAG
, PLL.CHANGE_PROMISED_DATE_REASON
, PLL.NOTE_TO_RECEIVER
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.VMI_FLAG
, TO_CHAR(PLL.RETROACTIVE_DATE
, 'DD-MON-YYYY HH24:MI:SS') RETROACTIVE_DATE
, PLL.SUPPLIER_ORDER_LINE_NUMBER
, TO_CHAR(PO_CORE_S.GET_TOTAL('S'
, PLL.LINE_LOCATION_ID)
, PGT.FORMAT_MASK) AMOUNT
, TO_CHAR(PLL.AMOUNT_RECEIVED
, PGT.FORMAT_MASK) AMOUNT_RECEIVED
, TO_CHAR(PLL.AMOUNT_BILLED
, PGT.FORMAT_MASK) AMOUNT_BILLED
, TO_CHAR(PLL.AMOUNT_CANCELLED
, PGT.FORMAT_MASK) AMOUNT_CANCELLED
, TO_CHAR(PLL.AMOUNT_ACCEPTED
, PGT.FORMAT_MASK) AMOUNT_ACCEPTED
, TO_CHAR(PLL.AMOUNT_REJECTED
, PGT.FORMAT_MASK) AMOUNT_REJECTED
, PLL.DROP_SHIP_FLAG
, TO_CHAR(PLL.SALES_ORDER_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') SALES_ORDER_UPDATE_DATE
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PLL.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GET_ONETIME_LOC(PLL.SHIP_TO_LOCATION_ID)) IS_SHIPMENT_ONE_TIME_LOC
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GET_ONETIME_ADDRESS(PLL.LINE_LOCATION_ID)) ONE_TIME_ADDRESS_DETAILS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GET_DROP_SHIP_DETAILS(PLL.LINE_LOCATION_ID)
, NULL) DETAILS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTPHONE()
, NULL) SHIP_CONT_PHONE
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTEMAIL()
, NULL) SHIP_CONT_EMAIL
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTPHONE()
, NULL) ULTIMATE_DELIVER_CONT_PHONE
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTEMAIL()
, NULL) ULTIMATE_DELIVER_CONT_EMAIL
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTNAME()
, NULL) SHIP_CONT_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTNAME()
, NULL) ULTIMATE_DELIVER_CONT_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCUSTNAME()
, NULL) SHIP_CUST_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCUSTLOCATION()
, NULL) SHIP_CUST_LOCATION
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCUSTNAME()
, NULL) ULTIMATE_DELIVER_CUST_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCUSTLOCATION()
, NULL) ULTIMATE_DELIVER_CUST_LOCATION
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTACTFAX()
, NULL) SHIP_TO_CONTACT_FAX
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTACTNAME()
, NULL) ULTIMATE_DELIVER_TO_CONT_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTACTFAX()
, NULL) ULTIMATE_DELIVER_TO_CONT_FAX
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPPINGMETHOD()
, NULL) SHIPPING_METHOD
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPPINGINSTRUCTIONS()
, NULL) SHIPPING_INSTRUCTIONS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETPACKINGINSTRUCTIONS()
, NULL) PACKING_INSTRUCTIONS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPRODUCTDESC()
, NULL) CUSTOMER_PRODUCT_DESC
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPONUMBER()
, NULL) CUSTOMER_PO_NUM
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPOLINENUM()
, NULL) CUSTOMER_PO_LINE_NUM
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPOSHIPMENTNUM()
, NULL) CUSTOMER_PO_SHIPMENT_NUM
, TO_CHAR(PLL.NEED_BY_DATE
, 'DD-MON-YYYY HH24:MI:SS') NEED_BY_DATE
, TO_CHAR( PLL.PROMISED_DATE
, 'DD-MON-YYYY HH24:MI:SS') PROMISED_DATE
, TO_CHAR(PLL.AMOUNT
, PGT.FORMAT_MASK) TOTAL_SHIPMENT_AMOUNT
, PLL.FINAL_MATCH_FLAG
, PLL.MANUAL_PRICE_CHANGE_FLAG
, PLL.TAX_NAME
, PLL.TRANSACTION_FLOW_HEADER_ID
, PLL.VALUE_BASIS
, PLL.MATCHING_BASIS
, PLL.PAYMENT_TYPE
, PLL.DESCRIPTION
, PLL.QUANTITY_FINANCED
, TO_CHAR(PLL.AMOUNT_FINANCED
, PGT.FORMAT_MASK) AMOUNT_FINANCED
, PLL.QUANTITY_RECOUPED
, TO_CHAR(PLL.AMOUNT_RECOUPED
, PGT.FORMAT_MASK) AMOUNT_RECOUPED
, TO_CHAR(PLL.RETAINAGE_WITHHELD_AMOUNT
, PGT.FORMAT_MASK) RETAINAGE_WITHHELD_AMOUNT
, TO_CHAR(PLL.RETAINAGE_RELEASED_AMOUNT
, PGT.FORMAT_MASK) RETAINAGE_RELEASED_AMOUNT
, PLL.WORK_APPROVER_ID
, PLL.BID_PAYMENT_ID
, TO_CHAR(PLL.AMOUNT_SHIPPED
, PGT.FORMAT_MASK) AMOUNT_SHIPPED
FROM PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL PL
, PO_COMMUNICATION_GT PGT
, MTL_UNITS_OF_MEASURE_TL MUM
WHERE PLL.PO_LINE_ID (+) = PL.PO_LINE_ID
AND (PLL.SHIPMENT_TYPE (+) <> 'BLANKET'
AND PGT.PO_RELEASE_ID IS NULL)
AND PLL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
AND MUM.LANGUAGE(+) = USERENV('LANG') UNION SELECT PLL.SHIPMENT_NUM
, TO_CHAR(NVL(PLL.NEED_BY_DATE
, PLL.PROMISED_DATE)
, 'DD-MON-YYYY HH24:MI:SS') DUE_DATE
, PLL.QUANTITY
, PLL.PRICE_OVERRIDE
, PLL.QUANTITY_CANCELLED
, PLL.CANCEL_FLAG
, TO_CHAR(PLL.CANCEL_DATE
, 'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.TAXABLE_FLAG
, TO_CHAR(PLL.START_DATE
, 'DD-MON-YYYY HH24:MI:SS') START_DATE
, TO_CHAR(PLL.END_DATE
, 'DD-MON-YYYY HH24:MI:SS') END_DATE
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, PLL.PO_HEADER_ID
, PL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, 'BLANKET'
, 'SCHEDULED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE'
, 'STANDARD'
, 'STANDARD'
, 'PLANNED'
, 'PLANNED'
, 'PREPAYMENT'
, 'PREPAYMENT') SHIPMENT_TYPE
, PLL.PO_RELEASE_ID
, PLL.CONSIGNED_FLAG
, PLL.USSGL_TRANSACTION_CODE
, PLL.GOVERNMENT_CONTEXT
, PLL.RECEIVING_ROUTING_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.CLOSED_REASON
, TO_CHAR(PLL.CLOSED_DATE
, 'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE
, PLL.CLOSED_BY
, PLL.ORG_ID
, PLL.UNIT_OF_MEASURE_CLASS
, PLL.ENCUMBER_NOW
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, PLL.QTY_RCV_TOLERANCE
, PLL.QTY_RCV_EXCEPTION_CODE
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.SOURCE_SHIPMENT_ID
, PLL.CLOSED_CODE
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_ID
, PLL.PROGRAM_UPDATE_DATE
, TO_CHAR(PLL.LAST_ACCEPT_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_ACCEPT_DATE
, PLL.ENCUMBERED_FLAG
, TO_CHAR(PLL.ENCUMBERED_DATE
, 'DD-MON-YYYY HH24:MI:SS') ENCUMBERED_DATE
, PLL.UNENCUMBERED_QUANTITY
, PLL.FOB_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, TO_CHAR(PLL.ESTIMATED_TAX_AMOUNT
, PGT.FORMAT_MASK) ESTIMATED_TAX_AMOUNT
, PLL.FROM_HEADER_ID
, PLL.FROM_LINE_ID
, PLL.FROM_LINE_LOCATION_ID
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, PLL.TERMS_ID
, PLL.APPROVED_FLAG
, TO_CHAR(PLL.APPROVED_DATE
, 'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE
, PLL.CLOSED_FLAG
, PLL.CANCELLED_BY
, PLL.FIRM_STATUS_LOOKUP_CODE
, TO_CHAR(PLL.FIRM_DATE
, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE
, TO_CHAR(PLL.LAST_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.LAST_UPDATE_LOGIN
, TO_CHAR(PLL.CREATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE
, PLL.CREATED_BY
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_REJECTED
, PLL.QUANTITY_BILLED
, NVL(MUM.UNIT_OF_MEASURE_TL
, PLL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE
, PLL.SHIP_VIA_LOOKUP_CODE
, PLL.GLOBAL_ATTRIBUTE_CATEGORY
, PLL.GLOBAL_ATTRIBUTE1
, PLL.GLOBAL_ATTRIBUTE2
, PLL.GLOBAL_ATTRIBUTE3
, PLL.GLOBAL_ATTRIBUTE4
, PLL.GLOBAL_ATTRIBUTE5
, PLL.GLOBAL_ATTRIBUTE6
, PLL.GLOBAL_ATTRIBUTE7
, PLL.GLOBAL_ATTRIBUTE8
, PLL.GLOBAL_ATTRIBUTE9
, PLL.GLOBAL_ATTRIBUTE10
, PLL.GLOBAL_ATTRIBUTE11
, PLL.GLOBAL_ATTRIBUTE12
, PLL.GLOBAL_ATTRIBUTE13
, PLL.GLOBAL_ATTRIBUTE14
, PLL.GLOBAL_ATTRIBUTE15
, PLL.GLOBAL_ATTRIBUTE16
, PLL.GLOBAL_ATTRIBUTE17
, PLL.GLOBAL_ATTRIBUTE18
, PLL.GLOBAL_ATTRIBUTE19
, PLL.GLOBAL_ATTRIBUTE20
, PLL.QUANTITY_SHIPPED
, PLL.COUNTRY_OF_ORIGIN_CODE
, PLL.TAX_USER_OVERRIDE_FLAG
, PLL.MATCH_OPTION
, PLL.TAX_CODE_ID
, PLL.CALCULATE_TAX_FLAG
, PLL.CHANGE_PROMISED_DATE_REASON
, PLL.NOTE_TO_RECEIVER
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.VMI_FLAG
, TO_CHAR(PLL.RETROACTIVE_DATE
, 'DD-MON-YYYY HH24:MI:SS') RETROACTIVE_DATE
, PLL.SUPPLIER_ORDER_LINE_NUMBER
, TO_CHAR(PO_CORE_S.GET_TOTAL('S'
, PLL.LINE_LOCATION_ID)
, PGT.FORMAT_MASK) AMOUNT
, TO_CHAR(PLL.AMOUNT_RECEIVED
, PGT.FORMAT_MASK) AMOUNT_RECEIVED
, TO_CHAR(PLL.AMOUNT_BILLED
, PGT.FORMAT_MASK) AMOUNT_BILLED
, TO_CHAR(PLL.AMOUNT_CANCELLED
, PGT.FORMAT_MASK) AMOUNT_CANCELLED
, TO_CHAR(PLL.AMOUNT_ACCEPTED
, PGT.FORMAT_MASK) AMOUNT_ACCEPTED
, TO_CHAR(PLL.AMOUNT_REJECTED
, PGT.FORMAT_MASK) AMOUNT_REJECTED
, PLL.DROP_SHIP_FLAG
, TO_CHAR(PLL.SALES_ORDER_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') SALES_ORDER_UPDATE_DATE
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PLL.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GET_ONETIME_LOC(PLL.SHIP_TO_LOCATION_ID)) IS_SHIPMENT_ONE_TIME_LOC
, DECODE(NVL(PLL.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GET_ONETIME_ADDRESS(PLL.LINE_LOCATION_ID)) ONE_TIME_ADDRESS_DETAILS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GET_DROP_SHIP_DETAILS(PLL.LINE_LOCATION_ID)
, NULL) DETAILS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTPHONE()
, NULL) SHIP_CONT_PHONE
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTEMAIL()
, NULL) SHIP_CONT_EMAIL
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTPHONE()
, NULL) ULTIMATE_DELIVER_CONT_PHONE
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTEMAIL()
, NULL) ULTIMATE_DELIVER_CONT_EMAIL
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTNAME()
, NULL) SHIP_CONT_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTNAME()
, NULL) ULTIMATE_DELIVER_CONT_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCUSTNAME()
, NULL) SHIP_CUST_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCUSTLOCATION()
, NULL) SHIP_CUST_LOCATION
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCUSTNAME()
, NULL) ULTIMATE_DELIVER_CUST_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCUSTLOCATION()
, NULL) ULTIMATE_DELIVER_CUST_LOCATION
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPCONTACTFAX()
, NULL) SHIP_TO_CONTACT_FAX
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTACTNAME()
, NULL) ULTIMATE_DELIVER_TO_CONT_NAME
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETDELIVERCONTACTFAX()
, NULL) ULTIMATE_DELIVER_TO_CONT_FAX
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPPINGMETHOD()
, NULL) SHIPPING_METHOD
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETSHIPPINGINSTRUCTIONS()
, NULL) SHIPPING_INSTRUCTIONS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETPACKINGINSTRUCTIONS()
, NULL) PACKING_INSTRUCTIONS
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPRODUCTDESC()
, NULL) CUSTOMER_PRODUCT_DESC
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPONUMBER()
, NULL) CUSTOMER_PO_NUM
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPOLINENUM()
, NULL) CUSTOMER_PO_LINE_NUM
, DECODE(PLL.DROP_SHIP_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETCUSTOMERPOSHIPMENTNUM()
, NULL) CUSTOMER_PO_SHIPMENT_NUM
, TO_CHAR(PLL.NEED_BY_DATE
, 'DD-MON-YYYY HH24:MI:SS') NEED_BY_DATE
, TO_CHAR( PLL.PROMISED_DATE
, 'DD-MON-YYYY HH24:MI:SS') PROMISED_DATE
, TO_CHAR(PLL.AMOUNT
, PGT.FORMAT_MASK) TOTAL_SHIPMENT_AMOUNT
, PLL.FINAL_MATCH_FLAG
, PLL.MANUAL_PRICE_CHANGE_FLAG
, PLL.TAX_NAME
, PLL.TRANSACTION_FLOW_HEADER_ID
, PLL.VALUE_BASIS
, PLL.MATCHING_BASIS
, PLL.PAYMENT_TYPE
, PLL.DESCRIPTION
, PLL.QUANTITY_FINANCED
, TO_CHAR(PLL.AMOUNT_FINANCED
, PGT.FORMAT_MASK) AMOUNT_FINANCED
, PLL.QUANTITY_RECOUPED
, TO_CHAR(PLL.AMOUNT_RECOUPED
, PGT.FORMAT_MASK) AMOUNT_RECOUPED
, TO_CHAR(PLL.RETAINAGE_WITHHELD_AMOUNT
, PGT.FORMAT_MASK) RETAINAGE_WITHHELD_AMOUNT
, TO_CHAR(PLL.RETAINAGE_RELEASED_AMOUNT
, PGT.FORMAT_MASK) RETAINAGE_RELEASED_AMOUNT
, PLL.WORK_APPROVER_ID
, PLL.BID_PAYMENT_ID
, TO_CHAR(PLL.AMOUNT_SHIPPED
, PGT.FORMAT_MASK) AMOUNT_SHIPPED
FROM PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL PL
, PO_COMMUNICATION_GT PGT
, MTL_UNITS_OF_MEASURE_TL MUM
WHERE PLL.PO_LINE_ID (+) = PL.PO_LINE_ID
AND (PLL.SHIPMENT_TYPE (+) = 'BLANKET'
AND PGT.PO_RELEASE_ID IS NOT NULL)
AND PLL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
AND MUM.LANGUAGE(+) = USERENV('LANG')