[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. 27-Sep-2007 Bill.Shi 120.11 Add the logic for AP standalone invoice.
9. 25-Feb-2008 rchandan 6843479 120.12 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
10. 08-Apr-2008 Lion 6977917 120.17 Issue: The standalone inovice 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 'standalone'
in the procedure 'extract_rgm_trxs'.
11. 22-Apr-2008 Lion 6991108 120.18 Issue: SERVICE TYPE IN SERVICE TAX REPOSITORY IS BLANK
Fix: Modify cursor l_get_si_tax_amt_csr in Procedure get_document_details to get the right value.
12. 19-May-2008 Changes by nprashar for bug #6636517 , added a NVL clause in join condition of cursor c_get_po_details.
13 . 4-Aug-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
--------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------- 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 (recs.repository_id) repository_id
, nvl(refs.reference_id, recs.reference_id) reference_id
, refs.invoice_id
, refs.item_line_id
, recs.source
, recs.service_type_code
, nvl(recs.organization_id, recs.inv_organization_id) organization_id
, recs.location_id
, (nvl(trx_credit_amount,0) + nvl(trx_debit_amount,0)) repository_tax_amt
, recs.organization_type
, recs.source_document_id
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'
);
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;
, '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
)
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
);
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
, document_id
, document_line_id
from jai_trx_repo_extract_gt gt
where gt.repository_source in ('AP','AR')
)
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 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 /*6843479*/
, 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 poh.ship_to_location_id = hl.location_id --Added by vkantamn for Bug#6083978 */
and pov.vendor_id = poh.vendor_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
pov.vendor_name party_name
, apa.invoice_num document_number
, apa.creation_date document_date
, apa.invoice_id document_id
, NULL document_line_id
, apla.line_number document_line_num
, NULL document_line_item
, NULL document_line_desc
, NULL document_line_qty
, NULL document_line_uom
, apla.amount document_line_amt
, 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 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
, substr(padil.text,1,240) document_line_desc
, 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
,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(jrcttl.tax_amount) service_tax_amount
from JAI_AR_TRX_LINES jrctl
,JAI_AR_TRX_TAX_LINES jrcttl
,jai_cmn_taxes_all jtc
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;
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
/*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
/*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
/*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
jasl.service_type_code
, SUM (jcdt.tax_amt)
FROM
jai_ap_invoice_lines jasl
, jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
WHERE jasl.invoice_id = jcdt.source_doc_id
AND jasl.invoice_line_number = jcdt.source_doc_line_id
AND jcdt.source_doc_line_id = lp_line_id
AND jcdt.source_doc_id = p_document_id
AND jcta.tax_id = jcdt.tax_id
AND jcta.tax_type = jai_constants.tax_type_service
GROUP BY jasl.service_type_code;
SELECT
SUM (jcdt.tax_amt)
FROM
jai_ap_invoice_lines jasl
, jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
WHERE jasl.invoice_id = p_document_id
AND jasl.parent_invoice_line_number = lp_line_id
AND jasl.invoice_id = jcdt.source_doc_id
AND jasl.invoice_line_number = jcdt.source_doc_line_id
AND jcdt.tax_id =jcta.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 jasl.service_type_code;
SELECT
jasl.service_type_code
FROM
jai_ap_invoice_lines jasl
WHERE invoice_id = p_document_id
AND invoice_line_number = lp_line_id
;
select rcv.organization_id organization_id
,rcv.location_id location_id
,rsl.po_header_id po_header_id
,rsl.po_line_id po_line_id
from jai_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';
-- Deleted by Lion for bug#6911533 on 2008/04/22
/*
OPEN l_get_si_tax_amt_csr(lp_line_id => p_document_line_id);
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,
rcv_transactions jirt,
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
);
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_AP_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 invoice_id = rec.document_id
and invoice_line_number = rec.DOCUMENT_LINE_NUM;
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
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
jail.invoice_id
, jail.invoice_line_number
FROM
jai_ap_invoice_lines jail
, ap_invoice_distributions_all aida
WHERE jail.invoice_id = aida.invoice_id
AND jail.invoice_line_number = aida.invoice_line_number
AND jail.invoice_id = lp_invoice_id
AND aida.invoice_line_number = lp_invoice_line_number; -- Modified by Lion for bug#6977917
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';
select recs.service_type_code
from jai_rgm_trx_records recs
,jai_rgm_trx_refs refs
where refs.invoice_id = p_document_id
and 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 = 'AP';