create or replace PROCEDURE CUX_CMI_PO_RCPT_NO_TAX(ERRBUF out varchar2,

RETCODE out varchar2,P_RECEIPT_ID NUMBER) IS

CURSOR c_issue_trxs IS
SELECT DISTINCT jtr.trx_number
,jtr.trx_id
,jtr.trx_line_id
FROM JAI_tAX_DET_FACTORS jtr
WHERE JTR.ENTITY_CODE = 'RCV_TRANSACTION'
AND JTR.APPLICATION_ID = 707
--AND JTR.REF_DOC_ENTITY_CODE ='PURCHASE_ORDER'
AND JTR.REF_DOC_APPLICATION_ID = 201
AND JTR.TRX_TYPE = 'RECEIVE'
AND (( P_RECEIPT_ID is not null and JTR.TRX_id = P_RECEIPT_ID ) or
( P_RECEIPT_ID is null AND jtr.trx_date > trunc(sysdate)-3 ) )
and jtr.org_id in (select to_number(LOOKUP_CODE)
from apps.fnd_lookup_values
where lookup_type = 'CUX_CMI_ORG_LISTING'
and LANGUAGE = 'US'
union
select to_number(LOOKUP_CODE)
from apps.fnd_lookup_values
where lookup_type = 'CUX_MI_ORG_LISTING'
and LANGUAGE = 'US')
AND EXISTS (SELECT '1'
FROM JAI_TAX_LINES JTP
WHERE JTP.trx_id = jtr.ref_doc_trx_id
AND JTP.trx_line_id = jtr.ref_doc_line_id
AND JTP.ENTITY_CODE = jtr.ref_doc_entity_code
AND jtp.application_id = jtr.ref_doc_application_id)
AND NOT EXISTS (SELECT 1
FROM JAI_TAX_LINES JTO
WHERE JTO.DET_FACTOR_ID = JTR.DET_FACTOR_ID);

CURSOR c_del_det_factors
(
cp_trx_number IN VARCHAR2
,cp_trx_id NUMBER
,cp_trx_line_id NUMBER
) IS
SELECT *
FROM jai_tax_det_factors JTDF
WHERE trx_number = cp_trx_number
AND entity_code = 'RCV_TRANSACTION'
AND application_id = 707
AND trx_id = cp_trx_id
AND trx_line_id = cp_trx_line_id
--AND last_updated_by <> '-26728068'
AND NOT EXISTS (SELECT 1
FROM JAI_TAX_LINES JTL
WHERE JTDF.DET_FACTOR_ID = JTL. DET_FACTOR_ID)
ORDER BY det_factor_id;
CURSOR c_get_parent_info
(
p_application_id NUMBER
,p_entity_code VARCHAR2
,p_trx_id NUMBER
,p_trx_line_id NUMBER
,p_trx_type VARCHAR2
) IS
SELECT jtl.*
FROM jai_tax_det_factors jtl
WHERE jtl.application_id = p_application_id
AND jtl.entity_code = p_entity_code
AND jtl.trx_id = p_trx_id
AND jtl.trx_line_id = p_trx_line_id
AND jtl.trx_type = p_trx_type;
lr_get_parent_info c_get_parent_info%ROWTYPE;
CURSOR c_det_factors_accounting
(
cp_trx_number IN VARCHAR2
,cp_trx_id NUMBER
,cp_trx_line_id NUMBER
) IS
SELECT *
FROM jai_tax_det_factors
WHERE trx_number = cp_trx_number
AND entity_code = 'RCV_TRANSACTION'
AND application_id = 707
AND trx_id = cp_trx_id
AND trx_line_id = cp_trx_line_id
AND last_updated_by = '-26728068'
ORDER BY det_factor_id;
CURSOR c_rcv_trx(p_trx_loc_line_id NUMBER) IS
SELECT *
FROM rcv_Transactions
WHERE transaction_id = p_trx_loc_line_id;
p_rcv_trx1 c_rcv_trx%ROWTYPE;
p_rcv_trx2 c_rcv_trx%ROWTYPE;
CURSOR c_get_po_dtls(pn_header_id NUMBER) IS
SELECT org_id
FROM po_headers_all
WHERE po_header_id = pn_header_id;
lr_get_po_dtls c_get_po_dtls%ROWTYPE;
CURSOR get_release_type(pn_release_id NUMBER) IS
SELECT release_type
,org_id
FROM po_releases_all
WHERE po_release_id = pn_release_id;
lv_release_type po_releases_all.release_type%TYPE;
ln_org_id po_releases_all.org_id%TYPE;
lv_process_message VARCHAR2(1000);
lv_process_status VARCHAR2(1000);
lv_call_from VARCHAR2(100);
BEGIN
----DBMS_OUTPUT.ENABLE(10000000);
dbms_output.put_line('start of data fix');
FOR issue_rec IN c_issue_trxs LOOP
dbms_output.put_line('issue receipt number=' || issue_rec.trx_number);
FOR lr_det_factor IN c_del_det_factors(issue_rec.trx_number
,issue_rec.trx_id
,issue_rec.trx_line_id) LOOP
OPEN c_rcv_trx(lr_det_factor.trx_loc_line_id);
FETCH c_rcv_trx
INTO p_rcv_trx2;
CLOSE c_rcv_trx;
dbms_output.put_line('p_rcv_trx2.po_release_id=' ||
p_rcv_trx2.po_release_id);
IF p_rcv_trx2.po_release_id IS NOT NULL THEN
/*r_det_factor.ref_doc_application_id := 201;
r_det_factor.ref_doc_entity_code := 'RELEASE';
r_det_factor.ref_doc_event_class_code := 'RELEASE';
r_det_factor.ref_doc_trx_id :=
p_rcv_trx2.po_release_id;
r_det_factor.ref_doc_line_id := p_rcv_trx2.po_line_id;
r_det_factor.ref_doc_loc_line_id :=
p_rcv_trx2.po_line_location_id;
r_det_factor.ref_doc_trx_level_type := 'SHIPMENT';*/
OPEN get_release_type(p_rcv_trx2.po_release_id);
FETCH get_release_type
INTO lv_release_type
,ln_org_id;
CLOSE get_release_type;
--r_det_factor.ref_doc_trx_type := lv_release_type;
dbms_output.put_line('lv_release_type=' || lv_release_type ||
',ln_org_id=' || ln_org_id);
OPEN c_get_parent_info(201
,'RELEASE'
,p_rcv_trx2.po_release_id
,p_rcv_trx2.po_line_id
,lv_release_type);
FETCH c_get_parent_info
INTO lr_get_parent_info;
CLOSE c_get_parent_info;
dbms_output.put_line('parent default tax category id =' ||
lr_get_parent_info.Override_Tax_Category_Id);
dbms_output.put_line('parent override tax category id =' ||
lr_get_parent_info.Default_Tax_Category_Id ||
'lr_get_parent_info.intended_use=' ||
lr_get_parent_info.intended_use);
UPDATE jai_tax_det_factors
SET tax_determination_basis = 'COPY_BASIS'
,default_tax_category_id =
NVL(lr_get_parent_info.Override_Tax_Category_Id
,lr_get_parent_info.Default_Tax_Cate
gory_Id)
,Override_Tax_Category_Id =
NVL(lr_get_parent_info.Override_Tax_Category_Id
,lr_get_parent_info.Default_Tax_Cate
gory_Id)
,intended_use = NVL(lr_get_parent_info.intended_use
,intended_use)
,ref_doc_application_id = 201
,ref_doc_entity_code = 'RELEASE'
,ref_doc_event_class_code = 'RELEASE'
,ref_doc_trx_id = p_rcv_trx2.po_release_id
,ref_doc_line_id = p_rcv_trx2.po_line_id
,ref_doc_loc_line_id = p_rcv_trx2.po_line_location_id
,ref_doc_trx_level_type = 'SHIPMENT'
,ref_doc_trx_type = lv_release_type
,org_id = NVL(ln_org_id
,org_id)
,last_updated_by = '-26728068'
,last_update_date = SYSDATE
WHERE det_factor_id = lr_det_factor.det_factor_id;
dbms_output.put_line('rows updated =' || SQL%ROWCOUNT);
ELSE
OPEN c_get_po_dtls(p_rcv_trx2.po_header_id);
FETCH c_get_po_dtls
INTO lr_get_po_dtls;
CLOSE c_get_po_dtls;
dbms_output.put_line('lr_get_po_dtls.org_id=' || lr_get_po_dtls.org_id);
OPEN c_get_parent_info(lr_det_factor.ref_doc_application_id
,lr_det_factor.ref_doc_entity_code
,lr_det_factor.ref_doc_trx_id
,lr_det_factor.ref_doc_line_id
,lr_det_factor.ref_doc_trx_type);
FETCH c_get_parent_info
INTO lr_get_parent_info;
CLOSE c_get_parent_info;
dbms_output.put_line('parent default tax category id =' ||
lr_get_parent_info.Override_Tax_Category_Id);
dbms_output.put_line('parent override tax category id =' ||
lr_get_parent_info.Default_Tax_Category_Id ||
'lr_get_parent_info.intended_use=' ||
lr_get_parent_info.intended_use);
UPDATE jai_tax_det_factors
SET tax_determination_basis = 'COPY_BASIS'
,default_tax_category_id =
NVL(lr_get_parent_info.Override_Tax_Category_Id
,lr_get_parent_info.Default_Tax_Cate
gory_Id)
,Override_Tax_Category_Id =
NVL(lr_get_parent_info.Override_Tax_Category_Id
,lr_get_parent_info.Default_Tax_Cate
gory_Id)
,REF_DOC_TRX_LEVEL_TYPE = NVL(lr_get_parent_info.TRX_LEVEL_TYPE
,REF_DOC_TRX_LEVEL_TYPE)
,REF_DOC_LOC_LINE_ID = lr_get_parent_info.trx_loc_line_id
,intended_use = NVL(lr_get_parent_info.intended_use
,intended_use)
,org_id = NVL(lr_get_po_dtls.org_id
,org_id)
,last_updated_by = '-26728068'
,last_update_date = SYSDATE
WHERE det_factor_id = lr_det_factor.det_factor_id;
dbms_output.put_line('rows updated =' || SQL%ROWCOUNT);
END IF;
JAI_TAX_DETERMINATION_PKG.determine_tax(pn_application_id =>
lr_det_factor.APPLICATION_ID
,pv_entity_code =>
lr_det_factor.ENTITY_CODE
,pv_event_class_code =>
lr_det_factor.EVENT_CLASS_CODE
,pn_org_id =>
lr_det_factor.ORG_ID
,pn_trx_id =>
lr_det_factor.trx_id
,pv_trx_type =>
lr_det_factor.trx_type
,pn_trx_line_id =>
lr_det_factor.trx_line_id
,pn_trx_loc_line_id =>
lr_det_factor.trx_loc_line_id
,pv_call_from => lv_call_from
,pv_process_status =>
lv_process_status
,pv_process_message =>
lv_process_message);
dbms_output.put_line('after determine_tax. lr_det_factor.det_factor_id=' ||
lr_det_factor.det_factor_id ||
'lv_process_message = --' || lv_process_message);
END LOOP; --det_factor_id
FOR lr_det_factor_acct IN c_det_factors_accounting(issue_rec.trx_number
,issue_rec.trx_id
,issue_rec.trx_line_id)
LOOP
OPEN c_rcv_trx(lr_det_factor_acct.trx_loc_line_id);
FETCH c_rcv_trx
INTO p_rcv_trx1;
CLOSE c_rcv_trx;
jai_tax_accounting_pkg.post_accounting(pn_application_id =>
lr_det_factor_acct.application_id
,pv_event_class_code =>
lr_det_factor_acct.event_class_code
,pv_event_type_code =>
lr_det_factor_acct.event_type_code
,pv_entity_code =>
lr_det_factor_acct.entity_code
,pv_tax_event_class =>
lr_det_factor_acct.tax_event_class_code
,pv_tax_event_type =>
lr_det_factor_acct.tax_event_type_code
,pn_det_factor_id =>
lr_det_factor_acct.det_factor_id
--,pn_claim_schedule_id => NULL
,pn_credit_amount => NULL
,pn_uncredit_amount => NULL
,pn_trx_id => NULL
,pn_trx_line_id => NULL
,pn_tax_line_id => NULL
,pn_tax_dist_id => NULL
,pr_rcv_trx => p_rcv_trx1
,pv_process_status =>
lv_process_status
,pv_process_message =>
lv_process_message);
dbms_output.put_line('after POST_ACCOUNTING. lv_process_message = ' ||
lv_process_message);
UPDATE jai_tax_det_factors
SET last_updated_by = '-267280681'
,last_update_date = SYSDATE
WHERE det_factor_id = lr_det_factor_acct.det_factor_id;
END LOOP;
END LOOP; --det_factor_id
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception->' || SQLERRM);
END CUX_CMI_PO_RCPT_NO_TAX;