[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wblt.document_id label_type_id,
ml.meaning label_type_name,
wblt.level_type_code level_type_code
-- Bug 3836484. The following literal is not good for performance
-- Remove the following since it is only used for trace message
-- , decode(wblt.level_type_code,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') level_type_name
FROM wms_bflow_label_type wblt, mfg_lookups ml
WHERE wblt.business_flow_code = p_business_flow
AND wblt.level_value_id =
decode(wblt.level_type_code, 10001,0,10002,FND_GLOBAL.RESP_APPL_ID
,10003,FND_GLOBAL.RESP_ID,10004, FND_GLOBAL.USER_ID)
AND nvl(wblt.enabled_flag, 'N') = 'Y'
AND ml.lookup_type = 'WMS_LABEL_TYPE'
AND ml.lookup_code = wblt.document_id
order by wblt.level_type_code desc;
SELECT wblt.document_id label_type_id,
ml.meaning label_type_name,
wblt.level_type_code level_type_code
FROM wms_bflow_label_type wblt, mfg_lookups ml
WHERE wblt.business_flow_code = 22
AND nvl(wblt.enabled_flag, 'N') = 'Y'
AND ml.lookup_type = 'WMS_LABEL_TYPE'
AND ml.lookup_code = wblt.document_id
MINUS
SELECT wblt.document_id label_type_id,
ml.meaning label_type_name,
wblt.level_type_code level_type_code
FROM wms_bflow_label_type wblt, mfg_lookups ml
WHERE wblt.business_flow_code = 42
AND nvl(wblt.enabled_flag, 'N') = 'Y'
AND ml.lookup_type = 'WMS_LABEL_TYPE'
AND ml.lookup_code = wblt.document_id
ORDER BY level_type_code DESC; --Added Order By clause for Bug#7214797
SELECT meaning FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND lookup_code = p_label_type_id;
SELECT ORGANIZATION_ID
INTO l_org_id
FROM RCV_TRANSACTIONS
WHERE GROUP_ID = p_transaction_id(1)
AND ROWNUM=1;
SELECT ORGANIZATION_ID
INTO l_org_id
FROM RCV_TRANSACTIONS
WHERE SHIPMENT_HEADER_ID = p_transaction_id(1)
AND ROWNUM=1;
SELECT label_format_name INTO l_format_name
FROM WMS_LABEL_FORMATS
WHERE label_format_id = p_format_id;
-- Deleted the IF condition around the call to the
-- WSH_REPORT_PRINTERS_PVT.GET_PRINTER() as part of cleanup since irrespective of
-- the fact that a default format is defined or not the printer has to be derived.
-- The format can also be derived in the individual label API's(INVLAP*B.pls) via
-- the rules engine anyways. Moreover the default printer is defined based on the
-- document (label type in this case and not on the format)
-- Get default printer
IF(l_debug = 1 ) THEN -- For Bug 4553439
trace('Value of org id before calling printer for business flow: ' || l_org_id, TRACE_PROMPT,TRACE_LEVEL);
SELECT REPLACE(tz_offset(a.timezone_code),':3',':5') INTO l_tz_offset FROM FND_TIMEZONES_VL a WHERE enabled_flag = 'Y' AND fnd_profile.VALUE('CLIENT_TIMEZONE_ID') = upgrade_tz_id;
SELECT to_char(l_sysdate+(l_tz_offset_hrs-l_db_offset)/24, G_DATE_FORMAT_MASK), to_char(l_sysdate+(l_tz_offset_hrs-l_db_offset)/24, 'HH24:MI:SS')
INTO l_date, l_time FROM dual;
SELECT to_char(l_sysdate, G_DATE_FORMAT_MASK), to_char(l_sysdate, 'HH24:MI:SS')
INTO l_date, l_time FROM dual;
SELECT user_name INTO G_USER
FROM FND_USER WHERE user_id = fnd_global.user_id;
SELECT value INTO l_character_set
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';
SELECT tag INTO l_xml_encoding
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'FND_ISO_CHARACTER_SET_MAP'
AND LOOKUP_CODE = l_character_set;
* Update the label content
* with the new job name, printer name, no of copies
***************************************************/
FUNCTION update_label_content(
p_label_content LONG
, p_job_name VARCHAR2
, p_printer_name VARCHAR2
, p_no_of_copy NUMBER) RETURN LONG IS
l_index NUMBER;
END update_label_content;
SELECT label_format_id,label_format_name
INTO p_label_format_id,p_label_format FROM WMS_LABEL_FORMATS
WHERE document_id = p_label_type_id
AND default_format_flag = 'Y';
select wlfv.label_field_id field_id,
wlfv.field_variable_name variable_name,
wlf.column_name column_name,
wlf.sql_stmt
from wms_label_field_variables wlfv, wms_label_fields_b wlf
where wlfv.label_field_id = wlf.label_field_id
and wlfv.label_format_id = p_format_id
order by wlf.column_name;
* Insert into WMS_LABEL_REQUESTS_HIST
************************************/
PROCEDURE insert_history_record(p_history_rec WMS_LABEL_REQUESTS_HIST%ROWTYPE) IS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO wms_label_requests_hist
( label_request_id,
LABEL_TYPE_ID ,
LABEL_FORMAT_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
LOCATOR_ID ,
LOT_NUMBER ,
REVISION ,
SERIAL_NUMBER ,
LPN_ID ,
SUPPLIER_ID ,
SUPPLIER_SITE_ID ,
SUPPLIER_ITEM_ID ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID ,
CUSTOMER_ITEM_ID ,
CUSTOMER_CONTACT_ID ,
FREIGHT_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
PRINTER_NAME ,
DELIVERY_ID ,
BUSINESS_FLOW_CODE ,
PACKAGE_ID ,
DELIVERY_DETAIL_ID,
SALES_ORDER_HEADER_ID,
SALES_ORDER_LINE_ID,
RULE_ID,
RULE_WEIGHT,
STRATEGY_ID,
LABEL_CONTENT,
JOB_NAME,
REQUEST_MODE_CODE,
REQUEST_DATE,
REQUEST_USER_ID,
OUTFILE_NAME,
OUTFILE_DIRECTORY,
NO_OF_COPY,
ENCODING,
ORIGINAL_REQUEST_ID,
STATUS_FLAG,
JOB_STATUS,
PRINTER_STATUS,
STATUS_TYPE,
ERROR_MESSAGE
)VALUES
( p_history_rec.label_request_id,
p_history_rec.LABEL_TYPE_ID ,
p_history_rec.LABEL_FORMAT_ID,
p_history_rec.ORGANIZATION_ID,
p_history_rec.INVENTORY_ITEM_ID,
p_history_rec.SUBINVENTORY_CODE,
p_history_rec.LOCATOR_ID ,
p_history_rec.LOT_NUMBER ,
p_history_rec.REVISION ,
p_history_rec.SERIAL_NUMBER ,
p_history_rec.LPN_ID ,
p_history_rec.SUPPLIER_ID ,
p_history_rec.SUPPLIER_SITE_ID ,
p_history_rec.SUPPLIER_ITEM_ID ,
p_history_rec.CUSTOMER_ID ,
p_history_rec.CUSTOMER_SITE_ID ,
p_history_rec.CUSTOMER_ITEM_ID ,
p_history_rec.CUSTOMER_CONTACT_ID ,
p_history_rec.FREIGHT_CODE ,
p_history_rec.LAST_UPDATE_DATE ,
p_history_rec.LAST_UPDATED_BY ,
p_history_rec.CREATION_DATE ,
p_history_rec.CREATED_BY ,
p_history_rec.LAST_UPDATE_LOGIN ,
p_history_rec.REQUEST_ID ,
p_history_rec.PROGRAM_APPLICATION_ID,
p_history_rec.PROGRAM_ID ,
p_history_rec.PROGRAM_UPDATE_DATE ,
p_history_rec.ATTRIBUTE_CATEGORY ,
p_history_rec.ATTRIBUTE1 ,
p_history_rec.ATTRIBUTE2 ,
p_history_rec.ATTRIBUTE3 ,
p_history_rec.ATTRIBUTE4 ,
p_history_rec.ATTRIBUTE5 ,
p_history_rec.ATTRIBUTE6 ,
p_history_rec.ATTRIBUTE7 ,
p_history_rec.ATTRIBUTE8 ,
p_history_rec.ATTRIBUTE9 ,
p_history_rec.ATTRIBUTE10 ,
p_history_rec.ATTRIBUTE11 ,
p_history_rec.ATTRIBUTE12 ,
p_history_rec.ATTRIBUTE13 ,
p_history_rec.ATTRIBUTE14 ,
p_history_rec.ATTRIBUTE15 ,
p_history_rec.PRINTER_NAME ,
p_history_rec.DELIVERY_ID ,
p_history_rec.BUSINESS_FLOW_CODE ,
p_history_rec.PACKAGE_ID ,
p_history_rec.DELIVERY_DETAIL_ID,
p_history_rec.SALES_ORDER_HEADER_ID,
p_history_rec.SALES_ORDER_LINE_ID,
p_history_rec.RULE_ID,
p_history_rec.RULE_WEIGHT,
p_history_rec.STRATEGY_ID,
p_history_rec.LABEL_CONTENT,
p_history_rec.JOB_NAME,
p_history_rec.REQUEST_MODE_CODE,
p_history_rec.REQUEST_DATE,
p_history_rec.REQUEST_USER_ID,
p_history_rec.OUTFILE_NAME,
p_history_rec.OUTFILE_DIRECTORY,
p_history_rec.NO_OF_COPY,
p_history_rec.ENCODING,
p_history_rec.ORIGINAL_REQUEST_ID,
p_history_rec.STATUS_FLAG,
p_history_rec.JOB_STATUS,
p_history_rec.PRINTER_STATUS,
p_history_rec.STATUS_TYPE,
p_history_rec.ERROR_MESSAGE
);
trace('Error in inserting into WMS_LABEL_REQUESTS_HIST record, Req ID:'|| p_history_rec.label_request_id,TRACE_PROMPT, TRACE_LEVEL);
END insert_history_record;
SELECT * FROM WMS_LABEL_REQUESTS
WHERE label_request_id = p_label_request_id;
l_hist_rec.LAST_UPDATE_DATE :=l_label_req_rec.LAST_UPDATE_DATE ;
l_hist_rec.LAST_UPDATED_BY :=l_label_req_rec.LAST_UPDATED_BY ;
l_hist_rec.LAST_UPDATE_LOGIN :=l_label_req_rec.LAST_UPDATE_LOGIN ;
l_hist_rec.PROGRAM_UPDATE_DATE :=l_label_req_rec.PROGRAM_UPDATE_DATE ;
insert_history_record(l_hist_rec);
trace('Record Inserted to WDRH', TRACE_PROMPT, TRACE_LEVEL);
* Update history record
***************************************/
PROCEDURE update_history_record(
p_label_request_id IN NUMBER
, p_status_flag IN VARCHAR2
, p_job_status IN VARCHAR2
, p_printer_status IN VARCHAR2
, p_status_type IN VARCHAR2
, p_outfile_name IN VARCHAR2
, p_outfile_directory IN VARCHAR2
, p_error_message IN VARCHAR2
) IS PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE wms_label_requests_hist
SET status_flag = nvl(p_status_flag, status_flag)
, job_status = nvl(p_job_status,job_status)
, printer_status = nvl(p_printer_status, printer_status)
, status_type = nvl(p_status_type, status_type)
, outfile_name = nvl(p_outfile_name, outfile_name)
, outfile_directory = nvl(p_outfile_directory, outfile_directory)
, error_message = nvl(p_error_message, error_message)
WHERE label_request_id = p_label_request_id;
END update_history_record;
* It will first insert a row into wms_label_requests
* then call the rules engine to get the label format
******************************************/
PROCEDURE GET_FORMAT_WITH_RULE
(
P_DOCUMENT_ID IN NUMBER,
P_LABEL_FORMAT_ID IN NUMBER ,
P_ORGANIZATION_ID IN NUMBER ,
P_INVENTORY_ITEM_ID IN NUMBER ,
P_SUBINVENTORY_CODE IN VARCHAR2 ,
P_LOCATOR_ID IN NUMBER ,
P_LOT_NUMBER IN VARCHAR2 ,
P_REVISION IN VARCHAR2 ,
P_SERIAL_NUMBER IN VARCHAR2 ,
P_LPN_ID IN NUMBER ,
P_SUPPLIER_ID IN NUMBER ,
P_SUPPLIER_SITE_ID IN NUMBER ,
P_SUPPLIER_ITEM_ID IN NUMBER ,
P_CUSTOMER_ID IN NUMBER ,
P_CUSTOMER_SITE_ID IN NUMBER ,
P_CUSTOMER_ITEM_ID IN NUMBER ,
P_CUSTOMER_CONTACT_ID IN NUMBER ,
P_FREIGHT_CODE IN VARCHAR2 ,
P_LAST_UPDATE_DATE IN DATE,
P_LAST_UPDATED_BY IN NUMBER,
P_CREATION_DATE IN DATE,
P_CREATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER ,
P_REQUEST_ID IN NUMBER ,
P_PROGRAM_APPLICATION_ID IN NUMBER ,
P_PROGRAM_ID IN NUMBER ,
P_PROGRAM_UPDATE_DATE IN DATE ,
P_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
P_ATTRIBUTE1 IN VARCHAR2 ,
P_ATTRIBUTE2 IN VARCHAR2 ,
P_ATTRIBUTE3 IN VARCHAR2 ,
P_ATTRIBUTE4 IN VARCHAR2 ,
P_ATTRIBUTE5 IN VARCHAR2 ,
P_ATTRIBUTE6 IN VARCHAR2 ,
P_ATTRIBUTE7 IN VARCHAR2 ,
P_ATTRIBUTE8 IN VARCHAR2 ,
P_ATTRIBUTE9 IN VARCHAR2 ,
P_ATTRIBUTE10 IN VARCHAR2 ,
P_ATTRIBUTE11 IN VARCHAR2 ,
P_ATTRIBUTE12 IN VARCHAR2 ,
P_ATTRIBUTE13 IN VARCHAR2 ,
P_ATTRIBUTE14 IN VARCHAR2 ,
P_ATTRIBUTE15 IN VARCHAR2 ,
P_PRINTER_NAME IN VARCHAR2 ,
P_DELIVERY_ID IN NUMBER ,
P_BUSINESS_FLOW_CODE IN NUMBER ,
P_PACKAGE_ID IN NUMBER ,
p_sales_order_header_id IN NUMBER , -- bug 2326102
p_sales_order_line_id IN NUMBER , -- bug 2326102
p_delivery_detail_id IN NUMBER , -- bug 2326102
p_use_rule_engine IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_label_format_id OUT NOCOPY NUMBER,
x_label_format OUT NOCOPY VARCHAR2,
x_label_request_id OUT NOCOPY NUMBER
)IS
l_wms_installed BOOLEAN := FALSE;
trace(' **Last Update Date : ' || P_LAST_UPDATE_DATE, TRACE_PROMPT, TRACE_LEVEL);
trace(' **Last Updated By : ' || P_LAST_UPDATED_BY, TRACE_PROMPT, TRACE_LEVEL);
SELECT wms_label_print_history_s.nextval into l_label_request_id from dual;
trace(' **Label Request ID inserted in the wms_label_requests table is :'|| l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
INSERT INTO wms_label_requests
( label_request_id,
DOCUMENT_ID ,
LABEL_FORMAT_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
LOCATOR_ID ,
LOT_NUMBER ,
REVISION ,
SERIAL_NUMBER ,
LPN_ID ,
SUPPLIER_ID ,
SUPPLIER_SITE_ID ,
SUPPLIER_ITEM_ID ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID ,
CUSTOMER_ITEM_ID ,
CUSTOMER_CONTACT_ID ,
FREIGHT_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
PRINTER_NAME ,
DELIVERY_ID ,
BUSINESS_FLOW_CODE ,
package_id ,
delivery_detail_id,
sales_order_header_id,
sales_order_line_id
)
VALUES
( l_label_request_id,
P_DOCUMENT_ID ,
P_LABEL_FORMAT_ID,
P_ORGANIZATION_ID,
P_INVENTORY_ITEM_ID,
P_SUBINVENTORY_CODE,
P_LOCATOR_ID ,
P_LOT_NUMBER ,
P_REVISION ,
P_SERIAL_NUMBER ,
P_LPN_ID ,
P_SUPPLIER_ID ,
P_SUPPLIER_SITE_ID ,
P_SUPPLIER_ITEM_ID ,
P_CUSTOMER_ID ,
P_CUSTOMER_SITE_ID ,
P_CUSTOMER_ITEM_ID ,
P_CUSTOMER_CONTACT_ID ,
P_FREIGHT_CODE ,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
P_LAST_UPDATE_LOGIN ,
P_REQUEST_ID ,
P_PROGRAM_APPLICATION_ID,
P_PROGRAM_ID ,
P_PROGRAM_UPDATE_DATE ,
P_ATTRIBUTE_CATEGORY ,
P_ATTRIBUTE1 ,
P_ATTRIBUTE2 ,
P_ATTRIBUTE3 ,
P_ATTRIBUTE4 ,
P_ATTRIBUTE5 ,
P_ATTRIBUTE6 ,
P_ATTRIBUTE7 ,
P_ATTRIBUTE8 ,
P_ATTRIBUTE9 ,
P_ATTRIBUTE10 ,
P_ATTRIBUTE11 ,
P_ATTRIBUTE12 ,
P_ATTRIBUTE13 ,
P_ATTRIBUTE14 ,
P_ATTRIBUTE15 ,
P_PRINTER_NAME ,
P_DELIVERY_ID ,
P_BUSINESS_FLOW_CODE,
p_package_id,
p_delivery_detail_id,
p_sales_order_header_id,
p_sales_order_line_id
);
trace('Inserted into WMS_LABEL_REQUESTS table, label_request_id=' ||l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
select label_format_name
into x_label_format
from wms_label_formats
where label_format_id = x_label_format_id;
update wms_label_requests
set label_format_id = x_label_format_id
where label_request_id = l_label_request_id;
trace(' In applying rules engine, row inserted, req_id='|| l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
deleted for both label-set and label-format. Because the driver to
populate in the history table is returned number of records from
respective PVT. But since this label set feature is NOT implemented
in other remaining PVT packages and this API does not get called
twice, This should only be delete for label-set.
FOR PVT1,2,3,4,5, there will be one extra record in the
wms_label_request table for each transaction but it will NOT be
transferred to hist rec or to generated xml as the respective
PVT1,2,3,4,5 does not return the first pseudo record.
We need to insert it for each time as rules engine works of
wms_label_request table
*/
DELETE FROM wms_label_requests
WHERE label_request_id = l_label_request_id
AND exists (SELECT label_format_id FROM wms_label_formats
WHERE label_format_id = x_label_format_id
AND document_id = p_document_id
AND Nvl(label_entity_type,0) = 1);
trace('Number of rows deleted for label-set :'||SQL%rowcount, TRACE_PROMPT, TRACE_LEVEL);
SELECT lot_control_code,revision_qty_control_code
INTO l_lot_control_code,l_rev_control_code
FROM MTL_SYSTEM_ITEMS_B
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number,revision
INTO l_lot_number,l_revision
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number = l_range_serial_numbers(i);
SELECT wip_entity_id into l_wip_entity_id
FROM WIP_FLOW_SCHEDULES
WHERE organization_id = p_organization_id
AND schedule_number = l_range_schedule_numbers(i);
/* inline branch the code so that we select the records from rti for *
* patchset level below J, and from rt for Patchset J and above *
*/
IF l_patch_level = 0 THEN
IF p_business_flow_code in (1,2,3,4) THEN
lpn_table_populated := 'N';
SELECT nvl(DECODE(p_business_flow_code,2,rti.transfer_lpn_id,rti.lpn_id),0)
INTO l_lpn_table(m)
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_transaction_id(m);
SELECT WMS_EPC_S2.nextval INTO EPC_GROUP_ID FROM DUAL;
trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
--,p_status_flag => 'S'
--Change made for 4179593
,p_status_flag => l_variable_data(k).label_status
,p_error_message => l_variable_data(k).error_message
,p_job_status => l_job_status
,p_printer_status => l_printer_status
,p_status_type => l_status_type);
trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
--,p_status_flag => 'S'
--Change made for 4179593
,p_status_flag => l_variable_data(k).label_status
,p_error_message => l_variable_data(k).error_message
,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
,p_outfile_directory => G_PROFILE_OUT_DIR
);
trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
XML file will be written and the history record will now be updated only if there was
no ERROR returned by the get_variable_data() from the relevant Label Type files.
IF nvl(l_return_status,'E') <> 'S' THEN
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'E');
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'S'
,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
,p_outfile_directory => G_PROFILE_OUT_DIR);
trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
--,p_status_flag => 'S'
--Change made for 4179593
,p_status_flag => l_variable_data(k).label_status
,p_error_message => l_variable_data(k).error_message
,p_job_status => l_job_status
,p_printer_status => l_printer_status
,p_status_type => l_status_type);
TCP/IP Print Request will be sent and the history record will now be updated only if there was
were no ERROR returned by the get_variable_data() from the relevant Label Type files.
IF nvl(l_return_status,'E') <> 'S' THEN
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'E'
,p_error_message => l_msg_data);
trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'S'
,p_job_status => l_job_status
,p_printer_status => l_printer_status
,p_status_type => l_status_type);
/* Bug 3417450 Delete the global table g_label_request_tbl before calling
* get_variable_data
*/
g_label_request_tbl.DELETE;
trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
--,p_status_flag => 'S'
--Change made for 4179593
,p_status_flag => l_variable_data(k).label_status
,p_error_message => l_variable_data(k).error_message
,p_job_status => l_job_status
,p_printer_status => l_printer_status
,p_status_type => l_status_type);
trace('Custom Labels Trace [INVLABPB.pls]: After update_history_record() ', TRACE_PROMPT, TRACE_LEVEL);
trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
--,p_status_flag => 'S'
--Change made for 4179593
,p_status_flag => l_variable_data(k).label_status
,p_error_message => l_variable_data(k).error_message
,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
,p_outfile_directory => G_PROFILE_OUT_DIR);
trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
XML file will be written and the history record will now be updated only if there was
no ERROR returned by the get_variable_data() from the relevant Label Type files.
IF nvl(l_return_status,'E') <> 'S' THEN
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'E');
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'S'
,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
,p_outfile_directory => G_PROFILE_OUT_DIR);
trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
--,p_status_flag => 'S'
--Change made for 4179593
,p_status_flag => l_variable_data(k).label_status
,p_error_message => l_variable_data(k).error_message
,p_job_status => l_job_status
,p_printer_status => l_printer_status
,p_status_type => l_status_type);
trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
TCP/IP Print Request will be sent and the history record will now be updated only if there was
were no ERROR returned by the get_variable_data() from the relevant Label Type files.
IF nvl(l_return_status,'E') <> 'S' THEN
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'E'
,p_error_message => l_msg_data);
trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_variable_data(k).label_request_id
,p_status_flag => 'S'
,p_job_status => l_job_status
,p_printer_status => l_printer_status
,p_status_type => l_status_type);
SELECT * FROM wms_label_requests_hist
WHERE label_request_id = p_hist_label_request_id;
select wms_label_print_history_s.nextval into l_history_rec.label_request_id
from dual;
select sysdate into l_sysdate from dual;
l_history_rec.last_update_date := l_sysdate;
l_history_rec.last_updated_by := fnd_global.user_id;
l_label_content := update_label_content(
l_history_rec.label_content,
l_history_rec.job_name,
l_printer_name,
l_no_of_copy);
trace('Inserting into history table for the reprint request', TRACE_PROMPT, TRACE_LEVEL);
insert_history_record(l_history_rec);
trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_history_rec.label_request_id
, p_status_flag => 'S'
, p_job_status => l_job_status
, p_printer_status => l_printer_status
, p_status_type => l_status_type);
update_history_record(
p_label_request_id => l_history_rec.label_request_id
, p_status_flag => 'E'
);
update_history_record(
p_label_request_id => l_history_rec.label_request_id
, p_status_flag => 'S'
, p_outfile_name => l_history_rec.job_name ||'.xml'
, p_outfile_directory => G_PROFILE_OUT_DIR
);
update_history_record(
p_label_request_id => l_history_rec.label_request_id
, p_status_flag => 'E'
, p_error_message => l_msg_data
);
trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
update_history_record(
p_label_request_id => l_history_rec.label_request_id
, p_status_flag => 'S'
, p_job_status => l_job_status
, p_printer_status => l_printer_status
, p_status_type => l_status_type);
select request_date, request_time, printer, label_format,
lpn, item, bus_flow, label_type, label_request_id
from
(select to_char(wlrh.creation_date, 'DD-MON-YY') request_date,
to_char(wlrh.creation_date, 'HH:MI:SS') request_time,
wlrh.printer_name printer,
wlf.label_format_name label_format,
wlpn.license_plate_number lpn,
msik.concatenated_segments item,
mfglkup1.meaning bus_flow,
mfglkup2.meaning label_type,
wlrh.label_request_id label_request_id
from wms_label_requests_hist wlrh,
wms_label_formats wlf,
wms_license_plate_numbers wlpn,
mtl_system_items_kfv msik,
mfg_lookups mfglkup1,
mfg_lookups mfglkup2
where wlrh.label_format_id = wlf.label_format_id (+)
and wlrh.lpn_id = wlpn.lpn_id (+)
and wlrh.inventory_item_id = msik.inventory_item_id (+)
and wlrh.organization_id = msik.organization_id (+)
and (wlrh.business_flow_code = mfglkup1.lookup_code (+)
and mfglkup1.lookup_type(+) = 'WMS_BUSINESS_FLOW')
and (wlrh.label_type_id = mfglkup2.lookup_code (+)
and mfglkup2.lookup_type(+) = 'WMS_LABEL_TYPE')
and nvl(wlrh.printer_name, '@@@') = nvl(p_printer_Name, nvl(wlrh.printer_name, '@@@'))
and nvl(wlrh.business_flow_code, -99) = nvl(p_bus_flow_Code, nvl(wlrh.business_flow_code, -99))
and nvl(wlrh.label_type_id, -99) = nvl(p_label_type_Id, nvl(wlrh.label_type_id, -99))
and nvl(wlrh.lpn_id, -99) = nvl(p_lpn_Id, nvl(wlrh.lpn_id, -99))
and wlrh.created_by = p_created_By
order by wlrh.creation_date desc,wlrh.label_request_id asc) wlrha --bug 16290289
where rownum <= p_Requests;
x_Message := 'Selection Criteria Returned Records';
x_Message := 'Selection Criteria Returned No Records';
x_Message := 'Selection Criteria Returned No Records';
SELECT MCR.CROSS_REFERENCE, MCR.DESCRIPTION, MCR.REVISION_ID, MCR.UOM_CODE
INTO x_gtin, x_gtin_desc, l_revision_id, l_uom_code
FROM MTL_CROSS_REFERENCES MCR, MTL_ITEM_REVISIONS_B MIR
WHERE CROSS_REFERENCE_TYPE = G_PROFILE_GTIN
AND MIR.INVENTORY_ITEM_ID = MCR.INVENTORY_ITEM_ID
AND MIR.INVENTORY_ITEM_ID = p_inventory_item_id
AND MIR.REVISION_ID = nvl(MCR.REVISION_ID,MIR.REVISION_ID)
AND MIR.REVISION = p_revision
AND (
( MCR.ORG_INDEPENDENT_FLAG = 'Y' AND MCR.ORGANIZATION_ID IS NULL AND MIR.ORGANIZATION_ID = p_organization_id) OR
( MCR.ORG_INDEPENDENT_FLAG = 'N' AND MCR.ORGANIZATION_ID = p_organization_id AND MCR.ORGANIZATION_ID = MIR.ORGANIZATION_ID)
)
AND NVL(MCR.UOM_CODE, NVL(p_unit_of_measure,'@@@')) = NVL(p_unit_of_measure, NVL(MCR.UOM_CODE,'@@@'));
SELECT MCR.CROSS_REFERENCE, MCR.DESCRIPTION, MCR.REVISION_ID, MCR.UOM_CODE
INTO x_gtin, x_gtin_desc, l_revision_id, l_uom_code
FROM MTL_CROSS_REFERENCES MCR
WHERE CROSS_REFERENCE_TYPE = G_PROFILE_GTIN
AND MCR.INVENTORY_ITEM_ID = p_inventory_item_id
AND MCR.REVISION_ID IS NULL
AND (
( MCR.ORG_INDEPENDENT_FLAG = 'Y' AND MCR.ORGANIZATION_ID IS NULL ) OR
( MCR.ORG_INDEPENDENT_FLAG = 'N' AND MCR.ORGANIZATION_ID = p_organization_id )
)
AND NVL(MCR.UOM_CODE,NVL(p_unit_of_measure,'@@@')) = NVL(p_unit_of_measure, NVL(MCR.UOM_CODE,'@@@'));
SELECT wlc.parent_lpn_id ,wlc.inventory_item_id , wlc.lot_number
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id
)
ORDER BY wlc.serial_summary_entry DESC ;
SELECT msnt.status_id status_id
FROM mtl_serial_numbers_temp msnt , mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = p_transaction_id
AND msnt.status_id is not null
UNION
SELECT msn.status_id status_id
FROM mtl_serial_numbers_temp msnt , mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = p_transaction_id
AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
AND msn.current_organization_id = p_organization_id
AND msnt.status_id is NULL;
SELECT status_id
FROM mtl_serial_numbers msn
WHERE msn.lpn_id = l_lpn_id
AND msn.current_organization_id = p_organization_id
AND serial_number not in (select serial_number from
mtl_serial_numbers msn1,mtl_serial_numbers_temp msnt
where msnt.transaction_temp_id = p_transaction_id
and msn1.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number);
SELECT mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id ,
NVL(mmtt.lpn_id,mmtt.content_lpn_id) lpn_id , mmtt.transaction_action_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id =p_transaction_id
AND mmtt.inventory_item_id = l_inventory_item_id
AND Nvl(mmtt.item_lot_control_code,-99) <> 2
UNION
SELECT mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id ,
NVL(mmtt.lpn_id,mmtt.content_lpn_id) lpn_id , mmtt.transaction_action_id
FROM mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id =p_transaction_id
AND mmtt.inventory_item_id = l_inventory_item_id
AND Nvl(mmtt.item_lot_control_code,-99) = 2
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND nvl(mtlt.lot_number,'@@@@') = nvl(l_lot_number,'@@@@');
SELECT wlpn.lpn_context , wlpn.subinventory_code , wlpn.locator_id
INTO l_lpn_context , l_subinventory_code , l_locator_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_lpn_id;
SELECT moqd.status_id into l_src_status
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.inventory_item_id = l_wlc_cur.inventory_item_id
AND moqd.organization_id = l_mmtt_cur.organization_id
AND nvl(moqd.lpn_id,-9999) = Nvl(l_mmtt_cur.lpn_id,-9999)
AND moqd.subinventory_code = l_mmtt_cur.subinventory_code
AND NVL(moqd.locator_id,-9999) = NVL(l_mmtt_cur.locator_id,-9999)
AND NVL(moqd.lot_number,'@@@@') = NVL(l_wlc_cur.lot_number,'@@@@')
AND ROWNUM = 1;
SELECT status_code into l_return_status_code
from mtl_material_statuses
where status_id = l_return_status_id;