[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select segment1
from mtl_system_items
where inventory_item_id = p_item_id and
organization_id=p_organization_id;
l_interface_transaction_id := gml_rcv_std_rcpt_apis.insert_txn_interface
(p_rcv_transaction_rec,
p_rcv_rcpt_rec,
p_group_id,
l_transaction_type,
p_organization_id,
p_rcv_transaction_rec.deliver_to_location_id,
p_source_type);
SELECT
'N' LINE_CHKBOX,
'VENDOR' SOURCE_TYPE_CODE,
'VENDOR' RECEIPT_SOURCE_CODE,
'PO' ORDER_TYPE_CODE,
'' ORDER_TYPE,
POLL.PO_HEADER_ID PO_HEADER_ID,
POH.SEGMENT1 PO_NUMBER,
POLL.PO_LINE_ID PO_LINE_ID,
POL.LINE_NUM PO_LINE_NUMBER,
POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,
POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,
POLL.PO_RELEASE_ID PO_RELEASE_ID,
POR.RELEASE_NUM PO_RELEASE_NUMBER,
TO_NUMBER(NULL) REQ_HEADER_ID,
NULL REQ_NUMBER,
TO_NUMBER(NULL) REQ_LINE_ID,
TO_NUMBER(NULL) REQ_LINE,
TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID,
POH.SEGMENT1 RCV_SHIPMENT_NUMBER,
POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID,
POL.LINE_NUM RCV_LINE_NUMBER,
POH.PO_HEADER_ID FROM_ORGANIZATION_ID,
POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
POH.VENDOR_ID VENDOR_ID,
'' SOURCE,
POH.VENDOR_SITE_ID VENDOR_SITE_ID,
'' OUTSIDE_OPERATION_FLAG,
POL.ITEM_ID ITEM_ID,
NULL uom_code,
POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM,
MUM.UOM_CLASS PRIMARY_UOM_CLASS,
NULL ITEM_ALLOWED_UNITS_LOOKUP_CODE,
NULL ITEM_LOCATOR_CONTROL,
'' RESTRICT_LOCATORS_CODE,
'' RESTRICT_SUBINVENTORIES_CODE,
NULL SHELF_LIFE_CODE,
NULL SHELF_LIFE_DAYS,
MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
NULL ITEM_REV_CONTROL_FLAG_FROM,
NULL ITEM_NUMBER,
POL.ITEM_REVISION ITEM_REVISION,
POL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
POL.CATEGORY_ID ITEM_CATEGORY_ID,
'' HAZARD_CLASS,
'' UN_NUMBER,
POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER,
POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
'' SHIP_TO_LOCATION,
NULL PACKING_SLIP,
POLL.RECEIVING_ROUTING_ID ROUTING_ID,
'' ROUTING_NAME,
POLL.NEED_BY_DATE NEED_BY_DATE,
NVL(POLL.PROMISED_DATE,POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE,
POLL.QUANTITY ORDERED_QTY,
POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM,
NULL USSGL_TRANSACTION_CODE,
POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,
POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE,
NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE) UNIT_PRICE,
POH.CURRENCY_CODE CURRENCY_CODE,
POH.RATE_TYPE CURRENCY_CONVERSION_TYPE,
POH.RATE_DATE CURRENCY_CONVERSION_DATE,
POH.RATE CURRENCY_CONVERSION_RATE,
POH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
pod.destination_type_code DESTINATION_TYPE_CODE,
pod.deliver_to_person_id DELIVER_TO_PERSON_ID,
pod.deliver_to_location_id DELIVER_TO_LOCATION_ID,
pod.destination_subinventory DESTINATION_SUBINVENTORY,
POLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
POLL.ATTRIBUTE1 ATTRIBUTE1,
POLL.ATTRIBUTE2 ATTRIBUTE2,
POLL.ATTRIBUTE3 ATTRIBUTE3,
POLL.ATTRIBUTE4 ATTRIBUTE4,
POLL.ATTRIBUTE5 ATTRIBUTE5,
POLL.ATTRIBUTE6 ATTRIBUTE6,
POLL.ATTRIBUTE7 ATTRIBUTE7,
POLL.ATTRIBUTE8 ATTRIBUTE8,
POLL.ATTRIBUTE9 ATTRIBUTE9,
POLL.ATTRIBUTE10 ATTRIBUTE10,
POLL.ATTRIBUTE11 ATTRIBUTE11,
POLL.ATTRIBUTE12 ATTRIBUTE12,
POLL.ATTRIBUTE13 ATTRIBUTE13,
POLL.ATTRIBUTE14 ATTRIBUTE14,
POLL.ATTRIBUTE15 ATTRIBUTE15,
POLL.CLOSED_CODE CLOSED_CODE,
NULL ASN_TYPE,
NULL BILL_OF_LADING,
TO_DATE(NULL) SHIPPED_DATE,
NULL FREIGHT_CARRIER_CODE,
NULL WAYBILL_AIRBILL_NUM,
NULL FREIGHT_BILL_NUM,
NULL VENDOR_LOT_NUM,
NULL CONTAINER_NUM,
NULL TRUCK_NUM,
NULL BAR_CODE_LABEL,
'' RATE_TYPE_DISPLAY,
POLL.MATCH_OPTION MATCH_OPTION,
POLL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE,
TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
TO_NUMBER(NULL) OE_ORDER_NUM,
TO_NUMBER(NULL) OE_ORDER_LINE_ID,
TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
TO_NUMBER(NULL) CUSTOMER_ID,
TO_NUMBER(NULL) CUSTOMER_SITE_ID,
NULL CUSTOMER_ITEM_NUM,
NULL pll_note_to_receiver,
pod.po_distribution_id,
pod.quantity_ordered - pod.quantity_delivered qty_ordered,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
'' DESTINATION_TYPE,
'' LOCATION,
pod.rate currency_conversion_rate_pod,
pod.rate_date currency_conversion_date_pod,
pod.project_id project_id,
pod.task_id task_id
FROM
PO_HEADERS POH,
PO_LINE_LOCATIONS POLL,
PO_LINES POL,
PO_RELEASES POR,
MTL_SYSTEM_ITEMS MSI,
MTL_UNITS_OF_MEASURE mum,
PO_DISTRIBUTIONS POD
WHERE
POD.PO_DISTRIBUTION_ID = v_po_distribution_id
AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID;
SELECT rcv_interface_groups_s.nextval
INTO l_group_id
FROM dual;
select primary_unit_of_measure
into inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
from mtl_system_items
where mtl_system_items.inventory_item_id = p_item_id
and mtl_system_items.organization_id = p_organization_id;
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT
'N' LINE_CHKBOX,
'VENDOR' SOURCE_TYPE_CODE,
'VENDOR' RECEIPT_SOURCE_CODE,
'PO' ORDER_TYPE_CODE,
'' ORDER_TYPE,
POLL.PO_HEADER_ID PO_HEADER_ID,
POH.SEGMENT1 PO_NUMBER,
POLL.PO_LINE_ID PO_LINE_ID,
POL.LINE_NUM PO_LINE_NUMBER,
POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,
POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,
POLL.PO_RELEASE_ID PO_RELEASE_ID,
POR.RELEASE_NUM PO_RELEASE_NUMBER,
TO_NUMBER(NULL) REQ_HEADER_ID,
NULL REQ_NUMBER,
TO_NUMBER(NULL) REQ_LINE_ID,
TO_NUMBER(NULL) REQ_LINE,
TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID,
POH.SEGMENT1 RCV_SHIPMENT_NUMBER,
POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID,
POL.LINE_NUM RCV_LINE_NUMBER,
POH.PO_HEADER_ID FROM_ORGANIZATION_ID,
POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
POH.VENDOR_ID VENDOR_ID,
'' SOURCE,
POH.VENDOR_SITE_ID VENDOR_SITE_ID,
'' OUTSIDE_OPERATION_FLAG,
POL.ITEM_ID ITEM_ID,
-- Bug 2073164
NULL uom_code,
POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM,
MUM.UOM_CLASS PRIMARY_UOM_CLASS,
NULL ITEM_ALLOWED_UNITS_LOOKUP_CODE,
NULL ITEM_LOCATOR_CONTROL,
'' RESTRICT_LOCATORS_CODE,
'' RESTRICT_SUBINVENTORIES_CODE,
NULL SHELF_LIFE_CODE,
NULL SHELF_LIFE_DAYS,
MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
NULL ITEM_REV_CONTROL_FLAG_FROM,
NULL ITEM_NUMBER,
POL.ITEM_REVISION ITEM_REVISION,
POL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
POL.CATEGORY_ID ITEM_CATEGORY_ID,
'' HAZARD_CLASS,
'' UN_NUMBER,
POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER,
POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
'' SHIP_TO_LOCATION,
NULL PACKING_SLIP,
POLL.RECEIVING_ROUTING_ID ROUTING_ID,
'' ROUTING_NAME,
POLL.NEED_BY_DATE NEED_BY_DATE,
NVL(POLL.PROMISED_DATE,POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE,
POLL.QUANTITY ORDERED_QTY,
POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM,
NULL USSGL_TRANSACTION_CODE,
POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,
POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE,
NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE) UNIT_PRICE,
POH.CURRENCY_CODE CURRENCY_CODE,
POH.RATE_TYPE CURRENCY_CONVERSION_TYPE,
POH.RATE_DATE CURRENCY_CONVERSION_DATE,
POH.RATE CURRENCY_CONVERSION_RATE,
POH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
pod.destination_type_code DESTINATION_TYPE_CODE,
pod.deliver_to_person_id DELIVER_TO_PERSON_ID,
pod.deliver_to_location_id DELIVER_TO_LOCATION_ID,
pod.destination_subinventory DESTINATION_SUBINVENTORY,
POLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
POLL.ATTRIBUTE1 ATTRIBUTE1,
POLL.ATTRIBUTE2 ATTRIBUTE2,
POLL.ATTRIBUTE3 ATTRIBUTE3,
POLL.ATTRIBUTE4 ATTRIBUTE4,
POLL.ATTRIBUTE5 ATTRIBUTE5,
POLL.ATTRIBUTE6 ATTRIBUTE6,
POLL.ATTRIBUTE7 ATTRIBUTE7,
POLL.ATTRIBUTE8 ATTRIBUTE8,
POLL.ATTRIBUTE9 ATTRIBUTE9,
POLL.ATTRIBUTE10 ATTRIBUTE10,
POLL.ATTRIBUTE11 ATTRIBUTE11,
POLL.ATTRIBUTE12 ATTRIBUTE12,
POLL.ATTRIBUTE13 ATTRIBUTE13,
POLL.ATTRIBUTE14 ATTRIBUTE14,
POLL.ATTRIBUTE15 ATTRIBUTE15,
POLL.CLOSED_CODE CLOSED_CODE,
NULL ASN_TYPE,
NULL BILL_OF_LADING,
TO_DATE(NULL) SHIPPED_DATE,
NULL FREIGHT_CARRIER_CODE,
NULL WAYBILL_AIRBILL_NUM,
NULL FREIGHT_BILL_NUM,
NULL VENDOR_LOT_NUM,
NULL CONTAINER_NUM,
NULL TRUCK_NUM,
NULL BAR_CODE_LABEL,
'' RATE_TYPE_DISPLAY,
POLL.MATCH_OPTION MATCH_OPTION,
POLL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE,
TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
TO_NUMBER(NULL) OE_ORDER_NUM,
TO_NUMBER(NULL) OE_ORDER_LINE_ID,
TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
TO_NUMBER(NULL) CUSTOMER_ID,
TO_NUMBER(NULL) CUSTOMER_SITE_ID,
NULL CUSTOMER_ITEM_NUM,
NULL pll_note_to_receiver,
--POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
pod.po_distribution_id,
pod.quantity_ordered - pod.quantity_delivered qty_ordered,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
'' DESTINATION_TYPE,
'' LOCATION,
pod.rate currency_conversion_rate_pod,
pod.rate_date currency_conversion_date_pod,
pod.project_id project_id,
pod.task_id task_id
FROM
PO_HEADERS POH,
PO_LINE_LOCATIONS POLL,
PO_LINES POL,
PO_RELEASES POR,
MTL_SYSTEM_ITEMS MSI,
MTL_UNITS_OF_MEASURE mum,
PO_DISTRIBUTIONS POD
WHERE
POD.PO_DISTRIBUTION_ID = v_po_distribution_id
AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID
AND (p_project_id is null or
(p_project_id = -9999 and pod.project_id is null) -- bug 2669021
or POD.project_id = p_project_id
)
and ( p_task_id is null or pod.task_id = p_task_id );
SELECT rcv_interface_groups_s.nextval
INTO l_group_id
FROM dual;
select primary_unit_of_measure
into gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
from mtl_system_items
where mtl_system_items.inventory_item_id = p_item_id
and mtl_system_items.organization_id = p_organization_id;
-- since they are not inserted in RTI, I am not calling it here
-- the code is in
-- rcv_transactions_sv.get_wip_info ()
END IF;
gml_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT
'N' LINE_CHKBOX,
'INTERNAL' SOURCE_TYPE_CODE,
DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') RECEIPT_SOURCE_CODE,
RSL.SOURCE_DOCUMENT_CODE ORDER_TYPE_CODE,
'' ORDER_TYPE,
RSH.SHIPMENT_HEADER_ID PO_HEADER_ID,
RSH.SHIPMENT_NUM PO_NUMBER,
RSL.SHIPMENT_LINE_ID PO_LINE_ID,
RSL.LINE_NUM PO_LINE_NUMBER,
RSL.SHIPMENT_LINE_ID PO_LINE_LOCATION_ID,
RSL.LINE_NUM PO_SHIPMENT_NUMBER,
RSH.SHIPMENT_HEADER_ID PO_RELEASE_ID,
RSH.SHIPMENT_HEADER_ID PO_RELEASE_NUMBER,
PORH.REQUISITION_HEADER_ID REQ_HEADER_ID,
PORH.SEGMENT1 REQ_NUMBER,
PORL.REQUISITION_LINE_ID REQ_LINE_ID,
PORL.LINE_NUM REQ_LINE,
RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID,
RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID,
RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER,
RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
RSL.LINE_NUM RCV_LINE_NUMBER,
RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID,
RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
RSL.SHIPMENT_LINE_ID VENDOR_ID,
'' SOURCE,
TO_NUMBER(NULL) VENDOR_SITE_ID,
'N' OUTSIDE_OPERATION_FLAG,
RSL.ITEM_ID ITEM_ID,
-- Bug 2073164
NULL uom_code,
RSL.UNIT_OF_MEASURE PRIMARY_UOM,
MUM.UOM_CLASS PRIMARY_UOM_CLASS,
NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL,
DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE,
DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE,
NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE,
NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS,
MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
DECODE(MSI1.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_FROM,
NULL ITEM_NUMBER,
RSL.ITEM_REVISION ITEM_REVISION,
RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
RSL.CATEGORY_ID ITEM_CATEGORY_ID,
'' HAZARD_CLASS,
'' UN_NUMBER,
RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER,
RSH.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
'' SHIP_TO_LOCATION,
RSH.PACKING_SLIP PACKING_SLIP,
RSL.ROUTING_HEADER_ID ROUTING_ID,
'' ROUTING_NAME,
PORL.NEED_BY_DATE NEED_BY_DATE,
RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE,
RSL.QUANTITY_SHIPPED ORDERED_QTY,
RSL.PRIMARY_UNIT_OF_MEASURE ORDERED_UOM,
RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
RSH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
NULL INSPECTION_REQUIRED_FLAG,
NULL RECEIPT_REQUIRED_FLAG,
NULL ENFORCE_SHIP_TO_LOCATION_CODE,
TO_NUMBER(NULL) UNIT_PRICE,
NULL CURRENCY_CODE,
NULL CURRENCY_CONVERSION_TYPE,
TO_DATE(NULL) CURRENCY_CONVERSION_DATE,
TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE,
NULL note_to_receiver,
--PORL.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
RSL.TO_SUBINVENTORY DESTINATION_SUBINVENTORY,
RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
RSL.ATTRIBUTE1 ATTRIBUTE1,
RSL.ATTRIBUTE2 ATTRIBUTE2,
RSL.ATTRIBUTE3 ATTRIBUTE3,
RSL.ATTRIBUTE4 ATTRIBUTE4,
RSL.ATTRIBUTE5 ATTRIBUTE5,
RSL.ATTRIBUTE6 ATTRIBUTE6,
RSL.ATTRIBUTE7 ATTRIBUTE7,
RSL.ATTRIBUTE8 ATTRIBUTE8,
RSL.ATTRIBUTE9 ATTRIBUTE9,
RSL.ATTRIBUTE10 ATTRIBUTE10,
RSL.ATTRIBUTE11 ATTRIBUTE11,
RSL.ATTRIBUTE12 ATTRIBUTE12,
RSL.ATTRIBUTE13 ATTRIBUTE13,
RSL.ATTRIBUTE14 ATTRIBUTE14,
RSL.ATTRIBUTE15 ATTRIBUTE15,
'OPEN' CLOSED_CODE,
NULL ASN_TYPE,
RSH.BILL_OF_LADING BILL_OF_LADING,
RSH.SHIPPED_DATE SHIPPED_DATE,
RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM,
RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM,
RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM,
RSL.CONTAINER_NUM CONTAINER_NUM,
RSL.TRUCK_NUM TRUCK_NUM,
RSL.BAR_CODE_LABEL BAR_CODE_LABEL,
NULL RATE_TYPE_DISPLAY,
'P' MATCH_OPTION,
NULL COUNTRY_OF_ORIGIN_CODE,
TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
TO_NUMBER(NULL) OE_ORDER_NUM,
TO_NUMBER(NULL) OE_ORDER_LINE_ID,
TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
TO_NUMBER(NULL) CUSTOMER_ID,
TO_NUMBER(NULL) CUSTOMER_SITE_ID,
NULL CUSTOMER_ITEM_NUM,
NULL pll_note_to_receiver,
--PORL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
NULL PO_DISTRIBUTION_ID,
NULL QTY_ORDERED,
NULL WIP_ENTITY_ID,
NULL WIP_OPERATION_SEQ_NUM,
NULL WIP_RESOURCE_SEQ_NUM,
NULL WIP_REPETITIVE_SCHEDULE_ID,
NULL WIP_LINE_ID,
NULL BOM_RESOURCE_ID,
'' DESTINATION_TYPE,
'' LOCATION,
NULL CURRENCY_CONVERSION_RATE_POD,
NULL CURRENCY_CONVERSION_DATE_POD,
NULL PROJECT_ID,
NULL TASK_ID
FROM
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
PO_REQUISITION_HEADERS PORH,
PO_REQUISITION_LINES PORL,
MTL_SYSTEM_ITEMS MSI,
MTL_SYSTEM_ITEMS MSI1,
MTL_UNITS_OF_MEASURE MUM
WHERE
RSH.RECEIPT_SOURCE_CODE <> 'VENDOR'
AND RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND MSI1.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND RSL.SHIPMENT_LINE_ID = v_shipment_line_id
AND (( rsl.source_document_code = 'REQ' and
exists
(select '1'
from po_req_distributions_all prd
where (p_project_id is null or
(p_project_id = -9999 and prd.project_id is null) or -- bug 2669021
prd.project_id = p_project_id
)
and (p_task_id is null or prd.task_id = p_task_id)
)
)or rsl.source_document_code <> 'REQ'
);
SELECT receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id
AND ship_to_org_id = p_organization_id;
SELECT rcv_interface_groups_s.nextval
INTO l_group_id
FROM dual;
select primary_unit_of_measure
into inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
from mtl_system_items
where mtl_system_items.inventory_item_id = p_item_id
and mtl_system_items.organization_id = p_organization_id;
SELECT cost_group_id
INTO l_rcv_transaction_rec.transfer_cost_group_id
FROM rcv_shipment_lines
WHERE shipment_line_id = l_rcv_transaction_rec.rcv_shipment_line_id;
-- update rss for req
inv_rcv_std_deliver_apis.update_rcv_serials_supply
(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => x_message,
p_shipment_line_id => l_rcv_transaction_rec.rcv_shipment_line_id
);
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT
'N' LINE_CHKBOX,
'CUSTOMER' SOURCE_TYPE_CODE,
'CUSTOMER' RECEIPT_SOURCE_CODE,
'' ORDER_TYPE_CODE,
'' ORDER_TYPE,
TO_NUMBER(NULL) PO_HEADER_ID,
NULL PO_NUMBER,
TO_NUMBER(NULL) PO_LINE_ID,
TO_NUMBER(NULL) PO_LINE_NUMBER,
TO_NUMBER(NULL) PO_LINE_LOCATION_ID,
TO_NUMBER(NULL) PO_SHIPMENT_NUMBER,
TO_NUMBER(NULL) PO_RELEASE_ID,
TO_NUMBER(NULL) PO_RELEASE_NUMBER,
TO_NUMBER(NULL) REQ_HEADER_ID,
NULL REQ_NUMBER,
TO_NUMBER(NULL) REQ_LINE_ID,
TO_NUMBER(NULL) REQ_LINE,
TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
TO_NUMBER(NULL) RCV_SHIPMENT_HEADER_ID,
NULL RCV_SHIPMENT_NUMBER,
TO_NUMBER(NULL) RCV_SHIPMENT_LINE_ID,
TO_NUMBER(NULL) RCV_LINE_NUMBER,
NVL(OEL.SHIP_TO_ORG_ID,OEH.SHIP_TO_ORG_ID) FROM_ORGANIZATION_ID,
NVL(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) TO_ORGANIZATION_ID,
TO_NUMBER(NULL) VENDOR_ID,
'' SOURCE,
TO_NUMBER(NULL) VENDOR_SITE_ID,
NULL OUTSIDE_OPERATION_FLAG,
OEL.INVENTORY_ITEM_ID ITEM_ID,
-- Bug 2073164
NULL uom_code,
MUM.UNIT_OF_MEASURE PRIMARY_UOM,
MUM.UOM_CLASS PRIMARY_UOM_CLASS,
NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE ,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
NVL(MSI.LOCATION_CONTROL_CODE ,1) ITEM_LOCATOR_CONTROL,
DECODE(MSI.RESTRICT_LOCATORS_CODE ,1 ,'Y' ,'N') RESTRICT_LOCATORS_CODE,
DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE ,1 ,'Y' ,'N') RESTRICT_SUBINVENTORIES_CODE,
NVL(MSI.SHELF_LIFE_CODE ,1) SHELF_LIFE_CODE,
NVL(MSI.SHELF_LIFE_DAYS ,0) SHELF_LIFE_DAYS,
MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
DECODE(MSI.REVISION_QTY_CONTROL_CODE ,1 ,'N' ,2 ,'Y' ,'N') ITEM_REV_CONTROL_FLAG_TO,
NULL ITEM_REV_CONTROL_FLAG_FROM,
MSI.SEGMENT1 ITEM_NUMBER,
OEL.ITEM_REVISION ITEM_REVISION,
MSI.DESCRIPTION ITEM_DESCRIPTION,
TO_NUMBER(NULL) ITEM_CATEGORY_ID,
NULL HAZARD_CLASS,
NULL UN_NUMBER,
NULL VENDOR_ITEM_NUMBER,
OEL.SHIP_FROM_ORG_ID SHIP_TO_LOCATION_ID,
'' SHIP_TO_LOCATION,
NULL PACKING_SLIP,
TO_NUMBER(NULL) ROUTING_ID,
NULL ROUTING_NAME,
OEL.REQUEST_DATE NEED_BY_DATE,
NVL(OEL.PROMISE_DATE, OEL.REQUEST_DATE) EXPECTED_RECEIPT_DATE,
OEL.ORDERED_QUANTITY ORDERED_QTY,
'' ORDERED_UOM,
NULL USSGL_TRANSACTION_CODE,
NULL GOVERNMENT_CONTEXT,
MSI.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
'Y' RECEIPT_REQUIRED_FLAG,
'N' ENFORCE_SHIP_TO_LOCATION_CODE,
OEL.UNIT_SELLING_PRICE UNIT_PRICE,
OEH.TRANSACTIONAL_CURR_CODE CURRENCY_CODE,
OEH.CONVERSION_TYPE_CODE CURRENCY_CONVERSION_TYPE,
OEH.CONVERSION_RATE_DATE CURRENCY_CONVERSION_DATE,
OEH.CONVERSION_RATE CURRENCY_CONVERSION_RATE,
NULL NOTE_TO_RECEIVER,
NULL DESTINATION_TYPE_CODE,
OEL.DELIVER_TO_CONTACT_ID DELIVER_TO_PERSON_ID,
OEL.DELIVER_TO_ORG_ID DELIVER_TO_LOCATION_ID,
NULL DESTINATION_SUBINVENTORY,
OEL.CONTEXT ATTRIBUTE_CATEGORY,
OEL.ATTRIBUTE1 ATTRIBUTE1,
OEL.ATTRIBUTE2 ATTRIBUTE2,
OEL.ATTRIBUTE3 ATTRIBUTE3,
OEL.ATTRIBUTE4 ATTRIBUTE4,
OEL.ATTRIBUTE5 ATTRIBUTE5,
OEL.ATTRIBUTE6 ATTRIBUTE6,
OEL.ATTRIBUTE7 ATTRIBUTE7,
OEL.ATTRIBUTE8 ATTRIBUTE8,
OEL.ATTRIBUTE9 ATTRIBUTE9,
OEL.ATTRIBUTE10 ATTRIBUTE10,
OEL.ATTRIBUTE11 ATTRIBUTE11,
OEL.ATTRIBUTE12 ATTRIBUTE12,
OEL.ATTRIBUTE13 ATTRIBUTE13,
OEL.ATTRIBUTE14 ATTRIBUTE14,
OEL.ATTRIBUTE15 ATTRIBUTE15,
NULL CLOSED_CODE,
NULL ASN_TYPE,
NULL BILL_OF_LADING,
TO_DATE(NULL) SHIPPED_DATE,
NULL FREIGHT_CARRIER_CODE,
NULL WAYBILL_AIRBILL_NUM,
NULL FREIGHT_BILL_NUM,
NULL VENDOR_LOT_NUM,
NULL CONTAINER_NUM,
NULL TRUCK_NUM,
NULL BAR_CODE_LABEL,
NULL RATE_TYPE_DISPLAY,
NULL MATCH_OPTION,
NULL COUNTRY_OF_ORIGIN_CODE,
OEL.HEADER_ID OE_ORDER_HEADER_ID,
OEH.ORDER_NUMBER OE_ORDER_NUM,
OEL.LINE_ID OE_ORDER_LINE_ID,
OEL.LINE_NUMBER OE_ORDER_LINE_NUM,
OEL.SOLD_TO_ORG_ID CUSTOMER_ID,
NVL(OEL.SHIP_TO_ORG_ID, OEH.SHIP_TO_ORG_ID) CUSTOMER_SITE_ID,
'' CUSTOMER_ITEM_NUM,
'' PLL_NOTE_TO_RECEIVER,
NULL PO_DISTRIBUTION_ID,
NULL QTY_ORDERED,
NULL WIP_ENTITY_ID,
NULL WIP_OPERATION_SEQ_NUM,
NULL WIP_RESOURCE_SEQ_NUM,
NULL WIP_REPETITIVE_SCHEDULE_ID,
NULL WIP_LINE_ID,
NULL BOM_RESOURCE_ID,
'' DESTINATION_TYPE,
'' LOCATION,
NULL CURRENCY_CONVERSION_RATE_POD,
NULL CURRENCY_CONVERSION_DATE_POD,
NULL PROJECT_ID,
NULL TASK_ID
FROM
OE_ORDER_LINES_all OEL,
OE_ORDER_HEADERS_all OEH,
MTL_SYSTEM_ITEMS MSI,
MTL_UNITS_OF_MEASURE MUM
WHERE OEL.LINE_CATEGORY_CODE='RETURN'
AND OEL.HEADER_ID = OEH.HEADER_ID
AND OEL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND OEL.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID
AND MSI.PRIMARY_UOM_CODE = MUM.UOM_CODE
AND OEL.BOOKED_FLAG='Y'
AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'
AND OEL.LINE_ID = v_oe_order_line_id
AND (p_project_id is null or
(p_project_id = -9999 and oel.project_id is null ) or -- bug 2669021
OEL.project_id = p_project_id
)
and ( p_task_id is null or OEL.task_id = p_task_id );
SELECT rcv_interface_groups_s.nextval
INTO l_group_id
FROM dual;
select primary_unit_of_measure
into inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
from mtl_system_items
where mtl_system_items.inventory_item_id = p_item_id
and mtl_system_items.organization_id = p_organization_id;
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT
'N' LINE_CHKBOX,
'ASN' SOURCE_TYPE_CODE,
'VENDOR' RECEIPT_SOURCE_CODE,
'PO' ORDER_TYPE_CODE,
'' ORDER_TYPE,
POLL.PO_HEADER_ID PO_HEADER_ID,
POH.SEGMENT1 PO_NUMBER,
POLL.PO_LINE_ID PO_LINE_ID,
POL.LINE_NUM PO_LINE_NUMBER,
POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,
POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,
POLL.PO_RELEASE_ID PO_RELEASE_ID,
POR.RELEASE_NUM PO_RELEASE_NUMBER,
TO_NUMBER(NULL) REQ_HEADER_ID,
NULL REQ_NUMBER,
TO_NUMBER(NULL) REQ_LINE_ID,
TO_NUMBER(NULL) REQ_LINE,
TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
RSH.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID,
RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER,
RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
RSL.LINE_NUM RCV_LINE_NUMBER,
NVL(RSL.FROM_ORGANIZATION_ID,POH.PO_HEADER_ID) FROM_ORGANIZATION_ID,
RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
RSH.VENDOR_ID VENDOR_ID,
'' SOURCE,
RSH.VENDOR_SITE_ID VENDOR_SITE_ID,
'' OUTSIDE_OPERATION_FLAG,
RSL.ITEM_ID ITEM_ID,
-- Bug 2073164
NULL uom_code,
RSL.UNIT_OF_MEASURE PRIMARY_UOM,
MUM.UOM_CLASS PRIMARY_UOM_CLASS,
NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL,
DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y', 'N') RESTRICT_LOCATORS_CODE,
DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE,
NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE,
NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS,
MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
NULL ITEM_REV_CONTROL_FLAG_FROM,
NULL ITEM_NUMBER,
RSL.ITEM_REVISION ITEM_REVISION,
RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
RSL.CATEGORY_ID ITEM_CATEGORY_ID,
'' HAZARD_CLASS,
'' UN_NUMBER,
RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER,
RSL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
'' SHIP_TO_LOCATION,
RSL.PACKING_SLIP PACKING_SLIP,
RSL.ROUTING_HEADER_ID ROUTING_ID,
'' ROUTING_NAME,
POLL.NEED_BY_DATE NEED_BY_DATE,
RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE,
POLL.QUANTITY ORDERED_QTY,
POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM,
RSL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
RSL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,
POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE,
NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE) UNIT_PRICE,
POH.CURRENCY_CODE CURRENCY_CODE,
POH.RATE_TYPE CURRENCY_CONVERSION_TYPE,
POH.RATE_DATE CURRENCY_CONVERSION_DATE,
POH.RATE CURRENCY_CONVERSION_RATE,
POH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
POD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
POD.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
POD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
POD.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY,
RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
RSL.ATTRIBUTE1 ATTRIBUTE1,
RSL.ATTRIBUTE2 ATTRIBUTE2,
RSL.ATTRIBUTE3 ATTRIBUTE3,
RSL.ATTRIBUTE4 ATTRIBUTE4,
RSL.ATTRIBUTE5 ATTRIBUTE5,
RSL.ATTRIBUTE6 ATTRIBUTE6,
RSL.ATTRIBUTE7 ATTRIBUTE7,
RSL.ATTRIBUTE8 ATTRIBUTE8,
RSL.ATTRIBUTE9 ATTRIBUTE9,
RSL.ATTRIBUTE10 ATTRIBUTE10,
RSL.ATTRIBUTE11 ATTRIBUTE11,
RSL.ATTRIBUTE12 ATTRIBUTE12,
RSL.ATTRIBUTE13 ATTRIBUTE13,
RSL.ATTRIBUTE14 ATTRIBUTE14,
RSL.ATTRIBUTE15 ATTRIBUTE15,
POLL.CLOSED_CODE CLOSED_CODE,
RSH.ASN_TYPE ASN_TYPE,
RSH.BILL_OF_LADING BILL_OF_LADING,
RSH.SHIPPED_DATE SHIPPED_DATE,
RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM,
RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM,
RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM,
RSL.CONTAINER_NUM CONTAINER_NUM,
RSL.TRUCK_NUM TRUCK_NUM,
RSL.BAR_CODE_LABEL BAR_CODE_LABEL,
'' RATE_TYPE_DISPLAY,
POLL.MATCH_OPTION MATCH_OPTION,
RSL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE,
TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
TO_NUMBER(NULL) OE_ORDER_NUM,
TO_NUMBER(NULL) OE_ORDER_LINE_ID,
TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
TO_NUMBER(NULL) CUSTOMER_ID,
TO_NUMBER(NULL) CUSTOMER_SITE_ID,
null CUSTOMER_ITEM_NUM,
NULL pll_note_to_receiver,
--POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
pod.po_distribution_id PO_DISTRIBUTION_ID,
pod.quantity_ordered - pod.quantity_delivered QTY_ORDERED,
pod.wip_entity_id WIP_ENTITY_ID,
pod.wip_operation_seq_num WIP_OPERATION_SEQ_NUM,
pod.wip_resource_seq_num WIP_RESOURCE_SEQ_NUM,
pod.wip_repetitive_schedule_id WIP_REPETITIVE_SCHEDULE_ID,
pod.wip_line_id WIP_LINE_ID,
pod.bom_resource_id BOM_RESOURCE_ID,
'' DESTINATION_TYPE,
'' LOCATION,
pod.rate CURRENCY_CONVERSION_RATE_POD,
pod.rate_date CURRENCY_CONVERSION_DATE_POD,
pod.project_id PROJECT_ID,
pod.task_id TASK_ID
FROM
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_HEADERS POH,
PO_LINE_LOCATIONS POLL,
PO_LINES POL,
PO_RELEASES POR,
MTL_SYSTEM_ITEMS MSI,
MTL_UNITS_OF_MEASURE MUM,
PO_DISTRIBUTIONS POD
WHERE
POD.PO_DISTRIBUTION_ID = v_po_distribution_id
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
AND NVL(MSI.ORGANIZATION_ID,RSL.TO_ORGANIZATION_ID) = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND POLL.LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.ASN_TYPE IN ('ASN','ASBN')
AND RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED'
AND rsl.shipment_line_id = v_shipment_line_id
and (p_project_id is null or
( p_project_id = -9999 and pod.project_id is null) or -- bug 2669021
pod.project_id = p_project_id
)
and (p_task_id is null or pod.task_id = p_task_id)
UNION
SELECT
'N' LINE_CHKBOX,
'INTERNAL' SOURCE_TYPE_CODE,
DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') RECEIPT_SOURCE_CODE,
RSL.SOURCE_DOCUMENT_CODE ORDER_TYPE_CODE,
'' ORDER_TYPE,
RSH.SHIPMENT_HEADER_ID PO_HEADER_ID,
RSH.SHIPMENT_NUM PO_NUMBER,
RSL.SHIPMENT_LINE_ID PO_LINE_ID,
RSL.LINE_NUM PO_LINE_NUMBER,
RSL.SHIPMENT_LINE_ID PO_LINE_LOCATION_ID,
RSL.LINE_NUM PO_SHIPMENT_NUMBER,
RSH.SHIPMENT_HEADER_ID PO_RELEASE_ID,
RSH.SHIPMENT_HEADER_ID PO_RELEASE_NUMBER,
PORH.REQUISITION_HEADER_ID REQ_HEADER_ID,
PORH.SEGMENT1 REQ_NUMBER,
PORL.REQUISITION_LINE_ID REQ_LINE_ID,
PORL.LINE_NUM REQ_LINE,
RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID,
RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID,
RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER,
RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
RSL.LINE_NUM RCV_LINE_NUMBER,
RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID,
RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
RSL.SHIPMENT_LINE_ID VENDOR_ID,
'' SOURCE,
TO_NUMBER(NULL) VENDOR_SITE_ID,
'N' OUTSIDE_OPERATION_FLAG,
RSL.ITEM_ID ITEM_ID,
-- Bug 2073164
NULL uom_code,
RSL.UNIT_OF_MEASURE PRIMARY_UOM,
MUM.UOM_CLASS PRIMARY_UOM_CLASS,
NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL,
DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE,
DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE,
NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE,
NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS,
MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
DECODE(MSI1.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_FROM,
NULL ITEM_NUMBER,
RSL.ITEM_REVISION ITEM_REVISION,
RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
RSL.CATEGORY_ID ITEM_CATEGORY_ID,
'' HAZARD_CLASS,
'' UN_NUMBER,
RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER,
RSH.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
'' SHIP_TO_LOCATION,
RSH.PACKING_SLIP PACKING_SLIP,
RSL.ROUTING_HEADER_ID ROUTING_ID,
'' ROUTING_NAME,
PORL.NEED_BY_DATE NEED_BY_DATE,
RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE,
RSL.QUANTITY_SHIPPED ORDERED_QTY,
RSL.PRIMARY_UNIT_OF_MEASURE ORDERED_UOM,
RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
RSH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
NULL INSPECTION_REQUIRED_FLAG,
NULL RECEIPT_REQUIRED_FLAG,
NULL ENFORCE_SHIP_TO_LOCATION_CODE,
TO_NUMBER(NULL) UNIT_PRICE,
NULL CURRENCY_CODE,
NULL CURRENCY_CONVERSION_TYPE,
TO_DATE(NULL) CURRENCY_CONVERSION_DATE,
TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE,
NULL note_to_receiver,
--PORL.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
RSL.TO_SUBINVENTORY DESTINATION_SUBINVENTORY,
RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
RSL.ATTRIBUTE1 ATTRIBUTE1,
RSL.ATTRIBUTE2 ATTRIBUTE2,
RSL.ATTRIBUTE3 ATTRIBUTE3,
RSL.ATTRIBUTE4 ATTRIBUTE4,
RSL.ATTRIBUTE5 ATTRIBUTE5,
RSL.ATTRIBUTE6 ATTRIBUTE6,
RSL.ATTRIBUTE7 ATTRIBUTE7,
RSL.ATTRIBUTE8 ATTRIBUTE8,
RSL.ATTRIBUTE9 ATTRIBUTE9,
RSL.ATTRIBUTE10 ATTRIBUTE10,
RSL.ATTRIBUTE11 ATTRIBUTE11,
RSL.ATTRIBUTE12 ATTRIBUTE12,
RSL.ATTRIBUTE13 ATTRIBUTE13,
RSL.ATTRIBUTE14 ATTRIBUTE14,
RSL.ATTRIBUTE15 ATTRIBUTE15,
'OPEN' CLOSED_CODE,
NULL ASN_TYPE,
RSH.BILL_OF_LADING BILL_OF_LADING,
RSH.SHIPPED_DATE SHIPPED_DATE,
RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM,
RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM,
RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM,
RSL.CONTAINER_NUM CONTAINER_NUM,
RSL.TRUCK_NUM TRUCK_NUM,
RSL.BAR_CODE_LABEL BAR_CODE_LABEL,
NULL RATE_TYPE_DISPLAY,
'P' MATCH_OPTION,
NULL COUNTRY_OF_ORIGIN_CODE,
TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
TO_NUMBER(NULL) OE_ORDER_NUM,
TO_NUMBER(NULL) OE_ORDER_LINE_ID,
TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
TO_NUMBER(NULL) CUSTOMER_ID,
TO_NUMBER(NULL) CUSTOMER_SITE_ID,
NULL CUSTOMER_ITEM_NUM,
NULL pll_note_to_receiver,
--PORL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
TO_NUMBER(NULL) PO_DISTRIBUTION_ID,
TO_NUMBER(NULL) QTY_ORDERED,
TO_NUMBER(NULL) WIP_ENTITY_ID,
TO_NUMBER(NULL) WIP_OPERATION_SEQ_NUM,
TO_NUMBER(NULL) WIP_RESOURCE_SEQ_NUM,
TO_NUMBER(NULL) WIP_REPETITIVE_SCHEDULE_ID,
TO_NUMBER(NULL) WIP_LINE_ID,
TO_NUMBER(NULL) BOM_RESOURCE_ID,
'' DESTINATION_TYPE,
'' LOCATION,
TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE_POD,
TO_DATE(NULL) CURRENCY_CONVERSION_DATE_POD,
TO_NUMBER(NULL) PROJECT_ID,
TO_NUMBER(NULL) TASK_ID
FROM
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
PO_REQUISITION_HEADERS PORH,
PO_REQUISITION_LINES PORL,
MTL_SYSTEM_ITEMS MSI,
MTL_SYSTEM_ITEMS MSI1,
MTL_UNITS_OF_MEASURE MUM
WHERE
RSH.RECEIPT_SOURCE_CODE <> 'VENDOR'
AND RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND MSI1.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND RSH.ASN_TYPE IS NULL
AND RSL.SHIPMENT_LINE_ID = v_shipment_line_id
AND (( rsl.source_document_code = 'REQ' and
exists
(select '1'
from po_req_distributions_all prd
where (p_project_id is null or
(p_project_id = -9999 and prd.project_id is null) or -- bug 2669021
prd.project_id = p_project_id
)
and (p_task_id is null or prd.task_id = p_task_id)
)
)or rsl.source_document_code <> 'REQ'
);
SELECT receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id
AND ship_to_org_id = p_organization_id;
SELECT rcv_interface_groups_s.nextval
INTO l_group_id
FROM dual;
SELECT primary_unit_of_measure
INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
SELECT cost_group_id
INTO l_rcv_transaction_rec.cost_group_id
FROM wms_lpn_contents wlpnc
WHERE organization_id = p_organization_id
AND parent_lpn_id = p_lpn_id
AND wlpnc.inventory_item_id = p_item_id
AND exists (SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_organization_id
AND cost_group_id = wlpnc.cost_group_id);
UPDATE wms_lpn_contents wlpnc
SET cost_group_id = NULL
WHERE organization_id = p_organization_id
AND parent_lpn_id = p_lpn_id
AND wlpnc.inventory_item_id = p_item_id
AND NOT exists (SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_organization_id
AND cost_group_id = wlpnc.cost_group_id);
-- update following fields from matching algorithm return value
l_rcv_transaction_rec.transaction_qty := l_rcpt_match_table_detail(match_result_count).quantity;
-- since they are not inserted in RTI, I am not calling it here
-- the code is in
-- rcv_transactions_sv.get_wip_info ()
END IF;
UPDATE wms_license_plate_numbers
SET lpn_context = 3
WHERE lpn_id = p_lpn_id;
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT
lpnc.lpn_id,
lpnc.inventory_item_id,
lpnc.revision,
lpnc.quantity,
lpnc.uom_code,
lpnc.lot_control_code,
lpnc.serial_number_control_code,
lpnc.primary_uom_code,
p_po_header_id,
lpnc.lot_number,
mln.expiration_date
FROM
mtl_lot_numbers mln,
(SELECT wlpn.lpn_id,
wlpnc.inventory_item_id,
msi.organization_id,
msi.lot_control_code,
msi.serial_number_control_code,
msi.primary_uom_code,
wlpnc.revision,
wlpnc.quantity,
wlpnc.uom_code,
wlpnc.lot_number,
wlpnc.source_line_id
FROM wms_lpn_contents wlpnc
, wms_license_plate_numbers wlpn
, mtl_system_items msi
, rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = p_shipment_header_id
AND (wlpn.source_header_id = rsh.shipment_header_id
OR wlpn.source_name = rsh.shipment_num)
AND wlpn.lpn_context IN (6, 7) -- only those pre-ASN receiving ones
AND wlpnc.parent_lpn_id = Nvl(p_lpn_id, wlpn.lpn_id)
AND wlpnc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id
AND wlpn.lpn_id = wlpnc.parent_lpn_id
AND (wlpnc.source_line_id IN
(SELECT pola.po_line_id
FROM po_lines_all pola
WHERE pola.po_header_id = Nvl(p_po_header_id, pola.po_header_id))
OR wlpnc.source_line_id IS NULL)
) lpnc
WHERE lpnc.inventory_item_id = mln.inventory_item_id(+)
AND lpnc.lot_number =mln.lot_number(+)
AND lpnc.organization_id =mln.organization_id(+);
SELECT serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = v_inventory_item_id
AND (revision = v_revision OR (revision IS NULL AND
v_revision IS NULL))
AND (lot_number = v_lot_number OR (lot_number IS NULL AND
v_lot_number IS NULL))
AND lpn_id = v_lpn_id;
l_msnt_last_update_date date_tab_tp;
l_msnt_last_updated_by number_tab_tp;
inv_rcv_std_rcpt_apis.update_lpn_org(p_organization_id => p_organization_id,
p_lpn_id => l_lpn_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_item_uoms_view
WHERE uom_code = l_uom_code
AND organization_id = p_organization_id
AND inventory_item_id = l_inventory_item_id;
inv_rcv_common_apis.insert_lot
(p_transaction_temp_id => l_transaction_temp_id,
p_created_by => fnd_global.user_id,
p_transaction_qty => l_quantity,
p_primary_qty => l_primary_qty,
p_lot_number => l_lot_number,
p_expiration_date => l_lot_expiration_date,
p_status_id => NULL,
x_serial_transaction_temp_id => l_serial_txn_temp_id,
x_return_status => l_return_status,
x_msg_data => l_msg_data);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_txn_temp_id
FROM dual;
l_msnt_last_update_date(l_serial_number_count) := Sysdate;
l_msnt_last_updated_by(l_serial_number_count) := fnd_global.user_id;
INSERT INTO mtl_serial_numbers_temp
(transaction_temp_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
fm_serial_number,
to_serial_number
)
VALUES
(l_msnt_transaction_temp_id(i),
l_msnt_last_update_date(i),
l_msnt_last_updated_by(i),
l_msnt_creation_date(i),
l_msnt_created_by(i),
l_msnt_fm_serial_number(i),
l_msnt_to_serial_number(i)
);
UPDATE mtl_serial_numbers
SET group_mark_id = l_serial_txn_temp_id
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = l_msnt_fm_serial_number(i);
UPDATE wms_license_plate_numbers
SET lpn_context = 3
WHERE source_header_id = p_shipment_header_id
AND lpn_id = Nvl(p_lpn_id, lpn_id);
update wms_lpn_histories
set source_name = 'ASNEXP',
source_header_id = INV_rcv_common_apis.g_rcv_global_var.interface_group_id
where lpn_context = 7
and parent_lpn_id in (select lpn_id
from wms_license_plate_numbers
WHERE source_header_id = p_shipment_header_id
AND lpn_id = Nvl(p_lpn_id, lpn_id)
);
/* SELECT MIN(rti.interface_transaction_id) */
/* Group BY LPN_ID is changed for Express Receipts */
/* Also duplicate print of LPN labels is avoided */
SELECT MAX(rti.interface_transaction_id)
FROM rcv_transactions_interface rti
WHERE rti.group_id = INV_rcv_common_apis.g_rcv_global_var.interface_group_id
GROUP BY decode(p_source_type, 'ASNEXP',rti.interface_transaction_id,'SHIPMENTEXP',rti.interface_transaction_id,null) ;
select ood.set_of_books_id
into inv_rcv_common_apis.g_po_startup_value.sob_id
FROM org_organization_definitions ood,
gl_sets_of_books sob
WHERE organization_id = p_organization_id
AND sob.set_of_books_id = ood.set_of_books_id;
/* SELECT MIN(rti.interface_transaction_id) */
/* Group BY LPN_ID is changed for Express Receipts */
/* Also duplicate print of LPN labels is avoided */
SELECT MAX(rti.interface_transaction_id)
FROM rcv_transactions_interface rti
WHERE rti.group_id = INV_rcv_common_apis.g_rcv_global_var.interface_group_id
GROUP BY decode(p_source_type, 'ASNEXP',rti.interface_transaction_id,'SHIPMENTEXP',rti.interface_transaction_id,null) ;
select ood.set_of_books_id
into inv_rcv_common_apis.g_po_startup_value.sob_id
FROM org_organization_definitions ood,
gl_sets_of_books sob
WHERE organization_id = p_organization_id
AND sob.set_of_books_id = ood.set_of_books_id;