[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
4. 23-Aug-2007 Bgowrava 6012570 120.6 modified the c_get_pa_details cursor query to select from the tables PA_DRAFT_INVOICES_ALL,
PA_PROJECTS_ALL instead of pa_draft_invoices_v. This was done to improve the performance
of the query
5. 24-Sep-2007 vkantamn 6083978 120.8 The org_id for the po has been changed to fetch from the
PO table.
Also New transaction source 'RECEIVING' has been added,
and the org_id has been picked from the
ja_in_rcv_transactions for the above invoices.
6. 04-Oct-2007 CSahoo 6457710 120.9 Added a ELSIF block related to projects in the procedure extract_rgm_trxs.
7. 10-Oct-2007 CSahoo 6457710 120.10 Modified the follwing cursors in get_document_details procedure
c_get_po_line_loc_srvtyp
c_get_so_line_srvtyp
c_get_rma_line_srvtyp
c_get_pa_inv_line_tax
c_get_ra_line_srvtyp
Added the cess and sh cess tax types in the AND clause.
8. 25-Feb-2008 rchandan 6841116 120.3.12000000.3 Issue : The PO Matched to Receipt transactions are not shown in the
'Service tax Repository Review' form after running the India Service Tax Processing' conc program.
Fix : This above issue has been fixed by adding a new elsif condition for the 'RECIVING'
in the procedure 'extract_rgm_trxs'.
This is forward port of bug#6323157
9. 3-march-2008 Changes by nprashar for bug # 6841116. Changes in procedure update_service_type.
10. 29-April-2008 Changes by nprashar for bug #6636517 , added a NVL clause in join condition of cursor c_get_po_details.
11. 31-july-2008 Changes by nprashar for bug 7172723.
Issue : India ST Processing concurrent should consider Third Party Invoices and
update India Service Tax Credit register report
Fix : Modifed following procedure to use receipt information for Third party
invoices which do not have reference to PO
1 - get_doc_from_reference - Added logic for third party invoices which do not have references to PO
Modified cursor - c_get_refs_rec
Added cursor - c_get_source_type,c_get_line_number,c_get_doc_details,c_get_ra_line_srvtyp
12. 03-Jul-2009 CSahoo for bug#8648359, File Version 120.3.12000000.8
Added an AND clause in the code in the procedure extract_rgm_trxs.
13. 14-Jul-2009 CSahoo for bug#8451703, File Version 120.3.12000000.9
Modified the IF clause in the procedure get_document_details.
14. 01-OCT-2009 JMEENA for bug#8943349
Issue: India Service Tax Processing Concurrent not processing Standalone Invoices
1. Modified procedure get_document_details and added cursor c_get_standalone_inv_details, c_get_standalone_org_loc
and c_get_standalone_inv_line_tax
2. Modified procedure derrive_doc_from_ref and added code for standalone invoice.
3. Modified procedure extract_rgm_trxs and added code to populate the table
jai_trx_repo_extract_gt for standalone invoice.
15. 08-Oct-2009 CSahoo for bug#8965721, File Version 120.19.12010000.7
Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
Fix: Modified the cursor c_get_doc_details. Replaced rcv_transactions by jai_rcv_transactions
16. 09-Dec-2009 CSahoo for bug#9192752, File Version 120.19.12010000.8
Issue: INTCUS:SERVCIE TAX PROCESSING LOG IS SHOWN ERROR MESSAGE
FIX: Modified the code in the procedure derrive_doc_from_ref. Moved the CLOSE cursor code
into the IF block.
17. 18-Dec-2009 Modifiey by Jia for FP Bug#6691866, File Version 120.19.12010000.10
Issue:
India - Service tax processor in landing in error. Error thrown is as below:
ORA-01652: unable to extend temp segment by 32 in tablespace MTEMP
This was a forward port issue of the R11i Bug#6652557.
In the query written for the cursor c_get_pa_details in procedure get_document_details,
four tables are used.But the join conditions in the where clause are only three which might be
leading to a cartesain join on the queries fetched.
FIX:
Modified cursor c_get_pa_details, included an inline-select statement for the field document_line_desc
and removed the Table pa_draft_invoice_items from the list to avoid the Cartesian join.
18. 18-Feb-2010 Bgowrava for bug#9385880, File Version 120.19.12010000.11
Issue: INDIA - SERVICE TAX PROCESSING IS COMPLETED WITH WARNING
Fix: Added a IF condition in the procedure get_doc_from_reference
19. 19-FEB-2010 JMEENA for bug#9298508
Modified procedure get_document_details and added cursor c_get_ra_tax_amt_applied and c_get_ra_line_amt_applied
to fetch the applied line and tax amount same is updated in temp table to show on service tax repository review form.
20. 29-oct-2010 vkaranam for bug#10085619
Modified procedure get_document_details,extract_rgm_trxs,get_doc_from_reference to handle the taxes attached to tp invoice.
20. 19-Jan-2011 Xiao Lv for bug#10634960
Issue: INDIA SERVICE TAX PROCESSING CONCURRENT DOES NOT PROCESS SERVICE FOR STANDALONE
Fixed: Modfiy Cursor c_get_standalone_org_loc, pick up loc/org from parent invoice line.
21. 31-Jan-2011 Bug 10434986
Description:
+ JAI_TRX_REPO_EXTRACT_GT is populated with incorrect Document Line ID
(Line Number of Tax Line is inserted into document line id column instead of Item Line Number)
+ Tax Amount and Line Amount are populated from jai_cmn_document_taxes (Tax Amount column)
Hence if partial payment is made, the same is not considered and the whole tax amount
is shown in the Service Tax Repository Review window
Fix:
+ Inserted Line Number of Item into Document Line ID
+ Fetched the Payment amount from ap_invoice_payments_all. Derived the Repository Amount
from JAI_RGM_TRX_RECORDS. Sibtracted the Tax Amount from Payment amount and derived the Line Amount
22. 14-Mar-2011 Bug 11821537
Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
i.e. Accounting changes to Accrual basis from Cash Basis
Fix: Fetched the Line amount and Tax Amount shown in the Repository Review Window from
cursor c_get_ra_trx_details instead of Receivable Application Table as the entire liability
arises at the point of invoices instead of cash receipt/credit memo application
22. 20-may-2011 vkaranam for bug#12560704
Issue:Service tax processing concurrent is completing in warning
charge account not defined for tax type
Fix: modified c_get_rcv_details cursor in geT_document_details procedure.
23. 23-MAY-2011 Bug 11932841
Description: Tax and Line amount in repository displays the total amount for the entire
document in each line
Fix: Get the Tax and Line amount per Line for the AR Invoice
24. 24-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: Compare the invoice accounting date with POT implementation date, so that it can process
transactions on cash basis as well as accrual basis.
25. 29-May-2011 Xiao for POT change, reg bug#12533434
Fixed: In cursor c_get_repo_amount, change the function as:
SELECT abs(SUM(NVL(jrec.TRX_DEBIT_AMOUNT,0))-SUM(NVL(jrec.TRX_CREDIT_AMOUNT,0)));
36. 14-dec-2011 Qiong for Bug 13456083 - PRE POT INVOICE UPDATE REPOSITORY INCORRECT
37. 19-dec-2011 Qiong for Bug 13405553 - Update repository imcorrect for payables reversal claim source
38. 20-dec-2011 Qinglei for Bug 13405591 - Repository UI show line amount and tax amount as 0 after invoice cancellation
39. 27-Dec-2011 Qinglei for Bug 13531399 - For Pre-POT AR invoice, tax amount is null after receipt application
40. 04-Jan-2012 Qiong for bug#13535329 ST invoice number on ar trnx can't be updated to st repository
42. 05-Jan-2012 Qinglei for Bug 13556198 - Repository UI show doesn't show line amount for receipt application/unapplication
43. 12-Jan-2012 Qiong for bug#13555753 ST proceesing report and repository should use reversal trx date and gl date
44. 18-Jan-2012 Qiong for bug#13597785 - TRX DATE IN REPOSITORY FOR AR TRX FROM PB SHOULD BE TRX'S TRX DATE
45. 18-JAN-2012 vkaranam for bug#13375399
issue:service distribute in entries are not appearing the service tax repository review form.
fix:
changes are done in extract_Rgm_trxs,update_service_type procedures
to include service_distribute_in entries.
46. 23-Jan-2012 amandali for bug 13430127
Issues Fixed: +PO/Receipt matched invoices should have AP invoice reference in repository review form
Modified the procedure get_document_details by adding cursors c_get_ap_inv_payment,c_get_repo_amount
and commented code for PURCHASING and RECEIVING and added code for the same to get details from AP tables.
Also made the similar changes in derrive_doc_from_ref.
47. 03-Feb-2012 Qiong for bug13598199 Issues when update repository for AP CLAIM
48. 09-FEB-2012 Wenqiong for bug13462951 line amount not correct in service repository
49. 28-Feb-2012 Qinglei for bug#13741544
Issue Fixed: For Pre-POT AR transaction after receipt unapplication, repository show line amount and
tax amount as zero.
50. 29-JAN-13 Bug No : 14341945
Description : stx.servicecharge:service:foreign currency invoice not converted to inr in repos
FIX: chaged the col names trx_credit_amount,trx_debit_amount to credit_amount, debit_amount
*/
-------------------------------------------------------------------------------------------------
-------------*/
/*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
/** Package level variables used in debug package*/
lv_object_name jai_cmn_debug_contexts.log_context%type default 'JAI_TRX_REPO_EXTRACT_PKG';
SELECT date_of_reversal
FROM jai_st_invoice_reversal
WHERE invoice_id = pn_document_id
AND SOURCE = substr(pv_source, 1, 2);
SELECT accounting_date
FROM ap_invoice_payments_all
WHERE invoice_payment_id = pn_source_document_id;
SELECT gl_date
FROM ar_receivable_applications_all
WHERE receivable_application_id = pn_source_document_id;
SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = pn_source_document_id;
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pn_document_id
AND account_class = 'REV';
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM jai_rgm_definitions
where regime_code = jai_constants.service_regime
)
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM jai_rgm_definitions
where regime_code = jai_constants.service_regime
)
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM jai_rgm_definitions
where regime_code = jai_constants.service_regime
)
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT
GL_DATE
FROM jai_ar_cash_receipts_all
WHERE cash_receipt_id = pn_document_id;
SELECT acrh.gl_date gl_date
FROM ar_cash_receipts_all acra,
ar_cash_receipt_history_all acrh
WHERE acra.cash_receipt_id = pn_document_id
AND acrh.cash_receipt_id = acra.cash_receipt_id
AND acrh.status = 'REVERSED';
SELECT nvl(discarded_flag,'N')
FROM ap_invoices_all aia,
ap_invoice_lines_all ail
WHERE aia.invoice_id = ail.invoice_id
AND aia.invoice_id = pn_document_id
AND ail.line_number = pv_document_line_id;
SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_id = pn_document_id
AND invoice_line_number = pv_document_line_id
AND nvl(reversal_flag,'N') = 'Y'
AND parent_reversal_id IS NOT NULL;
IS SELECT payment_date
FROM JAI_RGM_SETTLEMENTS
WHERE settlement_id = pn_source_document_id;
select (recs.repository_id) repository_id
, nvl(refs.reference_id, recs.reference_id) reference_id
, refs.invoice_id
, refs.item_line_id
,refs.line_id --added for bug#10085619 ,refers to the invoice dist id of the taxes
, recs.source
, recs.source_trx_type source_trx_type--Added by Wenqiong for Advanced Receipts
, recs.service_type_code
, nvl(recs.organization_id, recs.inv_organization_id) organization_id
, recs.location_id
, (nvl(credit_amount,0) + nvl(debit_amount,0)) repository_tax_amt -- modified for the bug 14341945
, recs.organization_type
, recs.source_document_id
, recs.invoice_no --Added by Qiong for Advanced Receipts
, recs.source_table_name --Added by Qiong for bug#13456083
from jai_rgm_trx_refs refs
, jai_rgm_trx_records recs
where recs.reference_id = refs.reference_id (+)
and ( p_organization_id is null
or (recs.organization_id = p_organization_id)
)
and (p_location_id is null or recs.location_id = p_location_id )
and trunc(transaction_date) between nvl (p_from_trx_date, trunc(transaction_date)) and nvl (p_to_trx_date, trunc(transaction_date))
and recs.regime_code = p_regime_code
and ( (p_query_settled_flag = 'N' and (recs.settlement_id is null))
or (p_query_settled_flag = jai_constants.yes)
)
and ( (p_query_only_null_srvtype = 'Y' and (recs.service_type_code is null))
or (p_query_only_null_srvtype = 'N')
)
and (p_source is null or p_source = recs.source )
and recs.organization_type = 'IO'
and recs.source in ('AP'
,'AR'
,'MANUAL'
,'SERVICE_DISTRIBUTE_OUT'
,'SERVICE_DISTRIBUTE_IN' /*added SERVICE_DISTRIBUTE_IN for bug#13375399*/
--Add by Chong.Lei for POT code port begin
--Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
, 'AP_REVERSAL'
, 'AP_CLAIM'
, 'AR_REVERSAL'
, 'AR_CLAIM'
--Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
--Add by Chong.Lei for POT code port end
);
select
sum((nvl(credit_amount,0) + nvl(debit_amount,0))) repository_tax_amt -- modified for the bug 14341945
from jai_rgm_trx_records recs,
jai_rgm_trx_refs refs
where recs.reference_id = refs.reference_id (+)
and recs.SOURCE_TRX_TYPE = pv_source_trx_type
and refs.item_line_id = pv_item_line_id
and refs.invoice_id = pn_invoice_id;
select
sum((nvl(credit_amount,0) + nvl(debit_amount,0))) repository_tax_amt--Modified by Junjian for bug#14341945 on 2-Aug-2012
from jai_rgm_trx_records recs,
jai_rgm_trx_refs refs
where recs.reference_id = refs.reference_id (+)
and recs.SOURCE_TRX_TYPE = pv_source_trx_type
and refs.item_line_id = pv_item_line_id
and refs.invoice_id = pn_invoice_id
;
SELECT nvl(discarded_flag,'N')
FROM ap_invoices_all aia,
ap_invoice_lines_all ail
WHERE aia.invoice_id = ail.invoice_id
AND aia.invoice_id = pn_invoice_id
AND ail.line_number = pv_invoice_line_num;
select name
from hr_organization_units
where organization_id = cp_organization_id;
select description
from hr_locations_all
where location_id = cp_location_id;
select recs.service_type_code
from jai_rgm_trx_records recs, jai_rgm_trx_refs refs
where recs.reference_id = refs.reference_id
and refs.invoice_id = cp_invoice_id
and refs.line_id = cp_line_id
and refs.source = cp_source
and recs.settlement_id is not null
and recs.service_type_code is not null
and rownum = 1;
select transfer_number
,transaction_date
,party_id
,location_id
from jai_rgm_dis_src_hdrs
where party_type = cp_party_type
and party_id = cp_party_id
and transfer_id = cp_transfer_id;
select transfer_number
,creation_date transaction_date
,destination_party_id
,location_id
from JAI_RGM_DIS_DES_HDRS
where destination_party_type = cp_party_type
and destination_party_id = cp_party_id
and transfer_id = cp_transfer_id;
select party_type
, party_id
, transaction_date
, remarks
, invoice_number
from JAI_RGM_MANUAL_TRXS
where transaction_number = cp_trx_number;
select vendor_name
from po_vendors
where vendor_id = cp_vendor_id;
select hzp.party_name
from hz_cust_accounts hzca
,hz_parties hzp
where hzca.cust_account_id = cp_party_id
and hzp.party_id = hzca.party_id;
select
aia.invoice_num,
substr(aia.invoice_num,instr(aia.invoice_num,'/',1,1)+1,instr(aia.invoice_num,'/',1,2)-instr(aia.invoice_num,'/',1,1)-1) rcp_no,
pha.segment1 po_num,
aia.invoice_date
from
po_headers_all pha,
rcv_transactions rt,
rcv_shipment_headers rsh,
ap_invoices_all aia
where
rsh.receipt_num=substr(aia.invoice_num,instr(aia.invoice_num,'/',1,1)+1,instr(aia.invoice_num,'/',1,2)-instr(aia.invoice_num,'/',1,1)-1) AND
rsh.shipment_header_id=rt.shipment_header_id AND
rt.po_header_id=pha.po_header_id AND
pha.org_id=aia.org_id AND
aia.invoice_id=cp_invoice_id
and rownum=1;
select pv.vendor_name,pv.vendor_id from
jai_rgm_trx_refs jrtr,
po_vendors pv
where invoice_id=cp_invoice_id
and pv.vendor_id=jrtr.party_id
and rownum=1;
select inv_dist_id,
line_num
from
(select
INVOICE_DISTRIBUTION_ID inv_dist_id,
row_number() over(ORDER BY INVOICE_DISTRIBUTION_ID) line_num
from ap_invoice_distributions_all
where INVOICE_ID=p_invoice_id
)
where inv_dist_id=p_line_id;
, 'Before insert into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.transaction_repository_id ='|| lr_trx_repo_extract.transaction_repository_id || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.transaction_reference_id ='|| lr_trx_repo_extract.transaction_reference_id || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_id ='|| lr_trx_repo_extract.document_id || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_id ='|| lr_trx_repo_extract.document_line_id || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_num ='|| lr_trx_repo_extract.document_line_num || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_item ='|| lr_trx_repo_extract.document_line_item || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_desc ='|| lr_trx_repo_extract.document_line_desc
);
insert into jai_trx_repo_extract_gt
(
transaction_repository_id
,transaction_reference_id
,transaction_source
,party_name
,document_number
,document_date
,document_id
,document_line_id
,document_line_num
,document_line_item
,document_line_desc
,document_line_qty
,document_line_uom
,document_line_amt
,document_currency_code
,repository_tax_amt
,organization_name
,location_name
,organization_id
,location_id
,inventory_item_id
,party_id
,service_type_code
,repository_invoice_id
,repository_line_id
,repository_source
,processed_flag
,gl_date --Add by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
,st_inv_number --Add by Qiong for Advanced Receipts
,source_trx_type --Add by Qiong for Advanced Receipts
)
values
(
lr_trx_repo_extract.transaction_repository_id
,lr_trx_repo_extract.transaction_reference_id
,lr_trx_repo_extract.transaction_source
,lr_trx_repo_extract.party_name
,lr_trx_repo_extract.document_number
,lr_trx_repo_extract.document_date
,lr_trx_repo_extract.document_id
,lr_trx_repo_extract.document_line_id
,lr_trx_repo_extract.document_line_num
,lr_trx_repo_extract.document_line_item
,lr_trx_repo_extract.document_line_desc
,lr_trx_repo_extract.document_line_qty
,lr_trx_repo_extract.document_line_uom
,lr_trx_repo_extract.document_line_amt
,lr_trx_repo_extract.document_currency_code
,lr_trx_repo_extract.repository_tax_amt
,lr_trx_repo_extract.organization_name
,lr_trx_repo_extract.location_name
,lr_trx_repo_extract.organization_id
,lr_trx_repo_extract.location_id
,lr_trx_repo_extract.inventory_item_id
,lr_trx_repo_extract.party_id
,lr_trx_repo_extract.service_type_code
,lr_trx_repo_extract.repository_invoice_id
,lr_trx_repo_extract.repository_line_id
,lr_trx_repo_extract.repository_source
,NULL
,lr_trx_repo_extract.gl_date --Add by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
,lr_trx_repo_extract.st_inv_number --Add by Qiong for Advanced Receipts
,lr_trx_repo_extract.source_trx_type --Add by Qiong for Advanced Receipts
);
jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After insert into jai_trx_repo_extract_gt');
for r_docs in (select distinct transaction_source
, source_trx_type --Add by Qiong for Advanced Receipts
, document_id
, document_line_id
, repository_line_id --Add by Qiong for bug#13405553
, transaction_repository_id --Added by Qinglei on 28-Feb-2012 for bug#13741544
from jai_trx_repo_extract_gt gt
--Added by Chong.Lei for POT code port begin
where gt.repository_source in ('AP','AR'
--Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
, 'AP_REVERSAL'
, 'AP_CLAIM'
, 'AR_REVERSAL'
, 'AR_CLAIM'
--Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
)
--Added by Chong.Lei for POT code port end
)
loop
lr_trx_repo_extract := null;
, 'Before update into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_id ='|| lr_trx_repo_extract.document_id || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_id ='|| lr_trx_repo_extract.document_line_id || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_num ='|| lr_trx_repo_extract.document_line_num || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_item ='|| lr_trx_repo_extract.document_line_item || fnd_global.local_chr(10) ||
'lr_trx_repo_extract.document_line_desc ='|| lr_trx_repo_extract.document_line_desc
);
update jai_trx_repo_extract_gt
set transaction_source = lr_trx_repo_extract.transaction_source
, party_name = lr_trx_repo_extract.party_name
, document_number = lr_trx_repo_extract.document_number
, document_date = lr_trx_repo_extract.document_date
, document_id = lr_trx_repo_extract.document_id
, document_line_id = lr_trx_repo_extract.document_line_id
, document_line_num = lr_trx_repo_extract.document_line_num
, document_line_item = lr_trx_repo_extract.document_line_item
, document_line_desc = lr_trx_repo_extract.document_line_desc
, document_line_qty = lr_trx_repo_extract.document_line_qty
, document_line_uom = lr_trx_repo_extract.document_line_uom
, document_line_amt = lr_trx_repo_extract.document_line_amt
, repository_tax_amt = lr_trx_repo_extract.repository_tax_amt
, document_currency_code = lr_trx_repo_extract.document_currency_code
, inventory_item_id = lr_trx_repo_extract.inventory_item_id
, party_id = lr_trx_repo_extract.party_id
, organization_id = nvl(lr_trx_repo_extract.organization_id, organization_id)
, location_id = nvl(lr_trx_repo_extract.location_id,location_id)
, service_type_code = nvl(lr_trx_repo_extract.service_type_code, service_type_code)
, updatable_flag = lr_trx_repo_extract.updatable_flag
, processed_flag = lr_trx_repo_extract.processed_flag
where transaction_source = r_docs.transaction_source
and ( (r_docs.document_id is not null and document_id = r_docs.document_id)
or r_docs.document_id is null -- incase of order management it will be null
)
and source_trx_type = NVL( r_docs.source_trx_type, source_trx_type) --Add by Qiong for advanced receipts bug13361952
and repository_line_id = NVL( r_docs.repository_line_id,repository_line_id) --Add by Qiong for bug#13405553
and document_line_id = r_docs.document_line_id;
, 'Number of rows updated ='||sql%rowcount
);
for r_org in (select distinct organization_id from jai_trx_repo_extract_gt where organization_id is not null)
loop
jai_cmn_debug_contexts_pkg.print
(ln_reg_id
, 'OPEN/FETCH/CLOSE c_get_organization_name, r_org.organization_id='||r_org.organization_id
);
update jai_trx_repo_extract_gt
set organization_name = lv_organization_name
where organization_id = r_org.organization_id;
for r_loc in (select distinct location_id from jai_trx_repo_extract_gt where location_id is not null )
loop
jai_cmn_debug_contexts_pkg.print
(ln_reg_id
,'OPEN/FETCH/CLOSE c_get_location_name, r_loc.location_id='||r_loc.location_id
);
update jai_trx_repo_extract_gt
set location_name = lv_location_name
where location_id = r_loc.location_id;
select pov.vendor_name party_name
,poh.segment1 document_number
,poh.creation_date document_date
,poh.po_header_id document_id
,pol.po_line_id document_line_id
,pol.line_num document_line_num
,msi.segment1 document_line_item
,pol.item_description document_line_desc
,pol.quantity document_line_qty
,pol.unit_meas_lookup_code document_line_uom
,(pol.unit_price * pol.quantity) document_line_amt
,poh.currency_code document_currency_code
,pol.item_id inventory_item_id
,poh.vendor_id party_id
-- ,fsp.inventory_organization_id organization_id /* Commented by vkantamn for Bug#6083978 */
,hl.inventory_organization_id organization_id /* Added by vkantamn for Bug#6083978 */
,poll.ship_to_location_id location_id
from po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll /*6841116*/
, mtl_system_items msi
, po_vendors pov
, hr_locations hl /* Added by vkantamn for Bug#6083978 */
-- , financials_system_parameters fsp /* Commented by vkantamn for Bug#6083978 */
where
--poh.po_header_id = p_document_id /* Commented by vkantamn for Bug#6083978 */
poh.po_header_id = cp_header_id /* Added by vkantamn for Bug#6083978 */
and pol.po_header_id = poh.po_header_id
-- and pol.po_line_id = p_document_line_id /* Commented by vkantamn for Bug#6083978 */
and pol.po_line_id = cp_line_id /* Added by vkantamn for Bug#6083978 */
and pol.po_line_id = poll.po_line_id
and poll.po_header_id = poh.po_header_id
and pol.item_id = msi.inventory_item_id (+)
--and nvl(msi.organization_id ,fsp.inventory_organization_id )= fsp.inventory_organization_id /* Commented by vkantamn for Bug#6026463 */
and nvl(poll.ship_to_location_id,poh.ship_to_location_id )= hl.location_id /*Commented by nprashar for bug # 6636517 and poh.ship_to_location_id = hl.location_id */ /* Added by vkantamn for Bug#6083978 */
and pov.vendor_id = poh.vendor_id ;
select pov.vendor_name party_name
,rsh.receipt_num document_number
,rta.transaction_date document_date
,rsh.shipment_header_id document_id
,rsl.shipment_line_id document_line_id
,rsl.line_num document_line_num
,msi.segment1 document_line_item
,rsl.item_description document_line_desc
,rsl.quantity_received document_line_qty
,rsl.unit_of_measure document_line_uom
,(pla.unit_price * rsl.quantity_received) document_line_amt
,rsh.currency_code document_currency_code
,rsl.item_id inventory_item_id
,rsh.vendor_id party_id
,hl.inventory_organization_id organization_id
,rsl.ship_to_location_id location_id
from rcv_transactions rta
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines_all pla
, mtl_system_items msi
, po_vendors pov
, hr_locations_all hl
where
rta.transaction_id = cp_transaction_id
and rta.po_line_id = pla.po_line_id
and rta.shipment_header_id = rsh.shipment_header_id
and rsh.shipment_header_id = rsl.shipment_header_id
and rsh.vendor_id = pov.vendor_id
and rsl.item_id = msi.inventory_item_id
and rsl.to_organization_id = msi.organization_id
and nvl(rsl.ship_to_location_id,rsh.ship_to_location_id )= hl.location_id;
select hzp.party_name party_name
, oeh.order_number document_number
, oeh.ordered_date document_date
, oeh.header_id document_id
, oel.line_id document_line_id
, oel.line_number document_line_num
, msi.segment1 document_line_item
, substr(oel.user_item_description,1,240) document_line_desc
, oel.ordered_quantity document_line_qty
, oel.order_quantity_uom document_line_uom
, nvl(oel.unit_selling_price * oel.ordered_quantity,0) document_line_amt
, oeh.transactional_curr_code document_currency_code
, oel.inventory_item_id inventory_item_id
, oeh.sold_to_org_id party_id
, oel.ship_from_org_id organization_id
, oel.line_category_code line_category_code
from
oe_order_headers_all oeh
,oe_order_lines_all oel
,hz_parties hzp
,hz_cust_accounts hzca
,mtl_system_items msi
where (p_document_id is null or p_document_id = '' or oeh.header_id = p_document_id)
and oel.header_id = oeh.header_id
and oel.line_id = p_document_line_id
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and hzca.cust_account_id = oel.sold_to_org_id
and hzca.party_id = hzp.party_id ;
select hzp.party_name
,rct.trx_number document_number
,rct.trx_date document_date
,rct.customer_trx_id document_id
,rctl.customer_trx_line_id document_line_id
,rctl.line_number document_line_num
,msi.segment1 document_line_item
,rctl.description document_line_desc
,rctl.quantity_invoiced document_line_qty
,rctl.uom_code document_line_uom
,rctl.extended_amount document_line_amt
,rct.invoice_currency_code document_currency_code
,rctl.inventory_item_id inventory_item_id
,nvl(rct.sold_to_customer_id, rct.bill_to_customer_id) party_id
,jrct.organization_id organization_id
,jrct.location_id location_id
from ra_customer_trx_all rct
,ra_customer_trx_lines_all rctl
,jai_ar_trxs jrct
,hz_parties hzp
,hz_cust_accounts hzca
,mtl_system_items msi
where rct.customer_trx_id = p_document_id
and jrct.customer_trx_id = rct.customer_trx_id
and rctl.customer_trx_id = rct.customer_trx_id
and rctl.customer_trx_line_id = p_document_line_id
and rctl.inventory_item_id = msi.inventory_item_id (+)
and nvl(msi.organization_id,jrct.organization_id) = jrct.organization_id
and hzca.cust_account_id = nvl(rct.sold_to_customer_id, rct.bill_to_customer_id)
and hzca.party_id = hzp.party_id;
/*modified the below cusrsor query to select from the tables PA_DRAFT_INVOICES_ALL,
PA_PROJECTS_ALL instead of pa_draft_invoices_v.*/
cursor c_get_pa_details
is
select c.customer_name party_name,
p.segment1
||'/'
||padi.draft_invoice_num
document_number
, padi.creation_date document_date
, jpadi.draft_invoice_id document_id
, jpadil.draft_invoice_line_id document_line_id
, jpadil.line_num document_line_num
, null document_line_item
-- Modified by Jia for FP Bug#6691866, Begin
-------------------------------------------------------------------------------------------------
--, substr(padil.text,1,240) document_line_desc -- Comment by Jia for FP Bug#6691866
, (select substr(padil.text,1,240) from pa_draft_invoice_items padil
where padil.draft_invoice_num = jpadi.draft_invoice_num
and padil.project_id =jpadi.project_id
and padil.line_num = jpadil.line_num ) document_line_desc -- Added by Jia for FP Bug#6691866
-------------------------------------------------------------------------------------------------
-- Modified by Jia for FP Bug#6691866, End
, null document_line_qty
, null document_line_uom
, jpadil.line_amt document_line_amt
, padi.inv_currency_code document_currency_code
, null inventory_item_id
, padi.ship_to_customer_id party_id
, jpadi.organization_id organization_id
, jpadi.location_id location_id
, jpadil.service_type_code service_type_code
from
PA_DRAFT_INVOICES_ALL padi,
PA_PROJECTS_ALL p
-- ,pa_draft_invoice_items padil Removed by Jia for FP Bug#6691866
,jai_pa_draft_invoices jpadi
,jai_pa_draft_invoice_lines jpadil
,PA_CUSTOMERS_V c
where jpadi.draft_invoice_id = p_document_id
and jpadil.draft_invoice_line_id = p_document_line_id
and jpadi.draft_invoice_id = jpadil.draft_invoice_id
and jpadi.project_id = padi.project_id
and jpadi.draft_invoice_num = padi.draft_invoice_num
and p.project_id=padi.project_id
and padi.ship_to_customer_id=c.customer_id;
select service_type_code, sum(jpollt.tax_amount) service_tax_amount
from JAI_PO_LINE_LOCATIONS jpoll
,jai_po_taxes jpollt
where jpoll.po_line_id = cp_po_line_id
and jpollt.line_location_id = jpoll.line_location_id
/*added the cess and sh cess tax types for bug#6457710*/
and jpollt.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
--and jpollt.tax_type = 'Service'
group by service_type_code;
select service_type_code, sum(jrlt.tax_amount) service_tax_amount
from JAI_PO_LINE_LOCATIONS jpoll
,jai_rcv_line_taxes jrlt
,rcv_transactions rt
where jpoll.po_line_id = cp_po_line_id
and rt.po_line_id = cp_po_line_id
and rt.transaction_type = 'DELIVER'
and rt.shipment_header_id = jrlt.shipment_header_id
and rt.shipment_line_id = jrlt.shipment_line_id
and jrlt.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
group by service_type_code;
select jrctl.service_type_code,
--sum(jrcttl.tax_amount) service_tax_amount
abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0))) service_tax_amount--Add by Xiao for POT Phase III, reg bug#12895841.
--Added by Chong.Lei for POT code port end
from JAI_AR_TRX_LINES jrctl
,JAI_AR_TRX_TAX_LINES jrcttl
,jai_cmn_taxes_all jtc
--Added by Chong.Lei for POT code port begin
, jai_rgm_trx_records jrec --Add by Xiao for POT Phase III, reg bug#12895841.
, jai_rgm_trx_refs jref --Add by Xiao for POT Phase III, reg bug#12895841.
--Added by Chong.Lei for POT code port end
where jrctl.customer_trx_line_id = cp_customer_trx_line_id
and jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
and jtc.tax_id = jrcttl.tax_id
/*added the cess and sh cess tax types for bug#6457710*/
and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
--and jtc.tax_type = 'Service'
-- group by service_type_code; --Commented by Chong.Lei for POT code port
SELECT Sum(Nvl(jrec.DEBIT_AMOUNT,0))+Sum(Nvl(jrec.CREDIT_AMOUNT,0)) -- modified for the bug 14341945
FROM jai_rgm_trx_refs jref, jai_rgm_trx_records jrec
WHERE jref.invoice_id= cp_customer_trx_id
AND jref.item_line_id = cp_cust_trx_line_id
AND jrec.source_trx_type = p_source_trx_type--Added by Qinglei on 28-Feb-2012 for bug#13741544
AND jrec.reference_id=jref.reference_id
AND jrec.TAX_TYPE IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
SELECT Sum(Nvl(line_applied,0)) * max(((SELECT line_amount
FROM jai_ar_trx_lines
WHERE customer_trx_id = cp_customer_trx_id
AND customer_trx_line_id = cp_cust_trx_line_id)/
(SELECT line_amount
FROM jai_ar_trxs
WHERE customer_trx_id = cp_customer_trx_id
)))
FROM AR_RECEIVABLE_APPLICATIONS_ALL araa,jai_rgm_trx_records jrtr
WHERE araa.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
/*Added by Qinglei for bug#13741544 begin*/
AND araa.receivable_application_id = jrtr.source_document_id
AND jrtr.repository_id = p_repository_id
/*Added by Qinglei for bug#13741544 end*/
AND araa.status= jai_constants.ar_status_app;
select service_type_code, sum(jstl.tax_amount) service_tax_amount
from JAI_OM_OE_SO_LINES jsl
, JAI_OM_OE_SO_TAXES jstl
, jai_cmn_taxes_all jtc
where jsl.line_id = cp_line_id
and jsl.line_id = jstl.line_id
and jstl.tax_id = jtc.tax_id
AND nvl(jtc.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
/*added the cess and sh cess tax types for bug#6457710*/
and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
--and jtc.tax_type = 'Service'
group by service_type_code;
select service_type_code, sum(jrtl.tax_amount) service_tax_amount
from JAI_OM_OE_RMA_LINES jrl
, JAI_OM_OE_RMA_TAXES jrtl
, JAI_CMN_TAXES_ALL jtc
where jrl.rma_line_id = cp_line_id
and jrl.rma_line_id = jrtl.rma_line_id
and jrtl.tax_id = jtc.tax_id
AND nvl(jtc.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
/*added the cess and sh cess tax types for bug#6457710*/
and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
--and jtc.tax_type = 'Service'
group by service_type_code;
select sum(tax_amt) service_tax_amount
from jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jtc
where jcdt.source_doc_line_id = cp_line_id
and jcdt.source_doc_type = jai_constants.PA_DRAFT_INVOICE
and jcdt.tax_id = jtc.tax_id
AND nvl(jtc.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
/*added the cess and sh cess tax types for bug#6457710*/
and jtc.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
select rcv.organization_id organization_id
,rcv.deliver_to_location_id location_id --12560704
,rsl.po_header_id po_header_id
,rsl.po_line_id po_line_id
,rcv.transaction_id /*Bug 12902363*/
from
--jai_rcv_transactions rcv, 12560704
rcv_transactions rcv ,
rcv_shipment_lines rsl
where
rcv.shipment_header_id = rsl.shipment_header_id
and rcv.shipment_line_id = rsl.shipment_line_id
and rcv.shipment_header_id = p_document_id
and rcv.shipment_line_id = p_document_line_id
and rcv.transaction_type = 'RECEIVE';
select pov.vendor_name party_name
,apa.invoice_num document_number
,apa.creation_date document_date
,apa.invoice_id document_id
,apla.line_number document_line_id /*10434986*/
,apla.line_number document_line_num
,NULL document_line_item
,NULL document_line_desc
,NULL document_line_qty
,NULL document_line_uom
,decode(nvl(apla.amount,0),0,apla.original_amount,apla.amount) document_line_amt
/*Modified by Qinglei 13-Dec-2011 Bug#13405591*/
,jasl.currency_code document_currency_code
,NULL inventory_item_id
,apa.vendor_id party_id
,jasl.organization_id organization_id
,jasl.location_id location_id
from ap_invoices_all apa
, ap_invoice_lines_all apla
, po_vendors pov
, jai_ap_invoice_lines jasl
where
apa.invoice_id = p_document_id
and apa.invoice_id = apla.invoice_id
and apla.line_number = p_document_line_id
and jasl.invoice_id = apa.invoice_id
and jasl.invoice_line_number = apla.line_number
and pov.vendor_id = apa.vendor_id ;
SELECT abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0))) --Modified by Qiong for reverse charge bug#16001407
FROM jai_rgm_trx_refs jref,
jai_rgm_trx_records jrec
WHERE jref.invoice_id = p_document_id
AND jref.item_line_id = p_document_line_id
AND jrec.source_document_id = p_taxline_no
AND jrec.reference_id = jref.reference_id
AND jrec.source = decode(pv_document_source, 'STANDALONE_INVOICE', 'AP',pv_document_source)
AND jrec.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
SELECT organization_id, location_id
FROM JAI_AP_INVOICE_LINES
where invoice_id = p_document_id
--Add by Xiao for fixing bug#10634960, begin
--------------------------------------------------------------------
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM JAI_AP_INVOICE_LINES jail
WHERE jail.invoice_line_number = p_document_line_id
AND jail.invoice_id = p_document_id)
--------------------------------------------------------------------
--Add by Xiao for fixing bug#10634960, end
and PARENT_INVOICE_LINE_NUMBER is NULL;
Select service_type_code,sum(jcdt.tax_amt)
from jai_ap_invoice_lines jasl,
jai_cmn_document_taxes jcdt,
jai_cmn_taxes_all jcta
where source_doc_line_id = cp_line_id
AND jcdt.source_doc_id = cp_invoice_id
AND jasl.invoice_id = jcdt.source_doc_id
and jasl.invoice_line_number = jcdt.source_doc_line_id
and jcta.tax_id = jcdt.tax_id
and jcta.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess)
GROUP BY service_type_code;
SELECT nvl(sum(amount), 0)--Add nvl by Xiao for POT Phase III, reg bug#12895841.
-- Added by Chong.Lei for POT code port end
FROM ap_invoice_payments_all
WHERE invoice_id = p_document_id;
SELECT abs(nvl(SUM(aila.amount),0))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = p_document_id
AND aila.line_type_lookup_code = 'PREPAY';
select invoice_amount
from ap_invoices_all
where invoice_id = p_document_id;
SELECT open_amt, original_line_amt
FROM jai_st_invoice_reversal
WHERE invoice_id = p_document_id
AND line_num = p_document_line_id;
SELECT SUM(aip.amount)
FROM ap_invoice_payments_all aip
WHERE EXISTS (SELECT 1 FROM jai_rgm_trx_refs jref,
jai_rgm_trx_records jrec
WHERE jref.invoice_id = p_document_id
AND jref.item_line_id = p_document_line_id
AND jrec.reference_id = jref.reference_id
AND jrec.source = 'AP_CLAIM'
AND jrec.source_document_id = p_taxline_no
AND aip.invoice_id = jref.invoice_id
AND aip.invoice_payment_id = jrec.source_document_id);
SELECT abs(SUM(NVL(jrec.DEBIT_AMOUNT,0))-SUM(NVL(jrec.CREDIT_AMOUNT,0)))--add abs, change '+' to '-' by Xiao for POT, reg bug#12533434
--Modified by Qiong for reverse charge bug#16001407
FROM jai_rgm_trx_refs jref,
jai_rgm_trx_records jrec
WHERE jref.invoice_id = p_document_id
AND jref.item_line_id = p_document_line_id --Add by Xiao fro POT Phase III, reg bug#12895841. --Modified by Chong.Lei for POT code port
AND jrec.reference_id = jref.reference_id
AND jrec.source = decode(pv_document_source, 'STANDALONE_INVOICE', 'AP','RECEIVING','AP','PURCHASING','AP',pv_document_source)--Add pv_document_source by Xiao for POT Phase III, reg bug#12895841.
/* Added receiving and purchasing source conditions in above decode for bug 13430127 */
AND jrec.tax_type IN (jai_constants.tax_type_service,jai_constants.tax_type_service_edu_cess,jai_constants.tax_type_sh_service_edu_cess);
SELECT service_type_code
FROM jai_ap_invoice_lines
WHERE invoice_id = p_document_id
AND invoice_line_number = p_document_line_id;
select hzp.party_name
,acra.receipt_number document_number
,acra.receipt_date document_date
,acra.cash_receipt_id document_id
,acra.amount document_line_amt
,jacr.exchange_rate exchange_rate
,jacr.currency_code document_currency_code
,jacr.customer_id party_id
,jacr.organization_id organization_id
,jacr.location_id location_id
,jacr.service_type_code service_type_code
,acra.reversal_date reversal_date --Added by Qiong for bug13555753
from ar_cash_receipts_all acra,
jai_ar_cash_receipts_all jacr,
hz_parties hzp,
hz_cust_accounts hca
WHERE acra.cash_receipt_id = jacr.cash_receipt_id
AND jacr.document_type = 'Service'
AND hca.cust_account_id = jacr.customer_id
AND hzp.party_id = hca.party_id
AND acra.cash_receipt_id = p_document_id;
select hzp.party_name
,acra.receipt_number document_number
,araa.apply_date document_date
,araa.receivable_application_id document_id
,araa.acctd_amount_applied_from document_line_amt
--Modified by Qinglei on 05-Jan-2012 for bug#13556198
,araa.applied_customer_trx_id applied_customer_trx_id
,araa.applied_customer_trx_line_id applied_customer_trx_line_id
,jacr.currency_code document_currency_code
,jacr.customer_id party_id
,jacr.organization_id organization_id
,jacr.location_id location_id
,jacr.service_type_code service_type_code
/*Added by Qinglei on 05-Jan-2012 for bug#13556198 begin*/
,acra.currency_code receipt_currency_code
,acra.exchange_date receipt_exchange_date
,acra.exchange_rate receipt_exchange_rate
,acra.exchange_rate_type receipt_exchange_rate_type
,acra.set_of_books_id
/*Added by Qinglei on 05-Jan-2012 for bug#13556198 end*/
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
jai_ar_cash_receipts_all jacr,
hz_parties hzp,
hz_cust_accounts hca
WHERE araa.cash_receipt_id = acra.cash_receipt_id
AND acra.cash_receipt_id = jacr.cash_receipt_id
AND jacr.document_type = 'Service'
AND hca.cust_account_id = jacr.customer_id
AND hzp.party_id = hca.party_id
AND araa.receivable_application_id = p_document_id;
SELECT SUM(nvl(jrec.trx_debit_amount,0)) + SUM(nvl(jrec.trx_credit_amount,0))
FROM jai_rgm_trx_refs jref,
jai_rgm_trx_records jrec
WHERE jrec.source_document_id = p_document_id
AND jref.reference_id = jrec.reference_id
AND jrec.source_table_name = 'AR_CASH_RECEIPTS_ALL'
AND jrec.source = 'AR'
AND jrec.source_trx_type = p_source_trx_type
AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT SUM(nvl(jrec.trx_debit_amount,0)) + SUM(nvl(jrec.trx_credit_amount,0))
FROM jai_rgm_trx_refs jref,
jai_rgm_trx_records jrec
WHERE jrec.source_document_id = p_document_id
AND jref.reference_id = jrec.reference_id
AND jrec.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND jrec.source = 'AR'
AND jrec.source_trx_type = p_source_trx_type
AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT rctl.line_number
FROM ra_customer_trx_all rct
,ra_customer_trx_lines_all rctl
WHERE rct.customer_trx_id = p_document_id
and rctl.customer_trx_id = rct.customer_trx_id
and rctl.customer_trx_line_id = p_document_line_id;
SELECT rct.trx_date document_date
FROM jai_trx_repo_extract_gt gt,
ra_customer_trx_all rct
WHERE gt.document_id = p_document_id
AND gt.document_line_id = p_document_line_id
AND gt.transaction_source = p_document_source
AND gt.repository_invoice_id = rct.customer_trx_id;
SELECT to_date(jrr.attribute_value, 'DD/MM/YYYY')
FROM jai_rgm_registrations jrr
, jai_rgm_definitions jrd
WHERE jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
AND jrr.attribute_code = 'EFF_DATE_ST_PT'
AND jrr.attribute_type_code = 'OTHERS'
AND jrr.registration_type = 'OTHERS';
SELECT SUM(nvl(jrec.debit_amount,0)) + SUM(nvl(jrec.credit_amount,0)) --Modified by Qiong for reverse charge bug#16001407
FROM jai_rgm_trx_refs jref,
jai_rgm_trx_records jrec
WHERE jref.invoice_id = p_document_id
AND jref.reference_id = jrec.reference_id
AND jref.item_line_id = p_document_line_id
--AND jrec.source_document_id = p_trx_repo_extract.document_line_num
AND jrec.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND jrec.source = 'AR_CLAIM'
AND jrec.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
select msl.segment1
FROM mtl_system_items msl
WHERE msl.inventory_item_id = p_inventory_item_id
and msl.organization_id = p_organization_id;
SELECT aila.line_number, aila.match_type
FROM ap_invoice_distributions_all aida,
ap_invoice_lines_all aila
WHERE aida.invoice_id = p_document_id
AND aida.invoice_distribution_id = p_document_line_id
AND aida.invoice_line_number = aila.line_number
AND aila.invoice_id = p_document_id;
SELECT po.ship_to_organization_id, po.ship_to_location_id
FROM po_line_locations_all po,ap_invoice_lines_all ap
WHERE po.line_location_id = ap.po_line_location_id
AND ap.invoice_id = p_document_id
AND ap.line_number = pn_line_number;
SELECT rcv.organization_id, rcv.location_id
FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
WHERE ap.rcv_transaction_id = rcv.transaction_id
AND ap.invoice_id = p_document_id
AND ap.line_number = pn_line_number;
SELECT pov.vendor_name party_name
,apa.invoice_num document_number
,apa.invoice_date document_date /* modified creation_date to invoice_date -13430127 */
,apa.invoice_id document_id
,aida.invoice_line_number document_line_id
,aida.invoice_line_number document_line_num
,NULL document_line_item
,NULL document_line_desc
,aida.quantity_invoiced document_line_qty --,NULL/*Modified for Bug 13430127 */
,aida.matched_uom_lookup_code document_line_uom --,NULL/*Modified for Bug 13430127 */
,decode(nvl(apla.amount,0),0,apla.original_amount,apla.amount) document_line_amt -- ,apla.amount /* Modified for Bug 13430127 */
,apa.payment_currency_code document_currency_code
,apla.inventory_item_id inventory_item_id
,apa.vendor_id party_id
,NULL organization_id
,NULL location_id
from ap_invoices_all apa
, ap_invoice_lines_all apla
, po_vendors pov
, ap_invoice_distributions_all aida
where
apa.invoice_id = p_document_id
AND aida.invoice_id = p_document_id
AND aida.invoice_distribution_id = p_document_line_id
AND apa.invoice_id = apla.invoice_id
AND apla.line_number = aida.invoice_line_number
AND pov.vendor_id = apa.vendor_id ;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM jai_rgm_definitions
where regime_code = jai_constants.service_regime
)
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM jai_rgm_definitions
where regime_code = jai_constants.service_regime
)
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = (SELECT regime_id
FROM jai_rgm_definitions
where regime_code = jai_constants.service_regime
)
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_id = p_document_id
AND invoice_line_number = cp_line_num;
SELECT rda.gl_date
FROM ra_cust_trx_line_gl_dist_all rda,
ra_customer_trx_lines_all rla
WHERE rda.customer_trx_id = p_document_id
AND rda.account_class = 'REV'
AND rda.customer_trx_line_id = rla.customer_trx_line_id
--Modified by Qinglei 27-Dec-2011 for bug#13531399
AND rla.customer_trx_line_id = p_document_line_id
AND rla.customer_trx_id = p_document_id;
OPEN get_ap_gl_date_cur(p_trx_repo_extract.document_line_num);-- Updated by Wenqiong for bug13462951 on Feb 09, 2012, add a parameter line num
select reference_id
, source
, invoice_id
, item_line_id
, line_id /*Added by nprashar for bug # 7172723*/
from jai_rgm_trx_refs refs
where refs.reference_id = p_reference_id;
select source
from ap_invoices_all aia
where aia.invoice_id in
(select invoice_id
from jai_rgm_Trx_refs refs
where refs.reference_id = p_reference_id);
select inv_dist_id,
line_num
from
(select
INVOICE_DISTRIBUTION_ID inv_dist_id,
row_number() over(ORDER BY INVOICE_DISTRIBUTION_ID) line_num
from ap_invoice_distributions_all
where INVOICE_ID=p_invoice_id
)
where inv_dist_id=p_line_id;
SELECT shipment_header_id,
shipment_line_id,
receipt_num,
creation_date,
qty_received,
tax_amount,
organization_id,
inventory_item_id,
uom_code,
location_id,
vendor_id,
vendor_site_id
FROM
(SELECT jrt.shipment_header_id shipment_header_id,
jrt.shipment_line_id shipment_line_id,
jrt.receipt_num receipt_num,
jrt.creation_date creation_date,
jrt.qty_received qty_received,
jrtxl.tax_amount tax_amount,
jrt.organization_id organization_id,
jrt.inventory_item_id inventory_item_id,
jirt.uom_code uom_code,
jirt.location_id location_id,
jrti.vendor_id vendor_id,
jrti.vendor_site_id vendor_site_id,
row_number() over(
ORDER BY jrtxl.shipment_line_id,jrtxl.tax_line_no) rn
FROM jai_rcv_lines jrt,
jai_rcv_transactions jirt,/* modified by vumaasha for bug 8965721 */
jai_rcv_tp_invoices jrti,
jai_rcv_line_taxes jrtxl -- join to ja_in_receipt_tax_lines added by vumaasha for 6856213
WHERE jrt.shipment_header_id = jrti.shipment_header_id
AND jrti.invoice_id = p_invoice_id
AND jrti.shipment_header_id = jirt.shipment_header_id
AND jirt.transaction_type = 'RECEIVE'
AND jirt.shipment_line_id = jrt.shipment_line_id
AND jrtxl.shipment_header_id = jirt.shipment_header_id
AND jrtxl.shipment_header_id = jrti.shipment_header_id
AND jirt.shipment_line_id = jrtxl.shipment_line_id)
WHERE rn =p_row_number ;
select service_type_code
from
jai_cmn_vendor_sites
where vendor_id= p_vendor_id
and vendor_site_id=p_vendor_site_id;
procedure update_service_type ( p_process_flag out nocopy varchar2
, p_process_message out nocopy varchar2
)
is
cursor c_get_recs_to_update
is
select *
from jai_trx_repo_extract_gt
where processed_flag = jai_constants.NO;
lv_member_name := 'UPDATE_SERVICE_TYPE';
jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Begin loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
for rec in c_get_recs_to_update
loop
-- For each record in temporary table which is not yet processed
jai_cmn_debug_contexts_pkg.print (ln_reg_id
,'rec.transaction_source='||rec.transaction_source || fnd_global.local_chr(10) ||
'rec.document_id='||rec.document_id || fnd_global.local_chr(10) ||
'rec.document_line_id='||rec.document_line_id || fnd_global.local_chr(10) ||
'rec.service_type_code='||rec.service_type_code
);
if rec.transaction_source in ('PURCHASING', 'RECEIVING') /*Updated by nprashar for bug # 6841116*/ then
update JAI_PO_LINE_LOCATIONS set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where po_header_id = rec.document_id
and po_line_id = rec.document_line_id;
update JAI_OM_OE_SO_LINES set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where header_id = rec.document_id
and line_id = rec.document_line_id;
update JAI_AR_TRX_LINES set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where customer_trx_id = rec.document_id
and customer_trx_line_id = rec.document_line_id;
update jai_rgm_manual_trxs
set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
where transaction_number = rec.document_id;
update jai_rgm_dis_src_hdrs
set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where transfer_id = rec.document_id;
,'No of rows updated in trx table='||sql%rowcount
);
update jai_rgm_trx_records
set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where source_document_id = rec.document_id
and source = 'SERVICE_DISTRIBUTE_IN';
,'No of rows updated in repository table for source SERVICE_DISTRIBUTE_IN='||sql%rowcount
);
update jai_pa_draft_invoice_lines
set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where draft_invoice_id = rec.document_id
and draft_invoice_line_id = rec.document_line_id;
update jai_ar_trx_lines
set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
, last_update_login = lv_login_id
where customer_trx_id = rec.repository_invoice_id
and customer_trx_line_id = rec.repository_line_id;
,'Rows updated in ja_in_ra_customer_trx_lines='||sql%rowcount
);
,'No of rows updated in trx table='||sql%rowcount
);
update jai_rgm_trx_records
set service_type_code = rec.service_type_code
, last_update_date = sysdate
, last_updated_by = lv_user_id
where repository_id = rec.transaction_repository_id;
,'No of rows updated in jai_rgm_trx_records table='||sql%rowcount
);
update jai_trx_repo_extract_gt
set processed_flag = 'Y'
where transaction_repository_id = rec.transaction_repository_id;
,'No of rows updated in jai_trx_repo_extract_gt table='||sql%rowcount
);
jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'End loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
end update_service_type;
select rct.created_from
, rct.interface_header_context
, rct.interface_header_attribute1 -- holds order number if context is ORDER_ENTRY
, rctl.interface_line_attribute6 -- holds order line id if context is ORDER_ENTRY
, rctl.interface_line_attribute1 -- 5876390, 6012570, holds PROJECT_NUMBER if context is PROJECTS INVOICES
, rctl.interface_line_attribute2 -- 5876390, 6012570, holds DRAFT_INVOICE_NUM if context is PROJECTS_INOVICES
, rctl.global_attribute_category ----Added by zhiwei for Bug#12604133
from ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
where rct.customer_trx_id = cp_customer_trx_id
and rct.customer_trx_id = rctl.customer_trx_id
and rctl.customer_trx_line_id = cp_customer_trx_line_id;
select pod.po_header_id
,pod.po_line_id
,apd.rcv_transaction_id /*Added by vkantamn for Bug#6083978*/
from po_distributions_all pod
,ap_invoice_distributions_all apd
where pod.po_distribution_id = apd.po_distribution_id
and apd.invoice_id = cp_invoice_id
and apd.invoice_distribution_id = cp_distribution_id;
select location_id
from jai_ar_trxs
where customer_trx_id = cp_customer_trx_id ;
select draft_invoice_id
,draft_invoice_line_id
from jai_pa_draft_invoice_lines jpdil
,pa_projects_all ppa
where ppa.segment1 = cp_project_number
and ppa.project_id = jpdil.project_id
and jpdil.draft_invoice_num = cp_draft_inv_num
and jpdil.line_num = cp_line_num;
select shipment_header_id,shipment_line_id
from rcv_transactions
where transaction_id = cp_rcv_trans_id;
select recs.service_type_code
from jai_rgm_trx_records recs
,jai_rgm_trx_refs refs
,ra_customer_trx_lines_all ractl
where ractl.interface_line_attribute6 = p_document_line_id
and ractl.interface_line_context = 'ORDER ENTRY'
and ractl.line_type = 'LINE'
and ractl.customer_trx_line_id = refs.item_line_id
and refs.reference_id = recs.reference_id
and recs.settlement_id is not null
and recs.service_type_code is not null
and recs.regime_code = 'SERVICE'
and recs.source = 'AR';
select recs.service_type_code
from jai_rgm_trx_records recs
,jai_rgm_trx_refs refs
where refs.item_line_id = p_document_line_id
and recs.reference_id = refs.reference_id
and recs.settlement_id is not null
and recs.service_type_code is not null
and recs.regime_code = 'SERVICE'
and recs.source = 'AR';
select recs.service_type_code
from jai_rgm_trx_records recs
,jai_rgm_trx_refs refs
,po_distributions_all pod
,ap_invoice_distributions_all apd
where pod.po_line_id = p_document_line_id
and pod.po_distribution_id = apd.po_distribution_id
and apd.invoice_distribution_id = refs.item_line_id
and recs.reference_id = refs.reference_id
and recs.settlement_id is not null
and recs.service_type_code is not null
and recs.regime_code = 'SERVICE'
and recs.source = 'AP';