[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** each transaction line is inserted to rcv transaction interface
** return true if grouping is successful
**************************************************************/
function replaceNull(inValue in number)
return number
is
begin
if (inValue = -9999) then
return null;
select rsl.shipment_line_id, rsl.quantity_shipped, nvl(rsl.quantity_received,0), rsl.unit_of_measure
from rcv_shipment_lines rsl, rcv_shipment_headers rsh
where rsl.po_line_location_id = p_lineLocationId
and nvl(rsl.quantity_shipped, 0) > nvl(rsl.quantity_received, 0)
and rsl.shipment_header_id = rsh.shipment_header_id
and rsh.asn_type in ('ASN','ASBN');
select org_id
into x_txn_org_id
from po_line_locations_all poll
where line_location_id = l_lineLocationId;
insert_rcv_txn_interface(x_source_type_code=>'ASN',
x_rcv_shipment_line_id=>l_rcvLineId,
x_po_header_id=>x_po_header_id(i),
x_line_location_id=>x_line_location_id(i) ,
x_receipt_qty=>l_receiptQty,
x_receipt_uom=>x_receipt_uom(i),
x_receipt_date=>x_receipt_date,
x_item_id=>replaceNull(x_item_id(i)),
x_uom_class=>x_uom_class(i),
x_org_id=>replaceNull(x_org_id(i)),
x_po_distribution_id=>x_po_distribution_id(i),
x_group_id=>x_group_id,
x_caller=>x_caller,
x_Comments=>x_Comments(i),
x_PackingSlip=>x_PackingSlip(i),
x_WayBillNum=>x_WayBillNum(i));
x_progress := 'groupPoTransaction 015 after insertion, hasEntryForRcvLine = TRUE, asn matches receiving quantity, exiting';
insert_rcv_txn_interface(x_source_type_code=>'ASN',
x_rcv_shipment_line_id=>l_rcvLineId,
x_po_header_id=>x_po_header_id(i),
x_line_location_id=>x_line_location_id(i) ,
x_receipt_qty=>l_receiptQty,
x_receipt_uom=>x_receipt_uom(i),
x_receipt_date=>x_receipt_date,
x_item_id=>replaceNull(x_item_id(i)),
x_uom_class=>x_uom_class(i),
x_org_id=>replaceNull(x_org_id(i)),
x_po_distribution_id=>x_po_distribution_id(i),
x_group_id=>x_group_id,
x_caller=>x_caller,
x_Comments=>x_Comments(i),
x_PackingSlip=>x_PackingSlip(i),
x_WayBillNum=>x_WayBillNum(i));
x_progress := 'groupPoTransaction 014 before insertion';
insert_rcv_txn_interface(x_source_type_code=>'ASN',
x_rcv_shipment_line_id=>l_rcvLineId,
x_po_header_id=>x_po_header_id(i),
x_line_location_id=>x_line_location_id(i) ,
x_receipt_qty=>l_receiptQty,
x_receipt_uom=>x_receipt_uom(i),
x_receipt_date=>x_receipt_date,
x_item_id=>replaceNull(x_item_id(i)),
x_uom_class=>x_uom_class(i),
x_org_id=>replaceNull(x_org_id(i)),
x_po_distribution_id=>x_po_distribution_id(i),
x_group_id=>x_group_id,
x_caller=>x_caller,
x_Comments=>x_Comments(i),
x_PackingSlip=>x_PackingSlip(i),
x_WayBillNum=>x_WayBillNum(i));
x_progress := 'groupPoTransaction 015 after insertion, hasEntryForRcvLine = FALSE, asn matches receiving quantity, exiting';
insert_rcv_txn_interface(x_source_type_code=>'ASN',
x_rcv_shipment_line_id=>l_rcvLineId,
x_po_header_id=>x_po_header_id(i),
x_line_location_id=>x_line_location_id(i) ,
x_receipt_qty=>l_receiptQty,
x_receipt_uom=>x_receipt_uom(i),
x_receipt_date=>x_receipt_date,
x_item_id=>replaceNull(x_item_id(i)),
x_uom_class=>x_uom_class(i),
x_org_id=>replaceNull(x_org_id(i)),
x_po_distribution_id=>x_po_distribution_id(i),
x_group_id=>x_group_id,
x_caller=>x_caller,
x_Comments=>x_Comments(i),
x_PackingSlip=>x_PackingSlip(i),
x_WayBillNum=>x_WayBillNum(i));
x_progress := 'groupPoTransaction 015 after insertion, asn matches less than receiving quantity';
insert_rcv_txn_interface(x_po_header_id=>x_po_header_id(i),
x_line_location_id=>x_line_location_id(i) ,
x_receipt_qty=>l_toReceiveQty,
x_receipt_uom=>x_receipt_uom(i),
x_receipt_date=>x_receipt_date,
x_item_id=>replaceNull(x_item_id(i)),
x_uom_class=>x_uom_class(i),
x_org_id=>replaceNull(x_org_id(i)),
x_po_distribution_id=>x_po_distribution_id(i),
x_group_id=>x_group_id,
x_caller=>x_caller,
x_Comments=>x_Comments(i),
x_PackingSlip=>x_PackingSlip(i),
x_WayBillNum=>x_WayBillNum(i));
delete from rcv_transactions_interface
where group_id = X_group_id;
** called by insertTransactionInterface
**************************************************************/
function groupInternalTransaction (x_req_line_id in rcvNumberArray,
x_receipt_qty in rcvNumberArray,
x_receipt_uom in rcvVarcharArray,
x_item_id in rcvNumberArray,
x_uom_class in rcvVarcharArray,
x_org_id in rcvNumberArray,
x_comments in rcvVarcharArray,
x_packingSlip in rcvVarcharArray,
x_waybillNum in rcvVarcharArray,
x_group_id in number,
x_receipt_date in date,
x_caller in varchar2)
return number
is
cursor rcv_header(p_reqLineId number) is
select shipment_line_id, quantity_shipped, nvl(quantity_received,0), unit_of_measure
from rcv_shipment_lines
where requisition_line_id = p_reqLineId
and nvl(quantity_shipped, 0) > nvl(quantity_received, 0);
insert_rcv_txn_interface_ir (l_rcvLineId,
x_req_line_id(i),
l_receiptQty,
x_receipt_uom(i),
x_receipt_date,
replaceNull(x_item_id(i)),
x_uom_class(i),
replaceNull(x_org_id(i)),
x_group_id,
x_caller,
x_Comments(i),
x_PackingSlip(i),
x_WayBillNum(i));
x_progress := 'groupInternalTransaction 025 after insertion, asn matches receiving quantity, exiting';
insert_rcv_txn_interface_ir (l_rcvLineId,
x_req_line_id(i),
l_receiptQty,
x_receipt_uom(i),
x_receipt_date,
replaceNull(x_item_id(i)),
x_uom_class(i),
replaceNull(x_org_id(i)),
x_group_id,
x_caller,
x_Comments(i),
x_PackingSlip(i),
x_WayBillNum(i));
delete from rcv_transactions_interface
where group_id = X_group_id;
procedure INSERT_RCV_TXN_INTERFACE_IR (
X_rcv_shipment_line_id IN number,
x_req_line_id in number,
X_receipt_qty IN number,
X_receipt_uom IN varchar2,
X_receipt_date IN date,
X_item_id IN number,
X_uom_class IN varchar2,
X_org_id IN number,
X_group_id IN number,
X_caller IN varchar2,
X_Comments IN varchar2 default null,
X_PackingSlip IN varchar2 default null,
X_WayBillNum IN varchar2 default null)
as
X_user_id number :=0;
SELECT HR.PERSON_ID
INTO x_employee_id
FROM FND_USER FND, per_people_f HR
WHERE FND.USER_ID = x_user_id
AND FND.EMPLOYEE_ID = HR.PERSON_ID
AND sysdate between hr.effective_start_date AND hr.effective_end_date
AND ROWNUM = 1;
SELECT unit_of_measure
INTO X_primary_uom
FROM mtl_units_of_measure
WHERE uom_class = x_uom_class
AND base_uom_flag = 'Y';
SELECT primary_unit_of_measure
INTO X_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = x_org_id;
x_progress := 'insert internal transaction, item_id=' || to_char(x_item_id);
x_progress := 'insert internal transaction, receipt_uom=' || x_receipt_uom || '; x_receipt_qty='|| to_char(x_receipt_qty);
x_progress := 'insert internal transaction, primary_uom= ' || X_primary_uom ||'; primary_qty=' || to_char(X_primary_qty);
INSERT INTO RCV_TRANSACTIONS_INTERFACE (
INTERFACE_TRANSACTION_ID,
GROUP_ID,
ORG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SOURCE_DOCUMENT_CODE,
DESTINATION_TYPE_CODE,
DESTINATION_CONTEXT,
RECEIPT_SOURCE_CODE,
TRANSACTION_DATE,
EXPECTED_RECEIPT_DATE,
QUANTITY,
UNIT_OF_MEASURE,
PRIMARY_QUANTITY,
PRIMARY_UNIT_OF_MEASURE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
EMPLOYEE_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
PO_UNIT_PRICE,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
ROUTING_HEADER_ID,
VENDOR_ID,
VENDOR_SITE_ID,
TRANSACTION_TYPE,
ITEM_ID,
ITEM_DESCRIPTION,
ITEM_REVISION,
CATEGORY_ID,
VENDOR_ITEM_NUM,
PACKING_SLIP,
LOCATION_ID,
SHIP_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
DELIVER_TO_LOCATION_ID,
FROM_ORGANIZATION_ID,
TO_ORGANIZATION_ID,
SUBINVENTORY,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
PARENT_TRANSACTION_ID,
INSPECTION_STATUS_CODE,
USE_MTL_LOT,
USE_MTL_SERIAL,
LOCATOR_ID,
REQUISITION_LINE_ID,
COMMENTS,
WAYBILL_AIRBILL_NUM,
USSGL_TRANSACTION_CODE )
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
X_group_id,
MO_GLOBAL.get_current_org_id,
SYSDATE,
X_user_id,
X_user_id,
SYSDATE,
X_user_id,
'REQ',
RSL.DESTINATION_TYPE_CODE,
RSL.DESTINATION_TYPE_CODE,
'INTERNAL ORDER',
X_receipt_date,
RSH.EXPECTED_RECEIPT_DATE,
X_receipt_qty,
X_receipt_uom,
X_primary_qty,
X_primary_uom,
rsh.shipment_header_id,
x_rcv_shipment_line_id,
X_employee_id,
NULL,
NULL,
NULL,
NULL,
NULL,
TO_NUMBER(NULL),
NULL,
TO_NUMBER(NULL),
NULL,
TO_DATE(NULL),
RSL.ROUTING_HEADER_ID,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
decode(x_caller,
'WEB','CONFIRM RECEIPT',
'WF','CONFIRM RECEIPT(WF)',
'WP4','CONFIRM RECEIPT',
'WP4_CONFIRM','CONFIRM RECEIPT',
'AUTO_RECEIVE','CONFIRM RECEIPT(WF)'),
RSL.ITEM_ID,
RSL.ITEM_DESCRIPTION,
RSL.ITEM_REVISION,
RSL.CATEGORY_ID,
RSL.VENDOR_ITEM_NUM,
X_PackingSlip,
RSH.SHIP_TO_LOCATION_ID,
RSH.SHIP_TO_LOCATION_ID,
rsl.deliver_to_person_id,
rsl.DELIVER_TO_LOCATION_ID,
RSL.FROM_ORGANIZATION_ID,
RSL.TO_ORGANIZATION_ID,
RSL.TO_SUBINVENTORY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'CONFIRM RECEIPT',
X_trx_proc_mode,
'CONFIRM',
TO_NUMBER(NULL),
NULL,
MSI.LOT_CONTROL_CODE,
MSI.SERIAL_NUMBER_CONTROL_CODE,
to_number(NULL),
x_req_line_id,
X_Comments,
X_WayBillNum,
NULL
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
MTL_SYSTEM_ITEMS MSI
WHERE RSH.RECEIPT_SOURCE_CODE <> 'VENDOR' AND
RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID AND
MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID and
RSL.SHIPMENT_LINE_ID = x_rcv_shipment_line_id;
x_progress := 'insert internal req transaction exception' || substr(SQLERRM,12,512);
end INSERT_RCV_TXN_INTERFACE_IR;
procedure INSERT_RCV_TXN_INTERFACE (X_source_type_code IN varchar2 default 'VENDOR',
X_rcv_shipment_line_id IN number default 0,
X_po_header_id IN number,
X_line_location_id IN number,
X_receipt_qty IN number,
X_receipt_uom IN varchar2,
X_receipt_date IN date,
X_item_id IN number,
X_uom_class IN varchar2,
X_org_id IN number,
X_po_distribution_id IN number,
X_group_id IN number,
X_caller IN varchar2,
X_Comments IN varchar2 default null,
X_PackingSlip IN varchar2 default null,
X_WayBillNum IN varchar2 default null
) as
X_user_id number := 0;
asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE x_source_type_code:' || x_source_type_code);
select shipment_header_id
into l_rcv_shipment_header_id
from rcv_shipment_lines
where shipment_line_id = X_rcv_shipment_line_id;
asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE l_rcv_shipment_header_id:' || l_rcv_shipment_header_id);
SELECT HR.PERSON_ID
INTO x_employee_id
FROM FND_USER FND, per_people_f HR
WHERE FND.USER_ID = x_user_id
AND FND.EMPLOYEE_ID = HR.PERSON_ID
AND sysdate between hr.effective_start_date AND hr.effective_end_date
AND ROWNUM = 1;
asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE x_employee_id :' || x_employee_id );
SELECT POL.MATCHING_BASIS, POL.JOB_ID
INTO X_MATCHING_BASIS, X_JOB_ID
FROM PO_LINES_ALL POL, PO_DISTRIBUTIONS_ALL POD
WHERE POL.PO_LINE_ID = POD.PO_LINE_ID
AND POD.PO_DISTRIBUTION_ID = X_PO_DISTRIBUTION_ID;
asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE X_MATCHING_BASIS' || X_MATCHING_BASIS);
SELECT unit_of_measure
INTO X_primary_uom
FROM mtl_units_of_measure
WHERE uom_class = x_uom_class
AND base_uom_flag = 'Y';
SELECT primary_unit_of_measure
INTO X_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = x_org_id;
asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE X_receipt_qty' || X_receipt_qty || ' X_receipt_amt ' || X_receipt_amt);
/* Insert the rows that were checked into RCV_TRANSACTIONS_INTERFACE */
SELECT POD.DESTINATION_TYPE_CODE,
'PO',
NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE),
POLL.PO_HEADER_ID,
POLL.PO_RELEASE_ID,
POLL.PO_LINE_ID,
POLL.LINE_LOCATION_ID,
POD.PO_DISTRIBUTION_ID ,
NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE),
POH.CURRENCY_CODE ,
POH.RATE,
POH.RATE_TYPE,
POH.RATE_DATE,
POLL.RECEIVING_ROUTING_ID,
POH.VENDOR_ID,
POH.VENDOR_SITE_ID,
POL.ITEM_ID,
SUBSTR( POL.ITEM_DESCRIPTION,1,240),
POL.ITEM_REVISION,
POL.CATEGORY_ID,
POL.VENDOR_PRODUCT_NUM,
POLL.SHIP_TO_LOCATION_ID,
POD.DELIVER_TO_PERSON_ID ,
POD.DELIVER_TO_LOCATION_ID ,
POH.PO_HEADER_ID,
POLL.SHIP_TO_ORGANIZATION_ID,
POD.DESTINATION_SUBINVENTORY ,
MSI.LOT_CONTROL_CODE,
MSI.SERIAL_NUMBER_CONTROL_CODE,
pod.wip_entity_id,
pod.wip_line_id,
pod.wip_repetitive_schedule_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.bom_resource_id,
--Bug 8893932 PO RECEIVED FROM IPROCUREMENT DOESNT POPULATE COUNTRY OF ORIGIN
POLL.COUNTRY_OF_ORIGIN_CODE
INTO
l_destination_type_code,
l_order_type_code,
l_expected_receipt_date,
l_po_header_id,
l_po_release_id,
l_po_line_id,
l_po_line_location_id,
l_po_distribution_id,
l_unit_price,
l_currency_code,
l_currency_conversion_rate,
l_currency_conversion_type,
l_currency_conversion_date,
l_routing_id,
l_vendor_id,
l_vendor_site_id,
l_item_id,
l_item_description,
l_item_revision,
l_item_category_id,
l_vendor_item_number,
l_ship_to_location_id,
l_deliver_to_person_id,
l_deliver_to_location_id,
l_from_organization_id,
l_to_organization_id,
l_destination_subinventory,
l_lot_control_code,
l_serial_number_control_code,
l_wip_entity_id,
l_wip_line_id,
l_wip_repetitive_schedule_id,
l_wip_operation_seq_num,
l_wip_resource_seq_num,
l_bom_resource_id,
--Bug 8893932 PO RECEIVED FROM IPROCUREMENT DOESNT POPULATE COUNTRY OF ORIGIN
l_country_of_origin
FROM MTL_SYSTEM_ITEMS MSI,
PO_LINES_ALL POL,
PO_DISTRIBUTIONS_ALL POD,
PO_HEADERS_ALL POH,
PO_LINE_LOCATIONS_ALL POLL
WHERE
NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND
NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND
NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND
POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND
POLL.RECEIVING_ROUTING_ID = 3 AND
POH.PO_HEADER_ID = POLL.PO_HEADER_ID AND
POL.PO_LINE_ID = POLL.PO_LINE_ID AND
POD.PO_HEADER_ID = POLL.PO_HEADER_ID AND
POD.PO_LINE_ID = POL.PO_LINE_ID AND
POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND
MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND
POH.PO_HEADER_ID = x_po_header_id and
POLL.LINE_LOCATION_ID = x_line_location_id and
POD.PO_DISTRIBUTION_ID = X_po_distribution_id;
asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE Inserting values in RCV_TRANSACTIONS_INTERFACE');
INSERT INTO RCV_TRANSACTIONS_INTERFACE (
INTERFACE_TRANSACTION_ID,
GROUP_ID,
ORG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SOURCE_DOCUMENT_CODE,
DESTINATION_TYPE_CODE,
DESTINATION_CONTEXT,
RECEIPT_SOURCE_CODE,
TRANSACTION_DATE,
EXPECTED_RECEIPT_DATE,
QUANTITY,
UNIT_OF_MEASURE,
PRIMARY_QUANTITY,
PRIMARY_UNIT_OF_MEASURE,
AMOUNT,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
EMPLOYEE_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
PO_UNIT_PRICE,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
ROUTING_HEADER_ID,
VENDOR_ID,
VENDOR_SITE_ID,
TRANSACTION_TYPE,
ITEM_ID,
ITEM_DESCRIPTION,
ITEM_REVISION,
CATEGORY_ID,
VENDOR_ITEM_NUM,
PACKING_SLIP,
LOCATION_ID,
SHIP_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
DELIVER_TO_LOCATION_ID,
FROM_ORGANIZATION_ID,
TO_ORGANIZATION_ID,
SUBINVENTORY,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
PARENT_TRANSACTION_ID,
INSPECTION_STATUS_CODE,
USE_MTL_LOT,
USE_MTL_SERIAL,
LOCATOR_ID,
-- REQUISITION_LINE_ID,
COMMENTS,
WAYBILL_AIRBILL_NUM,
USSGL_TRANSACTION_CODE,
JOB_ID,
MATCHING_BASIS,
COUNTRY_OF_ORIGIN_CODE)
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
X_group_id,
MO_GLOBAL.get_current_org_id,
SYSDATE,
X_user_id,
X_user_id,
SYSDATE,
X_user_id,
l_order_type_code, -- always PO for both standard and asn
l_DESTINATION_TYPE_CODE,
l_DESTINATION_TYPE_CODE,
'VENDOR', -- same for ASN and PO
X_receipt_date,
l_expected_receipt_date,
X_qty,
X_receipt_uom,
X_primary_qty,
X_primary_uom,
X_receipt_amt,
DECODE(x_source_type_code,'VENDOR', NULL, l_rcv_shipment_header_id),
DECODE(x_source_type_code,'VENDOR', NULL, x_rcv_shipment_line_id),
X_employee_id,
DECODE(l_order_type_code,'PO', l_po_header_id, NULL),
DECODE(l_order_type_code,'PO', l_po_release_id, NULL),
DECODE(l_order_type_code,'PO', l_po_line_id, NULL),
DECODE(l_order_type_code,'PO', l_po_line_location_id, NULL),
DECODE(l_order_type_code,'PO', l_po_distribution_id, NULL),
l_unit_price,
l_currency_code,
l_currency_conversion_rate,
l_currency_conversion_type,
l_currency_conversion_date,
l_routing_id,
l_vendor_id,
l_vendor_site_id,
decode(x_caller,
'WEB','CONFIRM RECEIPT',
'WF','CONFIRM RECEIPT(WF)',
'WP4','CONFIRM RECEIPT',
'WP4_CONFIRM','CONFIRM RECEIPT',
'AUTO_RECEIVE','CONFIRM RECEIPT(WF)'), -- 'EXPRESS DIRECT' this is the transaction_type
l_item_id,
l_item_description,
l_item_revision,
l_item_category_id,
l_vendor_item_number,
X_PackingSlip,
l_ship_to_location_id,
l_ship_to_location_id,
DECODE(l_order_type_code,'PO',l_deliver_to_person_id, l_deliver_to_person_id),
DECODE(l_order_type_code,'PO', l_DELIVER_TO_LOCATION_ID, L_DELIVER_TO_LOCATION_ID),
DECODE(l_order_type_code,'PO', NULL, l_from_organization_id),
l_to_organization_id,
DECODE(l_order_type_code,'PO', l_DESTINATION_SUBINVENTORY, L_DESTINATION_SUBINVENTORY),
DECODE(l_order_type_code,'PO',l_WIP_ENTITY_ID, NULL),
DECODE(l_order_type_code,'PO',l_WIP_LINE_ID, NULL),
DECODE(l_order_type_code,'PO',l_WIP_REPETITIVE_SCHEDULE_ID, NULL),
DECODE(l_order_type_code,'PO',l_WIP_OPERATION_SEQ_NUM, NULL),
DECODE(l_order_type_code,'PO',l_WIP_RESOURCE_SEQ_NUM, NULL),
DECODE(l_order_type_code,'PO',l_BOM_RESOURCE_ID, NULL),
'CONFIRM RECEIPT', -- 'EXPRESS' this is the processing_status_code
X_trx_proc_mode,
'CONFIRM', -- 'EXPRESS' this is the transaction_status_code
TO_NUMBER(NULL),
NULL,
l_lot_control_code,
l_serial_number_control_code,
to_number(NULL),
-- to_number(NULL), -- Bug#2718763 We no longer populate the requisition line id
X_Comments,
X_WayBillNum,
NULL,
X_JOB_ID,
nvl(x_matching_basis, 'QUANTITY'),
l_country_of_origin
FROM dual;
end INSERT_RCV_TXN_INTERFACE;
** create or update rcv shipment header
** call the txn processor
** API called from Java layer program
**************************************************************/
function process_transactions (X_group_id IN number,
X_caller IN varchar2,
X_Comments IN varchar2 default null,
X_PackingSlip IN varchar2 default null,
X_WayBillNum IN varchar2 default null)
return number is
X_return_code boolean := FALSE;
select org_id
into x_txn_org_id
from rcv_transactions_interface rti
where rti.group_id = x_group_id and rownum = 1;
* issues in Receiving Transaction Processor. We are inserting records into
* RTI and RSH and updating the RTI.shipment_header_id with RSH.Shipment_header_id
* and calling the transaction processor. Since the transaction processor runs in different
* transaction, commit is necessary here.
* Reverting the changes done as part of bug 3560995.
**/
commit;
** update rcv shipment header for ASN and Internal Shipment
** creates a header for those txns that have the same vendor and to_org_id
** return true if function successful
**************************************************************
function processRcvShipment (x_group_id in number,
x_caller in varchar2,
x_Comments in varchar2 default null,
x_PackingSlip in varchar2 default null,
x_WayBillNum in varchar2 default null,
x_Ussgl_Transaction_Code in varchar2 default null)
return boolean;
SELECT RTI.TO_ORGANIZATION_ID,
RTI.VENDOR_ID, RTI.WAYBILL_AIRBILL_NUM, POD.ORG_ID
FROM RCV_TRANSACTIONS_INTERFACE RTI, PO_DISTRIBUTIONS_ALL POD
WHERE GROUP_ID = X_GROUP_ID AND
SHIPMENT_LINE_ID IS NULL AND
RTI.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
GROUP BY RTI.TO_ORGANIZATION_ID, RTI.VENDOR_ID, RTI.WAYBILL_AIRBILL_NUM, POD.ORG_ID;
select distinct to_organization_id,
vendor_id,waybill_airbill_num
from rcv_transactions_interface
where group_id = X_group_id and shipment_line_id is null;
select to_organization_id, shipment_header_id, comments, packing_slip,waybill_airbill_num
from rcv_transactions_interface
where group_id = X_group_id and shipment_line_id is not null and
shipment_header_id is not null;
select distinct shipment_header_id
from rcv_transactions_interface trans
where group_id = X_group_id and shipment_line_id is not null and
shipment_header_id is not null;
X_last_update_login rcv_shipment_headers.last_update_login%type;
x_last_update_login := fnd_global.user_id;
SELECT HR.PERSON_ID
INTO x_employee_id
FROM FND_USER FND, per_people_f HR
WHERE FND.USER_ID = X_created_by
AND FND.EMPLOYEE_ID = HR.PERSON_ID
AND sysdate between hr.effective_start_date AND hr.effective_end_date
AND ROWNUM = 1;
SELECT to_char(next_receipt_num + 1)
INTO X_receipt_num
FROM rcv_parameters
WHERE organization_id = X_to_org_id
FOR UPDATE OF next_receipt_num;
SELECT count(*)
INTO X_count
FROM rcv_shipment_headers
WHERE receipt_num = X_receipt_num and
ship_to_org_id = X_to_org_id;
update rcv_parameters
set next_receipt_num = X_receipt_num
where organization_id = X_to_org_id;
SELECT rcv_shipment_headers_s.nextval
INTO X_shipment_header_id
FROM sys.dual;
INSERT INTO RCV_SHIPMENT_HEADERS (
SHIPMENT_HEADER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
ORGANIZATION_ID,
SHIP_TO_ORG_ID,
RECEIPT_NUM,
EMPLOYEE_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COMMENTS,
PACKING_SLIP,
WAYBILL_AIRBILL_NUM,
USSGL_TRANSACTION_CODE )
VALUES (
X_shipment_header_id,
SYSDATE,
X_created_by,
SYSDATE,
X_created_by,
X_last_update_login,
'VENDOR',
X_vendor_id,
X_to_org_id,
X_to_org_id,
X_receipt_num,
X_employee_id,
X_request_id,
X_pgm_app_id,
X_pgm_id,
SYSDATE,
X_Comments ,
X_PackingSlip,
x_line_waybill_airbill_num,
NULL);
update rcv_transactions_interface
set shipment_header_id = x_shipment_header_id
where group_id = X_group_id
and to_organization_id = x_to_org_id
and vendor_id = x_vendor_id
and shipment_line_id is null
AND waybill_airbill_num IS NULL;
update rcv_transactions_interface
set shipment_header_id = x_shipment_header_id
where group_id = X_group_id
and to_organization_id = x_to_org_id
and vendor_id = x_vendor_id
and shipment_line_id is null
AND waybill_airbill_num = x_line_waybill_airbill_num;
SELECT distinct vendor_site_id
INTO X_vendor_site_id
FROM rcv_transactions_interface
WHERE group_id = X_group_id and
shipment_header_id = x_shipment_header_id;
update rcv_shipment_headers
set vendor_site_id = X_vendor_site_id
where shipment_header_id = x_shipment_header_id;
/* update intransit shipment header according to user entered info */
asn_debug.put_line('number of PO receipt created is ' || to_char(x_rcpt_count-1));
select receipt_num
into X_receipt_num
from rcv_shipment_headers
where shipment_header_id = X_shipment_header_id and
receipt_num is not null;
SELECT to_char(next_receipt_num + 1)
INTO X_receipt_num
FROM rcv_parameters
WHERE organization_id = X_to_org_id
FOR UPDATE OF next_receipt_num;
SELECT count(*)
INTO X_count
FROM rcv_shipment_headers
WHERE receipt_num = X_receipt_num and
ship_to_org_id = X_to_org_id;
update rcv_parameters
set next_receipt_num = X_receipt_num
where organization_id = X_to_org_id;
update rcv_shipment_headers
set receipt_num=X_receipt_num
where shipment_header_id = X_shipment_header_id;
delete_rows boolean := FALSE;
select org_id
into x_txn_org_id
from rcv_transactions_interface rti
where rti.group_id = x_group_id and rownum = 1;
delete_rows := TRUE;
delete_rows := TRUE;
delete_rows := TRUE;
delete_rows := FALSE;
delete_rows := TRUE;
** Since the insert has already occurred, make sure to set the
** transaction status to error; otherwise the next query
if (delete_rows) then
BEGIN
delete from rcv_transactions_interface
where group_id = X_group_id;
SELECT COUNT(1)
INTO x_rec_count
FROM RCV_TRANSACTIONS_INTERFACE
WHERE group_id = x_group_id;
select distinct nvl(pod.wf_item_key,wf.ITEM_KEY)
from rcv_transactions rcv,po_line_locations_all poll,
po_distributions_all pod,wf_items wf
where group_id = x_group_id and
poll.line_location_id = rcv.po_line_location_id AND
pod.po_distribution_id = rcv.po_distribution_id
AND wf.item_type = 'PORCPT' AND
( wf.ITEM_KEY LIKE (rcv.po_header_id ||';'||
SELECT WIAS.ACTIVITY_STATUS
INTO x_act_status
FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
WF_ITEMS WI,
WF_PROCESS_ACTIVITIES PA
WHERE WIAS.ITEM_TYPE = wf_item_type
AND WIAS.ITEM_KEY = wf_item_key
AND WIAS.ITEM_TYPE = WI.ITEM_TYPE
AND WIAS.ITEM_KEY = WI.ITEM_KEY
AND WI.ROOT_ACTIVITY=PA.ACTIVITY_NAME
AND WIAS.PROCESS_ACTIVITY= PA.INSTANCE_ID;