VIEW: APPS.POS_ASN_SHIPMENTS_V

Source

View Text - Preformatted

SELECT ASND.SESSION_ID, TO_CHAR(ASND.ASN_LINE_ID) || DECODE(ASND.ASN_LINE_SPLIT_ID, 0, '', '-' || TO_CHAR(ASND.ASN_LINE_SPLIT_ID)), ASND.ASN_LINE_ID, ASND.ASN_LINE_SPLIT_ID, ASND.PO_LINE_LOCATION_ID, fnd_number.number_to_canonical( decode(ASND.QUANTITY_SHIPPED, 0, decode(ASND.ASN_LINE_SPLIT_ID, 0, pos_quantities_s.getavailablequantity(ASND.PO_LINE_LOCATION_ID) ,ASND.QUANTITY_SHIPPED), ASND.QUANTITY_SHIPPED)), ASND.UNIT_OF_MEASURE, PLL.QUANTITY_RECEIVED, ASND.EXPECTED_RECEIPT_DATE, ASND.COMMENTS, PLL.QUANTITY, MSI.CONCATENATED_SEGMENTS, POL.ITEM_DESCRIPTION, POL.VENDOR_PRODUCT_NUM, decode(PRL.PO_RELEASE_ID, NULL, POH.SEGMENT1, POH.SEGMENT1 || '-' || to_char(PRL.RELEASE_NUM)), NVL(PLL.NEED_BY_DATE, PLL.PROMISED_DATE), decode(pos_osp_job.r_count(ASND.PO_LINE_LOCATION_ID), 1, pos_osp_job.get_osp_info(ASND.PO_LINE_LOCATION_ID), 0, pos_osp_job.get_osp_info(ASND.PO_LINE_LOCATION_ID), decode(ASND.wip_job_info, null, pos_osp_job.get_osp_info(ASND.PO_LINE_LOCATION_ID), ' ' || ASND.wip_job_info ) ), nvl(ASND.po_distribution_id, pos_osp_job.get_po_distribution_id(ASND.PO_LINE_LOCATION_ID)), nvl(ASND.wip_entity_id, pos_osp_job.get_wip_entity_id(ASND.PO_LINE_LOCATION_ID)), nvl(ASND.wip_line_id, pos_osp_job.get_wip_line_id(ASND.PO_LINE_LOCATION_ID)), nvl(ASND.wip_operation_seq_num, pos_osp_job.get_wip_seq_num(ASND.PO_LINE_LOCATION_ID)), POL.ITEM_ID, MUM.UOM_CLASS, POH.PO_HEADER_ID FROM POS_ASN_SHOP_CART_DETAILS ASND, PO_HEADERS POH, PO_LINES POL, PO_LINE_LOCATIONS PLL, PO_RELEASES PRL, MTL_SYSTEM_ITEMS_KFV MSI, MTL_UNITS_OF_MEASURE MUM WHERE ASND.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID AND PLL.PO_LINE_ID = POL.PO_LINE_ID AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID(+) AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND NVL(MSI.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID AND MUM.UNIT_OF_MEASURE = ASND.UNIT_OF_MEASURE

View Text - HTML Formatted

SELECT ASND.SESSION_ID
, TO_CHAR(ASND.ASN_LINE_ID) || DECODE(ASND.ASN_LINE_SPLIT_ID
, 0
, ''
, '-' || TO_CHAR(ASND.ASN_LINE_SPLIT_ID))
, ASND.ASN_LINE_ID
, ASND.ASN_LINE_SPLIT_ID
, ASND.PO_LINE_LOCATION_ID
, FND_NUMBER.NUMBER_TO_CANONICAL( DECODE(ASND.QUANTITY_SHIPPED
, 0
, DECODE(ASND.ASN_LINE_SPLIT_ID
, 0
, POS_QUANTITIES_S.GETAVAILABLEQUANTITY(ASND.PO_LINE_LOCATION_ID)
, ASND.QUANTITY_SHIPPED)
, ASND.QUANTITY_SHIPPED))
, ASND.UNIT_OF_MEASURE
, PLL.QUANTITY_RECEIVED
, ASND.EXPECTED_RECEIPT_DATE
, ASND.COMMENTS
, PLL.QUANTITY
, MSI.CONCATENATED_SEGMENTS
, POL.ITEM_DESCRIPTION
, POL.VENDOR_PRODUCT_NUM
, DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM))
, NVL(PLL.NEED_BY_DATE
, PLL.PROMISED_DATE)
, DECODE(POS_OSP_JOB.R_COUNT(ASND.PO_LINE_LOCATION_ID)
, 1
, POS_OSP_JOB.GET_OSP_INFO(ASND.PO_LINE_LOCATION_ID)
, 0
, POS_OSP_JOB.GET_OSP_INFO(ASND.PO_LINE_LOCATION_ID)
, DECODE(ASND.WIP_JOB_INFO
, NULL
, POS_OSP_JOB.GET_OSP_INFO(ASND.PO_LINE_LOCATION_ID)
, ' ' || ASND.WIP_JOB_INFO ) )
, NVL(ASND.PO_DISTRIBUTION_ID
, POS_OSP_JOB.GET_PO_DISTRIBUTION_ID(ASND.PO_LINE_LOCATION_ID))
, NVL(ASND.WIP_ENTITY_ID
, POS_OSP_JOB.GET_WIP_ENTITY_ID(ASND.PO_LINE_LOCATION_ID))
, NVL(ASND.WIP_LINE_ID
, POS_OSP_JOB.GET_WIP_LINE_ID(ASND.PO_LINE_LOCATION_ID))
, NVL(ASND.WIP_OPERATION_SEQ_NUM
, POS_OSP_JOB.GET_WIP_SEQ_NUM(ASND.PO_LINE_LOCATION_ID))
, POL.ITEM_ID
, MUM.UOM_CLASS
, POH.PO_HEADER_ID
FROM POS_ASN_SHOP_CART_DETAILS ASND
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS PLL
, PO_RELEASES PRL
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_UNITS_OF_MEASURE MUM
WHERE ASND.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID(+)
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND NVL(MSI.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID
AND MUM.UNIT_OF_MEASURE = ASND.UNIT_OF_MEASURE