[Home] [Help]
113: accrue_on_receipt_flag po_distributions_all.accrue_on_receipt_flag%TYPE,
114: allow_flex_override_flag ap_system_parameters_all.allow_flex_override_flag%TYPE,
115: purch_encumbrance_flag financials_system_params_all.purch_encumbrance_flag%TYPE,
116: org_id ap_invoice_distributions_all.org_id%TYPE,
117: tax_regime_id zx_rec_nrec_dist.tax_regime_id%TYPE,
118: tax_id zx_rec_nrec_dist.tax_id%TYPE,
119: tax_status_id zx_rec_nrec_dist.tax_status_id%TYPE,
120: tax_jurisdiction_id zx_lines.tax_jurisdiction_id%TYPE,
121: par_dist_assets_tracking_flag ap_invoice_distributions_all.assets_tracking_flag%TYPE, -- Bug 13821160
114: allow_flex_override_flag ap_system_parameters_all.allow_flex_override_flag%TYPE,
115: purch_encumbrance_flag financials_system_params_all.purch_encumbrance_flag%TYPE,
116: org_id ap_invoice_distributions_all.org_id%TYPE,
117: tax_regime_id zx_rec_nrec_dist.tax_regime_id%TYPE,
118: tax_id zx_rec_nrec_dist.tax_id%TYPE,
119: tax_status_id zx_rec_nrec_dist.tax_status_id%TYPE,
120: tax_jurisdiction_id zx_lines.tax_jurisdiction_id%TYPE,
121: par_dist_assets_tracking_flag ap_invoice_distributions_all.assets_tracking_flag%TYPE, -- Bug 13821160
122: parent_dist_cancellation_flag ap_invoice_distributions_all.cancellation_flag%TYPE,
115: purch_encumbrance_flag financials_system_params_all.purch_encumbrance_flag%TYPE,
116: org_id ap_invoice_distributions_all.org_id%TYPE,
117: tax_regime_id zx_rec_nrec_dist.tax_regime_id%TYPE,
118: tax_id zx_rec_nrec_dist.tax_id%TYPE,
119: tax_status_id zx_rec_nrec_dist.tax_status_id%TYPE,
120: tax_jurisdiction_id zx_lines.tax_jurisdiction_id%TYPE,
121: par_dist_assets_tracking_flag ap_invoice_distributions_all.assets_tracking_flag%TYPE, -- Bug 13821160
122: parent_dist_cancellation_flag ap_invoice_distributions_all.cancellation_flag%TYPE,
123: parent_dist_reversal_flag ap_invoice_distributions_all.reversal_flag%TYPE,
121: par_dist_assets_tracking_flag ap_invoice_distributions_all.assets_tracking_flag%TYPE, -- Bug 13821160
122: parent_dist_cancellation_flag ap_invoice_distributions_all.cancellation_flag%TYPE,
123: parent_dist_reversal_flag ap_invoice_distributions_all.reversal_flag%TYPE,
124: parent_dist_parent_reversal_id ap_invoice_distributions_all.parent_reversal_id%TYPE,
125: reversed_tax_dist_id zx_rec_nrec_dist.reversed_tax_dist_id%TYPE,
126: adjusted_doc_tax_dist_id zx_rec_nrec_dist.adjusted_doc_tax_dist_id%TYPE,
127: applied_from_tax_dist_id zx_rec_nrec_dist.applied_from_tax_dist_id%TYPE,
128: prepay_distribution_id ap_invoice_distributions_all.prepay_distribution_id%TYPE,
129: prepay_tax_diff_amount ap_invoice_distributions_all.prepay_tax_diff_amount%TYPE,
122: parent_dist_cancellation_flag ap_invoice_distributions_all.cancellation_flag%TYPE,
123: parent_dist_reversal_flag ap_invoice_distributions_all.reversal_flag%TYPE,
124: parent_dist_parent_reversal_id ap_invoice_distributions_all.parent_reversal_id%TYPE,
125: reversed_tax_dist_id zx_rec_nrec_dist.reversed_tax_dist_id%TYPE,
126: adjusted_doc_tax_dist_id zx_rec_nrec_dist.adjusted_doc_tax_dist_id%TYPE,
127: applied_from_tax_dist_id zx_rec_nrec_dist.applied_from_tax_dist_id%TYPE,
128: prepay_distribution_id ap_invoice_distributions_all.prepay_distribution_id%TYPE,
129: prepay_tax_diff_amount ap_invoice_distributions_all.prepay_tax_diff_amount%TYPE,
130: invoice_id ap_invoice_distributions_all.invoice_id%TYPE,
123: parent_dist_reversal_flag ap_invoice_distributions_all.reversal_flag%TYPE,
124: parent_dist_parent_reversal_id ap_invoice_distributions_all.parent_reversal_id%TYPE,
125: reversed_tax_dist_id zx_rec_nrec_dist.reversed_tax_dist_id%TYPE,
126: adjusted_doc_tax_dist_id zx_rec_nrec_dist.adjusted_doc_tax_dist_id%TYPE,
127: applied_from_tax_dist_id zx_rec_nrec_dist.applied_from_tax_dist_id%TYPE,
128: prepay_distribution_id ap_invoice_distributions_all.prepay_distribution_id%TYPE,
129: prepay_tax_diff_amount ap_invoice_distributions_all.prepay_tax_diff_amount%TYPE,
130: invoice_id ap_invoice_distributions_all.invoice_id%TYPE,
131: batch_id ap_invoice_distributions_all.batch_id%TYPE,
130: invoice_id ap_invoice_distributions_all.invoice_id%TYPE,
131: batch_id ap_invoice_distributions_all.batch_id%TYPE,
132: set_of_books_id ap_invoice_distributions_all.set_of_books_id%TYPE,
133: pay_awt_group_id ap_invoice_distributions_all.pay_awt_group_id%TYPE, --bug8345264
134: account_source_tax_rate_id zx_rec_nrec_dist.account_source_tax_rate_id%TYPE
135: );
136:
137: --bug 12728634 Added new function
138:
2378: extra_po_erv ap_invoice_distributions_all.extra_po_erv%TYPE ,
2379: prepay_tax_diff_amount ap_invoice_distributions_all.prepay_tax_diff_amount%TYPE,
2380: invoice_distribution_id ap_invoice_distributions_all.invoice_distribution_id%TYPE,
2381: pay_awt_group_id ap_invoice_distributions_all.pay_awt_group_id%TYPE, --Bug8345264
2382: account_source_tax_rate_id zx_rec_nrec_dist.account_source_tax_rate_id%TYPE);
2383:
2384:
2385: TYPE ins_tax_dist_type IS TABLE OF r_ins_tax_dist_info;
2386: TYPE upd_tax_dist_type IS TABLE OF r_upd_tax_dist_info;
2413: AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
2414: AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
2415: AND gt.trx_id = aid.invoice_id
2416: AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions' -- 9485828
2417: FROM zx_rec_nrec_dist zd
2418: WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
2419: AND NVL(SELF_ASSESSED_FLAG, 'N') = 'N');
2420:
2421: TYPE frozen_tax_del IS TABLE OF frozen_dist_del%ROWTYPE
2442: AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
2443: AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
2444: AND gt.trx_id = aid.invoice_id
2445: AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions' -- 9485828
2446: FROM zx_rec_nrec_dist zd
2447: WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
2448: AND NVL(SELF_ASSESSED_FLAG, 'N') = 'Y');
2449:
2450: TYPE frozen_self_tax_del IS TABLE OF frozen_self_dist_del%ROWTYPE
2456:
2457: CURSOR insert_tax_dist IS
2458: SELECT /*+ leading(gt,zd) cardinality(gt,1) */
2459: zd.gl_date accounting_date , --Bug6809792
2460: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
2461: Ebtax will now store accounting date of opne period in their table*/
2462: decode(NVL(zl.tax_only_line_flag,'N'),
2463: 'Y',parent_tax_line.default_dist_ccid,
2464: parent_taxable_dist.dist_code_combination_id) dist_code_combination_id, ---for 6010950
2693: NULL),
2694: NULL) pay_awt_group_id, --Bug8345264
2695: zd.account_source_tax_rate_id account_source_tax_rate_id
2696: FROM zx_trx_headers_gt gt,
2697: zx_rec_nrec_dist zd,
2698: zx_lines zl,
2699: ap_invoices_all ai,
2700: ap_supplier_sites_all pvs,
2701: ap_system_parameters_all asp,
2777:
2778: CURSOR insert_tax_variances IS
2779: SELECT /*+ leading(gt,zd) cardinality(gt,1) */
2780: zd.gl_date accounting_date , --Bug6809792
2781: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
2782: Ebtax will now store accounting date of opne period in their table*/
2783: (CASE dist.line_type
2784: WHEN 'TERV' THEN
2785: DECODE(pd.destination_type_code, 'EXPENSE', pd.code_combination_id,
3040: NULL),
3041: NULL) pay_awt_group_id, --Bug8345264
3042: zd.account_source_tax_rate_id account_source_tax_rate_id
3043: FROM zx_trx_headers_gt gt,
3044: zx_rec_nrec_dist zd,
3045: zx_lines zl,
3046: ap_invoices_all ai,
3047: ap_supplier_sites_all pvs,
3048: ap_system_parameters_all asp,
3140:
3141: CURSOR update_tax_dist IS
3142: SELECT /*+ leading(gt,zd) cardinality(gt,1) */
3143: zd.gl_date accounting_date , --Bug6809792
3144: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
3145: Ebtax will now store accounting date of opne period in their table*/
3146: parent_taxable_dist.dist_code_combination_id dist_code_combination_id,
3147: -- this ccid is a temporary value that will be used if other conditions
3148: -- are met before inserting the tax distribution
3318: aid.invoice_distribution_id invoice_distribution_id,
3319: aid.pay_awt_group_id pay_awt_group_id, --bug8345264
3320: zd.account_source_tax_rate_id
3321: FROM zx_trx_headers_gt gt,
3322: zx_rec_nrec_dist zd,
3323: zx_lines zl,
3324: ap_invoices_all ai,
3325: ap_invoice_distributions_all aid,
3326: ap_supplier_sites_all pvs,
3359:
3360: CURSOR update_tax_variances IS
3361: SELECT /*+ leading(gt,zd) cardinality(gt,1) */
3362: zd.gl_date accounting_date , --Bug6809792
3363: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
3364: Ebtax will now store accounting date of opne period in their table*/
3365: (CASE aid.line_type_lookup_code
3366: WHEN 'TERV' THEN
3367: DECODE(pd.destination_type_code, 'EXPENSE', pd.code_combination_id,
3559: aid.invoice_distribution_id invoice_distribution_id,
3560: aid.pay_awt_group_id pay_awt_group_id, --Bug8345264
3561: zd.account_source_tax_rate_id account_source_tax_rate_id
3562: FROM zx_trx_headers_gt gt,
3563: zx_rec_nrec_dist zd,
3564: zx_lines zl,
3565: ap_invoices_all ai,
3566: ap_invoice_distributions_all aid,
3567: ap_supplier_sites_all pvs,
3601: -- bug 9231678 removed sign condition
3602:
3603: -- Cursors for self assessed distributions
3604: CURSOR insert_tax_self IS
3605: SELECT /*+ leading(gt,zd) cardinality(gt,1) INDEX (ZD ZX_REC_NREC_DIST_U3) */
3606: zd.gl_date accounting_date , --Bug6809792
3607: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
3608: Ebtax will now store accounting date of opne period in their table*/
3609: -- Modified dist_code_combination_id value for bug#Bug9437885
3603: -- Cursors for self assessed distributions
3604: CURSOR insert_tax_self IS
3605: SELECT /*+ leading(gt,zd) cardinality(gt,1) INDEX (ZD ZX_REC_NREC_DIST_U3) */
3606: zd.gl_date accounting_date , --Bug6809792
3607: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
3608: Ebtax will now store accounting date of opne period in their table*/
3609: -- Modified dist_code_combination_id value for bug#Bug9437885
3610: --DECODE(pd.accrue_on_receipt_flag, 'Y', pd.code_combination_id,parent_taxable_dist.dist_code_combination_id) dist_code_combination_id,
3611: DECODE(pd.destination_type_code,
3779: ai.set_of_books_id set_of_books_id,
3780: parent_taxable_dist.pay_awt_group_id pay_awt_group_id, --Bug8345264
3781: zd.account_source_tax_rate_id account_source_tax_rate_id
3782: FROM zx_trx_headers_gt gt,
3783: zx_rec_nrec_dist zd,
3784: zx_lines zl,
3785: ap_invoices_all ai,
3786: ap_supplier_sites_all pvs,
3787: ap_system_parameters_all asp,
3823:
3824: CURSOR update_tax_self IS
3825: SELECT /*+ leading(gt,zd) cardinality(gt,1) */
3826: zd.gl_date accounting_date , --Bug6809792
3827: /*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
3828: Ebtax will now store accounting date of opne period in their table*/
3829: aid.dist_code_combination_id dist_code_combination_id, --9437885
3830: -- this ccid is a temporary value that will be used if other conditions
3831: -- are met before inserting the tax distribution
3978: aid.invoice_distribution_id invoice_distribution_id,
3979: parent_taxable_dist.pay_awt_group_id pay_awt_group_id, --Bug8345264
3980: zd.account_source_tax_rate_id
3981: FROM zx_trx_headers_gt gt,
3982: zx_rec_nrec_dist zd,
3983: zx_lines zl,
3984: ap_invoices_all ai,
3985: ap_self_assessed_tax_dist_all aid,
3986: ap_supplier_sites_all pvs,
4657:
4658: BEGIN
4659: SELECT TAX_ONLY_LINE_FLAG
4660: INTO L_TAX_ONLY_LINE_FLAG
4661: FROM ZX_REC_NREC_DIST
4662: WHERE TRX_ID = L_INV_DIST_INS(I).INVOICE_ID
4663: AND APPLICATION_ID = 200
4664: AND REC_NREC_TAX_DIST_ID = L_INV_DIST_INS(I).DETAIL_TAX_DIST_ID;
4665: EXCEPTION
5824: AND gt.trx_id = dist.invoice_id
5825: AND dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TRV', 'TIPV', 'TERV')
5826: AND NOT EXISTS
5827: (SELECT 'Tax Distributions'
5828: FROM zx_rec_nrec_dist zd
5829: WHERE zd.rec_nrec_tax_dist_id = dist.detail_tax_dist_id
5830: AND NVL(SELF_ASSESSED_FLAG, 'N') = 'N')-- bug 7422547
5831: ); --Bug7634436
5832:
5919: AND gt.trx_id = dist.invoice_id
5920: AND dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
5921: AND NOT EXISTS
5922: (SELECT 'Tax Distributions'
5923: FROM zx_rec_nrec_dist zd
5924: WHERE zd.rec_nrec_tax_dist_id = dist.detail_tax_dist_id
5925: AND NVL(SELF_ASSESSED_FLAG, 'N') = 'Y')-- bug 7422547
5926: ); --bug7634436
5927:
6941: /*
6942: SELECT zl.discarded_flag
6943: INTO l_discarded_flag
6944: FROM zx_lines zl,
6945: zx_rec_nrec_dist zd,
6946: ap_invoice_distributions aid
6947: WHERE zl.tax_line_id = zd.tax_line_id
6948: AND nvl(zl.reporting_only_flag, 'N') = 'N'
6949: AND zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
7129:
7130: CURSOR Tax_Distributions IS
7131: SELECT aid.invoice_distribution_id
7132: FROM ap_invoice_distributions aid,
7133: zx_rec_nrec_dist zd
7134: WHERE aid.invoice_id = p_invoice_id
7135: AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
7136: AND zd.tax_line_id = p_detail_tax_line_id;
7137:
10299: -- Bug 9499176 : Replaced 1 SQL having NVL with 2 SQLs and added extra conditions
10300: IF p_line_number is NOT NULL THEN
10301: SELECT COUNT('INCLUSIVE TAX CALC ON POSTED DISTS')
10302: INTO l_allow_update
10303: FROM zx_rec_nrec_dist ZD
10304: WHERE ZD.application_id = 200
10305: AND ZD.entity_code = 'AP_INVOICES'
10306: AND ZD.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
10307: AND ZD.trx_id = p_invoice_id
10327: AND ROWNUM = 1 ;
10328: ELSE
10329: SELECT COUNT('INCLUSIVE TAX CALC ON POSTED DISTS')
10330: INTO l_allow_update
10331: FROM zx_rec_nrec_dist ZD
10332: WHERE ZD.application_id = 200
10333: AND ZD.entity_code = 'AP_INVOICES'
10334: AND ZD.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
10335: AND ZD.trx_id = p_invoice_id