[Home] [Help]
3441: ,SUM_INT_UNED_LINE_AMOUNT
3442: ,SUM_INT_UNED_TAX_AMOUNT
3443: --}
3444: )
3445: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
3446: p_gt_id , --GT_ID
3447: p_ae_sys_rec.base_currency , --BASE_CURRENCY
3448: trx.invoice_currency_code , --TO_CURRENCY
3449: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
3877: ,CASE WHEN g_uned_line_flag = 'INTERFACE' THEN it.sum_uned_line_amount ELSE NULL END -- SUM_INT_UNED_LINE_AMOUNT
3878: ,CASE WHEN g_uned_tax_flag = 'INTERFACE' THEN it.sum_uned_tax_amount ELSE NULL END -- SUM_INT_UNED_TAX_AMOUNT
3879: --}
3880: FROM ra_customer_trx trx,
3881: ra_customer_trx_lines_gt ctl,
3882: (SELECT
3883: gt_id gt_id
3884: ,customer_trx_id customer_trx_id
3885: ,customer_trx_line_id customer_trx_line_id
4026: sob_type,
4027: tax_link_id,
4028: tax_inc_flag
4029: )
4030: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
4031: p_gt_id , --GT_ID
4032: p_ae_sys_rec.base_currency , --BASE_CURRENCY
4033: trx.invoice_currency_code , --TO_CURRENCY
4034: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
4131: DECODE(ctl.line_type,'LINE','Y',
4132: 'CB' ,'Y',
4133: 'TAX','Y','N')
4134: FROM ra_customer_trx trx,
4135: ra_customer_trx_lines_gt ctl
4136: WHERE trx.customer_trx_id = p_customer_trx_id
4137: AND trx.customer_trx_id = ctl.customer_trx_id
4138: AND ctl.cm_amt_due_orig is not null;
4139:
5958: localdebug(' p_source_data_key3 :'||p_source_data_key3);
5959: localdebug(' p_source_data_key4 :'||p_source_data_key4);
5960: localdebug(' p_source_data_key5 :'||p_source_data_key5);
5961: localdebug(' p_log_inv_line :'||p_log_inv_line);
5962: localdebug(' Update rem amount in ra_customer_trx_lines_gt for regular transaction');
5963: END IF;
5964: IF p_customer_trx_line_id IS NOT NULL THEN
5965: IF p_log_inv_line = 'N' THEN
5966:
5963: END IF;
5964: IF p_customer_trx_line_id IS NOT NULL THEN
5965: IF p_log_inv_line = 'N' THEN
5966:
5967: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N1)*/ ra_customer_trx_lines_gt a
5968: SET (a.AMOUNT_DUE_REMAINING ,
5969: a.ACCTD_AMOUNT_DUE_REMAINING ,
5970: a.AMOUNT_DUE_ORIGINAL ,
5971: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
6132: --}
6133: AND a.line_type IN ('LINE','FREIGHT','TAX','CB','CHARGES');
6134:
6135: ELSE
6136: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N1)*/ ra_customer_trx_lines_gt a
6137: SET (a.AMOUNT_DUE_REMAINING ,
6138: a.ACCTD_AMOUNT_DUE_REMAINING ,
6139: a.AMOUNT_DUE_ORIGINAL ,
6140: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
6300: = DECODE(p_source_data_key5, NULL, '-99', a.source_data_key5)
6301: --}
6302: AND a.line_type IN ('LINE','CB');
6303:
6304: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N2)*/ ra_customer_trx_lines_gt a
6305: SET (a.AMOUNT_DUE_REMAINING ,
6306: a.ACCTD_AMOUNT_DUE_REMAINING ,
6307: a.AMOUNT_DUE_ORIGINAL ,
6308: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
6469: --}
6470: AND a.line_type IN ('FREIGHT','TAX','CHARGES');
6471: END IF; -- End of p_log_inv_line check
6472: ELSE
6473: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N1)*/ ra_customer_trx_lines_gt a
6474: SET (a.AMOUNT_DUE_REMAINING ,
6475: a.ACCTD_AMOUNT_DUE_REMAINING ,
6476: a.AMOUNT_DUE_ORIGINAL ,
6477: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
11895: set_of_books_id,
11896: sob_type
11897: -- se_gt_id
11898: )
11899: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
11900: p_gt_id , --GT_ID
11901: p_ae_sys_rec.base_currency , --BASE_CURRENCY
11902: trx.invoice_currency_code , --TO_CURRENCY
11903: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
12133: p_ae_sys_rec.set_of_books_id,
12134: p_ae_sys_rec.sob_type
12135: -- g_se_gt_id
12136: FROM ra_customer_trx trx,
12137: ra_customer_trx_lines_gt ctl
12138: WHERE trx.customer_trx_id = p_customer_trx_id
12139: AND trx.customer_trx_id = ctl.customer_trx_id
12140: -- AND NVL(ctl.group_id,'00') = p_group_id
12141: --{HYUBPAGP
12237: sob_type,
12238: tax_link_id,
12239: tax_inc_flag)
12240: -- se_gt_id)
12241: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
12242: p_gt_id , --GT_ID
12243: p_ae_sys_rec.base_currency , --BASE_CURRENCY
12244: trx.invoice_currency_code , --TO_CURRENCY
12245: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
12390: 'TAX','Y','N')
12391: -- g_se_gt_id
12392: FROM ra_customer_trx trx,
12393: ( select *
12394: from ra_customer_trx_lines_gt ctl2
12395: where ctl2.customer_trx_id = p_customer_trx_id
12396: and ctl2.customer_trx_line_id = p_customer_trx_line_id
12397: union all
12398: select *
12395: where ctl2.customer_trx_id = p_customer_trx_id
12396: and ctl2.customer_trx_line_id = p_customer_trx_line_id
12397: union all
12398: select *
12399: from ra_customer_trx_lines_gt ctl2
12400: where ctl2.customer_trx_id = p_customer_trx_id
12401: and ctl2.LINK_TO_CUST_TRX_LINE_ID = p_customer_trx_line_id
12402: ) ctl
12403: WHERE trx.customer_trx_id = p_customer_trx_id;
13107: IS
13108: CURSOR cu_line_loaded (p_customer_trx_id IN NUMBER,
13109: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE ) IS
13110: SELECT 'x'
13111: FROM ra_customer_trx_lines_gt
13112: WHERE customer_trx_id = p_customer_trx_id
13113: AND customer_trx_line_id = NVL(p_customer_trx_line_id, customer_trx_line_id);
13114: l_exist VARCHAR2(1);
13115:
13114: l_exist VARCHAR2(1);
13115:
13116: CURSOR c_frt_chrg IS
13117: SELECT MAX(line_type)
13118: FROM ra_customer_trx_lines_gt
13119: WHERE line_type IN ('CHARGES','FREIGHT')
13120: AND customer_trx_id = p_customer_trx_id
13121: GROUP BY line_type;
13122:
13135:
13136: IF g_mode_process IN ('R12_NLB','R12_MERGE','R12_11ICASH')
13137: THEN
13138:
13139: INSERT INTO ra_customer_trx_lines_gt
13140: (customer_trx_line_id,
13141: link_to_cust_trx_line_id,
13142: customer_trx_id ,
13143: set_of_books_id ,
13277: l_rows := sql%rowcount;
13278: localdebug(' rows inserted = ' || l_rows);
13279: ELSE
13280:
13281: INSERT INTO ra_customer_trx_lines_gt
13282: (CUSTOMER_TRX_LINE_ID,
13283: LINK_TO_CUST_TRX_LINE_ID,
13284: CUSTOMER_TRX_ID ,
13285: SET_OF_BOOKS_ID ,
13397: localdebug('flow for regular cm');
13398: localdebug(' g_cm_trx_id :'||g_cm_trx_id);
13399: localdebug(' g_cm_upg_mthd :'||g_cm_upg_mthd);
13400: IF g_cm_upg_mthd IN ('R12_NLB','R12_MERGE') THEN
13401: UPDATE ra_customer_trx_lines_gt tl
13402: SET(cm_amt_due_orig, cm_amt_due_rem, cm_acctd_amt_due_orig, cm_acctd_amt_due_rem) =
13403: (SELECT sum(sum_amt),
13404: sum(sum_amt),
13405: sum(sum_acctd_amt),
13421: IF PG_DEBUG = 'Y' THEN
13422: localdebug(' rows updated = ' || l_rows);
13423: END IF;
13424: ELSE
13425: UPDATE ra_customer_trx_lines_gt tl
13426: SET(cm_amt_due_orig, cm_amt_due_rem, cm_acctd_amt_due_orig, cm_acctd_amt_due_rem) =
13427: (SELECT sum(amount_due_original),
13428: sum(amount_due_remaining),
13429: sum(acctd_amount_due_original),
13458: PROCEDURE final_update_inv_ctl_rem_orig
13459: (p_customer_trx IN ra_customer_trx%ROWTYPE)
13460: IS
13461: CURSOR c(p_customer_trx_id IN NUMBER) IS
13462: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
13463: b.AMOUNT_DUE_REMAINING ,
13464: b.ACCTD_AMOUNT_DUE_REMAINING,
13465: b.AMOUNT_DUE_ORIGINAL ,
13466: b.ACCTD_AMOUNT_DUE_ORIGINAL ,
13472: b.frt_ed_acctd_amount,
13473: b.frt_uned_amount,
13474: b.frt_uned_acctd_amount,
13475: b.customer_trx_line_id
13476: FROM ra_customer_trx_lines_gt b
13477: WHERE b.customer_trx_id = p_customer_trx_id;
13478:
13479: l_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
13480: l_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
14364: END;
14365:
14366:
14367: PROCEDURE display_cust_trx_row
14368: (p_record IN ra_customer_trx_lines_gt%ROWTYPE)
14369: IS
14370: BEGIN
14371: IF PG_DEBUG = 'Y' THEN
14372: localdebug('arp_det_dist_pkg.display_cust_trx_row()+');
14403: PROCEDURE display_cust_trx_gt(p_customer_trx_id IN NUMBER)
14404: IS
14405: CURSOR c IS
14406: SELECT *
14407: FROM ra_customer_trx_lines_gt
14408: WHERE customer_trx_id = p_customer_trx_id;
14409: l_record c%ROWTYPE;
14410: BEGIN
14411: IF PG_DEBUG = 'Y' THEN
17867: | x_freight_rem The remaining freight amount for the level TRANSACTION only|
17868: | x_charges_rem The remaining charges amount for the level TRANSACTION only|
17869: +-----------------------------------------------------------------------------+
17870: | Action : |
17871: | Read the remaining amount on ra_customer_trx_lines_gt |
17872: +-----------------------------------------------------------------------------*/
17873: PROCEDURE get_latest_amount_remaining
17874: (p_customer_trx_id IN NUMBER,
17875: p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
17887: x_msg_data OUT NOCOPY VARCHAR2,
17888: x_msg_count OUT NOCOPY NUMBER)
17889: IS
17890: CURSOR c_trx IS
17891: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
17892: NVL(SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))),0) line_rem,
17893: NVL(SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))),0) tax_rem,
17894: NVL(SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))),0) +
17895: NVL(SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))),0) chrg_rem,
17894: NVL(SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))),0) +
17895: NVL(SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))),0) chrg_rem,
17896: NVL(SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))),0) +
17897: NVL(SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))),0) frt_rem
17898: FROM ra_customer_trx_lines_gt
17899: WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
17900:
17901: CURSOR c_line IS
17902: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
17898: FROM ra_customer_trx_lines_gt
17899: WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
17900:
17901: CURSOR c_line IS
17902: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
17903: NVL(SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))),0) line_rem,
17904: NVL(SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))),0) tax_rem,
17905: NVL(SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))),0) +
17906: NVL(SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))),0) chrg_rem,
17905: NVL(SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))),0) +
17906: NVL(SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))),0) chrg_rem,
17907: NVL(SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))),0) +
17908: NVL(SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))),0) frt_rem
17909: FROM ra_customer_trx_lines_gt
17910: WHERE CUSTOMER_TRX_ID = p_customer_trx_id
17911: AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
17912:
17913: CURSOR c_gp IS
17910: WHERE CUSTOMER_TRX_ID = p_customer_trx_id
17911: AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
17912:
17913: CURSOR c_gp IS
17914: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
17915: NVL(SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))),0) line_rem,
17916: NVL(SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))),0) tax_rem,
17917: NVL(SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))),0) +
17918: NVL(SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))),0) chrg_rem,
17917: NVL(SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))),0) +
17918: NVL(SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))),0) chrg_rem,
17919: NVL(SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))),0) +
17920: NVL(SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))),0) frt_rem
17921: FROM ra_customer_trx_lines_gt
17922: WHERE CUSTOMER_TRX_ID = p_customer_trx_id
17923: AND source_data_key1 = p_source_data_key1
17924: AND source_data_key2 = p_source_data_key2
17925: AND source_data_key3 = p_source_data_key3