[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT jg_info_v5
FROM jg_zz_vat_trx_gt;
SELECT SUBSTR(ven.vendor_name, 1, 10) ven_name
,SUBSTR(ven.segment1, 1, 8) ven_no
,inv.invoice_type_lookup_code inv_type
,SUM(nvl(dis.base_amount, dis.amount)) tax_amt
,item.tax_recovery_rate rec_per
,NULL company
,NULL acc_no
,tax.percentage_rate tax_rate
,tax.tax_rate_id tax_id
,tax.offset_tax_rate_code offset_tax_rate_code
,inv.global_attribute1 tax_type
,SUBSTR(inv.invoice_num, 1, 10) inv_no
,MIN(dis.accounting_date) acc_date
,inv.invoice_id invoice_id
,inv.cancelled_date cancelled_date
,COUNT(dis.charge_applicable_to_dist_id) item_line_cnt
,MAX(dis.charge_applicable_to_dist_id) charge_dist_id
,chk.void_date check_void_date
,SUM(NVL(aip.invoice_base_amount, aip.amount)) pay_amt
,dis.line_type_lookup_code line_type_lookup_code
,item.line_type_lookup_code line_type_lookup_code_item
,ppdis.line_type_lookup_code line_type_lookup_code_prepay
,item.reversal_flag reversal_flag_item
,aip.reversal_flag reversal_flag_pay
,ppdis.reversal_flag reversal_flag_prepay
,dis.parent_reversal_id parent_reversal_id
,inv.base_amount base_amount
,inv.invoice_amount invoice_amount
,chk.void_date void_date
,chk.future_pay_due_date future_pay_due_date
,chk.check_date check_date
,inv.payment_status_flag payment_status_flag
,aip.accounting_date accounting_date
,aip.reversal_inv_pmt_id reversal_inv_pmt_id
,zl.application_id
,zl.event_class_code
,zl.trx_line_id
,zl.entity_code
FROM po_vendors ven
,ap_invoices inv
,ap_invoice_distributions dis
,zx_rates_b tax
,ap_invoice_distributions item
,ap_invoices pp
,ap_invoice_distributions ppdis
,ap_checks chk
,ap_invoice_payments aip
,ap_invoice_lines apl
,zx_lines zl
,zx_lines_det_factors zldf
WHERE ( ( P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID )
OR ( P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID )
OR ( P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID
and get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY) )
AND ven.vendor_id = inv.vendor_id
AND dis.invoice_id = inv.invoice_id
AND tax.tax_rate_id = dis.tax_code_id
AND dis.charge_applicable_to_dist_id =
item.invoice_distribution_id
AND inv.global_attribute_category = zldf.document_sub_type
-- AND dis.line_type_lookup_code = 'TAX'
AND inv.invoice_id = apl.invoice_id
AND apl.invoice_id = zl.trx_id
AND apl.line_number = zl.trx_line_number
AND apl.application_id = zl.application_id
AND zl.entity_code = 'AP_INVOICES'
AND inv.invoice_type_lookup_code = zl.event_class_code
AND dis.invoice_line_number = apl.line_number
AND zl.application_id = zldf.application_id
AND zl.event_class_code = zldf.event_class_code
AND zl.entity_code = zldf.entity_code
AND zl.trx_id = zldf.trx_id
AND zl.trx_line_id = zldf.trx_line_id
--
AND dis.match_status_flag IS NOT NULL
AND dis.accounting_date BETWEEN p_start_date AND p_end_date
AND dis.tax_recoverable_flag = 'Y'
AND item.prepay_distribution_id = ppdis.invoice_distribution_id
AND ppdis.invoice_id = pp.invoice_id
AND inv.invoice_id = aip.invoice_id
AND chk.check_id = aip.check_id
AND aip.accounting_date BETWEEN p_start_date AND p_end_date
GROUP BY SUBSTR(ven.vendor_name, 1, 10)
,SUBSTR(ven.segment1, 1, 8)
,inv.invoice_type_lookup_code
,item.tax_recovery_rate
,tax.percentage_rate
,tax.tax_rate_id
,tax.offset_tax_rate_code
,inv.global_attribute1
,SUBSTR(inv.invoice_num, 1, 10)
,inv.invoice_id
,inv.cancelled_date
,chk.void_date
,dis.line_type_lookup_code
,item.line_type_lookup_code
,ppdis.line_type_lookup_code
,item.reversal_flag
,aip.reversal_flag
,ppdis.reversal_flag
,dis.parent_reversal_id
,inv.base_amount
,inv.invoice_amount
,chk.void_date
,chk.future_pay_due_date
,chk.check_date
,inv.payment_status_flag
,aip.accounting_date
,aip.reversal_inv_pmt_id
,zl.application_id
,zl.event_class_code
,zl.trx_line_id
,zl.entity_code;
SELECT ledger_id
,chart_of_accounts_id
,ledger_name
,currency_code
FROM gl_ledger_le_v
WHERE legal_entity_id = G_LE_ID
AND ledger_category_code = 'PRIMARY';
SELECt last_day(add_months((P_REP_DATE),-1))+1 START_DATE,
last_day((P_REP_DATE)) END_DATE
FROM dual;
SELECT SUM(jg_info_n17) cs_item_tax_amt
,jg_info_n8 charge_dist_id
,jg_info_n7 item_line_cnt
,jg_info_n1 tax_amt
,jg_info_n5 invoice_id
,jg_info_v8 tax_type
,jg_info_v4 inv_type
,jg_info_n10 l_real_inv_amt
,jg_info_n11 l_txbl_disc_amt
,jg_info_n12 l_payment_amt
,jg_info_d2 check_void_date
FROM JG_ZZ_VAT_TRX_GT
WHERE jg_info_v30='JEFRTXDC'
GROUP BY jg_info_n8
,jg_info_n7
,jg_info_n1
,jg_info_n5
,jg_info_v8
,jg_info_v4
,jg_info_n10
,jg_info_n11
,jg_info_n12
,jg_info_d2;
SELECT substr(name, 1, 40) name
,precision
INTO l_curr_name
,g_precision
FROM fnd_currencies_vl
WHERE currency_code = p_functcurr;
SELECT disc_is_inv_less_tax_flag
FROM ap_system_parameters;
SELECT xla_event.event_id
,xla_head.ae_header_id
,xla_line.code_combination_id
,xla_head.period_name
,zx_dist.rec_nrec_tax_dist_id
FROM zx_lines zx_line
,zx_lines_det_factors zx_det
,zx_rec_nrec_dist zx_dist
,zx_taxes_vl zx_tax
,zx_rates_vl zx_rate
,xla_transaction_entities xla_ent
,xla_events xla_event
,xla_ae_headers xla_head
,xla_ae_lines xla_line
,xla_distribution_links xla_dist
,xla_acct_class_assgns acs
,xla_assignment_defns_b asd
WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
AND zx_det.application_id = zx_line.application_id
AND zx_det.application_id = 200
AND zx_det.entity_code = zx_line.entity_code
AND zx_det.event_class_code = zx_line.event_class_code
AND zx_det.trx_id = zx_line.trx_id
AND zx_line.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
AND zx_det.application_id = xla_ent.application_id
AND xla_ent.entity_code = 'AP_INVOICES'
AND xla_ent.entity_id = xla_event.entity_id
AND xla_event.event_id = xla_head.event_id
AND xla_head.ae_header_id = xla_line.ae_header_id
AND xla_dist.event_id = xla_event.event_id
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_code = asd.program_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = 'Y'
AND acs.accounting_class_code = xla_line.accounting_class_code -- Accounting Joins Enda
AND xla_dist.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND xla_dist.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND zx_line.tax_line_id = zx_dist.tax_line_id
AND zx_det.tax_reporting_flag = 'Y'
AND zx_line.tax_id = zx_tax.tax_id
AND zx_line.tax_rate_id = nvl(zx_rate.source_id, zx_rate.tax_rate_id)
AND zx_line.entity_code = p_entity_code
AND zx_line.trx_id = p_trx_id
AND zx_line.application_id = p_application_id
AND zx_line.event_class_code = p_event_class_code
AND zx_line.trx_line_id = p_trx_line_id
AND (zx_rate.source_id IS NOT NULL);
SELECT xla_event.event_id
,xla_head.ae_header_id
,xla_line.code_combination_id
,xla_head.period_name
,zx_dist.rec_nrec_tax_dist_id
FROM zx_lines zx_line
,zx_lines_det_factors zx_det
,zx_rec_nrec_dist zx_dist
,zx_taxes_vl zx_tax
,zx_rates_vl zx_rate
,xla_transaction_entities xla_ent
,xla_events xla_event
,xla_ae_headers xla_head
,xla_ae_lines xla_line
,xla_distribution_links xla_dist
,xla_acct_class_assgns acs
,xla_assignment_defns_b asd
WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
AND zx_det.application_id = zx_line.application_id
AND zx_det.application_id = 200
AND zx_det.entity_code = zx_line.entity_code
AND zx_det.event_class_code = zx_line.event_class_code
AND zx_det.trx_id = zx_line.trx_id
AND zx_line.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
AND zx_det.application_id = xla_ent.application_id
AND xla_ent.entity_code = 'AP_INVOICES'
AND xla_ent.entity_id = xla_event.entity_id
AND xla_event.event_id = xla_head.event_id
AND xla_head.ae_header_id = xla_line.ae_header_id
AND xla_dist.event_id = xla_event.event_id
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_code = asd.program_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = 'Y'
AND acs.accounting_class_code = xla_line.accounting_class_code -- Accounting Joins Enda
AND xla_dist.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND xla_dist.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND zx_line.tax_line_id = zx_dist.tax_line_id
AND zx_det.tax_reporting_flag = 'Y'
AND zx_line.tax_id = zx_tax.tax_id
AND zx_line.tax_rate_id = nvl(zx_rate.source_id, zx_rate.tax_rate_id)
AND zx_line.entity_code = p_entity_code
AND zx_line.trx_id = p_trx_id
AND zx_line.application_id = p_application_id
AND zx_line.event_class_code = p_event_class_code
AND zx_line.trx_line_id = p_trx_line_id
AND (zx_rate.source_id IS NOT NULL);
select cfgd.legal_entity_id,
cfg.ledger_id,
cfg.balancing_segment_value,
cfg.entity_identifier
INTO P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
P_COMPANY,
l_entity_identifier
from jg_zz_vat_rep_entities cfg
,jg_zz_vat_rep_entities cfgd
where cfg.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
and ( ( cfg.entity_type_code = 'ACCOUNTING'
and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
)
or
( cfg.entity_type_code = 'LEGAL'
and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
)
);
SELECT period_start_date,
period_end_date
INTO l_start_date,
l_end_date
FROM JG_ZZ_VAT_REP_STATUS
WHERE VAT_REPORTING_ENTITY_ID=P_VAT_REPORTING_ENTITY_ID
AND TAX_CALENDAR_PERIOD= P_PERIOD
AND ROWNUM = 1;
SELECT registration_number,legal_entity_name INTO l_tax_payer_id,l_legal_entity_name
FROM xle_registrations_v
WHERE legal_entity_id= P_LEGAL_ENTITY_ID
and legislative_category= 'INCOME_TAX'
and identifying = 'Y';
INSERT INTO JG_ZZ_VAT_TRX_GT
(
jg_info_n1
,jg_info_v1
,jg_info_v2
,jg_info_v3
,jg_info_v4 --l_tax_payer_id
,jg_info_v5
,jg_info_v6
,jg_info_v7
,jg_info_v8
,jg_info_v9
,jg_info_v10
,jg_info_v11
,jg_info_v12
,jg_info_v13
,jg_info_v14 --l_func_curr
-- ,jg_info_v15 --l_reporting_status
,jg_info_v16
,jg_info_d1
,jg_info_d2
,jg_info_v18
,jg_info_v19
,jg_info_v30
)
VALUES
(
G_LE_ID
,l_company_name
,l_legal_entity_name
,l_registration_num
,l_tax_payer_id
,l_contact_name
,l_address_line_1
,l_address_line_2
,l_address_line_3
,l_address_line_4
,l_city
,l_country
,l_phone_number
,l_postal_code
,l_func_curr
-- ,l_reporting_status
,l_tax_regime
,l_period_end_date
,l_period_start_date
,l_ledger_name
,l_entity_identifier
,'H'
);
INSERT
INTO jg_zz_vat_trx_gt(
jg_info_v2 --ven_name
, jg_info_v3 --ven_no
, jg_info_n18 --recoverable_tax_amount
, jg_info_n2 --rec_per
, jg_info_v5 --company
, jg_info_v6 --acc_no
, jg_info_n4 --tax_rate
, jg_info_v7 --tax_id
, jg_info_v8 --tax_type
, jg_info_v9 --inv_no
, jg_info_d1 --acc_date
, jg_info_n12 --l_payment_amt
, jg_info_n13 --l_txbl_amt
, jg_info_n19 --l_prt_inv_amt
, jg_info_v10 -- company_desc
, jg_info_v11 -- invoice status
, jg_info_v30)
SELECT
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ven_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8) ven_no,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
zl.rec_nrec_rate rec_per,
get_balancing_segment(acctinfo.dist_code_combination_id) company,
get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
tax.percentage_rate tax_rate,
tax.tax_rate_id tax_id,
'DEB/M' tax_type,
-- bug 8299240 - start
-- SUBSTR(inv.invoice_num, 1, 10) inv_no,
inv.invoice_num inv_no,
-- bug 8299240 - end
dis.accounting_date acc_date,
NULL payment_amt,
nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
decode(dis.parent_reversal_id, NULL, decode(nvl(dis.reversal_flag, 'N'), 'Y', nvl(inv.exchange_rate,1)*inv.cancelled_amount, nvl(inv.exchange_rate,1)*inv.invoice_amount),
-1 * nvl(inv.exchange_rate,1) * inv.cancelled_amount) prt_inv_amt,
NULL,
decode(dis.parent_reversal_id, NULL,' ','C'),
'JEFRTXDC'
FROM zx_rec_nrec_dist zl,
zx_rates_b tax,
ap_invoices inv,
ap_invoice_distributions dis,
po_vendors ven,
ap_invoice_distributions acctinfo
WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_id = dis.invoice_id
AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
AND inv.invoice_id = acctinfo.invoice_id
AND zl.trx_id = inv.invoice_id
AND zl.recoverable_flag = 'Y'
AND tax.tax_rate_id = zl.tax_rate_id
AND zl.entity_code = 'AP_INVOICES'
AND dis.line_type_lookup_code <> 'PREPAY'
AND zl.trx_line_dist_id = dis.invoice_distribution_id
AND dis.match_status_flag IS NOT NULL
AND dis.accounting_date BETWEEN l_start_date AND l_end_date
--bug10422464 - start
AND ((nvl(dis.reversal_flag,'N') = 'N' AND nvl(zl.reverse_flag,'N') = 'N')
or
(nvl(dis.reversal_flag,'N') = 'Y' AND nvl(zl.reverse_flag,'N') = 'Y'
and exists (select 'x'
from ap_invoice_distributions dis2
where dis2.invoice_id = inv.invoice_id
and dis2.accounting_date not between l_start_date and l_end_date)))
--bug10422464 - end
AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf
WHERE SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'DEB/M'
AND zldf.trx_id = inv.invoice_id )
GROUP BY zl.trx_line_dist_id,
tax.tax_rate_id,
tax.percentage_rate,
zl.rec_nrec_rate,
decode(dis.parent_reversal_id, NULL, decode(nvl(dis.reversal_flag, 'N'), 'Y', nvl(inv.exchange_rate,1)*inv.cancelled_amount, nvl(inv.exchange_rate,1)*inv.invoice_amount),
-1 * nvl(inv.exchange_rate,1) * inv.cancelled_amount),
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8),
inv.invoice_type_lookup_code,
get_balancing_segment(acctinfo.dist_code_combination_id),
get_accounting_segment(acctinfo.dist_code_combination_id),
dis.accounting_date,
inv.invoice_num,
nvl(inv.exchange_rate,1)*dis.amount,
inv.cancelled_date,
decode(dis.parent_reversal_id, NULL,' ','C');
INSERT
INTO jg_zz_vat_trx_gt(
jg_info_v2 --ven_name
, jg_info_v3 --ven_no
, jg_info_n18 --recoverable_tax_amount
, jg_info_n2 --rec_per
, jg_info_v5 --company
, jg_info_v6 --acc_no
, jg_info_n4 --tax_rate
, jg_info_v7 --tax_id
, jg_info_v8 --tax_type
, jg_info_v9 --inv_no
, jg_info_d1 --acc_date
, jg_info_n12 --l_payment_amt
, jg_info_n13 --l_txbl_amt
, jg_info_n19 --l_prt_inv_amt
, jg_info_v10 -- company_desc
, jg_info_v11 -- invoice status
, jg_info_v30)
SELECT
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ven_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8) ven_no,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
zl.rec_nrec_rate rec_per,
get_balancing_segment(acctinfo.dist_code_combination_id) company,
get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
tax.percentage_rate tax_rate,
tax.tax_rate_id tax_id,
'DEB/M' tax_type,
-- bug 8299240 - start
-- SUBSTR(inv.invoice_num, 1, 10) inv_no,
inv.invoice_num inv_no,
-- bug 8299240 - end
dis.accounting_date acc_date,
NULL payment_amt,
nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
NULL,
'P',
'JEFRTXDC'
FROM zx_rec_nrec_dist zl,
zx_rates_b tax,
ap_invoices inv,
ap_invoice_distributions dis,
po_vendors ven,
ap_invoice_distributions acctinfo
WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_id = dis.invoice_id
AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
AND inv.invoice_id = acctinfo.invoice_id
AND zl.trx_id = inv.invoice_id
AND zl.recoverable_flag = 'Y'
AND tax.tax_rate_id = zl.tax_rate_id
AND zl.entity_code = 'AP_INVOICES'
AND dis.line_type_lookup_code = 'PREPAY'
AND zl.trx_line_dist_id = dis.invoice_distribution_id
AND dis.match_status_flag IS NOT NULL
AND (dis.accounting_date BETWEEN l_start_date AND l_end_date)
AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf
WHERE SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'DEB/M'
AND zldf.trx_id = inv.invoice_id )
GROUP BY zl.trx_line_dist_id,
tax.tax_rate_id,
tax.percentage_rate,
zl.rec_nrec_rate,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8),
inv.invoice_type_lookup_code,
get_balancing_segment(acctinfo.dist_code_combination_id),
get_accounting_segment(acctinfo.dist_code_combination_id),
dis.accounting_date,
inv.invoice_num,
nvl(inv.exchange_rate,1)*dis.amount,
inv.cancelled_date,
inv.invoice_amount,
inv.cancelled_amount;
INSERT
INTO jg_zz_vat_trx_gt(
jg_info_v2 --ven_name
, jg_info_v3 --ven_no
, jg_info_n18 --recoverable_tax_amount
, jg_info_n2 --rec_per
, jg_info_v5 --company
, jg_info_v6 --acc_no
, jg_info_n4 --tax_rate
, jg_info_v7 --tax_id
, jg_info_v8 --tax_type
, jg_info_v9 --inv_no
, jg_info_d1 --acc_date
, jg_info_n12 --l_payment_amt
, jg_info_n13 --l_txbl_amt
, jg_info_n19 --l_prt_inv_amt
, jg_info_v10 -- company_desc
, jg_info_v11 -- invoice status
, jg_info_v30)
SELECT
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ven_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8) ven_no,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) * ((nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1) * nvl(aip.discount_taken,0))
/nullif(decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),0)) recoverable_tax_amount, --bug 13596406
zl.rec_nrec_rate rec_per,
get_balancing_segment(acctinfo.dist_code_combination_id) company,
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
tax.percentage_rate tax_rate,
tax.tax_rate_id tax_id,
'CRE/M' tax_type,
-- bug 8299240 - start
-- SUBSTR(inv.invoice_num, 1, 10) inv_no,
inv.invoice_num inv_no,
-- bug 8299240 - end
decode(chk.future_pay_due_date, NULL, aip.accounting_date, decode(sign(aip.accounting_date-chk.future_pay_due_date),1, aip.accounting_date, chk.future_pay_due_date)) acc_date,
(nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1) * nvl(aip.discount_taken,0)) payment_amt,
nvl(inv.exchange_rate,1)*nvl(dis.total_dist_amount,dis.amount) * ((nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1) * nvl(aip.discount_taken,0))
/nullif(decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),0)) txbl_amt, --bug 13596406
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
NULL,
decode(aip.reversal_inv_pmt_id,NULL,' ','V'),
'JEFRTXDC'
FROM zx_rates_b tax,
ap_invoices inv,
ap_invoice_distributions dis,
po_vendors ven,
ap_invoice_payments aip,
ap_checks_all chk,
ap_invoice_distributions acctinfo,
zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
ON (accounts.TAX_ACCOUNT_ENTITY_ID =
nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
AND accounts.LEDGER_ID = zl.LEDGER_ID )
WHERE ((p_ledger_id IS NULL
AND p_company IS NULL
AND inv.legal_entity_id = g_le_id) OR(p_ledger_id IS NOT NULL
AND p_company IS NULL
AND inv.set_of_books_id = p_ledger_id) OR(p_company IS NOT NULL
AND inv.set_of_books_id = p_ledger_id
AND get_balancing_segment(dis.dist_code_combination_id) = p_company))
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_id = dis.invoice_id
AND aip.invoice_id = inv.invoice_id
AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
AND inv.invoice_id = acctinfo.invoice_id
AND chk.check_id = aip.check_id
AND zl.trx_id = inv.invoice_id
AND zl.recoverable_flag = 'Y'
AND tax.tax_rate_id = zl.tax_rate_id
AND zl.entity_code = 'AP_INVOICES'
AND zl.trx_line_dist_id = dis.invoice_distribution_id
AND dis.line_type_lookup_code <> 'PREPAY'
AND dis.match_status_flag IS NOT NULL
AND decode(chk.future_pay_due_date, NULL, aip.accounting_date, decode(sign(aip.accounting_date-chk.future_pay_due_date),1, aip.accounting_date, chk.future_pay_due_date)) BETWEEN l_start_date AND l_end_date
-- AND dis.parent_reversal_id IS NULL --Bug13717126
--bug10422464 - start
AND ((nvl(dis.reversal_flag,'N') = 'N' AND nvl(zl.reverse_flag,'N') = 'N')
or
(nvl(dis.reversal_flag,'N') = 'Y' AND nvl(zl.reverse_flag,'N') = 'Y'
and exists (select 'x'
from ap_invoice_distributions dis2
where dis2.invoice_id = inv.invoice_id
and dis2.accounting_date not between l_start_date and l_end_date)))
--bug10422464 - end
AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
AND zldf.trx_id = inv.invoice_id )
GROUP BY zl.trx_line_dist_id,
tax.tax_rate_id,
tax.percentage_rate,
zl.rec_nrec_rate,
inv.cancelled_amount,
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8),
inv.invoice_type_lookup_code,
get_balancing_segment(acctinfo.dist_code_combination_id),
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
aip.accounting_date,
inv.invoice_num,
dis.total_dist_amount,
dis.amount,
inv.cancelled_date,
aip.amount,
aip.discount_taken,
inv.invoice_amount,
chk.future_pay_due_date,
decode(aip.reversal_inv_pmt_id,NULL,' ','V'),
inv.exchange_rate,
aip.exchange_rate;
-- The first insert is for the positive line, the second insert
-- is for the negative line
INSERT
INTO jg_zz_vat_trx_gt(
jg_info_v2 --ven_name
, jg_info_v3 --ven_no
, jg_info_n18 --recoverable_tax_amount
, jg_info_n2 --rec_per
, jg_info_v5 --company
, jg_info_v6 --acc_no
, jg_info_n4 --tax_rate
, jg_info_v7 --tax_id
, jg_info_v8 --tax_type
, jg_info_v9 --inv_no
, jg_info_d1 --acc_date
, jg_info_n12 --l_payment_amt
, jg_info_n13 --l_txbl_amt
, jg_info_n19 --l_prt_inv_amt
, jg_info_v10 -- company_desc
, jg_info_v11 -- invoice status
, jg_info_v30)
SELECT
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ven_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8) ven_no,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt)*-1 recoverable_tax_amount,
zl.rec_nrec_rate rec_per,
get_balancing_segment(acctinfo.dist_code_combination_id) company,
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
tax.percentage_rate tax_rate,
tax.tax_rate_id tax_id,
'CRE/M' tax_type,
-- bug 8299240 - start
-- SUBSTR(inv.invoice_num, 1, 10) inv_no,
inv.invoice_num inv_no,
-- bug 8299240 - end
dis.accounting_date acc_date,
(SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) + SUM(nvl(inv.exchange_rate,1)*nrec.rec_nrec_tax_amt) + nvl(inv.exchange_rate,1)*dis.amount)*-1 payment_amt,
nvl(inv.exchange_rate,1)*dis.amount*-1 txbl_amt,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
NULL,
'P',
'JEFRTXDC'
FROM zx_rates_b tax,
ap_invoices inv,
ap_invoice_distributions dis,
po_vendors ven,
zx_rec_nrec_dist nrec,
ap_invoice_distributions acctinfo,
zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
ON (accounts.TAX_ACCOUNT_ENTITY_ID =
nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
AND accounts.LEDGER_ID = zl.LEDGER_ID )
WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_id = dis.invoice_id
AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
AND inv.invoice_id = acctinfo.invoice_id
AND zl.trx_id = inv.invoice_id
AND zl.recoverable_flag = 'Y'
AND tax.tax_rate_id = zl.tax_rate_id
AND zl.entity_code = 'AP_INVOICES'
AND zl.trx_line_dist_id = dis.invoice_distribution_id
AND dis.match_status_flag IS NOT NULL
AND dis.accounting_date BETWEEN l_start_date AND l_end_date
AND dis.line_type_lookup_code = 'PREPAY'
AND nrec.entity_code = 'AP_INVOICES'
AND nrec.trx_line_dist_id = dis.invoice_distribution_id
AND nrec.trx_id = inv.invoice_id
AND nrec.recoverable_flag = 'N'
AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
AND zldf.trx_id = inv.invoice_id )
GROUP BY zl.trx_line_dist_id,
tax.tax_rate_id,
tax.percentage_rate,
zl.rec_nrec_rate,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8),
inv.invoice_type_lookup_code,
get_balancing_segment(acctinfo.dist_code_combination_id),
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
dis.accounting_date,
inv.invoice_num,
dis.amount,
inv.cancelled_date,
inv.exchange_rate;
INSERT
INTO jg_zz_vat_trx_gt(
jg_info_v2 --ven_name
, jg_info_v3 --ven_no
, jg_info_n18 --recoverable_tax_amount
, jg_info_n2 --rec_per
, jg_info_v5 --company
, jg_info_v6 --acc_no
, jg_info_n4 --tax_rate
, jg_info_v7 --tax_id
, jg_info_v8 --tax_type
, jg_info_v9 --inv_no
, jg_info_d1 --acc_date
, jg_info_n12 --l_payment_amt
, jg_info_n13 --l_txbl_amt
, jg_info_n19 --l_prt_inv_amt
, jg_info_v10 -- company_desc
, jg_info_v11 -- invoice status
, jg_info_v30)
SELECT
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ven_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8) ven_no,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
zl.rec_nrec_rate rec_per,
get_balancing_segment(acctinfo.dist_code_combination_id) company,
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
tax.percentage_rate tax_rate,
tax.tax_rate_id tax_id,
'CRE/M' tax_type,
-- bug 8299240 - start
-- SUBSTR(inv.invoice_num, 1, 10) inv_no,
inv.invoice_num inv_no,
-- bug 8299240 - end
dis.accounting_date acc_date,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) + SUM(nvl(inv.exchange_rate,1)*nrec.rec_nrec_tax_amt) + nvl(inv.exchange_rate,1)*dis.amount payment_amt,
nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
NULL,
'P',
'JEFRTXDC'
FROM zx_rates_b tax,
ap_invoices inv,
ap_invoice_distributions dis,
po_vendors ven,
zx_rec_nrec_dist nrec,
ap_invoice_distributions acctinfo,
zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
ON (accounts.TAX_ACCOUNT_ENTITY_ID =
nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
AND accounts.LEDGER_ID = zl.LEDGER_ID )
WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_id = dis.invoice_id
AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
AND inv.invoice_id = acctinfo.invoice_id
AND zl.trx_id = inv.invoice_id
AND zl.recoverable_flag = 'Y'
AND tax.tax_rate_id = zl.tax_rate_id
AND zl.entity_code = 'AP_INVOICES'
AND zl.trx_line_dist_id = dis.invoice_distribution_id
AND dis.match_status_flag IS NOT NULL
AND dis.accounting_date BETWEEN l_start_date AND l_end_date
AND dis.line_type_lookup_code = 'PREPAY'
AND nrec.entity_code = 'AP_INVOICES'
AND nrec.trx_line_dist_id = dis.invoice_distribution_id
AND nrec.trx_id = inv.invoice_id
AND nrec.recoverable_flag = 'N'
AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
AND zldf.trx_id = inv.invoice_id )
GROUP BY zl.trx_line_dist_id,
tax.tax_rate_id,
tax.percentage_rate,
zl.rec_nrec_rate,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8),
inv.invoice_type_lookup_code,
get_balancing_segment(acctinfo.dist_code_combination_id),
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
dis.accounting_date,
inv.invoice_num,
dis.amount,
inv.cancelled_date,
inv.exchange_rate;
INSERT
INTO jg_zz_vat_trx_gt(
jg_info_v2 --ven_name
, jg_info_v3 --ven_no
, jg_info_n18 --recoverable_tax_amount
, jg_info_n2 --rec_per
, jg_info_v5 --company
, jg_info_v6 --acc_no
, jg_info_n4 --tax_rate
, jg_info_v7 --tax_id
, jg_info_v8 --tax_type
, jg_info_v9 --inv_no
, jg_info_d1 --acc_date
, jg_info_n12 --l_payment_amt
, jg_info_n13 --l_txbl_amt
, jg_info_n19 --l_prt_inv_amt
, jg_info_v10 -- company_desc
, jg_info_v11 -- invoice status
, jg_info_v30)
SELECT
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ven_name,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8) ven_no,
SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) * ((-1 * nvl(inv.exchange_rate,1) * pre.amount)
/decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) recoverable_tax_amount,
zl.rec_nrec_rate rec_per,
get_balancing_segment(acctinfo.dist_code_combination_id) company,
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
tax.percentage_rate tax_rate,
tax.tax_rate_id tax_id,
'CRE/M' tax_type,
-- bug 8299240 - start
-- SUBSTR(inv.invoice_num, 1, 10) inv_no,
inv.invoice_num inv_no,
-- bug 8299240 - end
pre.accounting_date acc_date,
-1 * nvl(inv.exchange_rate,1) * pre.amount payment_amt,
nvl(inv.exchange_rate,1) * dis.amount * ((-1 * nvl(inv.exchange_rate,1) * pre.amount)
/decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) txbl_amt,
decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
NULL,
'PP',
'JEFRTXDC'
FROM zx_rates_b tax,
ap_invoices inv,
ap_invoice_distributions dis,
po_vendors ven,
ap_invoice_distributions pre,
ap_invoice_distributions acctinfo,
zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
ON (accounts.TAX_ACCOUNT_ENTITY_ID =
nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
AND accounts.LEDGER_ID = zl.LEDGER_ID )
WHERE ((p_ledger_id IS NULL
AND p_company IS NULL
AND inv.legal_entity_id = g_le_id) OR(p_ledger_id IS NOT NULL
AND p_company IS NULL
AND inv.set_of_books_id = p_ledger_id) OR(p_company IS NOT NULL
AND inv.set_of_books_id = p_ledger_id
AND get_balancing_segment(dis.dist_code_combination_id) = p_company))
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_id = dis.invoice_id
AND inv.invoice_id = pre.invoice_id
AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
AND inv.invoice_id = acctinfo.invoice_id
AND zl.trx_id = inv.invoice_id
AND zl.recoverable_flag = 'Y'
AND tax.tax_rate_id = zl.tax_rate_id
AND zl.entity_code = 'AP_INVOICES'
AND zl.trx_line_dist_id = dis.invoice_distribution_id
AND dis.line_type_lookup_code = 'ITEM'
AND dis.match_status_flag IS NOT NULL
AND pre.line_type_lookup_code = 'PREPAY'
AND pre.match_status_flag IS NOT NULL
AND NOT EXISTS ( SELECT 1 FROM zx_rec_nrec_dist zlp where
zlp.trx_id = inv.invoice_id
AND zlp.entity_code = 'AP_INVOICES'
AND zlp.trx_line_dist_id = pre.invoice_distribution_id )
AND pre.accounting_date BETWEEN l_start_date AND l_end_date
AND dis.parent_reversal_id IS NULL
--bug10422464 - start
AND ((nvl(dis.reversal_flag,'N') = 'N' AND nvl(zl.reverse_flag,'N') = 'N')
or
(nvl(dis.reversal_flag,'N') = 'Y' AND nvl(zl.reverse_flag,'N') = 'Y'
and exists (select 'x'
from ap_invoice_distributions dis2
where dis2.invoice_id = inv.invoice_id
and dis2.accounting_date not between l_start_date and l_end_date)))
--bug10422464 - end
AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
AND zldf.trx_id = inv.invoice_id )
GROUP BY zl.trx_line_dist_id,
tax.tax_rate_id,
tax.percentage_rate,
zl.rec_nrec_rate,
inv.cancelled_amount,
-- bug 8299240 - start
-- SUBSTR(ven.vendor_name, 1, 10) ven_name,
ven.vendor_name ,
-- bug 8299240 - end
SUBSTR(ven.segment1, 1, 8),
inv.invoice_type_lookup_code,
get_balancing_segment(acctinfo.dist_code_combination_id),
get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
pre.accounting_date,
inv.invoice_num,
dis.amount,
inv.cancelled_date,
pre.amount,
inv.invoice_amount,
inv.exchange_rate;
SELECT distinct ffv.description
INTO l_company_desc
FROM fnd_id_flex_segments_vl fif
,fnd_flex_values_vl ffv
WHERE fif.id_flex_code = 'GL#'
AND fif.application_id = 101
AND fif.id_flex_num = g_struct_num
AND ffv.flex_value = c_balancing_segment.jg_info_v5
AND ffv.flex_value_set_id = fif.flex_value_set_id;
UPDATE jg_zz_vat_trx_gt
SET jg_info_v10 = l_company_desc
WHERE jg_info_v5 = c_balancing_segment.jg_info_v5;
fnd_file.put_line(fnd_file.log,' An error occured while inserting and updating data to the global tmp table. Error : ' || SUBSTR(SQLERRM, 1, 200));
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_v1 --ven_name
,jg_info_v2 --ven_no
,jg_info_v3 --inv_type
,jg_info_n1 --tax_amt
,jg_info_n2 --rec_per
,jg_info_v4 --company
,jg_info_v5 --acc_no
,jg_info_n3 --tax_rate
,jg_info_v6 --tax_id
,jg_info_v7 --offset_tax_rate_code
,jg_info_v8 --tax_type
,jg_info_v9 --inv_no
,jg_info_d1 --acc_date
,jg_info_n4 --invoice_id
,jg_info_d2 --cancelled_date
,jg_info_n5 --item_line_cnt
,jg_info_n6 --charge_dist_id
,jg_info_d3 --check_void_date
,jg_info_n7 --pay_amt
,jg_info_v10 --line_type_lookup_code
,jg_info_v11 --line_type_lookup_code_item
,jg_info_v12 --line_type_lookup_code_prepay
,jg_info_v13 --reversal_flag_item
,jg_info_v14 --reversal_flag_pay
,jg_info_v15 --reversal_flag_prepay
,jg_info_n8 --parent_reversal_id
,jg_info_n9 --base_amount
,jg_info_n10 --invoice_amount
,jg_info_d4 --void_date
,jg_info_d5 --future_pay_due_date
,jg_info_d6 --check_date
,jg_info_v16 --payment_status_flag
,jg_info_d7 --accounting_date
,jg_info_n11 --reversal_inv_pmt_id
,jg_info_v17 --c_company
)
VALUES
(c_data_rec1.ven_name --jg_info_v1
,c_data_rec1.ven_no --jg_info_v2
,c_data_rec1.inv_type --jg_info_v3
,c_data_rec1.tax_amt --jg_info_n1
,c_data_rec1.rec_per --jg_info_n2
,c_data_rec1.company --jg_info_v4
,c_data_rec1.acc_no --jg_info_v5
,c_data_rec1.tax_rate --jg_info_n3
,c_data_rec1.tax_id --jg_info_v6
,c_data_rec1.offset_tax_rate_code --jg_info_v7
,c_data_rec1.tax_type --jg_info_v8
,c_data_rec1.inv_no --jg_info_v9
,c_data_rec1.acc_date --jg_info_d1
,c_data_rec1.invoice_id --jg_info_n4
,c_data_rec1.cancelled_date --jg_info_d2
,c_data_rec1.item_line_cnt --jg_info_n5
,c_data_rec1.charge_dist_id --jg_info_n6
,c_data_rec1.check_void_date --jg_info_d3
,c_data_rec1.pay_amt --jg_info_n7
,c_data_rec1.line_type_lookup_code --jg_info_v10
,c_data_rec1.line_type_lookup_code_item --jg_info_v11
,c_data_rec1.line_type_lookup_code_prepay --jg_info_v12
,c_data_rec1.reversal_flag_item --jg_info_v13
,c_data_rec1.reversal_flag_pay --jg_info_v14
,c_data_rec1.reversal_flag_prepay --jg_info_v15
,c_data_rec1.parent_reversal_id --jg_info_n8
,c_data_rec1.base_amount --jg_info_n9
,c_data_rec1.invoice_amount --jg_info_n10
,c_data_rec1.void_date --jg_info_d4
,c_data_rec1.future_pay_due_date --jg_info_d5
,c_data_rec1.check_date --jg_info_d6
,c_data_rec1.payment_status_flag --jg_info_v16
,c_data_rec1.accounting_date --jg_info_d7
,c_data_rec1.reversal_inv_pmt_id --jg_info_n11
,l_company_desc --jg_info_v17
);
fnd_file.put_line(fnd_file.log,' An error occured while inserting data into the global tmp table in the generic cursor. Error : ' || SUBSTR(SQLERRM, 1, 200));
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT SUM(nvl(p.invoice_base_amount, p.amount)) amount
FROM ap_invoice_payments p
WHERE p.invoice_id = p_invoice_id
AND p.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT SUM(nvl(pp.base_amount, pp.amount) + nvl(ppt.base_amount, ppt.amount)) prepay_amount
FROM ap_invoice_distributions pp
,ap_invoice_distributions ppt
WHERE pp.invoice_id = p_invoice_id
AND ppt.invoice_id = p_invoice_id
AND pp.line_type_lookup_code = 'PREPAY'
AND pp.charge_applicable_to_dist_id = ppt.invoice_distribution_id
AND nvl(pp.reversal_flag, 'N') <> 'Y'
AND pp.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT (-1) * SUM(nvl(ppp.base_amount, ppp.amount)) prepay_amount
FROM ap_invoice_distributions ppp
WHERE ppp.invoice_id = p_invoice_id
AND ppp.line_type_lookup_code = 'PREPAY'
AND nvl(ppp.reversal_flag, 'N') <> 'Y'
AND ppp.accounting_date BETWEEN p_start_date AND p_end_date
AND NOT EXISTS (SELECT 'x'
FROM ap_invoice_distributions ptax
WHERE ptax.invoice_id = p_invoice_id
AND ppp.charge_applicable_to_dist_id = ptax.invoice_distribution_id);
SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
FROM ap_invoice_distributions dis
WHERE dis.invoice_id = p_invoice_id
AND dis.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','PREPAY')
AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND tax.tax_rate_id = dis.tax_code_id
AND dis.invoice_id = p_invoice_id
AND tax.offset_tax_rate_code = p_offset_tax_rate_code
AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
WHERE tax.tax_rate_id = dis.tax_code_id
AND dis.invoice_id = p_invoice_id
AND tax.tax_rate_id = p_tax_id
AND dis.line_type_lookup_code = 'PREPAY'
AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT SUM(nvl(dis.base_amount, dis.amount) * aip.discount_lost / p_real_inv_amt) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
,ap_invoice_payments aip
,ap_invoices inv
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND inv.invoice_id = dis.invoice_id
AND inv.invoice_id = aip.invoice_id
AND dis.invoice_id = p_invoice_id
AND tax.tax_rate_id = dis.tax_code_id
AND tax.tax_rate_id = p_tax_id
AND aip.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT SUM(nvl(dis.base_amount, dis.amount) * aip.discount_lost / p_real_inv_amt) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
,ap_invoice_payments aip
,ap_invoices inv
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND inv.invoice_id = dis.invoice_id
AND inv.invoice_id = aip.invoice_id
AND dis.invoice_id = p_invoice_id
AND tax.tax_rate_id = dis.tax_code_id
AND tax.offset_tax_rate_code = p_offset_tax_rate_code
AND aip.accounting_date BETWEEN p_start_date AND p_end_date;
SELECT (-1) * SUM(nvl(dis.base_amount, dis.amount)) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND dis.invoice_id = p_invoice_id
AND tax.tax_rate_id = p_tax_id
AND dis.tax_code_id = tax.tax_rate_id
AND dis.parent_reversal_id IS NOT NULL;
SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND dis.invoice_id = p_invoice_id
AND tax.tax_rate_id = p_tax_id
AND dis.tax_code_id = tax.tax_rate_id;
SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND dis.invoice_id = p_invoice_id
AND tax.offset_tax_rate_code = p_offset_tax_rate_code
AND dis.tax_code_id = tax.tax_rate_id;
SELECT SUM(nvl(dis.base_amount, dis.amount)) * (p_payment_amt / p_real_inv_amt) amount
FROM ap_invoice_distributions dis
,zx_rates_b tax
WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
AND dis.invoice_id = p_invoice_id
AND tax.tax_rate_id = p_tax_id
AND dis.tax_code_id = tax.tax_rate_id;
SELECT round(SUM(nvl(dis.base_amount, dis.amount)), G_PRECISION) remaining_amount
FROM ap_invoice_distributions dis
WHERE dis.invoice_id = p_invoice_id
AND dis.accounting_date <= p_end_date;
SELECT SUM(nvl(disc.discount_taken, 0)) discount_taken
FROM ap_invoice_payments disc
,ap_checks chk
WHERE disc.invoice_id = p_invoice_id
AND disc.check_id = chk.check_id
AND nvl(chk.future_pay_due_date, chk.check_date) BETWEEN
p_start_date AND p_end_date;
SELECT SUM(decode(invoice_includes_prepay_flag, 'Y', nvl(base_amount, amount), 0)) iipp_amt
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id;
,p_mode => 'SELECT'
,p_qualifier => 'GL_ACCOUNT');
l_stmt := ' SELECT ' || l_accounting_segment ||
' FROM GL_CODE_COMBINATIONS ' ||
' WHERE CODE_COMBINATION_ID = :LLCID';
SELECT ffv.description
FROM fnd_id_flex_segments_vl fif
,fnd_flex_values_vl ffv
WHERE fif.id_flex_code = 'GL#'
AND fif.application_id = 101
AND fif.id_flex_num = p_coaid
AND ffv.flex_value = p_company
AND ffv.flex_value_set_id = fif.flex_value_set_id;
,p_mode => 'SELECT'
,p_qualifier => 'GL_BALANCING');
l_stmt := ' SELECT ' || l_balancing_segment ||
' FROM GL_CODE_COMBINATIONS ' ||
' WHERE CODE_COMBINATION_ID = :LLCID';
SELECT ffv.description
FROM fnd_id_flex_segments_vl fif
,fnd_flex_values_vl ffv
WHERE fif.id_flex_code = 'GL#'
AND fif.application_id = 101
AND fif.id_flex_num = p_coaid
AND ffv.flex_value = p_company
AND ffv.flex_value_set_id = fif.flex_value_set_id;
,p_mode => 'SELECT'
,p_qualifier => 'GL_BALANCING');
l_stmt := ' SELECT ' || l_balancing_segment ||
' FROM GL_CODE_COMBINATIONS ' ||
' WHERE CODE_COMBINATION_ID = :LLCID';