ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 1A6953417EC84A65AA399CDBC63186BDY select null action_, null status_, null message_, null modified_columns_, to_number(null) request_id_, to_number(null) header_row_id, to_number(null) line_row_id, rsh.shipment_header_id, rsl.shipment_line_id, mp.organization_code ship_to_organization, aps.vendor_name supplier, assa.vendor_site_code supplier_site, rsh.shipment_num shipment_number, xxen_util.meaning(rsh.asn_type,'ASN_TYPE',201) asn_type, rsh.shipped_date shipment_date, rsh.expected_receipt_date, rsh.freight_carrier_code freight_carrier, xxen_util.meaning(rsh.freight_terms,'FREIGHT TERMS',201) freight_terms, rsh.bill_of_lading, rsh.packing_slip, rsh.waybill_airbill_num, rsh.num_of_containers number_of_containers, rsh.freight_bill_number, rsh.gross_weight, rsh.gross_weight_uom_code gross_weight_uom, rsh.net_weight, rsh.net_weight_uom_code net_weight_uom, rsh.tar_weight tare_weight, rsh.tar_weight_uom_code tare_weight_uom, rsh.packaging_code, rsh.special_handling_code, rsh.comments, rsh.invoice_num invoice_number, rsh.invoice_date, rsh.invoice_amount, rsh.tax_amount, rsh.freight_amount, (select atv.name from ap_terms_vl atv where rsh.payment_terms_id=atv.term_id) payment_terms, pha.segment1 po_number, pra.release_num release_number, pla.line_num, plla.shipment_num po_shipment_num, msiv.concatenated_segments item, pla.item_description, rsl.unit_of_measure, plla.quantity quantity_ordered, plla.quantity_received, plla.quantity_shipped, rsl.quantity_shipped ship_quantity, rsl.packing_slip line_packing_slip, rsl.vendor_lot_num, rsl.container_num, rsl.truck_num, (select ftv.territory_short_name from fnd_territories_vl ftv where rsl.country_of_origin_code=ftv.territory_code) country_of_origin, rsl.comments line_comments, nvl(xxen_util.meaning(rsl.shipment_line_status_code,'SHIPMENT LINE STATUS',201),rsl.shipment_line_status_code) line_status, null cancel_shipment, to_number(null) upload_row from mtl_parameters mp, rcv_shipment_headers rsh, ap_suppliers aps, ap_supplier_sites_all assa, rcv_shipment_lines rsl, po_line_locations_all plla, po_lines_all pla, po_headers_all pha, po_releases_all pra, mtl_system_items_vl msiv where mp.organization_id=rsh.ship_to_org_id and rsh.asn_type in ('ASN','ASBN') and rsh.vendor_id=aps.vendor_id and rsh.vendor_site_id=assa.vendor_site_id(+) and rsh.shipment_header_id=rsl.shipment_header_id and rsl.shipment_line_status_code in ('EXPECTED','PARTIALLY RECEIVED') and rsl.po_line_location_id=plla.line_location_id and plla.po_line_id=pla.po_line_id and pla.po_header_id=pha.po_header_id and plla.po_release_id=pra.po_release_id(+) and rsl.item_id=msiv.inventory_item_id(+) and rsl.to_organization_id=msiv.organization_id(+) and 1=1 Initial version. U A xxen_po_upload.upload_asn xxen_po_upload.post_asn US PO Advance Shipment Notice Upload Creates advance shipment notices (ASN) and advance shipment and billing notices (ASBN) against approved purchase order shipments, as a supplier does in iSupplier Portal or by EDI, and cancels them. Each row is one shipment line. Rows with the same Ship-To Organization, supplier and Shipment Number form one shipment, whose header values are taken from its first row. The supplier and supplier site are those of the purchase order. The rows are loaded into the receiving open interface and processed by the Receiving Transaction Processor; its errors are shown in the Message column. Create, Update downloads the open lines of existing shipments. Cancel Shipment cancels a shipment that has not been received yet, with all of its lines. Shipments shipped more than 12 months ago cannot be cancelled. An ASBN needs Invoice Number, Invoice Date and Invoice Amount; the Receiving Transaction Processor passes its invoice on to Payables Open Interface Import. Enginatics R12 only Upload 1=1 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 rsh.ship_to_org_id=:ship_to_organization_id LOV INV Organization Code (accessible) FA6954EC34B240D9A30C047DDF5BD14D select mp.organization_id id, mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code Y US Ship To Organization 3 30 1=1 aps.vendor_name=:supplier LOV custom select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where aps.vendor_id in (select rsh.vendor_id from rcv_shipment_headers rsh where rsh.asn_type in ('ASN','ASBN') and rsh.ship_to_org_id=:$flex$.ship_to_organization) order by aps.vendor_name US Supplier 4 40 1=1 rsh.shipment_num=:shipment_number LOV custom select distinct rsh.shipment_num value, aps.vendor_name description from rcv_shipment_headers rsh, ap_suppliers aps where rsh.ship_to_org_id=:$flex$.ship_to_organization and rsh.asn_type in ('ASN','ASBN') and rsh.vendor_id=aps.vendor_id and (:$flex$.supplier is null or aps.vendor_name=:$flex$.supplier) order by 1 US Shipment Number 5 50 1=1 pha.segment1=:po_number Char US PO Number 6 60 1=1 rsh.shipped_date>=:shipment_date_from Date US Shipment Date From 7 70 1=1 rsh.shipped_date<:shipment_date_to+1 Date :$flex$.shipment_date_from US Shipment Date To 1C09D43211BA40CD9AC0E83F498EEF89 Advance Shipment and Billing Notice Creates ASBNs, with the invoice fields. ENGINATICS 5 ASN_TYPE Y 10 BILL_OF_LADING 14 COMMENTS 34 CONTAINER_NUM 36 COUNTRY_OF_ORIGIN 7 EXPECTED_RECEIPT_DATE 19 FREIGHT_AMOUNT 8 FREIGHT_CARRIER 9 FREIGHT_TERMS 17 INVOICE_AMOUNT 16 INVOICE_DATE 15 INVOICE_NUMBER 25 ITEM 26 ITEM_DESCRIPTION 37 LINE_COMMENTS 23 LINE_NUM 32 LINE_PACKING_SLIP 13 NUMBER_OF_CONTAINERS 11 PACKING_SLIP 20 PAYMENT_TERMS 21 PO_NUMBER 24 PO_SHIPMENT_NUM 28 QUANTITY_ORDERED 29 QUANTITY_RECEIVED 30 QUANTITY_SHIPPED 22 RELEASE_NUMBER 6 SHIPMENT_DATE 4 SHIPMENT_NUMBER 31 SHIP_QUANTITY 1 SHIP_TO_ORGANIZATION 2 SUPPLIER 3 SUPPLIER_SITE 18 TAX_AMOUNT 35 TRUCK_NUM 27 UNIT_OF_MEASURE 33 VENDOR_LOT_NUM 12 WAYBILL_AIRBILL_NUM Upload Mode C Y S Site PO Number Shipment Date From Shipment Date To Shipment Number Supplier CE82987D41C64401A87A8A15348D35AC All Columns All shipment header and line fields, including the invoice, weight and packaging fields. ENGINATICS 5 ASN_TYPE Y 10 BILL_OF_LADING 48 CANCEL_SHIPMENT 14 COMMENTS 43 CONTAINER_NUM 45 COUNTRY_OF_ORIGIN 7 EXPECTED_RECEIPT_DATE 28 FREIGHT_AMOUNT 15 FREIGHT_BILL_NUMBER 8 FREIGHT_CARRIER 9 FREIGHT_TERMS 16 GROSS_WEIGHT 17 GROSS_WEIGHT_UOM 26 INVOICE_AMOUNT 25 INVOICE_DATE 24 INVOICE_NUMBER 34 ITEM 35 ITEM_DESCRIPTION 46 LINE_COMMENTS 32 LINE_NUM 41 LINE_PACKING_SLIP 47 LINE_STATUS 18 NET_WEIGHT 19 NET_WEIGHT_UOM 13 NUMBER_OF_CONTAINERS 22 PACKAGING_CODE 11 PACKING_SLIP 29 PAYMENT_TERMS 30 PO_NUMBER 33 PO_SHIPMENT_NUM 37 QUANTITY_ORDERED 38 QUANTITY_RECEIVED 39 QUANTITY_SHIPPED 31 RELEASE_NUMBER 6 SHIPMENT_DATE 4 SHIPMENT_NUMBER 40 SHIP_QUANTITY 1 SHIP_TO_ORGANIZATION 23 SPECIAL_HANDLING_CODE 2 SUPPLIER 3 SUPPLIER_SITE 20 TARE_WEIGHT 21 TARE_WEIGHT_UOM 27 TAX_AMOUNT 44 TRUCK_NUM 36 UNIT_OF_MEASURE 42 VENDOR_LOT_NUM 12 WAYBILL_AIRBILL_NUM Upload Mode C S Site 7A0FF2C90D5A46E8B8A90419D472E7D8 Cancel Shipments Downloads the open lines of existing shipments to cancel them. ENGINATICS 5 ASN_TYPE Y 17 CANCEL_SHIPMENT 7 EXPECTED_RECEIPT_DATE 12 ITEM 13 ITEM_DESCRIPTION 10 LINE_NUM 16 LINE_STATUS 8 PO_NUMBER 11 PO_SHIPMENT_NUM 9 RELEASE_NUMBER 6 SHIPMENT_DATE 4 SHIPMENT_NUMBER 15 SHIP_QUANTITY 1 SHIP_TO_ORGANIZATION 2 SUPPLIER 3 SUPPLIER_SITE 14 UNIT_OF_MEASURE Upload Mode C,U Y S Site 8B30351CB7604376AAE19303C8BD3AF1 Default Creates shipment notices with the main shipment header and line fields, and cancels downloaded shipments. ENGINATICS 5 ASN_TYPE Y 10 BILL_OF_LADING 33 CANCEL_SHIPMENT 14 COMMENTS 28 CONTAINER_NUM 30 COUNTRY_OF_ORIGIN 7 EXPECTED_RECEIPT_DATE 8 FREIGHT_CARRIER 9 FREIGHT_TERMS 19 ITEM 20 ITEM_DESCRIPTION 31 LINE_COMMENTS 17 LINE_NUM 26 LINE_PACKING_SLIP 32 LINE_STATUS 13 NUMBER_OF_CONTAINERS 11 PACKING_SLIP 15 PO_NUMBER 18 PO_SHIPMENT_NUM 22 QUANTITY_ORDERED 23 QUANTITY_RECEIVED 24 QUANTITY_SHIPPED 16 RELEASE_NUMBER 6 SHIPMENT_DATE 4 SHIPMENT_NUMBER 25 SHIP_QUANTITY 1 SHIP_TO_ORGANIZATION 2 SUPPLIER 3 SUPPLIER_SITE 29 TRUCK_NUM 21 UNIT_OF_MEASURE 27 VENDOR_LOT_NUM 12 WAYBILL_AIRBILL_NUM Upload Mode C S Site 8B30351CB7604376AAE19303C8BD3AF1 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 REQUEST_ID_ Number number Y 6 HEADER_ROW_ID Number number Y Y 7 LINE_ROW_ID Number number Y Y 8 SHIPMENT_HEADER_ID Number number Y Y 9 SHIPMENT_LINE_ID Number number Y Y 10 SHIP_TO_ORGANIZATION LOV varchar2 3 INV Organization Code (accessible) FA6954EC34B240D9A30C047DDF5BD14D select mp.organization_id id, mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code :$flex$.ship_to_organization.value Y 11 SUPPLIER Char varchar2 240 select distinct aps.vendor_name from mtl_parameters mp, po_headers_all pha, ap_suppliers aps where mp.organization_code=:$column$.ship_to_organization and pha.segment1=:$column$.po_number and pha.po_header_id in (select plla.po_header_id from po_line_locations_all plla where plla.ship_to_organization_id=mp.organization_id) and pha.vendor_id=aps.vendor_id Y 12 SUPPLIER_SITE Char varchar2 15 select distinct assa.vendor_site_code from mtl_parameters mp, po_headers_all pha, ap_supplier_sites_all assa where mp.organization_code=:$column$.ship_to_organization and pha.segment1=:$column$.po_number and pha.po_header_id in (select plla.po_header_id from po_line_locations_all plla where plla.ship_to_organization_id=mp.organization_id) and pha.vendor_site_id=assa.vendor_site_id Y 13 SHIPMENT_NUMBER Char varchar2 30 Y 14 ASN_TYPE LOV custom varchar2 select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='ASN_TYPE' and flvv.view_application_id=201 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code in ('ASN','ASBN') order by flvv.meaning xxen_util.meaning('ASN','ASN_TYPE',201) Y 15 SHIPMENT_DATE Date date sysdate Y 16 EXPECTED_RECEIPT_DATE Date date :$column$.shipment_date Y 17 FREIGHT_CARRIER LOV custom varchar2 25 select ofv.freight_code value, ofv.description from mtl_parameters mp, org_freight_vl ofv where mp.organization_code=:$column$.ship_to_organization and mp.organization_id=ofv.organization_id and nvl(ofv.disable_date,sysdate+1)>sysdate order by ofv.freight_code 18 FREIGHT_TERMS LOV custom varchar2 select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='FREIGHT TERMS' and flvv.view_application_id=201 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and nvl(flvv.end_date_active,sysdate+1)>sysdate order by flvv.meaning 19 BILL_OF_LADING Char varchar2 25 20 PACKING_SLIP Char varchar2 25 21 WAYBILL_AIRBILL_NUM Char varchar2 30 22 NUMBER_OF_CONTAINERS Number number 23 FREIGHT_BILL_NUMBER Char varchar2 35 24 GROSS_WEIGHT Number number 25 GROSS_WEIGHT_UOM LOV varchar2 3 INV UOM Code (active, translated) 8AEDD3EE31DF40488E61A436D2B0B86A select muomv.uom_code id, muomv.uom_code value, muomv.unit_of_measure description from mtl_units_of_measure_vl muomv where muomv.disable_date is null order by 1 26 NET_WEIGHT Number number 27 NET_WEIGHT_UOM LOV varchar2 3 INV UOM Code (active, translated) 8AEDD3EE31DF40488E61A436D2B0B86A select muomv.uom_code id, muomv.uom_code value, muomv.unit_of_measure description from mtl_units_of_measure_vl muomv where muomv.disable_date is null order by 1 28 TARE_WEIGHT Number number 29 TARE_WEIGHT_UOM LOV varchar2 3 INV UOM Code (active, translated) 8AEDD3EE31DF40488E61A436D2B0B86A select muomv.uom_code id, muomv.uom_code value, muomv.unit_of_measure description from mtl_units_of_measure_vl muomv where muomv.disable_date is null order by 1 30 PACKAGING_CODE Char varchar2 5 31 SPECIAL_HANDLING_CODE Char varchar2 3 32 COMMENTS Char varchar2 240 33 INVOICE_NUMBER Char varchar2 30 34 INVOICE_DATE Date date 35 INVOICE_AMOUNT Number number 36 TAX_AMOUNT Number number 37 FREIGHT_AMOUNT Number number 38 PAYMENT_TERMS LOV varchar2 50 AP Payment Terms 0185D467AD6954E0E0630100007F5847 select atv.term_id id, atv.name value, atv.description description from ap_terms_vl atv where atv.enabled_flag = 'Y' and trunc(sysdate) between nvl(atv.start_date_active,trunc(sysdate)) and nvl(atv.end_date_active,trunc(sysdate)) order by value 39 PO_NUMBER LOV custom varchar2 20 select distinct pha.segment1 value, aps.vendor_name description from mtl_parameters mp, po_line_locations_all plla, po_headers_all pha, ap_suppliers aps where mp.organization_code=:$column$.ship_to_organization and plla.ship_to_organization_id=mp.organization_id and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and plla.approved_flag='Y' and nvl(plla.cancel_flag,'N')='N' and nvl(plla.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING') and plla.po_header_id=pha.po_header_id and pha.vendor_id=aps.vendor_id order by 1 Y 40 RELEASE_NUMBER LOV custom number select distinct pra.release_num value, null description from mtl_parameters mp, po_line_locations_all plla, po_releases_all pra, po_headers_all pha where mp.organization_code=:$column$.ship_to_organization and plla.ship_to_organization_id=mp.organization_id and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and plla.approved_flag='Y' and nvl(plla.cancel_flag,'N')='N' and nvl(plla.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING') and plla.po_release_id=pra.po_release_id and pra.po_header_id=pha.po_header_id and pha.segment1=:$column$.po_number order by 1 41 LINE_NUM LOV custom number select distinct pla.line_num value, pla.item_description description from mtl_parameters mp, po_headers_all pha, po_lines_all pla, po_line_locations_all plla where mp.organization_code=:$column$.ship_to_organization and pha.segment1=:$column$.po_number and pha.po_header_id=pla.po_header_id and pla.po_line_id=plla.po_line_id and plla.ship_to_organization_id=mp.organization_id and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and plla.approved_flag='Y' and nvl(plla.cancel_flag,'N')='N' and nvl(plla.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING') order by 1 Y 42 PO_SHIPMENT_NUM LOV custom number select plla.shipment_num value, plla.quantity||' '||pla.unit_meas_lookup_code||' ordered, '||nvl(plla.quantity_shipped,0)||' shipped, '||nvl(plla.quantity_received,0)||' received' description from mtl_parameters mp, po_line_locations_all plla, po_lines_all pla, po_headers_all pha where mp.organization_code=:$column$.ship_to_organization and plla.ship_to_organization_id=mp.organization_id and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and plla.approved_flag='Y' and nvl(plla.cancel_flag,'N')='N' and nvl(plla.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING') and decode((select pra.release_num from po_releases_all pra where plla.po_release_id=pra.po_release_id),:$column$.release_number,1)=1 and plla.po_line_id=pla.po_line_id and pla.line_num=:$column$.line_num and pla.po_header_id=pha.po_header_id and pha.segment1=:$column$.po_number order by 1 Y 43 ITEM Char varchar2 40 select distinct msiv.concatenated_segments from mtl_parameters mp, po_headers_all pha, po_lines_all pla, mtl_system_items_vl msiv where mp.organization_code=:$column$.ship_to_organization and pha.segment1=:$column$.po_number and pha.po_header_id=pla.po_header_id and pla.line_num=:$column$.line_num and pla.po_line_id in (select plla.po_line_id from po_line_locations_all plla where plla.ship_to_organization_id=mp.organization_id) and pla.item_id=msiv.inventory_item_id and msiv.organization_id=mp.organization_id Y 44 ITEM_DESCRIPTION Char varchar2 240 select distinct pla.item_description from mtl_parameters mp, po_headers_all pha, po_lines_all pla where mp.organization_code=:$column$.ship_to_organization and pha.segment1=:$column$.po_number and pha.po_header_id=pla.po_header_id and pla.line_num=:$column$.line_num and pla.po_line_id in (select plla.po_line_id from po_line_locations_all plla where plla.ship_to_organization_id=mp.organization_id) Y 45 UNIT_OF_MEASURE Char varchar2 25 select distinct pla.unit_meas_lookup_code from mtl_parameters mp, po_headers_all pha, po_lines_all pla where mp.organization_code=:$column$.ship_to_organization and pha.segment1=:$column$.po_number and pha.po_header_id=pla.po_header_id and pla.line_num=:$column$.line_num and pla.po_line_id in (select plla.po_line_id from po_line_locations_all plla where plla.ship_to_organization_id=mp.organization_id) Y 46 QUANTITY_ORDERED Number number select plla.quantity from mtl_parameters mp, po_line_locations_all plla, po_lines_all pla, po_headers_all pha where mp.organization_code=:$column$.ship_to_organization and plla.ship_to_organization_id=mp.organization_id and plla.shipment_num=:$column$.po_shipment_num and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and decode((select pra.release_num from po_releases_all pra where plla.po_release_id=pra.po_release_id),:$column$.release_number,1)=1 and plla.po_line_id=pla.po_line_id and pla.line_num=:$column$.line_num and pla.po_header_id=pha.po_header_id and pha.segment1=:$column$.po_number Y 47 QUANTITY_RECEIVED Number number select plla.quantity_received from mtl_parameters mp, po_line_locations_all plla, po_lines_all pla, po_headers_all pha where mp.organization_code=:$column$.ship_to_organization and plla.ship_to_organization_id=mp.organization_id and plla.shipment_num=:$column$.po_shipment_num and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and decode((select pra.release_num from po_releases_all pra where plla.po_release_id=pra.po_release_id),:$column$.release_number,1)=1 and plla.po_line_id=pla.po_line_id and pla.line_num=:$column$.line_num and pla.po_header_id=pha.po_header_id and pha.segment1=:$column$.po_number Y 48 QUANTITY_SHIPPED Number number select plla.quantity_shipped from mtl_parameters mp, po_line_locations_all plla, po_lines_all pla, po_headers_all pha where mp.organization_code=:$column$.ship_to_organization and plla.ship_to_organization_id=mp.organization_id and plla.shipment_num=:$column$.po_shipment_num and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and decode((select pra.release_num from po_releases_all pra where plla.po_release_id=pra.po_release_id),:$column$.release_number,1)=1 and plla.po_line_id=pla.po_line_id and pla.line_num=:$column$.line_num and pla.po_header_id=pha.po_header_id and pha.segment1=:$column$.po_number Y 49 SHIP_QUANTITY Number number Y 50 LINE_PACKING_SLIP Char varchar2 25 51 VENDOR_LOT_NUM Char varchar2 30 52 CONTAINER_NUM Char varchar2 35 53 TRUCK_NUM Char varchar2 35 54 COUNTRY_OF_ORIGIN LOV varchar2 80 Country 8E2FF36EDE8379D2E0530100007F1FF2 select ftv.territory_code id, ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name 55 LINE_COMMENTS Char varchar2 240 56 LINE_STATUS Char varchar2 80 Y 57 CANCEL_SHIPMENT LOV custom varchar2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.shipment_header_id is not null 58 UPLOAD_ROW Number number Y U 1 p_ship_to_organization_id in Number SHIP_TO_ORGANIZATION U 2 p_shipment_number in Char SHIPMENT_NUMBER U 3 p_asn_type in Char ASN_TYPE U 4 p_shipment_date in Date SHIPMENT_DATE U 5 p_expected_receipt_date in Date EXPECTED_RECEIPT_DATE U 6 p_freight_carrier in Char Y FREIGHT_CARRIER U 7 p_freight_terms in Char Y FREIGHT_TERMS U 8 p_bill_of_lading in Char Y BILL_OF_LADING U 9 p_packing_slip in Char Y PACKING_SLIP U 10 p_waybill_airbill_num in Char Y WAYBILL_AIRBILL_NUM U 11 p_number_of_containers in Number Y NUMBER_OF_CONTAINERS U 12 p_freight_bill_number in Char Y FREIGHT_BILL_NUMBER U 13 p_gross_weight in Number Y GROSS_WEIGHT U 14 p_gross_weight_uom in Char Y GROSS_WEIGHT_UOM U 15 p_net_weight in Number Y NET_WEIGHT U 16 p_net_weight_uom in Char Y NET_WEIGHT_UOM U 17 p_tare_weight in Number Y TARE_WEIGHT U 18 p_tare_weight_uom in Char Y TARE_WEIGHT_UOM U 19 p_packaging_code in Char Y PACKAGING_CODE U 20 p_special_handling_code in Char Y SPECIAL_HANDLING_CODE U 21 p_comments in Char Y COMMENTS U 22 p_invoice_number in Char Y INVOICE_NUMBER U 23 p_invoice_date in Date Y INVOICE_DATE U 24 p_invoice_amount in Number Y INVOICE_AMOUNT U 25 p_tax_amount in Number Y TAX_AMOUNT U 26 p_freight_amount in Number Y FREIGHT_AMOUNT U 27 p_payment_terms_id in Number Y PAYMENT_TERMS U 28 p_po_number in Char PO_NUMBER U 29 p_release_number in Number Y RELEASE_NUMBER U 30 p_line_num in Number LINE_NUM U 31 p_po_shipment_num in Number PO_SHIPMENT_NUM U 32 p_ship_quantity in Number Y SHIP_QUANTITY U 33 p_line_packing_slip in Char Y LINE_PACKING_SLIP U 34 p_vendor_lot_num in Char Y VENDOR_LOT_NUM U 35 p_container_num in Char Y CONTAINER_NUM U 36 p_truck_num in Char Y TRUCK_NUM U 37 p_country_of_origin_code in Char Y COUNTRY_OF_ORIGIN U 38 p_line_comments in Char Y LINE_COMMENTS U 39 p_cancel_shipment in Char Y CANCEL_SHIPMENT U 40 p_shipment_header_id in Number Y SHIPMENT_HEADER_ID U 41 p_header_row_id in Number Y HEADER_ROW_ID U 42 p_line_row_id in Number Y LINE_ROW_ID U 43 p_header_row_id_out out Number HEADER_ROW_ID U 44 p_line_row_id_out out Number LINE_ROW_ID U 45 status_ out Char STATUS_CODE_ U 46 message_ out Char MESSAGE_ P 1 p_ship_to_organization_id in Number SHIP_TO_ORGANIZATION P 2 request_id_ out Number REQUEST_ID_ P 3 status_ out Char STATUS_CODE_ P 4 message_ out Char MESSAGE_ Error SQL select xu.action_, xxen_upload.status_meaning(xxen_upload.status_error) status_, coalesce( (select listagg(pie.error_message,chr(10)) within group (order by pie.interface_transaction_id) from po_interface_errors pie where pie.interface_line_id=xu.line_row_id and pie.table_name in ('RCV_HEADERS_INTERFACE','RCV_TRANSACTIONS_INTERFACE')), ( select listagg(x.error_message,chr(10)) within group (order by x.interface_transaction_id) from (select pie.error_message, pie.interface_transaction_id from po_interface_errors pie where pie.interface_header_id=xu.header_row_id and pie.table_name in ('RCV_HEADERS_INTERFACE','RCV_TRANSACTIONS_INTERFACE') and rownum<=10) x ), xu.message_, 'No error message returned. Check the Receiving Transaction Processor request '||xu.request_id_||'.' ) message_, xu.modified_columns_, xu.request_id_, xu.header_row_id, xu.line_row_id, xu.shipment_header_id, xu.shipment_line_id, xu.ship_to_organization, xu.supplier, xu.supplier_site, xu.shipment_number, xu.asn_type, xu.shipment_date, xu.expected_receipt_date, xu.freight_carrier, xu.freight_terms, xu.bill_of_lading, xu.packing_slip, xu.waybill_airbill_num, xu.number_of_containers, xu.freight_bill_number, xu.gross_weight, xu.gross_weight_uom, xu.net_weight, xu.net_weight_uom, xu.tare_weight, xu.tare_weight_uom, xu.packaging_code, xu.special_handling_code, xu.comments, xu.invoice_number, xu.invoice_date, xu.invoice_amount, xu.tax_amount, xu.freight_amount, xu.payment_terms, xu.po_number, xu.release_number, xu.line_num, xu.po_shipment_num, xu.item, xu.item_description, xu.unit_of_measure, xu.quantity_ordered, xu.quantity_received, xu.quantity_shipped, xu.ship_quantity, xu.line_packing_slip, xu.vendor_lot_num, xu.container_num, xu.truck_num, xu.country_of_origin, xu.line_comments, xu.line_status, xu.cancel_shipment, xu.upload_row from xxen_po_advance_shipmen_4357_u xu where nvl(xu.status_code_,xxen_upload.status_error)<>xxen_upload.status_success or xu.line_row_id in (select rti.interface_transaction_id from rcv_transactions_interface rti) or xu.header_row_id in (select rhi.header_interface_id from rcv_headers_interface rhi where rhi.processing_status_code='ERROR') or xu.cancel_shipment is not null and xu.header_row_id in (select rti.header_interface_id from rcv_transactions_interface rti) Order By upload_row, ship_to_organization, supplier, shipment_number, po_number, line_num, po_shipment_num Success SQL select xu.action_, xu.status_, case when rsl.shipment_line_status_code='CANCELLED' then 'Shipment cancelled.' when xu.header_row_id is null then 'No change.' else xxen_util.meaning(rsh.asn_type,'ASN_TYPE',201)||' '||rsh.shipment_num||' created.' end message_, xu.modified_columns_, xu.request_id_, xu.header_row_id, xu.line_row_id, xu.shipment_header_id, xu.shipment_line_id, mp.organization_code ship_to_organization, aps.vendor_name supplier, assa.vendor_site_code supplier_site, rsh.shipment_num shipment_number, xxen_util.meaning(rsh.asn_type,'ASN_TYPE',201) asn_type, rsh.shipped_date shipment_date, rsh.expected_receipt_date, rsh.freight_carrier_code freight_carrier, xxen_util.meaning(rsh.freight_terms,'FREIGHT TERMS',201) freight_terms, rsh.bill_of_lading, rsh.packing_slip, rsh.waybill_airbill_num, rsh.num_of_containers number_of_containers, rsh.freight_bill_number, rsh.gross_weight, rsh.gross_weight_uom_code gross_weight_uom, rsh.net_weight, rsh.net_weight_uom_code net_weight_uom, rsh.tar_weight tare_weight, rsh.tar_weight_uom_code tare_weight_uom, rsh.packaging_code, rsh.special_handling_code, rsh.comments, rsh.invoice_num invoice_number, rsh.invoice_date, rsh.invoice_amount, rsh.tax_amount, rsh.freight_amount, (select atv.name from ap_terms_vl atv where rsh.payment_terms_id=atv.term_id) payment_terms, pha.segment1 po_number, (select pra.release_num from po_releases_all pra where plla.po_release_id=pra.po_release_id) release_number, pla.line_num, plla.shipment_num po_shipment_num, (select msiv.concatenated_segments from mtl_system_items_vl msiv where rsl.item_id=msiv.inventory_item_id and rsl.to_organization_id=msiv.organization_id) item, pla.item_description, rsl.unit_of_measure, plla.quantity quantity_ordered, plla.quantity_received, plla.quantity_shipped, rsl.quantity_shipped ship_quantity, rsl.packing_slip line_packing_slip, rsl.vendor_lot_num, rsl.container_num, rsl.truck_num, (select ftv.territory_short_name from fnd_territories_vl ftv where rsl.country_of_origin_code=ftv.territory_code) country_of_origin, rsl.comments line_comments, nvl(xxen_util.meaning(rsl.shipment_line_status_code,'SHIPMENT LINE STATUS',201),rsl.shipment_line_status_code) line_status, xu.cancel_shipment, xu.upload_row from xxen_po_advance_shipmen_4357_u xu, rcv_shipment_headers rsh, mtl_parameters mp, ap_suppliers aps, ap_supplier_sites_all assa, rcv_shipment_lines rsl, po_line_locations_all plla, po_lines_all pla, po_headers_all pha where xu.status_code_=xxen_upload.status_success and nvl(xu.line_row_id,-1) not in (select rti.interface_transaction_id from rcv_transactions_interface rti) and nvl(xu.header_row_id,-1) not in (select rhi.header_interface_id from rcv_headers_interface rhi where rhi.processing_status_code='ERROR') and (xu.cancel_shipment is null or nvl(xu.header_row_id,-1) not in (select rti.header_interface_id from rcv_transactions_interface rti where rti.header_interface_id is not null)) and nvl(xu.shipment_header_id,(select rhi.receipt_header_id from rcv_headers_interface rhi where xu.header_row_id=rhi.header_interface_id))=rsh.shipment_header_id and rsh.ship_to_org_id=mp.organization_id and rsh.vendor_id=aps.vendor_id and rsh.vendor_site_id=assa.vendor_site_id(+) and rsh.shipment_header_id=rsl.shipment_header_id and nvl(xu.shipment_line_id,rsl.shipment_line_id)=rsl.shipment_line_id and rsl.po_line_location_id=plla.line_location_id and plla.shipment_num=xu.po_shipment_num and plla.po_line_id=pla.po_line_id and pla.line_num=xu.line_num and pla.po_header_id=pha.po_header_id and pha.segment1=xu.po_number