[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Build_OPM_Selections
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_api_version IN NUMBER,
p_batch_number IN NUMBER,
p_process_all_flag IN NUMBER,
p_printer IN VARCHAR2,
p_user_print_style IN VARCHAR2,
p_number_of_copies IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_CODE_BLOCK VARCHAR2(2000);
L_API_NAME CONSTANT VARCHAR2(30) := 'Build OPM Selections';
SELECTION_INSERT_ERROR EXCEPTION;
PROCESS_SELECTIONS_ERROR EXCEPTION;
SELECT om.order_id,
om.line_no,
om.bol_id,
om.item_id,
om.shipcust_id,
om.holdreas_code,
mtl.segment1 item_no,
om.shipping_ind,
om.picking_ind,
om.order_no,
om.bol_no,
cust.cust_no
FROM op_cust_mst cust,
gr_order_info_v om,
mtl_system_items mtl
WHERE om.bol_id >= GlobalBatchHeader.shipment_from
AND om.bol_id <= GlobalBatchHeader.shipment_to
AND om.from_whse = TO_CHAR(g_default_orgid)
AND om.shipaddr_id = cust.of_ship_to_site_use_id
AND mtl.inventory_item_id = om.item_id
AND om.shipping_ind = 0
AND om.delete_mark = 0
UNION
SELECT om.order_id,
om.line_no,
om.bol_id,
om.item_id,
om.shipcust_id,
om.holdreas_code,
ic.item_no,
om.shipping_ind,
om.picking_ind,
om.order_no,
om.bol_no,
cust.cust_no
FROM op_cust_mst cust,
gr_order_info_v om,
ic_item_mst ic
WHERE om.bol_id >= GlobalBatchHeader.shipment_from
AND om.bol_id <= GlobalBatchHeader.shipment_to
AND om.from_whse = g_default_whse
AND om.shipcust_id = cust.cust_id
AND ic.item_id = om.item_id
AND om.shipping_ind = 0
AND om.delete_mark = 0;
SELECT om.order_id,
om.line_no,
om.bol_id,
om.item_id,
om.shipcust_id,
om.holdreas_code,
mtl.segment1 item_no,
om.shipping_ind,
om.picking_ind,
om.order_no,
om.bol_no,
cust.cust_no
FROM op_cust_mst cust,
gr_order_info_v om,
mtl_system_items mtl
WHERE om.actual_shipdate >= GlobalBatchHeader.shipment_date_from
AND om.actual_shipdate <= GlobalBatchHeader.shipment_date_to
AND om.from_whse = TO_CHAR(g_default_orgid)
AND om.shipaddr_id = cust.of_ship_to_site_use_id
AND mtl.inventory_item_id = om.item_id
AND om.shipping_ind = 0
AND om.delete_mark = 0
UNION
SELECT om.order_id,
om.line_no,
om.bol_id,
om.item_id,
om.shipcust_id,
om.holdreas_code,
ic.item_no,
om.shipping_ind,
om.picking_ind,
om.order_no,
om.bol_no,
cust.cust_no
FROM op_cust_mst cust,
gr_order_info_v om,
ic_item_mst ic
WHERE om.actual_shipdate >= GlobalBatchHeader.shipment_date_from
AND om.actual_shipdate <= GlobalBatchHeader.shipment_date_to
AND om.from_whse = g_default_whse
AND om.shipcust_id = cust.cust_id
AND ic.item_id = om.item_id
AND om.shipping_ind = 0
AND om.delete_mark = 0;
SELECT om.order_id,
om.line_no,
om.line_id,
om.bol_id,
om.item_id,
om.shipcust_id,
om.holdreas_code,
om.hold_code,
mtl.segment1 item_no,
om.shipping_ind,
om.picking_ind,
om.order_no,
om.bol_no,
cust.cust_no
FROM op_cust_mst cust,
gr_order_info_v om,
mtl_system_items mtl
WHERE om.order_id >= GlobalBatchHeader.order_from
AND om.order_id <= GlobalBatchHeader.order_to
AND om.from_whse = TO_CHAR(g_default_orgid)
AND om.shipaddr_id = cust.of_ship_to_site_use_id
AND mtl.inventory_item_id = om.item_id
UNION
SELECT DISTINCT om.order_id,
om.line_no,
om.line_id,
om.bol_id,
om.item_id,
om.shipcust_id,
om.holdreas_code,
om.hold_code,
ic.item_no,
om.shipping_ind,
om.picking_ind,
om.order_no,
om.bol_no,
cust.cust_no
FROM op_cust_mst cust,
gr_order_info_v om,
ic_item_mst ic
WHERE om.order_id >= GlobalBatchHeader.order_from
AND om.order_id <= GlobalBatchHeader.order_to
AND om.from_whse = g_default_whse
AND om.shipcust_id = cust.cust_id
AND ic.item_id = om.item_id;
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = g_default_whse;
SELECT ooh.order_hold_id
FROM oe_order_holds_all ooh
WHERE (LocalOrderDetail.order_id = ooh.header_id
OR LocalOrderDetail.line_id = ooh.line_id)
AND ooh.hold_release_id IS NULL;
SAVEPOINT Build_OPM_Selections;
** and exists on the batch selection header and the
** status is set to '1' indicating entered.
*/
l_code_block := 'Validate the batch number';
DELETE
FROM gr_selection sd
WHERE sd.batch_no = p_batch_number;
Insert_Selection_Row
('GR_ON_HOLD_NO_SHIP',
'CODE',
LocalShipmentDate.holdreas_code,
LocalShipmentDate.order_id,
LocalShipmentDate.line_no,
g_default_document,
'N',
LocalShipmentDate.cust_no,
LocalShipmentDate.bol_no,
l_return_status);
RAISE Selection_Insert_Error;
Check_Selected_Line
(l_return_status,
x_msg_count,
x_msg_data);
RAISE Process_Selections_Error;
Insert_Selection_Row
('GR_ON_HOLD_NO_SHIP',
'CODE',
LocalShipmentDetail.holdreas_code,
LocalShipmentDetail.order_id,
LocalShipmentDetail.line_no,
g_default_document,
'N',
LocalShipmentDetail.cust_no,
LocalShipmentDetail.bol_no,
l_return_status);
RAISE Selection_Insert_Error;
Check_Selected_Line
(l_return_status,
x_msg_count,
x_msg_data);
RAISE Process_Selections_Error;
Insert_Selection_Row
('GR_ON_HOLD_NO_PICK',
'CODE',
LocalOrderDetail.holdreas_code,
LocalOrderDetail.order_id,
LocalOrderDetail.line_no,
'',
'N',
LocalOrderDetail.cust_no,
'',
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_ON_HOLD_NO_SHIP',
'CODE',
LocalOrderDetail.holdreas_code,
LocalOrderDetail.order_id,
LocalOrderDetail.line_no,
'',
'N',
LocalOrderDetail.cust_no,
'',
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_ORDER_ALREADY_SHIPPED',
'',
'',
LocalOrderDetail.order_id,
LocalOrderDetail.line_no,
'',
'N',
LocalOrderDetail.cust_no,
'',
l_return_status);
RAISE Selection_Insert_Error;
Check_Selected_Line
(l_return_status,
x_msg_count,
x_msg_data);
RAISE Process_Selections_Error;
Insert_Selection_Row
('GR_ON_HOLD_NO_PICK',
'CODE',
LocalHoldRecord.order_hold_id,
LocalOrderDetail.order_id,
LocalOrderDetail.line_no,
'',
'N',
LocalOrderDetail.cust_no,
'',
l_return_status);
RAISE Selection_Insert_Error;
Check_Selected_Line
(l_return_status,
x_msg_count,
x_msg_data);
RAISE Process_Selections_Error;
** Update the header status to Selected
*/
UPDATE gr_selection_header
SET status = 2
WHERE batch_no = p_batch_number;
** to process the selected lines.
*/
IF p_process_all_flag = 1 THEN
Process_Selections
(errbuf,
retcode,
p_commit,
'F',
p_init_msg_list,
p_validation_level,
1.0,
p_batch_number,
p_process_all_flag,
p_printer,
p_user_print_style,
p_number_of_copies,
l_return_status,
x_msg_count,
x_msg_data);
RAISE Process_Selections_Error;
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
WHEN Selection_Insert_Error THEN
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
('GR_NO_RECORD_INSERTED',
'CODE',
g_order_number || ' ' || g_order_line,
x_msg_count,
x_msg_data,
x_return_status);
WHEN Process_Selections_Error THEN
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
ROLLBACK TO SAVEPOINT Build_OPM_Selections;
END Build_OPM_Selections;
** This procedure takes the selections for the batch that are
** stored in the gr_selection table and generates the cover
** letters and documents for the items in the selection.
*/
PROCEDURE Process_Selections
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_api_version IN NUMBER,
p_batch_number IN NUMBER,
p_process_all_flag IN NUMBER,
p_printer IN VARCHAR2,
p_user_print_style IN VARCHAR2,
p_number_of_copies IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_CODE_BLOCK VARCHAR2(2000);
L_API_NAME CONSTANT VARCHAR2(30) := 'Process Selections';
L_HEADER_STATUS GR_SELECTION_HEADER.status%TYPE;
UPDATE_HISTORY_ERROR EXCEPTION;
** Get the line details. Only select line details
** marked for print that have been selected and have
** not been updated.
*/
CURSOR c_get_line_details
IS
SELECT sd.ROWID,
sd.document_code,
sd.item_code,
sd.recipient_code,
sd.line_status,
sd.order_no,
sd.order_line_number,
/* 22-Aug-2003 Mercy Thomas BUG 2932007 - Added the column shipment_no to the cursor */
sd.shipment_no
/* 22-Aug-2003 Mercy Thomas BUG 2932007 - End of the code change */
FROM gr_selection sd
WHERE sd.batch_no = g_batch_number
AND sd.print_flag = 'Y'
AND sd.line_status <> 0
AND sd.line_status <> 8;
SELECT SUBSTR(message, 1, 100) message, order_no, order_line_number
FROM gr_selection sd
WHERE sd.batch_no = g_batch_number;
SELECT distinct order_no, line_no
FROM gr_order_info_v
WHERE order_id = g_order_number;
** and exists on the batch selection header and the
** status is set to 2, 4, 5 or 6 indicating selected, a
** a restart, a rerun or printed.
*/
l_code_block := 'Validate the batch number';
SELECT gr_work_build_docs_s.nextval INTO g_session_id
FROM dual;
UPDATE gr_selection_header
SET status = 3
WHERE batch_no = p_batch_number;
** 17-Jun-2003 Mercy Thomas BUG 2932007 - If the Process Selections, is done separately, to populate the Global vaiables for Document
** Management added the following code
**
*/
IF g_report_type = 4 THEN
g_item_code := LocalDetailRecord.item_code;
SAVEPOINT Process_Document_Selection;
UPDATE gr_selection
SET line_status = 3
WHERE ROWID = LocalDetailRecord.ROWID;
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(LocalDetailRecord.document_code,
LocalDetailRecord.item_code,
l_language_code,
GlobalRecipient.disclosure_code,
l_return_status);
UPDATE gr_selection
SET line_status = 6,
document_code = NVL(document_code, g_default_document)
WHERE ROWID = LocalDetailRecord.ROWID;
SELECT gr_work_build_docs_s.nextval INTO g_session_id
FROM dual;
** Update the header status to Print Completed
*/
UPDATE gr_selection_header
SET status = 6
WHERE batch_no = p_batch_number;
** to process the selected lines.
*/
IF p_process_all_flag = 1 THEN
/* l_return_status := FND_API.G_RET_STS_SUCCESS; */
Update_Dispatch_History
(errbuf,
retcode,
p_commit,
p_init_msg_list,
p_validation_level,
p_api_version,
p_batch_number,
l_return_status,
x_msg_count,
x_msg_data);
RAISE Update_History_Error;
WHEN Update_History_Error THEN
Handle_Error_Messages
('GR_UNEXPECTED_ERROR',
'TEXT',
l_msg_data,
x_msg_count,
x_msg_data,
l_return_status);
END Process_Selections;
** This procedure updates the dispatch history tables for the
** batch. The selections and print should have been carried out
** before this procedure is run.
*/
PROCEDURE Update_Dispatch_History
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_api_version IN NUMBER,
p_batch_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/*
** Alpha Variables
*/
L_CODE_BLOCK VARCHAR2(2000);
L_API_NAME CONSTANT VARCHAR2(30) := 'Update Dispatch History';
DISPATCH_HISTORY_INSERT_ERROR EXCEPTION;
SELECT sd.batch_no,
sd.order_no,
sd.order_line_number,
sd.document_code,
sd.item_code,
sd.print_flag,
sd.recipient_code,
sd.date_msds_sent,
sd.user_id,
sd.user_override
FROM gr_selection sd
WHERE sd.batch_no = p_batch_number;
SELECT ri.recipient_code
FROM gr_recipient_info ri
WHERE ri.recipient_code = BatchDetails.recipient_code;
SELECT cm.cust_name
FROM op_cust_mst cm
WHERE cm.cust_no = BatchDetails.recipient_code;
SELECT language
FROM gr_document_print
WHERE document_code = BatchDetails.document_code;
SELECT recipient_code,
item_code,
line_status
FROM gr_selection
WHERE batch_no = p_batch_number;
SELECT item_code
FROM gr_item_general
WHERE item_code = v_item_code;
SELECT ig1.item_code,
gi.item_no
FROM gr_item_general ig1,
gr_generic_items_b gi
WHERE gi.item_no = v_item_code
AND gi.item_code = ig1.item_code;
SAVEPOINT Update_Dispatch_History;
** and exists on the batch selection header.
*/
l_code_block := 'Validate the batch number';
** Now update the batch header status to in process
*/
CLOSE g_get_batch_status;
l_code_block := 'Update the batch header';
UPDATE gr_selection_header
SET status = 7
WHERE batch_no = p_batch_number;
** Now process the details and update dispatch history
*/
l_code_block := 'Process the batch details';
GR_RECIPIENT_INFO_PKG.Insert_Row
(l_commit,
l_called_by_form,
BatchDetails.recipient_code,
LocalCustRecord.cust_name,
BatchDetails.document_code,
GlobalBatchHeader.territory_code,
'0', /* Do not print recipient product code */
'0', /* Do not update address */
'0', /* Do not disclose all ingredients */
'O', /* Print organization address on document */
'R', /* Print documents as required */
'', /* No recipient specific disclosure code */
'', /* No recipient region code */
'', /* No special cover letter */
'0', /* No additional documents */
'0', /* No Other addresses to send to */
'1', /* Send documents to ship address */
'0', /* Do not send to invoice address */
l_blank_category,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_user_id,
l_current_date,
l_user_id,
l_current_date,
l_user_id,
--LocalRecipientRecord.time_period,
l_rowid,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Dispatch_History_Insert_Error;
** Bug 2342375 Mercy Thomas 08/15/2002 Added the following code to check for the validity of the item code before Inserting
** the Dispatch History Table.
*/
v_item_code := BatchDetails.item_code;
GR_DISPATCH_HISTORIES_PKG.Insert_Row
(l_commit,
l_called_by_form,
BatchDetails.document_code,
1, /* Document text id */
/*
** Bug 2342375 Mercy Thomas 08/15/2002 Changed the BatchDetails.item_code to l_item_code
*/
l_item_code,
/*
** Bug 2342375 Mercy Thomas 08/15/2002 End of the Code Changes
*/
BatchDetails.recipient_code,
l_msds_date,
1, /* Dispatch method */
1, /* Cover letter text id */
l_blank_category,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_blank_attribute,
l_user_id,
l_current_date,
l_user_id,
l_current_date,
l_user_id,
l_rowid,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Dispatch_History_Insert_Error;
** Update the line status
*/
UPDATE gr_selection sd
SET sd.line_status = 8
WHERE sd.batch_no = BatchDetails.batch_no
AND sd.order_no = BatchDetails.order_no
AND sd.order_line_number = BatchDetails.order_line_number;
** Update the header status to Updated
*/
UPDATE gr_selection_header
SET status = 8
WHERE batch_no = p_batch_number;
ROLLBACK TO SAVEPOINT Update_Dispatch_History;
ROLLBACK TO SAVEPOINT Update_Dispatch_History;
ROLLBACK TO SAVEPOINT Update_Dispatch_History;
ROLLBACK TO SAVEPOINT Update_Dispatch_History;
WHEN Dispatch_History_Insert_Error THEN
ROLLBACK TO SAVEPOINT Update_Dispatch_History;
ROLLBACK TO SAVEPOINT Update_Dispatch_History;
END Update_Dispatch_History;
** recipient and item selections to print cover letters and documents.
**
** p_items_to_print indicates if master items or inventriy items or both
** are to be printed.
*/
PROCEDURE Print_Recipients
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_recipient_from IN VARCHAR2,
p_recipient_to IN VARCHAR2,
p_item_code_from IN VARCHAR2,
p_item_code_to IN VARCHAR2,
p_changed_after IN VARCHAR2,
p_printer IN VARCHAR2,
p_user_print_style IN VARCHAR2,
p_number_of_copies IN NUMBER,
p_items_to_print IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/*
** Alpha Variables
*/
L_CODE_BLOCK VARCHAR2(2000);
SELECT ri.recipient_code,
ri.recipient_name,
ri.document_code,
ri.document_print_frequency,
ri.disclosure_code,
ri.region_code,
ri.territory_code,
ri.shipping_address,
ri.invoice_address,
ri.additional_address_flag,
cp.language,
cp.document_code country_document
FROM gr_recipient_info ri,
gr_country_profiles cp
WHERE ri.recipient_code >= p_recipient_from
AND ri.recipient_code <= p_recipient_to
AND ri.territory_code = cp.territory_code;
SELECT rl.language
FROM gr_region_languages rl
WHERE rl.region_code = LocalRecipient.region_code;
SELECT ig1.item_code
FROM gr_item_general ig1
WHERE ig1.item_code >= p_item_code_from
AND ig1.item_code <= p_item_code_to
AND (ig1.ingredient_flag = 'N'
OR (ig1.ingredient_flag = 'Y'
AND p_item_code_from = p_item_code_to));
SELECT ids.item_code,
ids.last_doc_update_date
FROM gr_item_doc_statuses ids
WHERE ids.item_code = LocalItemRec.item_code
AND ids.document_code = g_default_document;
SELECT cu.addr_id
FROM op_cust_mst cu
WHERE cu.cust_no = LocalRecipient.recipient_code;
SELECT gib.item_code,
gib.item_no
FROM gr_generic_items_b gib
WHERE gib.item_code = LocalItem.item_code;
SELECT gib.item_code,
gib.item_no
FROM gr_generic_items_b gib
WHERE gib.item_no >= p_item_code_from
AND gib.item_no <= p_item_code_to;
SELECT gr_work_build_docs_s.nextval INTO g_session_id
FROM dual;
LocalItem.last_doc_update_date <= l_changed_after THEN
l_code_block := 'Do not print';
Print_Document_Selection
(g_default_document,
LocalItem.item_code,
LocalRecipient.language,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
LocalRecipient.language,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalItem.item_code,
LocalRecipient.language,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
LocalRecipient.language,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalItem.item_code,
l_language_code,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
LocalRecipient.language,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalItem.item_code,
l_language_code,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
LocalRecipient.language,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
l_language_code,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
l_language_code,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
l_language_code,
LocalRecipient.disclosure_code,
l_return_status);
Print_Document_Selection
(g_default_document,
LocalInventList.item_no,
l_language_code,
LocalRecipient.disclosure_code,
l_return_status);
PROCEDURE Insert_Selection_Row
(p_message_code IN VARCHAR2,
p_token_name IN VARCHAR2,
p_token_value IN VARCHAR2,
p_order_id IN NUMBER,
p_order_line_number IN NUMBER,
p_document_code IN VARCHAR2,
p_print_flag IN VARCHAR2,
p_cust_no IN VARCHAR2,
p_shipment_no IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
/*
**Alpha Variables
*/
L_SELECTION_MESSAGE GR_SELECTION.message%TYPE;
L_LINE_STATUS GR_SELECTION.line_status%TYPE;
L_USER_ID GR_SELECTION.user_id%TYPE;
SELECT COUNT(*)
FROM gr_selection sd
WHERE sd.batch_no = g_batch_number
AND sd.order_no = p_order_id
AND sd.order_line_number = p_order_line_number;
l_selection_message := FND_MESSAGE.Get;
** is selected, otherwise the line status is not
** selected.
*/
IF p_print_flag = 'Y' THEN
l_line_status := 2;
INSERT INTO gr_selection
(batch_no,
order_no,
order_line_number,
line_status,
document_code,
print_flag,
user_id,
item_code,
recipient_code,
shipment_no,
message,
user_override,
date_msds_sent)
VALUES
(g_batch_number,
p_order_id,
p_order_line_number,
l_line_status,
p_document_code,
p_print_flag,
l_user_id,
g_item_code,
p_cust_no,
p_shipment_no,
l_selection_message,
'N',
'');
FND_FILE.PUT(FND_FILE.LOG,' Selection Insert Error:'||sqlerrm);
END Insert_Selection_Row;
PROCEDURE Check_Selected_Line
(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/*
** Alphanumeric variables
*/
L_CODE_BLOCK VARCHAR2(2000);
SELECTION_INSERT_ERROR EXCEPTION;
** Check order and line are not already selected on an
** open batch (status 8 is completed, 9 is cancelled)
*/
CURSOR c_check_selections
IS
SELECT sd.batch_no
FROM gr_selection_header sh,
gr_selection sd
WHERE ( (sd.order_no = g_order_number /* Same order and line number */
AND sd.order_line_number = g_order_line)
OR ( (NOT EXISTS (SELECT 1
FROM gr_recipient_info
WHERE recipient_code = g_recipient_code)
-- AND document_print_frequency = 'A')
OR sh.batch_no = g_batch_number
)
AND (sd.item_code = g_item_code /* Item and recipient selected */
AND sd.recipient_code = g_recipient_code)
)
)
AND sd.batch_no = sh.batch_no
AND sh.status <> 8
AND sh.status <> 9
ORDER BY sd.BATCH_NO DESC;
LocalSelection c_check_selections%ROWTYPE;
SELECT ids.rebuild_item_doc_flag,
ids.last_doc_update_date,
dh.date_msds_sent
FROM gr_dispatch_histories dh,
gr_item_doc_statuses ids
WHERE ids.item_code = g_item_code
/* Fix for B1255401 */
AND dh.dispatch_method_code <> 99
AND ids.document_code = GlobalRecipient.document_code
AND dh.date_msds_sent = (SELECT MAX(dh1.date_msds_sent)
FROM gr_dispatch_histories dh1
WHERE dh1.item_code = g_item_code
AND dh1.document_code = GlobalRecipient.document_code
AND dh1.recipient_code = g_recipient_code);
OPEN c_check_selections;
FETCH c_check_selections INTO LocalSelection;
IF c_check_selections%FOUND THEN
CLOSE c_check_selections;
l_code_block := ' Order line already selected in print job ';
Insert_Selection_Row
('GR_ORDER_ALREADY_SELECTED',
'CODE',
LocalSelection.batch_no,
g_order_number,
g_order_line,
g_default_document,
'N',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
CLOSE c_check_selections;
Insert_Selection_Row
('GR_NO_SAFETY_INFO',
'ITEM',
g_item_code,
g_order_number,
g_order_line,
g_default_document,
'N',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
** the dispatch history update.
*/
OPEN g_get_recipient;
Insert_Selection_Row
('GR_NO_RECIPIENT_DO_PRINT',
'',
'',
g_order_number,
g_order_line,
g_default_document,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_NO_DEFAULT_DOCUMENT',
'CODE',
g_default_country,
g_order_number,
g_order_Line,
'',
'N',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_ALWAYS_PRINT',
'',
'',
g_order_number,
g_order_Line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_NEVER_PRINT',
'',
'',
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'N',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_FIRST_DISPATCH',
'',
'',
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
ELSIF LocalDispatchRcd.last_doc_update_date > LocalDispatchRcd.date_msds_sent THEN
--utl_file.put_line(pg_fp, 'doc_update > ');
Insert_Selection_Row
('GR_DOCUMENT_CHANGED',
'DATE',
TO_CHAR(LocalDispatchRcd.date_msds_sent,'DD-MON-YYYY'),
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_DISPATCH_QUARTERLY',
'',
'',
g_order_number,
g_order_Line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
FND_MESSAGE.SET_NAME('GR', 'GR_ORDER_ALREADY_SELECTED');
Insert_Selection_Row
('GR_DISPATCH_QUARTERLY',
'',
'',
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
FND_MESSAGE.SET_NAME('GR', 'GR_ORDER_ALREADY_SELECTED');
Insert_Selection_Row
('GR_DISPATCH_QUARTERLY',
'',
'',
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
FND_MESSAGE.SET_NAME('GR', 'GR_ORDER_ALREADY_SELECTED');
Insert_Selection_Row
('GR_DISPATCH_AS_REQUIRED',
'',
'',
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'Y',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
Insert_Selection_Row
('GR_NO_DOCUMENT_REQUIRED',
'',
'',
g_order_number,
g_order_line,
GlobalRecipient.document_code,
'N',
g_recipient_code,
g_shipment_number,
l_return_status);
RAISE Selection_Insert_Error;
END IF; /* c_check_selections */
WHEN Selection_Insert_Error THEN
Handle_Error_Messages
('GR_NO_RECORD_INSERTED',
'CODE',
g_order_number || ' ' || g_order_line,
x_msg_count,
x_msg_data,
x_return_status);
END Check_Selected_Line;
PROCEDURE Print_Document_Selection
(p_document_code IN VARCHAR2,
p_item_code IN VARCHAR2,
p_language_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
/*
** Alphanumeric Variables
*/
L_CODE_BLOCK VARCHAR2(2000);
SELECT dd.print_font,
dd.print_size,
dd.text_line
FROM gr_document_details dd
WHERE dd.document_text_id = l_document_text_id
ORDER BY dd.text_line_number;
SELECT om.orgn_name,
oa.addr1,
oa.addr2,
oa.addr3,
oa.addr4,
oa.postal_code,
oa.state_code,
oa.country_code,
oc.daytime_contact_name,
oc.daytime_telephone,
oc.daytime_extension,
oc.daytime_area_code,
oc.evening_contact_name,
oc.evening_telephone,
oc.evening_extension,
oc.evening_area_code
FROM gr_organization_contacts oc,
sy_addr_mst_v oa,
sy_orgn_mst om
WHERE om.orgn_code = g_default_orgn
AND om.addr_id = oa.addr_id
AND oc.orgn_code = om.orgn_code;
SELECT hou.name,
oa.addr1,
oa.addr2,
oa.addr3,
oa.addr4,
oa.postal_code,
oa.state_code,
oa.country_code,
oc.daytime_contact_name,
oc.daytime_telephone,
oc.daytime_extension,
oc.daytime_area_code,
oc.evening_contact_name,
oc.evening_telephone,
oc.evening_extension,
oc.evening_area_code
FROM gr_organization_contacts oc,
sy_addr_mst_v oa,
hr_operating_units hou,
gl_plcy_mst gl,
sy_orgn_mst om
WHERE om.orgn_code = g_default_orgn
AND oc.orgn_code = om.orgn_code
AND om.addr_id = oa.addr_id
AND om.co_code = gl.co_code
AND gl.org_id = hou.organization_id;
SELECT lab.data_position_indicator,
lat.label_description
FROM gr_labels_tl lat,
gr_labels_b lab
WHERE lab.label_code = l_label_code
AND lat.label_code = lab.label_code
AND lat.language = p_language_code;
SELECT geog_desc
FROM sy_geog_mst
WHERE geog_type = 1
AND geog_code = V_country_code;
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
l_text_line,
'M',
l_return_status);
Insert_Work_Row
(p_item_code,
LocalDocRecord.print_font,
LocalDocRecord.print_size,
LocalDocRecord.text_line,
'M',
l_return_status);
END Print_Document_Selection;
PROCEDURE Insert_Work_Row
(p_item_code IN VARCHAR2,
p_print_font IN VARCHAR2,
p_print_size IN NUMBER,
p_text_line IN VARCHAR2,
p_line_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
IF g_line_number IS NULL THEN
g_line_number := 0;
INSERT INTO gr_work_worksheets
(session_id,
text_line_number,
item_code,
print_font,
text_line,
line_type,
print_size)
VALUES
(g_session_id,
g_line_number,
p_item_code,
p_print_font,
p_text_line,
p_line_type,
p_print_size);
FND_FILE.PUT(FND_FILE.LOG, TO_CHAR(g_session_id) || ' Error inserting work record');
END Insert_Work_Row;
FND_FILE.PUT(FND_FILE.LOG, ' Rebuild Flag is selected ');
FND_FILE.PUT(FND_FILE.LOG, ' Rebuild Flag is not selected ');
SELECT gr_work_build_docs_s.nextval INTO g_session_id
FROM dual;