[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from ap_invoice_distributions_all
where invoice_id = cp_trx_id;
select aida.*
from ap_invoice_distributions_all aida
where aida.invoice_id = cp_trx_id
and aida.line_type_lookup_code = 'PREPAY'
and aida.amount > 0
and aida.prepay_distribution_id is not null
and not exists (select 1
from jai_ap_tds_prepayments
where invoice_id = cp_trx_id
and invoice_distribution_id_prepay = aida.parent_reversal_id
and unapply_flag = 'Y');
select invoice_id
,invoice_distribution_id
,match_status_flag
from ap_invoice_distributions_all
where invoice_id = cp_trx_id
and match_status_flag is not null;
select *
from OE_ORDER_LINES_ALL oola
where oola.HEADER_ID = cp_header_id
and not exists (select 1
from JAI_OM_OE_RMA_LINES joorl
where joorl.RMA_HEADER_ID = cp_header_id
and oola.LINE_ID = joorl.RMA_LINE_ID)
and not exists (select 1
from JAI_OM_OE_SO_LINES joosl
where joosl.HEADER_ID = cp_header_id
and oola.LINE_ID = joosl.LINE_ID);
cursor c_order_lines_update(cp_header_id OE_ORDER_LINES.HEADER_ID%TYPE)
is
select *
from OE_ORDER_LINES oola
where oola.HEADER_ID = cp_header_id
and ( exists (select 1
from JAI_OM_OE_SO_LINES joosl
where joosl.HEADER_ID = cp_header_id
and oola.LINE_ID = joosl.LINE_ID)
or exists (select 1
from JAI_OM_OE_RMA_LINES joorl
where joorl.RMA_HEADER_ID = cp_header_id
and oola.LINE_ID = joorl.RMA_LINE_ID));
select *
from JAI_OM_OE_SO_LINES
where HEADER_ID = cp_header_id
and LINE_ID = cp_line_id;
select *
from JAI_OM_OE_RMA_LINES
where RMA_HEADER_ID = cp_header_id
and RMA_LINE_ID = cp_line_id;
select * from AP_INVOICE_LINES_ALL
where invoice_id = cp_invoice_id
and line_number= cp_line_id;
lv_action := JAI_CONSTANTS.inserting;
Need update the latest match status flag before perform prepay application,
otherwise jai_ap_tds_generation_pkg.status_update_chk_validate will prevent from unappying.
*/
FOR inv_dist_rec in c_get_inv_dist(p_transaction_rec.TRX_ID)
LOOP
update jai_ap_tds_inv_taxes
set match_status_flag = inv_dist_rec.match_status_flag
where invoice_id = inv_dist_rec.invoice_id
and invoice_distribution_id = inv_dist_rec.invoice_distribution_id;
(p_event_class_rec.tax_event_type_code IN ('UPDATE') AND --Added update by Chong for open interface issue 2013/04/09
p_event_class_rec.event_type_code IN ('STANDARD UPDATED')
)
) THEN
jai_tax_processing_pkg.open_interface
( p_event_class_rec => p_event_class_rec,
pv_return_status => x_return_status
);
lv_action := JAI_CONSTANTS.inserting;
lv_action := JAI_CONSTANTS.inserting;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call jai_po_proc_pkg.delete_po_taxes and delete_jai_po_lines with p_trx_line_id ' || p_trx_line_id );
jai_po_proc_pkg.delete_po_taxes(pn_line_loc_id => p_trx_line_id );
jai_po_proc_pkg.delete_jai_po_lines(pn_line_loc_id => p_trx_line_id);
lv_action := JAI_CONSTANTS.inserting;
FOR rec_ool in c_order_lines_update (p_event_class_rec.trx_id)
LOOP
if (rec_ool.LINE_CATEGORY_CODE = 'ORDER') then
open c_jai_so_lines (rec_ool.HEADER_ID, rec_ool.LINE_ID);
jai_om_tax_processing_pkg.update_tax (
pr_old => rec_old_ool ,
pr_new => rec_ool ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
sales order form should also update the same and redefault the taxes. This
has been achieved ny deleteing the order and tax lines in the
ja_in_oe_order_lines_aiu_trg trigger and then the inserts the same in the
normal course of execution.
2. Copy of Legacy Rma return order to another return order has also been made
possible due to this fix.
3. When ever a copied line is split then the line number and shipment line
number were not getting updated in the Localised Sales Order Line.
*/
jai_om_tax_processing_pkg.populate_tax (
pr_old => rec_old_ool ,
pr_new => rec_ool ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
select *
from ap_invoice_distributions_all
where invoice_id = cp_trx_id
and match_status_flag is not null
and line_type_lookup_code <> 'PREPAY'; --Added by Chong for bug#13802244, sync flag should exclude prepayements line
select aida.*
from ap_invoice_distributions_all aida
where aida.invoice_id = cp_trx_id
and line_type_lookup_code = 'PREPAY'
and amount < 0
and prepay_distribution_id is not null
and not exists (select 1
from jai_ap_tds_prepayments
where invoice_id = cp_trx_id
and invoice_distribution_id_prepay = aida.invoice_distribution_id);
lv_action := JAI_CONSTANTS.inserting;
Need update the latest match status flag before perform prepay application,
otherwise jai_ap_tds_generation_pkg.status_update_chk_validate will prevent from Appying.
*/
FOR inv_dist_rec in c_get_inv_dist(p_event_class_rec.TRX_ID)
LOOP
update jai_ap_tds_inv_taxes
set match_status_flag = inv_dist_rec.match_status_flag
where invoice_id = inv_dist_rec.invoice_id
and (invoice_distribution_id = inv_dist_rec.invoice_distribution_id
or invoice_distribution_id = 1
);
select set_of_books_id
from ap_invoices_all
where invoice_id = cp_trx_id;
select set_of_books_id
from ra_customer_trx_all
where customer_trx_id = cp_trx_id;
select org_id
from oe_order_headers_all
where header_id = cp_trx_id;
SELECT org_id
FROM PO_REQUISITION_HEADERS_ALL
WHERE REQUISITION_HEADER_ID = cp_trx_id;
SELECT org_id
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = cp_trx_id;
select set_of_books_id
from hr_operating_units
Where organization_id = cp_org_id;
if pn_application_id = 200 then -- called from AP --Updated by Cholei for bug#14189751
--derive ledger id from invoice id
open c_get_ledger_id(pn_trx_id); ----Updated by Cholei for bug#14189751
fetch c_get_om_org_id into ln_org_id; --Updated by Chong from l_ledger_id 20130419
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
SELECT *
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_LINE_ID = p_customer_trx_line_id;
SELECT *
FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_LINE_ID = p_customer_trx_line_id;
SELECT customer_trx_line_id
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = p_customer_trx_id
AND LINE_TYPE = 'LINE';
lv_action := JAI_CONSTANTS.inserting;
/*SELECT ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_id(i)
INTO ln_trx_line_id
FROM DUAL
WHERE ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_id(i) = ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.trx_id
AND ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.entity_code(i) = 'TRANSACTIONS'
--AND ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.event_class_code(i) = 'INVOICE'
--AND ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.event_type_code(i) = 'INV_CREATE'
;*/
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
SELECT *
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
SELECT *
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_LINE_ID = p_customer_trx_line_id;
JAI_AR_IMPORT_SYNC_PKG.update_trx_number(
--JAI_AR_RCTA_TRIGGER_PKG.ARU_T2(
pr_old => t_jai_ar_trx_rec
,pr_new => t_ra_customer_trx_rec
,pv_action => lv_action
,pv_return_code => lv_return_code
,pv_return_message => lv_return_message
);
JAI_AR_IMPORT_SYNC_PKG.update_complete_flag(
--JAI_AR_RCTA_TRIGGER_PKG.ARU_T4(
pr_old => t_jai_ar_trx_rec
,pr_new => t_ra_customer_trx_rec
,pv_action => lv_action
,pv_return_code => lv_return_code
,pv_return_message => lv_return_message
);
SELECT aia.invoice_id
, aia.source
, aia.vendor_id
FROM ap_invoices_all aia
WHERE aia.invoice_id = cp_invoice_id;
SELECT count(1) cnt
FROM jai_ap_tds_thhold_grps
WHERE vendor_id = cp_vendor_id;
SELECT invoice_to_tds_authority_id invoice_id
, invoice_to_tds_authority_num invoice_num
FROM jai_ap_tds_thhold_trxs
WHERE invoice_id = cp_invoice_id;
SELECT payment_status_flag
FROM ap_invoices_all
WHERE invoice_id = cp_invoice_id;
| Purpose : Call CREATE_ACCOUNTING to insert jai journal entry to gl interface table |
| TDD Reference : Section 8.5 |
| Assumptions : |
| Called From : ZX_SRVC_TYP_PKG.synchronize_tax |
|--------------------------------------------------------------------------------------------------------------------------------|
| parameters IN/OUT Type Required Description and Purpose |
| ------------ -------- ------ ---------- ------------------------- |
| pv_return_status OUT VARCHAR2 YES Return status |
| |
---------------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE CREATE_ACCOUNTING(pv_return_status OUT NOCOPY VARCHAR2) IS
-- cursor to get invoice distributions
cursor c_get_inv_dists(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
is
select aida.*
from ap_invoice_distributions_all aida
where aida.invoice_id = cp_trx_id;
| Purpose : Insert jai journal entry to gl interface table |
| TDD Reference : Section 8.5 |
| Assumptions : |
| Called From : JAI_TAX_PROCESSING_PKG.create_accounting |
|--------------------------------------------------------------------------------------------------------------------------------|
| parameters IN/OUT Type Required Description and Purpose |
| ------------ -------- ------ ---------- ------------------------- |
| pr_rec IN RECORD yes ap distribution record |
| pv_action IN VARCHAR2 yes distribution level action |
| pv_return_code IN NUMBER yes Return status |
| pv_return_message IN VARCHAR2 yes Return message |
---------------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE CREATE_ACCOUNTING
( pr_rec ap_invoice_distributions_all%rowtype ,
pv_action varchar2 ,
pv_return_code out nocopy varchar2 ,
pv_return_message out nocopy varchar2
)
IS
Cursor check_loc_tax(ln_invoice_id number) is
select 'Y'
from JAI_AP_MATCH_INV_TAXES
where invoice_id = ln_invoice_id ;
SELECT invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = pr_rec.invoice_id;
SELECT jcta.tax_type
FROM jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
, jai_ap_invoice_lines jail
, jai_rgm_registrations jrr
, jai_rgm_definitions jrd
WHERE jcdt.source_doc_id = pr_rec.invoice_id
AND jail.invoice_id = pr_rec.invoice_id
AND jail.invoice_line_number = jcdt.source_doc_line_id
AND jail.invoice_line_number = pr_rec.invoice_line_number
AND jcdt.modvat_flag = 'Y'
AND jcdt.tax_id = jcta.tax_id
AND jcta.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime;
SELECT jamt.tax_amount,jcta.tax_type
FROM jai_ap_match_inv_taxes jamt
, jai_cmn_taxes_all jcta
, jai_rgm_registrations jrr
, jai_rgm_definitions jrd
WHERE jamt.invoice_id = pr_rec.invoice_id
AND jamt.invoice_line_number = pr_rec.invoice_line_number
AND jamt.tax_id=jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND jcta.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime;
SELECT line.match_type,line.line_number
FROM ap_invoice_lines_all line, jai_ap_match_inv_taxes jamt
WHERE line.invoice_id = pr_rec.invoice_id
and jamt.invoice_id=line.invoice_id
and jamt.invoice_distribution_id=pr_rec.invoice_distribution_id
and jamt.po_distribution_id=pr_rec.po_distribution_id
and line.line_number=jamt.parent_invoice_line_number
-- AND line.po_distribution_id = line.po_distribution_id
AND line.line_type_lookup_code ='ITEM'
AND line.match_type IS NOT NULL;
SELECT exchange_date
, exchange_rate
, exchange_rate_type
, invoice_date
, invoice_currency_code
FROM ap_invoices_all
WHERE invoice_id = pr_rec.invoice_id ;
SELECT regime_id
FROM jai_rgm_definitions
WHERE regime_code = jai_constants.service_regime;
SELECT organization_id,location_id
FROM jai_ap_invoice_lines
WHERE invoice_id = pr_rec.invoice_id
AND invoice_line_number = (SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = pr_rec.invoice_line_number
AND jail.invoice_id = pr_rec.invoice_id)
AND parent_invoice_line_number IS NULL;
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 = pr_rec.invoice_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 = pr_rec.invoice_id
AND ap.line_number = pn_line_number;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
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 = p_regime_id
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 = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT cancelled_date
FROM ap_invoices_all
WHERE invoice_id = pr_rec.invoice_id;
select 'Y'
from jai_cmn_journal_entries jje
where jje.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND jje.source_trx_id = P_INVOICE_DISTRIBUTION_ID;
SELECT 'Y'
FROM ap_invoice_distributions_all
WHERE invoice_id = pr_rec.invoice_id
AND invoice_distribution_id = pr_rec.parent_reversal_id
AND period_name = pr_rec.period_name;
select ai.*
from ap_invoices_all ai
where ai.invoice_id = cp_trx_id;
select ail.*
from ap_invoice_lines_all ail
where ail.invoice_id = cp_invoice_id
and ail.match_type in ('ITEM_TO_RECEIPT', 'ITEM_TO_PO', 'PO_PRICE_ADJUSTMENT')
and ail.discarded_flag = 'N';
select apsa.*
from ap_payment_schedules_all apsa
where apsa.invoice_id = cp_trx_id;
select parent_inv_payment_priority
from jai_ap_tds_thhold_trxs
where invoice_to_vendor_id = cp_invoice_id;
select *
from JAI_PO_RCV_TAXES_V jpr
where jpr.match_type = cp_match_type
and jpr.trx_id = cp_trx_id
and jpr.vendor_id = cp_vendor_id;
select sum(nvl(amount,0))
from ap_invoice_lines_all
where invoice_id=cp_trx_id
and line_type_lookup_code in ('ITEM', 'MISCELLANEOUS', 'FREIGHT') --Added by Chong for bug#16743694 2013/05/07
;
(p_event_class_rec.tax_event_type_code IN ('UPDATE') AND --Added update by Chong for open interface issue 2013/04/09
p_event_class_rec.event_type_code IN ('STANDARD UPDATED')
)
) THEN
lv_action := JAI_CONSTANTS.inserting;
update ap_payment_schedules_all
set payment_priority = ln_parent_inv_payment_priority
where payment_num = inv_ps_rec.payment_num
and invoice_id = inv_rec.invoice_id;
update ap_invoices_all
set invoice_amount = nvl(l_total_line_amount,0)
where invoice_id= p_event_class_rec.trx_id;