[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
RAISE_UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_return_msg,
p_ref_id => p_app_ref_id,
p_doc_no => NULL,
p_part_doc_no => l_ctg_name,
p_msg_text => x_msg_data,
p_status_code => 1,
p_int_ctl_num => p_int_cont_num);
x_operation := 'UPDATE';
SELECT po_header_id
INTO x_po_hdr_id
FROM PO_HEADERS_ALL
WHERE VENDOR_ORDER_NUM = l_ctg_name
AND vendor_id = p_tp_id -- Bug #5006663
AND NVL(CANCEL_FLAG, 'N') = 'N'
AND NVL(CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED');
x_operation := 'INSERT';
RAISE_UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_return_msg,
p_ref_id => p_app_ref_id,
p_doc_no => NULL,
p_part_doc_no => l_ctg_name,
p_msg_text => l_msg_text,
p_status_code => 0,
p_int_ctl_num => p_int_cont_num);
cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
SELECT org_id
INTO l_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = p_tp_site_id;
SAVEPOINT PO_UPDATE_TXN;
IF x_operation = 'INSERT' THEN
-- Create a new BPO
-- While creating a new po, vendor document num
-- needs to be filled with catalog name
-- Need to insert a row with action as ORIGINAL
/* Bug : 3630042. In case of multiple messages due to
grouping factor, we never know the action is create.
We will populate it in the workflow using the procedure
SET_ACTION_CREATE_OR_UPDATE
*/
INSERT INTO po_headers_interface(interface_header_id,
batch_id,
--action,
document_type_code,
vendor_id,
vendor_site_id,
effective_date,
expiration_date,
vendor_doc_num,
org_id,
amount_agreed
)
values(p_itf_hdr_id,
p_itf_hdr_id,
--'ORIGINAL',
'BLANKET',
p_tp_id,
p_tp_site_id,
p_eff_date,
p_exp_date,
l_ctg_name,
l_org_id,
0);
INSERT INTO po_headers_interface(interface_header_id,
batch_id,
action,
document_type_code,
vendor_id,
vendor_site_id,
effective_date,
expiration_date,
vendor_doc_num,
org_id,
amount_agreed
)
values(p_itf_hdr_id,
p_itf_hdr_id,
'UPDATE',
'BLANKET',
p_tp_id,
p_tp_site_id,
p_eff_date,
p_exp_date,
l_ctg_name,
l_org_id,
0);
ROLLBACK TO PO_UPDATE_TXN;
cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
ROLLBACK TO PO_UPDATE_TXN;
cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
SELECT UOM_CODE
INTO l_uom_code
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UNIT_OF_MEASURE = p_uom;
SELECT line_num
INTO l_line_num
FROM PO_LINES_INTERFACE
WHERE interface_header_id = p_itf_hdr_id
AND nvl(ITEM, '-1') = nvl(p_item, '-1')
AND nvl(UOM_CODE, '-1') = nvl(l_uom_code, '-1')
AND ROWNUM < 2; -- All the rows returned by this query have either the same line_num or no rows
RAISE_UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_return_msg,
p_ref_id => p_app_ref_id,
p_doc_no => NULL,
p_part_doc_no => NULL,
p_msg_text => x_msg_data,
p_status_code => 1,
p_int_ctl_num => p_int_cont_num);
SELECT cln_generic_s.nextval
INTO l_line_num
FROM DUAL;
INSERT INTO po_lines_interface(interface_header_id,
interface_line_id,
item,
ITEM_REVISION,
CATEGORY,
ITEM_DESCRIPTION,
MIN_ORDER_QUANTITY,
UOM_CODE,
line_num,
VENDOR_PRODUCT_NUM,
PRICE_BREAK_LOOKUP_CODE,
LINE_ATTRIBUTE1,
LINE_ATTRIBUTE2,
LINE_ATTRIBUTE3,
LINE_ATTRIBUTE4,
LINE_ATTRIBUTE5,
LINE_ATTRIBUTE6,
LINE_ATTRIBUTE7,
LINE_ATTRIBUTE8,
LINE_ATTRIBUTE9,
LINE_ATTRIBUTE10,
LINE_ATTRIBUTE11,
LINE_ATTRIBUTE12,
LINE_ATTRIBUTE13,
LINE_ATTRIBUTE14,
LINE_ATTRIBUTE15)
values(p_itf_hdr_id,
p_itf_lin_id,
p_item,
p_item_rev,
p_category,
p_item_desc,
p_item_min_ord_quan,
l_uom_code,
l_line_num,
p_vdr_part_num,
'NON CUMULATIVE',
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);
cln_debug_pub.Add('Inserted a row into for the line', 1);
IF p_operation = 'INSERT' THEN
-- Create a new BPO Line
SELECT cln_generic_s.nextval
INTO l_line_num
FROM DUAL;
INSERT INTO po_lines_interface(interface_header_id,
interface_line_id,
item,
ITEM_REVISION,
CATEGORY,
ITEM_DESCRIPTION,
MIN_ORDER_QUANTITY,
-- UOM_CODE, How to get uom code from uom ?
line_num,
VENDOR_PRODUCT_NUM,
LINE_ATTRIBUTE1,
LINE_ATTRIBUTE2,
LINE_ATTRIBUTE3,
LINE_ATTRIBUTE4,
LINE_ATTRIBUTE5,
LINE_ATTRIBUTE6,
LINE_ATTRIBUTE7,
LINE_ATTRIBUTE8,
LINE_ATTRIBUTE9,
LINE_ATTRIBUTE10,
LINE_ATTRIBUTE11,
LINE_ATTRIBUTE12,
LINE_ATTRIBUTE13,
LINE_ATTRIBUTE14,
LINE_ATTRIBUTE15)
values(p_itf_hdr_id,
p_itf_lin_id,
p_item,
p_item_rev,
p_category,
p_item_desc,
p_item_min_ord_quan,
l_line_num,
p_vdr_part_num,
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);
SELECT line_num
INTO x_line_num
FROM PO_LINES_ALL POL ,
MTL_SYSTEM_ITEMS_KFV MIS,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
-- MTL_CATEGORIES_KFV MCT
WHERE POL.ITEM_ID = MIS.INVENTORY_ITEM_ID (+)
AND NVL(MIS.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)
= FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = POL.ORG_ID
AND POL.PO_HEADER_ID = p_hdr_id
-- (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE VENDOR_ORDER_NUM = l_ctg_name)
AND upper(MIS.CONCATENATED_SEGMENTS) = upper(p_item)
AND upper(POL.UNIT_MEAS_LOOKUP_CODE) = upper(p_uom);
SELECT cln_generic_s.nextval
INTO l_line_num
FROM DUAL;
INSERT INTO po_lines_interface(interface_header_id,
interface_line_id,
item,
line_num)
values(p_itf_hdr_id,
p_itf_lin_id,
p_item,
l_line_num);
ROLLBACK TO PO_UPDATE_TXN;
cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
ROLLBACK TO PO_UPDATE_TXN;
cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
x_bpo_cur_updated IN OUT NOCOPY VARCHAR2,
p_app_ref_id IN VARCHAR2,
p_int_cont_num IN VARCHAR2,
p_ctg_name IN VARCHAR2,
p_itf_hdr_id IN NUMBER,
p_itf_lin_id IN NUMBER,
p_line_num IN NUMBER,
p_item IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_eff_date IN DATE,
p_exp_date IN DATE,
p_quantity IN NUMBER,
p_price IN NUMBER,
p_price_uom IN VARCHAR2,
p_price_currency IN VARCHAR2)
IS
l_return_status VARCHAR2(1000);
cln_debug_pub.Add('x_bpo_cur_updated:' || x_bpo_cur_updated, 1);
IF x_bpo_cur_updated = 'NO' AND p_price_currency IS NOT NULL THEN
UPDATE po_headers_interface
SET currency_code = p_price_currency
WHERE interface_header_id = p_itf_hdr_id;
x_bpo_cur_updated := 'YES';
SELECT count(*)
INTO l_count
FROM po_lines_interface
WHERE interface_header_id = p_itf_hdr_id
AND interface_line_id = interface_line_id
AND line_num = p_line_num;
SELECT unit_price
INTO l_line_price
FROM po_lines_interface
WHERE interface_header_id = p_itf_hdr_id
AND interface_line_id = interface_line_id
AND line_num = p_line_num;
UPDATE po_lines_interface
SET unit_price = p_price
WHERE interface_header_id = p_itf_hdr_id
AND interface_line_id = interface_line_id
AND line_num = p_line_num;
SELECT cln_generic_s.nextval
INTO l_line_ship_num
FROM DUAL;
INSERT INTO po_lines_interface(interface_header_id,
interface_line_id,
item,
ITEM_REVISION,
line_num,
shipment_num,
unit_price,
effective_date,
expiration_date,
quantity,
PRICE_BREAK_LOOKUP_CODE)
values(p_itf_hdr_id,
p_itf_lin_id,
p_item,
p_item_rev,
p_line_num,
l_line_ship_num,
p_price,
p_eff_date,
p_exp_date,
p_quantity,
'NON CUMULATIVE');
ROLLBACK TO PO_UPDATE_TXN;
cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
ROLLBACK TO PO_UPDATE_TXN;
cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
l_create_upd_items := FND_PROFILE.VALUE('CLN_2A1_PO_CREATE_UPDATE_ITEMS');
SELECT org_id
INTO l_org_id
FROM ecx_tp_headers eth, po_vendor_sites_all povs
WHERE eth.tp_header_id = l_tp_header_id
and povs.vendor_site_id = eth.party_site_id;
UPDATE po_headers_interface
SET vendor_doc_num = p_catalog_name
WHERE vendor_doc_num = p_catalog_name
AND vendor_id = p_vendor_id
AND ACTION is NULL;
SELECT po_header_id
INTO l_po_header_id
FROM po_headers_all
WHERE vendor_order_num = p_catalog_name
AND vendor_id = p_vendor_id;
UPDATE po_headers_interface
SET action = 'UPDATE'
WHERE batch_id = p_batch_id;
SELECT count('x')
INTO l_interface_hdr_rec_count
FROM po_headers_interface
WHERE vendor_doc_num = p_catalog_name
AND vendor_id = p_vendor_id
AND ACTION = 'ORIGINAL'
AND nvl(process_code,'~') NOT IN ('ACCEPTED', 'REJECTED');
SELECT po_header_id
INTO l_po_header_id
FROM po_headers_all
WHERE vendor_order_num = p_catalog_name
AND vendor_id = p_vendor_id;
UPDATE po_headers_interface
SET action = 'UPDATE'
WHERE batch_id = p_batch_id;
UPDATE po_headers_interface
SET action = 'ORIGINAL'
WHERE batch_id = p_batch_id;
PROCEDURE SET_ACTION_CREATE_OR_UPDATE(
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
x_resultout IN OUT NOCOPY VARCHAR2)
IS
l_error_code NUMBER;
cln_debug_pub.Add('ENTERING SET_ACTION_CREATE_OR_UPDATE API', 2);
IF (l_operation = 'UPDATE') THEN
--If the operation is already update, then need not do anything
IF (l_Debug_Level <= 1) THEN
cln_debug_pub.Add('Operation is update. Nothing to do.', 1);
cln_debug_pub.Add('EXITING SET_ACTION_CREATE_OR_UPDATE API', 2);
cln_debug_pub.Add('EXITING SET_ACTION_CREATE_OR_UPDATE API', 2);
cln_debug_pub.Add('EXITING SET_ACTION_CREATE_OR_UPDATE API', 2);
END SET_ACTION_CREATE_OR_UPDATE;
SELECT INTERFACE_LINE_ID, BATCH_ID,
TABLE_NAME, COLUMN_NAME, ERROR_MESSAGE, ERROR_MESSAGE_NAME
FROM PO_INTERFACE_ERRORS
WHERE INTERFACE_HEADER_ID = p_int_hdr_id;
select count(*)
into l_count
from po_interface_errors
where interface_header_id = l_int_hdr_id;
SELECT segment1
INTO l_bpo_number
FROM PO_HEADERS_ALL
WHERE VENDOR_ORDER_NUM = l_catalog_name
AND vendor_id = l_vendor_id -- Bug #5006663
AND NVL(CANCEL_FLAG, 'N') = 'N'
AND NVL(CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED');
RAISE_UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_return_msg,
p_ref_id => l_app_ref_id,
p_doc_no => l_bpo_number,
p_part_doc_no => NULL,
p_msg_text => l_msg_data,
p_status_code => 0,
p_int_ctl_num => l_int_ctl_num);
RAISE_UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_return_msg,
p_ref_id => l_app_ref_id,
p_doc_no => NULL,
p_part_doc_no => NULL,
p_msg_text => l_msg_data,
p_status_code => 1,
p_int_ctl_num => l_int_ctl_num);
SELECT PARTY_ID, PARTY_SITE_ID
INTO l_tp_id, l_tp_site_id
FROM ECX_TP_HEADERS
WHERE TP_HEADER_ID = p_tp_header_id;
PROCEDURE RAISE_UPDATE_COLLABORATION(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_ref_id IN VARCHAR2,
p_doc_no IN VARCHAR2,
p_part_doc_no IN VARCHAR2,
p_msg_text IN VARCHAR2,
p_status_code IN NUMBER,
p_int_ctl_num IN VARCHAR2)
IS
l_cln_ch_parameters wf_parameter_list_t;
cln_debug_pub.Add('ENTERING RAISE_UPDATE_COLLABORATION', 2);
FND_MESSAGE.SET_TOKEN('EVENT','Update');
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',
l_event_key, NULL, l_cln_ch_parameters, NULL);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update raised', 1);
cln_debug_pub.Add('EXITING RAISE_UPDATE_COLLABORATION', 2);
cln_debug_pub.Add('EXITING RAISE_UPDATE_COLLABORATION', 2);
END RAISE_UPDATE_COLLABORATION;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;