[Home] [Help]
62: p_index IN NUMBER,
63: x_return_status OUT NOCOPY VARCHAR2);
64:
65: PROCEDURE insert_item_dist(
66: p_tax_line_rec IN zx_lines%ROWTYPE,
67: x_return_status OUT NOCOPY VARCHAR2);
68:
69: PROCEDURE insert_global_table(
70: p_rec_nrec_dist_tbl IN OUT NOCOPY rec_nrec_dist_tbl_type,
121: p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
122: x_return_status OUT NOCOPY VARCHAR2) IS
123:
124: CURSOR get_item_dist_csr(
125: c_trx_line_id zx_lines.trx_line_id%TYPE,
126: c_trx_level_type zx_lines.trx_level_type%TYPE) IS
127: SELECT /*+ INDEX(ZX_ITM_DISTRIBUTIONS_GT ZX_ITM_DISTRIBUTIONS_GT_U1 ZX_ITM_DISTRIBUTIONS_GT_U1) */
128: trx_line_dist_id,
129: trx_line_id,
122: x_return_status OUT NOCOPY VARCHAR2) IS
123:
124: CURSOR get_item_dist_csr(
125: c_trx_line_id zx_lines.trx_line_id%TYPE,
126: c_trx_level_type zx_lines.trx_level_type%TYPE) IS
127: SELECT /*+ INDEX(ZX_ITM_DISTRIBUTIONS_GT ZX_ITM_DISTRIBUTIONS_GT_U1 ZX_ITM_DISTRIBUTIONS_GT_U1) */
128: trx_line_dist_id,
129: trx_line_id,
130: trx_level_type,
180: AND trx_line_id = c_trx_line_id
181: AND trx_level_type = c_trx_level_type;
182:
183: CURSOR get_pseuso_line_info_csr(
184: c_trx_line_id zx_lines.trx_line_id%TYPE,
185: c_trx_level_type zx_lines.trx_level_type%TYPE) IS
186: SELECT trx_line_id,
187: trx_level_type,
188: line_intended_use,
181: AND trx_level_type = c_trx_level_type;
182:
183: CURSOR get_pseuso_line_info_csr(
184: c_trx_line_id zx_lines.trx_line_id%TYPE,
185: c_trx_level_type zx_lines.trx_level_type%TYPE) IS
186: SELECT trx_line_id,
187: trx_level_type,
188: line_intended_use,
189: line_amt,
209: -- adjusted_doc_line_id,
210: -- applied_from_trx_level_type,
211: -- adjusted_doc_trx_level_type,
212: nvl(trx_line_gl_date, trx_date) -- item dist gl date
213: FROM zx_lines_det_factors
214: WHERE application_id = p_event_class_rec.application_id
215: AND event_class_code = p_event_class_rec.event_class_code
216: AND entity_code = p_event_class_rec.entity_code
217: AND trx_id = p_event_class_rec.trx_id
276: l_tax_line_tbl tax_line_tbl_type;
277: tax_line_counter NUMBER;
278:
279: l_counter NUMBER;
280: l_old_trx_line_id zx_lines.trx_line_id%TYPE;
281: l_old_trx_level_type zx_lines.trx_level_type%TYPE;
282: l_trx_line_id zx_lines.trx_line_id%TYPE;
283: l_trx_level_type zx_lines.trx_level_type%TYPE;
284: l_rec_nrec_dist_tbl rec_nrec_dist_tbl_type;
277: tax_line_counter NUMBER;
278:
279: l_counter NUMBER;
280: l_old_trx_line_id zx_lines.trx_line_id%TYPE;
281: l_old_trx_level_type zx_lines.trx_level_type%TYPE;
282: l_trx_line_id zx_lines.trx_line_id%TYPE;
283: l_trx_level_type zx_lines.trx_level_type%TYPE;
284: l_rec_nrec_dist_tbl rec_nrec_dist_tbl_type;
285:
278:
279: l_counter NUMBER;
280: l_old_trx_line_id zx_lines.trx_line_id%TYPE;
281: l_old_trx_level_type zx_lines.trx_level_type%TYPE;
282: l_trx_line_id zx_lines.trx_line_id%TYPE;
283: l_trx_level_type zx_lines.trx_level_type%TYPE;
284: l_rec_nrec_dist_tbl rec_nrec_dist_tbl_type;
285:
286: -- begin and end index for tax distribution for the same tax line and item dist
279: l_counter NUMBER;
280: l_old_trx_line_id zx_lines.trx_line_id%TYPE;
281: l_old_trx_level_type zx_lines.trx_level_type%TYPE;
282: l_trx_line_id zx_lines.trx_line_id%TYPE;
283: l_trx_level_type zx_lines.trx_level_type%TYPE;
284: l_rec_nrec_dist_tbl rec_nrec_dist_tbl_type;
285:
286: -- begin and end index for tax distribution for the same tax line and item dist
287: l_rec_nrec_dist_begin_index NUMBER;
343: IF (p_event_class_rec.tax_event_type_code = 'RE-DISTRIBUTE')
344: AND p_event_class_rec.quote_flag <> 'Y'
345: THEN
346:
347: -- call TRL service to update Item_Dist_Changed_Flag on ZX_LINES.
348: --
349: ZX_TRL_MANAGE_TAX_PKG.Update_Item_Dist_Changed_Flag(
350: x_return_status,
351: p_event_class_rec);
1087: SELECT tax_line_id,
1088: tax_amt,
1089: tax_amt_tax_curr,
1090: tax_amt_funcl_curr
1091: FROM zx_lines
1092: WHERE trx_id =
1093: p_event_class_rec.trx_id
1094: AND application_id =
1095: p_event_class_rec.application_id
1549: x_return_status OUT NOCOPY VARCHAR2) IS
1550:
1551: CURSOR get_hold_status_csr IS
1552: SELECT DISTINCT tax_hold_code - tax_hold_released_code
1553: FROM zx_lines
1554: WHERE tax_hold_code > 0
1555: AND trx_id = p_transaction_rec.trx_id
1556: AND application_id = p_transaction_rec.application_id
1557: AND entity_code = p_transaction_rec.entity_code
1862: THEN 'N'
1863: ELSE 'Y' END JUR_CODE_NOT_EFFECTIVE
1864:
1865: FROM
1866: ZX_LINES line ,
1867: ZX_REGIMES_B regime ,
1868: ZX_TAXES_B tax ,
1869: ZX_STATUS_B status ,
1870: ZX_RATES_B rate ,
2023: l_org_id_tbl,
2024: l_gl_date_tbl
2025: FROM zx_rec_nrec_dist zd, zx_reverse_dist_gt gt,
2026: --Bug 9883546
2027: zx_reverse_trx_lines_gt lines_gt, zx_lines zl
2028: WHERE zd.application_id = gt.reversed_appln_id
2029: AND zd.entity_code = gt.reversed_entity_code
2030: AND zd.event_class_code = gt.reversed_evnt_cls_code
2031: AND zd.trx_id = gt.reversed_trx_id
2075:
2076: ELSE
2077:
2078: -- bug 14740699 forcing hint
2079: SELECT /*+ leading (GT, ZL) index (ZL ZX_LINES_U1) */
2080: zd.rec_nrec_tax_dist_id,
2081: gt.reversing_appln_id,
2082: gt.reversing_entity_code,
2083: gt.reversing_evnt_cls_code,
2103: l_summary_tax_line_id_tbl,
2104: l_rvrsng_trx_number_tbl,
2105: l_org_id_tbl,
2106: l_gl_date_tbl
2107: FROM zx_rec_nrec_dist zd, zx_reverse_dist_gt gt, zx_lines zl
2108: WHERE zd.application_id = gt.reversed_appln_id
2109: AND zd.entity_code = gt.reversed_entity_code
2110: AND zd.event_class_code = gt.reversed_evnt_cls_code
2111: AND zd.trx_id = gt.reversed_trx_id
3425: and p_gl_date between nvl(start_date_active,p_gl_date) and nvl(end_date_active, p_gl_date);
3426:
3427: Cursor line_acc_src_tax_rate_id(p_tax_line_id IN NUMBER) is
3428: select account_source_tax_rate_id
3429: from zx_lines
3430: where tax_line_id = p_tax_line_id;
3431:
3432: Cursor get_location_segment_id_csr(p_tax_line_id IN NUMBER) is
3433: select location_segment_id
3430: where tax_line_id = p_tax_line_id;
3431:
3432: Cursor get_location_segment_id_csr(p_tax_line_id IN NUMBER) is
3433: select location_segment_id
3434: from ra_customer_trx_lines_all inv, zx_lines cm, zx_lines zxinv
3435: where cm.tax_line_id = p_tax_line_id
3436: and cm.adjusted_doc_trx_id = inv.customer_trx_id
3437: and cm.adjusted_doc_tax_line_id = inv.tax_line_id
3438: and inv.line_type = 'TAX'
3740: | |
3741: * ======================================================================*/
3742:
3743: PROCEDURE insert_item_dist(
3744: p_tax_line_rec IN zx_lines%ROWTYPE,
3745: x_return_status OUT NOCOPY VARCHAR2) IS
3746:
3747: BEGIN
3748:
3851: --p_tax_line_rec.doc_event_status,
3852: p_tax_line_rec.tax_amt,
3853: -- 'N', -- Quote_Flag what should it be?
3854: Historical_Flag
3855: FROM zx_lines_det_factors
3856: WHERE application_id = p_tax_line_rec.application_id
3857: AND event_class_code = p_tax_line_rec.event_class_code
3858: AND entity_code = p_tax_line_rec.entity_code
3859: AND trx_id = p_tax_line_rec.trx_id
3911:
3912: detail_ctr number;
3913:
3914: CURSOR fetch_tax_lines_csr IS
3915: SELECT * FROM zx_lines
3916: WHERE trx_id = p_event_class_rec.trx_id
3917: AND application_id = p_event_class_rec.application_id
3918: AND entity_code = p_event_class_rec.entity_code
3919: AND event_class_code = p_event_class_rec.event_class_code
3936: AND mrc_tax_line_flag = 'N'
3937: --6900725
3938: ORDER BY trx_line_id, trx_level_type, account_source_tax_rate_id nulls first;
3939:
3940: CURSOR get_funcl_curr_info_csr (c_ledger_id ZX_LINES.LEDGER_ID%TYPE) IS
3941: SELECT cur.currency_code
3942: FROM fnd_currencies cur, gl_sets_of_books sob
3943: WHERE sob.set_of_books_id = c_ledger_id
3944: AND cur.currency_code = sob.currency_code;
4232: tax_reporting_flag,
4233: input_tax_classification_code,
4234: output_tax_classification_code,
4235: hq_estb_party_tax_prof_id
4236: FROM zx_lines_det_factors
4237: WHERE application_id = p_tax_line_tbl(p_index).application_id
4238: AND entity_code = p_tax_line_tbl(p_index).entity_code
4239: AND event_class_code = p_tax_line_tbl(p_index).event_class_code
4240: AND trx_id = p_tax_line_tbl(p_index).trx_id
4386: IF get_trx_info_csr%NOTFOUND THEN
4387: IF (g_level_statement >= g_current_runtime_level ) THEN
4388: FND_LOG.STRING(g_level_statement,
4389: 'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.populate_trx_line_info',
4390: 'NO DATA found in zx_lines_det_factors for trx line'||
4391: p_tax_line_tbl(p_index).trx_line_id);
4392: END IF;
4393: END IF;
4394: CLOSE get_trx_info_csr;
4703: ORDER BY tax_line_id, unrounded_rec_nrec_tax_amt DESC;
4704:
4705: CURSOR get_tax_line_amt_csr(p_tax_line_id NUMBER) IS
4706: SELECT tax_amt_funcl_curr
4707: FROM zx_lines
4708: WHERE tax_line_id = p_tax_line_id;
4709:
4710: CURSOR get_mau_info_csr IS
4711: SELECT nvl( cur.minimum_accountable_unit, power( 10, (-1 * precision))),
4990: -- 3. Calculate rounding difference for recoverable tax.
4991: -- 4. Calculate rounding difference for non-recoverable tax.
4992: -- 5. If rounding difference exists, adjust it to recoverable or
4993: -- non-recoverable tax distribution repeecttively.
4994: -- 6. Get tax amount from zx_lines for this tax_line_id
4995: -- 7. Calculate rounding difference for all tax distributions with
4996: -- the same tax_line_id.
4997: -- 8. If rounding difference > 0, adjust it to the largest
4998: -- non_recoverable tax distribution. If rounding difference < 0,
5276: END IF; -- l_non_zero_nrec(or rec)_tax_lines_tbl.COUNT <> 0
5277:
5278: END IF; -- l_nrec_tax_rounding_diff <> 0
5279:
5280: -- check rounding difference between tax line in zx_lines and
5281: -- the tax distributions in zx_rec_nrec_tax_dist with the same
5282: -- tax_line_id.
5283: --
5284: -- get the tax_amt for this tax_line_id in zx_lines
5280: -- check rounding difference between tax line in zx_lines and
5281: -- the tax distributions in zx_rec_nrec_tax_dist with the same
5282: -- tax_line_id.
5283: --
5284: -- get the tax_amt for this tax_line_id in zx_lines
5285: --
5286: OPEN get_tax_line_amt_csr(l_tax_line_id_tbl(i));
5287: FETCH get_tax_line_amt_csr INTO l_total_tax_line_amt;
5288: CLOSE get_tax_line_amt_csr;
5397: THEN
5398:
5399: -- If a tax line has only one tax distribution, there should not
5400: -- be rounding difference between the tax_amt_funcl_curr of the tax
5401: -- line in zx_lines and rec_nrec_tax_amt_funcl_curr of the tax
5402: -- distributions in zx_rec_nrec_tax_dist with the same tax_line_id.
5403: -- They are all converted from the same unrounded_tax_amt.
5404: -- So, no action is required here.
5405:
5411: --
5412:
5413:
5414: -- Initialize the PLSQL tables and local variables if a tax line in
5415: -- zx_lines has multiple tax distributions in rec_nrec_tax_dist.
5416: --
5417: l_non_zero_rec_tax_lines_tbl.DELETE;
5418: l_non_zero_nrec_tax_lines_tbl.DELETE;
5419:
5785: c_tax zx_taxes_b.tax%TYPE,
5786: c_tax_regime_code zx_regimes_b.tax_regime_code%TYPE,
5787: c_recovery_type_code zx_rules_b.recovery_type_code%TYPE,
5788: -- c_reference_application_id zx_rules_b.application_id%TYPE,
5789: c_tax_line_id zx_lines.tax_line_id%TYPE) IS
5790: SELECT rules.service_type_code,
5791: rules.priority,
5792: factor_dtls.determining_factor_code
5793: FROM zx_sco_rules_b_v rules,
5790: SELECT rules.service_type_code,
5791: rules.priority,
5792: factor_dtls.determining_factor_code
5793: FROM zx_sco_rules_b_v rules,
5794: zx_lines lines,
5795: zx_evnt_cls_mappings mappings,
5796: zx_det_factor_templ_b factors,
5797: zx_det_factor_templ_dtl factor_dtls
5798: WHERE rules.service_type_code = c_service_type_code
6005: BEGIN
6006:
6007: SELECT INTERNAL_ORGANIZATION_ID
6008: INTO l_internal_org_id
6009: FROM ZX_LINES
6010: WHERE tax_line_id = p_tax_line_id;
6011:
6012: EXCEPTION
6013: WHEN OTHERS THEN
6404: l_error_buffer VARCHAR2(200);
6405:
6406: Cursor line_acc_src_tax_rate_id(p_tax_line_id IN NUMBER) is
6407: select account_source_tax_rate_id
6408: from zx_lines
6409: where tax_line_id = p_tax_line_id;
6410:
6411: Cursor get_location_id(c_tax_line_id number) is
6412: SELECT det.ship_to_location_id,det.ship_from_location_id,
6423: det.rdng_ship_from_pty_tx_prof_id, det.rdng_bill_to_pty_tx_prof_id,
6424: det.rdng_bill_from_pty_tx_prof_id, det.rdng_ship_to_pty_tx_p_st_id,
6425: det.rdng_ship_from_pty_tx_p_st_id, det.rdng_bill_to_pty_tx_p_st_id,
6426: det.rdng_bill_from_pty_tx_p_st_id
6427: FROM zx_lines_det_factors det, zx_lines
6428: WHERE det.trx_id = zx_lines.trx_id
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6424: det.rdng_bill_from_pty_tx_prof_id, det.rdng_ship_to_pty_tx_p_st_id,
6425: det.rdng_ship_from_pty_tx_p_st_id, det.rdng_bill_to_pty_tx_p_st_id,
6426: det.rdng_bill_from_pty_tx_p_st_id
6427: FROM zx_lines_det_factors det, zx_lines
6428: WHERE det.trx_id = zx_lines.trx_id
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6432: and det.event_class_code = zx_lines.event_class_code
6425: det.rdng_ship_from_pty_tx_p_st_id, det.rdng_bill_to_pty_tx_p_st_id,
6426: det.rdng_bill_from_pty_tx_p_st_id
6427: FROM zx_lines_det_factors det, zx_lines
6428: WHERE det.trx_id = zx_lines.trx_id
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6432: and det.event_class_code = zx_lines.event_class_code
6433: and zx_lines.tax_line_id = c_tax_line_id;
6426: det.rdng_bill_from_pty_tx_p_st_id
6427: FROM zx_lines_det_factors det, zx_lines
6428: WHERE det.trx_id = zx_lines.trx_id
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6432: and det.event_class_code = zx_lines.event_class_code
6433: and zx_lines.tax_line_id = c_tax_line_id;
6434:
6427: FROM zx_lines_det_factors det, zx_lines
6428: WHERE det.trx_id = zx_lines.trx_id
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6432: and det.event_class_code = zx_lines.event_class_code
6433: and zx_lines.tax_line_id = c_tax_line_id;
6434:
6435: Cursor get_geography_type(c_tax_rate_id number) is
6428: WHERE det.trx_id = zx_lines.trx_id
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6432: and det.event_class_code = zx_lines.event_class_code
6433: and zx_lines.tax_line_id = c_tax_line_id;
6434:
6435: Cursor get_geography_type(c_tax_rate_id number) is
6436: SELECT zone_geography_type, override_geography_type,
6429: and det.trx_line_id = zx_lines.trx_line_id
6430: and det.application_id = zx_lines.application_id
6431: and det.entity_code = zx_lines.entity_code
6432: and det.event_class_code = zx_lines.event_class_code
6433: and zx_lines.tax_line_id = c_tax_line_id;
6434:
6435: Cursor get_geography_type(c_tax_rate_id number) is
6436: SELECT zone_geography_type, override_geography_type,
6437: tax_id, tax, tax_regime_code
6441: WHERE tax_rate_id = c_tax_rate_id);
6442:
6443: Cursor is_jurisdiction_acc_appl(c_tax_line_id number) is
6444: SELECT Count(*)
6445: FROM zx_lines
6446: WHERE tax_line_id = c_tax_line_id
6447: AND tax_provider_id IS NOT NULL;
6448:
6449: Cursor get_geography_use(c_zone_geography_type varchar2,
6842: 'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.INIT_MAND_COLUMNS.BEGIN',
6843: 'ZX_TRD_SERVICES_PUB_PKG.INIT_MAND_COLUMNS(+)');
6844: END IF;
6845:
6846: SELECT ZX_LINES_DET_FACTORS_S.nextval
6847: INTO p_event_class_rec.event_id
6848: FROM DUAL;
6849:
6850: