[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PARENT_ID, REJECT_LOOKUP_CODE
FROM AP_INTERFACE_REJECTIONS
WHERE PARENT_ID = p_invoice_id
AND PARENT_TABLE = 'AP_INVOICES_INTERFACE';
SELECT PARENT_ID, REJECT_LOOKUP_CODE
FROM AP_INTERFACE_REJECTIONS
WHERE PARENT_ID in (SELECT INVOICE_LINE_ID FROM AP_INVOICE_LINES_INTERFACE WHERE INVOICE_ID = p_invoice_id)
AND PARENT_TABLE = 'AP_INVOICE_LINES_INTERFACE';
SELECT 'x'
INTO l_count_failed_rows
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM ap_interface_rejections air
WHERE parent_table = 'AP_INVOICES_INTERFACE'
AND parent_id = p_invoice_id
UNION ALL
SELECT 'x'
FROM ap_interface_rejections air
WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND parent_id in (select invoice_line_id from ap_invoice_lines_interface aili
where aili.invoice_id = p_invoice_id)
);
SELECT party_id, party_site_id, party_type
INTO l_party_id,l_party_site_id,l_party_type
FROM ecx_tp_headers
WHERE tp_header_id = p_tp_header_id;
SELECT org_id
INTO l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = l_party_id and vendor_site_id = l_party_site_id;
SELECT M4R_3C4_DOCUMENT_NUM_S.NEXTVAL
INTO l_doc_id
FROM dual;
SELECT party_id
INTO l_party_id
FROM hz_party_sites
WHERE party_site_id = l_party_site_id;
SELECT customer_trx_id, org_id, trx_number,trx_date
INTO l_customer_trx_id, l_organization_id, l_trx_number,l_inv_date
FROM CLN_3C3_INVOICE_V
WHERE document_transfer_id = l_doc_transfer_id AND ROWNUM < 2;
SELECT name --This is equivalent to TERM_NAME column of AR_XML_PAYMENT_TERMS_V
INTO x_pay_t_code
FROM ra_terms t, ar_payment_schedules_all ps
WHERE t.term_id = ps.term_id
AND ps.customer_trx_id = p_customer_trx_id
AND rownum < 2;
SELECT sum(tax_amount)
INTO x_tax_amount
FROM AR_XML_INVOICE_TAX_V
WHERE link_to_cust_trx_line_id = p_customer_trx_line_id;
SELECT trx_date
INTO l_trx_date
FROM CLN_3C3_INVOICE_V
WHERE document_transfer_id = p_doc_trnsfr_id and rownum < 2;
SELECT ol.orig_sys_document_ref,ol.orig_sys_line_ref, ol.orig_sys_shipment_ref
INTO x_po_num, x_po_line_num,x_po_ship_num
FROM oe_order_headers oh, oe_order_lines ol
WHERE oh.org_id = ol.org_id
AND oh.header_id = ol.header_id
AND oh.org_id = trim(p_org_id)
AND oh.order_number = to_number(trim(p_so_num))
AND oh.version_number = nvl(to_number(trim(p_so_rev_num)),0)
AND ol.line_number = to_number(trim(p_so_lin_num))
AND rownum < 2;
PROCEDURE CLN_UPDATE_DOC_STATUS(p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
x_resultout IN OUT NOCOPY VARCHAR2) AS
l_status VARCHAR2(10);
cln_debug_pub.Add('Entering CLN_UPDATE_DOC_STATUS',2);
SELECT EXT_TYPE, EXT_SUBTYPE
INTO l_ext_trx_type,l_ext_trx_subtype
FROM ecx_ext_processes
WHERE transaction_id = (SELECT transaction_id
FROM ecx_transactions
WHERE transaction_type = l_transaction_type
AND transaction_subtype = l_transaction_subtype);
SELECT 'x'
INTO l_status
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM ecx_doclogs
WHERE document_number = l_doc_id
AND item_type = p_itemtype -- Changed to fix Bug #5031346
AND item_key = p_itemkey -- Changed to fix Bug #5031346
AND direction = 'OUT');
UPDATE ar_document_transfers
SET status = decode(l_status,'x','TRANSMITTED','FAILED')
WHERE document_transfer_id = l_doc_id;
cln_debug_pub.Add('ar_document_transfers row updated', 1);
cln_debug_pub.Add('Exiting CLN_UPDATE_DOC_STATUS',2);
cln_debug_pub.Add('Exception in CLN_UPDATE_DOC_STATUS with Error code' || l_error_code ||
'and Errror Message' || l_error_msg,6);
cln_debug_pub.Add('----------- ERROR:EXITING CLN_UPDATE_DOC_STATUS ------------', 2);
END CLN_UPDATE_DOC_STATUS;
PROCEDURE RAISE_UPDATE (p_document_id IN VARCHAR2,
p_int_cnt_num IN NUMBER,
p_org_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_msg_data VARCHAR2(100);
l_update_cln_parameter_list wf_parameter_list_t;
cln_debug_pub.Add('Entering RAISE_UPDATE_EVENT procedure with parameters----', 1);
SELECT sysdate
INTO l_date
FROM dual;
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update called with the following parameters',1);
l_update_cln_parameter_list := wf_parameter_list_t();
WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'SUCCESS', l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', 'CLN_3C3_INVOICE_RCVD', l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('DOCUMENT_NO',p_document_id,l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('DOCUMENT_CREATION_DATE',l_canonical_date,l_update_cln_parameter_list);--sysdate
WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_int_cnt_num,l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('ORG_ID',p_org_id,l_update_cln_parameter_list);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update',
p_event_key => p_document_id ||'.'|| p_int_cnt_num,
p_parameters => l_update_cln_parameter_list);
cln_debug_pub.Add('----------- EXITING RAISE_UPDATE_EVENT ------------', 2);
cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 6);
END RAISE_UPDATE;
SELECT sysdate
INTO l_date
FROM dual;
cln_debug_pub.Add('------Calling RAISE_UPDATE_EVENT with ERROR status------',1);
cln_debug_pub.Add('---- SETTING EVENT PARAMETERS FOR UPDATE COLLABORATION ----', 1);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
SELECT PARTY_ID, PARTY_SITE_ID,PARTY_TYPE
INTO l_party_id, l_party_site_id, l_party_type
FROM ECX_TP_HEADERS
WHERE TP_HEADER_ID = l_sender_header_id ;
l_update_coll_msg VARCHAR2(2000);
l_update_coll_msg := 'CLN_CH_XML_CONSUMED_SUCCESS';
SELECT status_code,completion_text,phase_code
INTO l_status_code, l_concurrent_msg,l_phase_code
FROM fnd_concurrent_requests
WHERE request_id = l_request_id;
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
SELECT REJECT_REASON_STRING
INTO l_error_reject_string
FROM CLN_AP_INVOICE_REJECTION_ARCH
WHERE invoice_id = l_invoice_id;
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'COLL_UPDATE_MSG', l_return_desc_tp);
PROCEDURE UPDATE_INV_HEADER_INTERFACE( p_invoice_id IN NUMBER,
p_proprietary_doc_Identifier IN VARCHAR2,
p_inv_curr_code IN VARCHAR2,
p_inv_amount IN NUMBER,
p_inv_date IN VARCHAR2,
p_inv_type_lookup_code IN VARCHAR2,
x_invoice_num IN OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_data IN OUT NOCOPY VARCHAR2 ) IS
l_msg_data VARCHAR2(100);
cln_debug_pub.Add('---------------- ENTERING CLN_NTFYINVC_PKG.UPDATE_INV_HEADER_INTERFACE -----------------', 2);
UPDATE ap_invoices_interface
SET INVOICE_CURRENCY_CODE = p_inv_curr_code,
INVOICE_AMOUNT = P_inv_amount,
INVOICE_DATE = l_db_inv_date,
INVOICE_NUM = p_proprietary_doc_identifier,
vendor_email_address = '3C4',
source = 'XML GATEWAY'
--INVOICE_TYPE_LOOKUP_CODE = p_inv_type_lookup_code
WHERE invoice_id = p_invoice_id;
cln_debug_pub.Add('Update ap_invoices_interface, is successful' , 1);
cln_debug_pub.Add('---------------- EXITING CLN_NTFYINVC_PKG.UPDATE_INV_HEADER_INTERFACE -----------------', 2);
cln_debug_pub.Add('---------------- EXITING CLN_NTFYINVC_PKG.UPDATE_INV_HEADER_INTERFACE with ERROR-----------------', 2);
END UPDATE_INV_HEADER_INTERFACE;
SELECT PARENT_ID, REJECT_LOOKUP_CODE
FROM AP_INTERFACE_REJECTIONS
WHERE PARENT_ID = p_invoice_id
AND PARENT_TABLE = 'AP_INVOICES_INTERFACE';
SELECT PARENT_ID, REJECT_LOOKUP_CODE
FROM AP_INTERFACE_REJECTIONS
WHERE PARENT_ID in (SELECT INVOICE_LINE_ID FROM AP_INVOICE_LINES_INTERFACE WHERE INVOICE_ID = p_invoice_id)
AND PARENT_TABLE = 'AP_INVOICE_LINES_INTERFACE';
SELECT invoice_num, invoice_date, invoice_amount, po_number
INTO l_invoice_number, l_invoice_date, l_invoice_amount, l_po_number
FROM ap_invoices_interface
WHERE invoice_id = p_invoice_id;
cln_debug_pub.Add('Before inserting data into CLN_AP_INVOICE_REJECTION_ARCH', 1);
INSERT INTO CLN_AP_INVOICE_REJECTION_ARCH(
invoice_id,
xmlg_internal_control_number,
invoice_number,
reference_id,
po_number,
invoice_amount,
invoice_date,
reject_reason_string,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES(
p_invoice_id,
p_group_id,
l_invoice_number,
p_external_doc_ref,
l_po_number,
l_invoice_amount,
l_invoice_date,
l_error_reject_string,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
cln_debug_pub.Add('Before inserting data', 1);
SELECT invoice_num, po_number, invoice_amount, invoice_date
INTO x_invoice_num, x_po_num, x_invoice_amt, x_invoice_date
FROM ap_invoices_interface
WHERE invoice_id = p_invoice_id;
SELECT invoice_number, po_number, invoice_amount, invoice_date
INTO x_invoice_num, x_po_num, x_invoice_amt, x_invoice_date
FROM CLN_AP_INVOICE_REJECTION_ARCH
WHERE invoice_id = p_invoice_id;