[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Delete_Failed_Rec(p_dctn_req_id NUMBER);
Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,
p_status IN VARCHAR2);
select 'Y' from dual where not exists(select 1 from pa_deductions_all
where deduction_req_id=ded_id);
l_dctn_hdr.delete;
log_message ('Before inserting into header', g_api_name);
INSERT INTO PA_DEDUCTIONS_ALL(
deduction_req_id
,project_id
,vendor_id
,vendor_site_id
,change_doc_num
,change_doc_type
,ci_id
,po_number
,po_header_id
,deduction_req_num
,debit_memo_num
,currency_code
,conversion_ratetype
,conversion_ratedate
,conversion_rate
,total_amount
,total_pfc_amount
,deduction_req_date
,debit_memo_date
,description
,status
,document_type
,org_id
,creation_date
,created_by )
SELECT
p_dctn_hdr(i).deduction_req_id
,p_dctn_hdr(i).project_id
,p_dctn_hdr(i).vendor_id
,p_dctn_hdr(i).vendor_site_id
,p_dctn_hdr(i).change_doc_num
,p_dctn_hdr(i).change_doc_type
,p_dctn_hdr(i).ci_id
,p_dctn_hdr(i).po_number
,p_dctn_hdr(i).po_header_id
,p_dctn_hdr(i).deduction_req_num
,p_dctn_hdr(i).debit_memo_num
,p_dctn_hdr(i).currency_code
,p_dctn_hdr(i).conversion_ratetype
,p_dctn_hdr(i).conversion_ratedate
,p_dctn_hdr(i).conversion_rate
,0
,0
,p_dctn_hdr(i).deduction_req_date
,p_dctn_hdr(i).debit_memo_date
,p_dctn_hdr(i).description
,p_dctn_hdr(i).status
,DECODE(p_dctn_hdr(i).ci_id, NULL,'M','C')
,p_dctn_hdr(i).org_id
,SYSDATE
,g_user_id FROM DUAL;
log_message ('Before inserting into deduction transaction ', g_api_name);
INSERT INTO PA_DEDUCTION_TRANSACTIONS_ALL (
deduction_req_id
,deduction_req_tran_id
,project_id
,task_id
,expenditure_type
,expenditure_org_id
,quantity
,override_quantity
,expenditure_item_id
,projfunc_currency_code
,orig_projfunc_amount
,override_projfunc_amount
,conversion_ratetype
,conversion_ratedate
,conversion_rate
,amount
,expenditure_item_date
,gl_date
,creation_date
,created_by
,description
)
SELECT
p_dctn_dtl(i).deduction_req_id
,PA_DEDUCTION_TXNS_S.nextval
,p_dctn_dtl(i).project_id
,p_dctn_dtl(i).task_id
,p_dctn_dtl(i).expenditure_type
,p_dctn_dtl(i).expenditure_org_id
,p_dctn_dtl(i).quantity
,nvl(p_dctn_dtl(i).override_quantity,p_dctn_dtl(i).quantity)
,p_dctn_dtl(i).expenditure_item_id
,p_dctn_dtl(i).projfunc_currency_code
,p_dctn_dtl(i).orig_projfunc_amount
,nvl(p_dctn_dtl(i).override_projfunc_amount,p_dctn_dtl(i).orig_projfunc_amount)
,p_dctn_dtl(i).conversion_ratetype
,p_dctn_dtl(i).conversion_ratedate
,p_dctn_dtl(i).conversion_rate
,p_dctn_dtl(i).amount
,p_dctn_dtl(i).expenditure_item_date
,p_dctn_dtl(i).expenditure_item_date
,SYSDATE
,g_user_id
,p_dctn_dtl(i).description FROM DUAL WHERE p_dctn_dtl(i).status IS NULL;
UPDATE PA_DEDUCTIONS_ALL SET total_amount = nvl((
SELECT SUM(amount) FROM
PA_DEDUCTION_TRANSACTIONS_ALL
WHERE deduction_req_id = l_dctn_tbl_hdrid(i) --l_dctn_hdrtbl(i).p_dctn_hdr_id
),0) ,
total_pfc_amount = nvl((
SELECT
SUM(nvl(override_projfunc_amount,orig_projfunc_amount)) FROM
PA_DEDUCTION_TRANSACTIONS_ALL
WHERE deduction_req_id = l_dctn_tbl_hdrid(i)
),0)
WHERE deduction_req_id = l_dctn_tbl_hdrid(i);--l_dctn_hdrtbl(i).p_dctn_hdr_id;
Procedure Update_Deduction_Hdr( p_dctn_hdr IN OUT NOCOPY g_dctn_hdrtbl
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_calling_mode IN VARCHAR2) Is
l_dctn_hdr g_dctn_hdrtbl;
g_api_name := 'Update_Deduction_Hdr';
log_message ('In Update deduction header procedure',g_api_name);
UPDATE pa_deductions_all
SET debit_memo_num = p_dctn_hdr(I).debit_memo_num
,debit_memo_date = p_dctn_hdr(I).debit_memo_date
,conversion_ratetype = p_dctn_hdr(I).conversion_ratetype
,conversion_ratedate = p_dctn_hdr(I).conversion_ratedate
,conversion_rate = p_dctn_hdr(I).conversion_rate
,total_amount = nvl(p_dctn_hdr(I).total_amount,nvl(total_amount,0))
,description = p_dctn_hdr(I).description
,status = 'WORKING'
,last_updated_by = g_user_id
,last_updation_date = SYSDATE
WHERE deduction_req_id = p_dctn_hdr(I).deduction_req_id;
Procedure Update_Deduction_Txn( p_dctn_dtl IN OUT NOCOPY g_dctn_txntbl
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_calling_mode IN VARCHAR2) Is
l_dctn_hdrtbl g_dctn_hdrtbl_amt;
g_api_name := 'Update_Deduction_Txn';
log_message ('In Update deduction transaction procedure',g_api_name);
UPDATE pa_deduction_transactions_all
SET task_id = p_dctn_dtl(i).task_id
,expenditure_type = p_dctn_dtl(i).expenditure_type
,expenditure_org_id = p_dctn_dtl(i).expenditure_org_id
,quantity = p_dctn_dtl(i).quantity
,override_quantity = nvl(p_dctn_dtl(i).override_quantity,p_dctn_dtl(i).quantity)
,projfunc_currency_code = p_dctn_dtl(i).projfunc_currency_code
,orig_projfunc_amount = p_dctn_dtl(i).orig_projfunc_amount
,override_projfunc_amount = nvl(p_dctn_dtl(i).override_projfunc_amount,
p_dctn_dtl(i).orig_projfunc_amount)
,conversion_ratetype = p_dctn_dtl(i).conversion_ratetype
,conversion_ratedate = p_dctn_dtl(i).conversion_ratedate
,conversion_rate = p_dctn_dtl(i).conversion_rate
,expenditure_item_date = p_dctn_dtl(i).expenditure_item_date
,amount = p_dctn_dtl(i).amount
,description = p_dctn_dtl(i).description
,last_updated_by = g_user_id
,last_updation_date = SYSDATE
WHERE deduction_req_tran_id = p_dctn_dtl(I).deduction_req_tran_id; END LOOP;
UPDATE PA_DEDUCTIONS_ALL SET total_amount = nvl((
SELECT SUM(amount) FROM
PA_DEDUCTION_TRANSACTIONS_ALL
WHERE deduction_req_id = l_dctn_tbl_hdrid(i) --l_dctn_hdrtbl(i).p_dctn_hdr_id
),0),
total_pfc_amount = nvl((
SELECT SUM(nvl(override_projfunc_amount,orig_projfunc_amount)) FROM
PA_DEDUCTION_TRANSACTIONS_ALL
WHERE deduction_req_id = l_dctn_tbl_hdrid(i)
),0)
WHERE deduction_req_id = l_dctn_tbl_hdrid(i);--l_dctn_hdrtbl(i).p_dctn_hdr_id;
Procedure Delete_Deduction_Hdr( p_dctn_hdrid g_dctn_hdrid
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
) Is
Begin
g_api_name := 'Delete_Deduction_Hdr';
log_message ('In Delete deduction header procedure',g_api_name);
DELETE PA_DEDUCTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) AND status NOT IN('PROCESSED','SUBMITTED');
DELETE PA_DEDUCTION_TRANSACTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) AND NOT EXISTS(
SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) );
Procedure Delete_Deduction_Txn( p_dctn_txnid g_dctn_txnid
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
) Is
Begin
g_api_name := 'Delete_Deduction_Txn';
log_message ('In Delete deduction transaction procedure',g_api_name);
UPDATE PA_DEDUCTIONS_ALL dctn_hdr SET total_amount = total_amount-nvl(
(SELECT amount FROM PA_DEDUCTION_TRANSACTIONS_ALL dctn_txn
WHERE deduction_req_tran_id = p_dctn_txnid(I)
AND deduction_req_id = dctn_hdr.deduction_req_id
AND EXISTS (
SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = dctn_txn.deduction_req_id
AND status NOT IN('PROCESSED','SUBMITTED','APPROVED'))),0);
DELETE PA_DEDUCTION_TRANSACTIONS_ALL dctn_txn WHERE deduction_req_tran_id = p_dctn_txnid(I)
AND EXISTS (
SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = dctn_txn.deduction_req_id
AND status NOT IN('PROCESSED','SUBMITTED','APPROVED'));
SELECT 'N'
FROM PA_DEDUCTIONS_ALL
WHERE deduction_req_num = p_dctn_req_num
AND deduction_req_id <> p_dctn_req_id;
SELECT 'N'
FROM PA_DEDUCTIONS_ALL
WHERE debit_memo_num = p_debit_memo_num
AND org_id = p_org_id
AND vendor_id = p_vendor_id
AND deduction_req_id <> nvl(p_dctn_req_id,-99);
SELECT 'N'
FROM DUAL WHERE EXISTS (
SELECT 1
FROM AP_INVOICES_ALL
WHERE invoice_num = p_debit_memo_num
AND vendor_id = p_vendor_id
AND org_id = p_org_id
UNION ALL
SELECT 1
FROM AP_INVOICES_INTERFACE
WHERE invoice_num = p_debit_memo_num
AND vendor_id = p_vendor_id
AND org_id = p_org_id
AND nvl(status, 'REJECTED') <> 'REJECTED');
SELECT PO_INQ_SV.get_po_total (type_lookup_code,
po_header_id,
'') FROM PO_HEADERS_ALL WHERE po_header_id = p_po_header_id;
SELECT project_id,
vendor_id,
po_number,
deduction_req_date,
org_id
FROM PA_DEDUCTIONS_ALL
WHERE deduction_req_id = p_dctn_req_id;
SELECT 'Y'
FROM PA_DEDUCTION_TRANSACTIONS_ALL
WHERE expenditure_item_id = c_exp_item_id
AND deduction_req_tran_id <> p_dctn_txn_id;
SELECT PO_INQ_SV.get_po_total (type_lookup_code,
po_header_id,
'') FROM PO_HEADERS_ALL WHERE po_header_id = p_po_header_id;
SELECT
system_linkage_function
,start_date_active
,end_date_active
FROM pa_expend_typ_sys_links
WHERE system_linkage_function = 'VI'
AND expenditure_type = p_etype ;
SELECT * FROM PA_DEDUCTION_TRANSACTIONS_ALL
WHERE deduction_req_id = p_dctn_req_id;
UPDATE PA_DEDUCTIONS_ALL pda
SET debit_memo_num = l_dctn_hdrtbl(1).debit_memo_num
WHERE deduction_req_id = p_dctn_req_id;
Update_Deduction_Status(p_dctn_req_id,
'SUBMITTED');
UPDATE PA_DEDUCTIONS_ALL pda
SET status = DECODE(p_return_status,'E','REJECTED',status)
WHERE deduction_req_id = p_dctn_req_id;
Delete_Failed_Rec(p_dctn_req_id);
SELECT *
FROM PA_DEDUCTION_TRANSACTIONS_ALL WHERE deduction_req_id = p_dctn_req_id;
Update_Deduction_Status(p_dctn_hdr_rec.deduction_req_id,
'APPROVED');
SELECT AP_INVOICES_INTERFACE_S.nextval
INTO l_int_invoice_id
FROM SYS.DUAL;
Update_Deduction_Status(p_dctn_hdr_rec.deduction_req_id,
'FAILED');
log_message ('Before inserting invoice header : '||l_groupId, g_api_name);
INSERT INTO AP_INVOICES_INTERFACE (
invoice_id
,invoice_num
,invoice_type_lookup_code
,invoice_date
,vendor_id
,vendor_site_id
,invoice_amount
,invoice_currency_code
,description
,voucher_num
,application_id
,product_table
,reference_key1
,calc_tax_during_import_flag
,group_id
,source
,creation_date
,created_by
,org_id )
VALUES (
p_invoice_id
,p_invoice_num
,'DEBIT'
,p_invoice_date
,p_vendor_id
,p_vendor_site_id
,-p_invoice_amount
,p_invoice_currency_code
,p_description
,p_invoice_num
,275
,'PA_DEDUCTIONS_ALL'
,p_deduction_req_id
,p_tax_flag
,l_groupid
,'Oracle Project Accounting'
,SYSDATE
,g_user_id
,p_org_id );
log_message ('After inserting into invoice interface table',g_api_name);
log_message ('Before inserting lines into interface lines table', g_api_name);
SELECT ap_invoice_lines_interface_s.nextval
INTO l_invoice_line_id
FROM sys.dual;
INSERT INTO AP_INVOICE_LINES_INTERFACE (
invoice_id
,invoice_line_id
,line_type_lookup_code
,amount
,quantity_invoiced
,org_id
,project_id
,task_id
,expenditure_type
,expenditure_organization_id
,expenditure_item_date
,project_accounting_context
,accounting_date
,description
,pa_addition_flag
,creation_date
,created_by
)
VALUES (
p_invoice_id
,l_invoice_line_id
,'ITEM'
,-p_amount
,p_qty_invoiced
,p_org_id
,p_project_id
,p_task_id
,p_expenditure_type
,p_expenditure_org
,p_expenditure_item_date
,p_project_acct_context
,p_accounting_date
,p_description
,'N'
,SYSDATE
,g_user_id) ;
log_message ('After inserting into interface :'||SQL%ROWCOUNT|| ' records inserted',
g_api_name);
Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,
p_status IN VARCHAR2) IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE PA_DEDUCTIONS_ALL SET status = p_status WHERE deduction_req_id = p_dctn_hdr_id;
SELECT * FROM AP_INVOICES_INTERFACE
WHERE product_table = 'PA_DEDUCTIONS_ALL'
AND reference_key1 = p_dctn_req_id;
SELECT * FROM PA_DEDUCTIONS_ALL
WHERE deduction_req_id = p_dctn_req_id;
select pded.deduction_req_num,pded.debit_memo_num,pded.deduction_req_date,
pded.debit_memo_date,pded.currency_code,pa.segment1,pa.name
,v.vendor_name
,vs.vendor_site_code
,hr.name hr_name
from pa_deductions_all pded,pa_projects_all pa
,po_vendors v, po_vendor_sites_all vs,hr_organization_units hr
where pa.project_id=pded.project_id and
pded.vendor_id= v.vendor_id and pded.vendor_site_id=vs.vendor_site_id
and pa.org_id=hr.organization_id
and pded.deduction_req_id = p_dctn_req_id;
select lookup.description reason,
pta.task_number task_num,
al.expenditure_type exp_type,
hr.name exp_org,
al.amount
from AP_INVOICE_LINES_INTERFACE al
, ap_interface_rejections ar,
AP_INVOICES_INTERFACE ad,
pa_tasks pta,hr_organization_units hr,
fnd_lookup_values lookup
where ar.parent_id=al.invoice_line_id
and ar.parent_table='AP_INVOICE_LINES_INTERFACE'
and al.invoice_id=ad.invoice_id
and ad.product_table = 'PA_DEDUCTIONS_ALL'
and al.task_id=pta.task_id
and hr.organization_id=al.expenditure_organization_id
and ar.reject_lookup_code = lookup.lookup_code
and lookup.lookup_type='REJECT CODE'
and view_application_id=200
and lookup.language=USERENV('LANG')
and ad.reference_key1 = p_dctn_req_id;
select lookup.description reason
from ap_interface_rejections ar,
AP_INVOICES_INTERFACE ad,
fnd_lookup_values lookup
where ar.parent_id=ad.invoice_id
and ar.parent_table='AP_INVOICES_INTERFACE'
and ad.product_table = 'PA_DEDUCTIONS_ALL'
and ar.reject_lookup_code = lookup.lookup_code
and lookup.lookup_type='REJECT CODE'
and view_application_id=200
and lookup.language=USERENV('LANG')
and ad.reference_key1 = p_dctn_req_id;
SELECT FND_GLOBAL.CONC_REQUEST_ID
INTO p_conc_request_id
FROM DUAL;
Update_Deduction_Status(p_dctn_req_id,
'REJECTED');
Update_Deduction_Status(p_dctn_req_id,
'FAILED');
Delete_Failed_Rec(p_dctn_req_id);
Update_Deduction_Status(p_dctn_req_id,
'PROCESSED');
Update_Deduction_Status(p_dctn_req_id,
'FAILED');
Delete_Failed_Rec(p_dctn_req_id);
Delete_Failed_Rec(p_dctn_req_id);
Update_Deduction_Status(p_dctn_req_id,
'FAILED');
PROCEDURE Delete_Failed_Rec(p_dctn_req_id NUMBER) IS
BEGIN
NULL;
DELETE AP_INVOICE_LINES_INTERFACE WHERE invoice_id IN(
SELECT invoice_id FROM AP_INVOICES_INTERFACE WHERE
product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
DELETE AP_INVOICES_INTERFACE WHERE
product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
DELETE AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id IN(
SELECT invoice_id FROM AP_INVOICES_ALL WHERE
product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
DELETE AP_INVOICE_LINES_ALL WHERE invoice_id IN(
SELECT invoice_id FROM AP_INVOICES_ALL WHERE
product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
DELETE AP_INVOICES_ALL WHERE
product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
END Delete_Failed_Rec;
SELECT 'N'
FROM PA_DEDUCTIONS_ALL
WHERE debit_memo_num = p_debit_memo_num
AND org_id = p_org_id
AND vendor_id = p_vendor_id
AND deduction_req_id <> nvl(p_dctn_req_id,-99);
SELECT 'N'
FROM DUAL WHERE EXISTS (
SELECT 1
FROM AP_INVOICES_ALL
WHERE invoice_num = p_debit_memo_num
AND vendor_id = p_vendor_id
AND org_id = p_org_id
UNION ALL
SELECT 1
FROM AP_INVOICES_INTERFACE
WHERE invoice_num = p_debit_memo_num
AND vendor_id = p_vendor_id
AND org_id = p_org_id
AND nvl(status, 'REJECTED') <> 'REJECTED');
SELECT PA_DEDUCTIONS_DM_S.nextval
INTO l_next_number FROM sys.DUAL;
SELECT invoice_num
FROM ap_invoices_all apinv
WHERE apinv.vendor_id = p_vendor_id
AND apinv.vendor_site_id = p_vendor_site_id
AND EXISTS
(SELECT 1 FROM ap_invoice_distributions_all
WHERE invoice_id = apinv.invoice_id
AND parent_invoice_id = ( SELECT invoice_id from ap_invoices_all
WHERE source = 'Oracle Project Accounting'
AND invoice_type_lookup_code = 'DEBIT'
AND product_table='PA_DEDUCTIONS_ALL'
AND reference_key1 = ded_req_num));