[Home] [Help]
769:
770: /*-------------------------------------------------------------------------+
771: | update_ctl_rem_orig |
772: +-------------------------------------------------------------------------+
773: | Update ra_customer_trx_lines |
774: | remaining and original amount |
775: | base on result in ra_ar_gt |
776: +-------------------------------------------------------------------------+
777: | parameter: |
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:
4192: localdebug(' g_uned_req :'||g_uned_req);
4193: END IF;
4194: ------------------------------------------------------------------------
4195: -- Create groups
4196: -- The group_id used currently is ra_customer_trx_lines.SOURCE_DATA_KEY1
4197: ------------------------------------------------------------------------
4198: INSERT INTO ra_ar_gt
4199: (gt_id,
4200: gp_level,
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 ,
7971: 'N') -- tax_inc_flag
7972: FROM ra_ar_gt rar,
7973: ar_distributions rrc,
7974: ar_receivable_applications ra,
7975: ra_customer_trx_lines ctl
7976: WHERE rar.gt_id = p_gt_id
7977: AND rar.ref_customer_trx_id = p_customer_trx_id
7978: AND rar.gp_level = 'L'
7979: AND ra.applied_customer_trx_id = p_customer_trx_id
8444: 'Y' -- ref_mf_dist_flag
8445: FROM ra_ar_gt rar,
8446: psa_mf_adj_dist_all rrc,
8447: ra_cust_trx_line_gl_dist ctlgd,
8448: ra_customer_trx_lines ctl,
8449: ar_adjustments adj
8450: WHERE rar.gt_id = p_gt_id
8451: AND rar.ref_customer_trx_id = p_customer_trx_id
8452: AND rar.gp_level = 'L'
9524: localdebug(' Setting the Original and Remaining amounts R12');
9525: END IF;
9526:
9527: FORALL i IN l_ctl_tab.FIRST .. l_ctl_tab.LAST
9528: UPDATE ra_customer_trx_lines
9529: SET AMOUNT_DUE_REMAINING = l_amt_tab(i),
9530: ACCTD_AMOUNT_DUE_REMAINING = l_acctd_amt_tab(i),
9531: AMOUNT_DUE_ORIGINAL = l_amt_tab(i),
9532: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_tab(i),
9623: localdebug(' Setting the Original and Remaining amounts MFAR');
9624: END IF;
9625:
9626: FORALL i IN l_ctl_tab.FIRST .. l_ctl_tab.LAST
9627: UPDATE ra_customer_trx_lines
9628: SET AMOUNT_DUE_REMAINING = l_rem_amt_tab(i), -- Rem
9629: ACCTD_AMOUNT_DUE_REMAINING = l_rem_amt_tab(i), -- Rem transaction currency = functional
9630: AMOUNT_DUE_ORIGINAL = l_orig_amt_tab(i), -- original
9631: ACCTD_AMOUNT_DUE_ORIGINAL = l_orig_amt_tab(i), -- original
9706: ard.ref_account_class,
9707: ard.activity_bucket,
9708: ctl.line_type
9709: FROM ar_distributions ard,
9710: ra_customer_trx_lines ctl
9711: WHERE ctl.customer_trx_id = p_customer_trx_id
9712: AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
9713: GROUP BY
9714: ard.ref_customer_trx_line_id,
9776: EXIT;
9777: END IF;
9778:
9779: FORALL i IN l_CUSTOMER_TRX_LINE_ID.FIRST .. l_CUSTOMER_TRX_LINE_ID.LAST
9780: UPDATE ra_customer_trx_lines
9781: SET amount_due_original = l_sum_orig(i),
9782: acctd_amount_due_original = l_sum_acctd_orig(i),
9783: AMOUNT_DUE_REMAINING = l_sum_orig(i) + l_REM_TYPE_LINE(i),
9784: ACCTD_AMOUNT_DUE_REMAINING = l_sum_acctd_orig(i) + l_ACCTD_REM_TYPE_LINE(i),
9896: /*FORALL*/
9897: FOR i IN l_ctl_tab.FIRST .. l_ctl_tab.LAST
9898: LOOP
9899: BEGIN
9900: UPDATE ra_customer_trx_lines
9901: SET AMOUNT_DUE_REMAINING = l_amt_tab(i),
9902: ACCTD_AMOUNT_DUE_REMAINING = l_acctd_amt_tab(i),
9903: AMOUNT_DUE_ORIGINAL = l_amt_tab(i),
9904: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_tab(i),
9932: --org_id,
9933: customer_trx_line_id,
9934: l_msg
9935: FROM
9936: ra_customer_trx_lines lines
9937: WHERE
9938: lines.customer_trx_id = l_ctl_hd_tab(i);
9939: END;
9940: END LOOP;
9954: WHERE ctt.CUST_TRX_TYPE_ID = inv.CUST_TRX_TYPE_ID
9955: AND ctt.TYPE in ('INV','DM'))
9956: AND NOT EXISTS (
9957: SELECT 1
9958: FROM ra_customer_trx_lines l, ra_interface_errors e
9959: WHERE l.customer_trx_id = inv.customer_trx_id
9960: AND l.customer_trx_line_id = e.interface_line_id);
9961:
9962: -- Setting the Original and Remaining amounts R12
10009: --org_id,
10010: customer_trx_line_id,
10011: l_msg
10012: FROM
10013: ra_customer_trx_lines lines
10014: WHERE
10015: lines.customer_trx_id = l_ctl_app_tab(i);
10016: END;
10017: END LOOP;
10048: SELECT trx.upgrade_method,
10049: ctl.amount_due_original,
10050: ctl.amount_due_remaining
10051: FROM ra_customer_trx trx,
10052: ra_customer_trx_lines ctl
10053: WHERE trx.customer_trx_id = p_customer_trx_id
10054: AND ctl.customer_trx_id = trx.customer_trx_id;
10055:
10056: l_trx_type VARCHAR2(30);
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;
13102: +------------------------------------------------*/
13103: PROCEDURE copy_trx_lines
13104: (p_customer_trx_id IN NUMBER,
13105: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
13106: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
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'
13105: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
13106: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
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);
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 ,
13214: -- DECODE(typ.type, 'BR','BR_BR_ORIG_ASSIG', --BR assigned to a BR
13215: -- 'BR_TRX_ORIG_ASSIG')--Regular TRX assigned to BR
13216: -- ) -- LINE_ORIGIN
13217: --}
13218: FROM ra_customer_trx_lines tl,
13219: -- ra_customer_trx br,
13220: -- ra_cust_trx_types typ,
13221: -- Amount for original and remaining for all type of lines on reve line
13222: (SELECT SUM(AMOUNT) sum_amt,
13237: ard.ref_customer_trx_line_id,
13238: ard.activity_bucket
13239: FROM ar_adjustments adj,
13240: ar_distributions ard,
13241: ra_customer_trx_lines ctl
13242: WHERE ctl.customer_trx_id = p_customer_trx_id
13243: AND ctl.line_type = 'LINE'
13244: AND adj.customer_trx_id = p_customer_trx_id
13245: AND adj.adjustment_id = ard.source_id
13254: ard.ref_customer_trx_line_id,
13255: ard.activity_bucket
13256: FROM ar_receivable_applications ra,
13257: ar_distributions ard,
13258: ra_customer_trx_lines ctl
13259: WHERE ctl.customer_trx_id = p_customer_trx_id
13260: AND ctl.line_type = 'LINE'
13261: AND ra.applied_customer_trx_id = p_customer_trx_id
13262: AND ra.receivable_application_id = ard.source_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 ,
13356: -- DECODE(typ.type, 'BR','BR_BR_ORIG_ASSIG', --BR assigned to a BR
13357: -- 'BR_TRX_ORIG_ASSIG')--Regular TRX assigned to BR
13358: -- ) -- LINE_ORIGIN
13359: --}
13360: FROM ra_customer_trx_lines tl
13361: -- ra_customer_trx br,
13362: -- ra_cust_trx_types typ
13363: WHERE tl.customer_trx_id = p_customer_trx_id
13364: AND (tl.customer_trx_line_id = NVL(p_customer_trx_line_id, tl.customer_trx_line_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),
13411: FROM ra_cust_trx_line_gl_dist
13412: WHERE customer_trx_id = g_cm_trx_id
13413: GROUP BY customer_trx_line_id)
13414: cm_gld,
13415: ra_customer_trx_lines cm_tl
13416: WHERE cm_tl.customer_trx_id = g_cm_trx_id
13417: AND cm_gld.customer_trx_line_id = cm_tl.customer_trx_line_id
13418: AND cm_tl.previous_customer_trx_line_id = tl.customer_trx_line_id)
13419: WHERE customer_trx_id = p_customer_trx_id;
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),
13428: sum(amount_due_remaining),
13429: sum(acctd_amount_due_original),
13430: sum(acctd_amount_due_remaining)
13431: FROM
13432: ra_customer_trx_lines cm_tl
13433: WHERE cm_tl.customer_trx_id = g_cm_trx_id
13434: AND cm_tl.previous_customer_trx_line_id = tl.customer_trx_line_id)
13435: WHERE customer_trx_id = p_customer_trx_id;
13436: l_rows := sql%rowcount;
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;
13546: END IF;
13547: IF l_boundary_flag = 'N' THEN
13548:
13549: FORALL i IN l_ctl_id_tab.FIRST .. l_ctl_id_tab.LAST
13550: UPDATE ra_customer_trx_lines
13551: SET AMOUNT_DUE_REMAINING = l_amt_rem_tab(i),
13552: ACCTD_AMOUNT_DUE_REMAINING = l_acctd_amt_rem_tab(i),
13553: AMOUNT_DUE_ORIGINAL = l_amt_orig_tab(i),
13554: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_orig_tab(i),
13564: AND customer_trx_id = p_customer_trx.customer_trx_id;
13565:
13566: ELSE
13567: FORALL i IN l_ctl_id_tab.FIRST .. l_ctl_id_tab.LAST
13568: UPDATE ra_customer_trx_lines
13569: SET AMOUNT_DUE_REMAINING = l_amt_rem_tab(i),
13570: ACCTD_AMOUNT_DUE_REMAINING = decode(l_boundary_flag, 'C',0,l_acctd_amt_rem_tab(i)),
13571: AMOUNT_DUE_ORIGINAL = l_amt_orig_tab(i),
13572: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_orig_tab(i),
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
14431: x_amt_rem IN OUT NOCOPY g_amt_rem_type) IS
14432: CURSOR c IS
14433: SELECT SUM(NVL(amount_due_original,extended_amount)),
14434: line_type
14435: FROM ra_customer_trx_lines
14436: WHERE customer_trx_id = p_customer_trx_id
14437: GROUP BY line_type;
14438:
14439: CURSOR tl_for_rl IS
14437: GROUP BY line_type;
14438:
14439: CURSOR tl_for_rl IS
14440: SELECT customer_trx_line_id
14441: FROM ra_customer_trx_lines
14442: WHERE customer_trx_id = p_customer_trx_id
14443: AND line_type IN ('LINE','CB')
14444: MINUS
14445: SELECT link_to_cust_trx_line_id
14442: WHERE customer_trx_id = p_customer_trx_id
14443: AND line_type IN ('LINE','CB')
14444: MINUS
14445: SELECT link_to_cust_trx_line_id
14446: FROM ra_customer_trx_lines
14447: WHERE customer_trx_id = p_customer_trx_id
14448: AND line_type = 'TAX';
14449:
14450: l_amt NUMBER;
15676: p_adj_rec IN ar_adjustments%ROWTYPE)
15677: IS
15678: CURSOR c1 IS
15679: SELECT ctl.customer_trx_id
15680: FROM ra_customer_trx_lines ctl
15681: WHERE ctl.customer_trx_id = p_customer_trx_id
15682: AND ctl.autorule_complete_flag||'' = 'N'
15683: GROUP BY ctl.customer_trx_id;
15684: l_dummy NUMBER;
15743: ,x_msg_data OUT NOCOPY VARCHAR2)
15744: IS
15745: CURSOR c1(p_customer_trx_id IN NUMBER) IS
15746: SELECT ctl.customer_trx_id
15747: FROM ra_customer_trx_lines ctl
15748: WHERE ctl.customer_trx_id = p_customer_trx_id
15749: AND ctl.autorule_complete_flag||'' = 'N'
15750: GROUP BY ctl.customer_trx_id;
15751:
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
18014: FROM
18015: (SELECT SUM(DECODE(line_type,'LINE',amount_due_remaining,0)) Line_Balance
18016: ,SUM(DECODE(line_type,'TAX',amount_due_remaining,0)) Tax_Balance
18017: ,customer_trx_id
18018: FROM ra_customer_trx_lines
18019: GROUP BY customer_trx_id) a,
18020: (SELECT SUM(DECODE(line_type,'LINE', frt_adj_remaining, 0)) Frt_adj,
18021: SUM(DECODE(line_type,'LINE', frt_ed_amount, 0)) Frt_ed,
18022: SUM(DECODE(line_type,'LINE', frt_uned_amount, 0)) Frt_uned,
18021: SUM(DECODE(line_type,'LINE', frt_ed_amount, 0)) Frt_ed,
18022: SUM(DECODE(line_type,'LINE', frt_uned_amount, 0)) Frt_uned,
18023: SUM(DECODE(line_type,'FREIGHT',amount_due_remaining, 0)) Frt_app,
18024: customer_trx_id
18025: FROM ra_customer_trx_lines
18026: GROUP BY customer_trx_id) b,
18027: (SELECT SUM(DECODE(line_type,'LINE', chrg_amount_remaining, 0)) chrg_adj,
18028: SUM(DECODE(line_type,'CHARGES',amount_due_remaining, 0)) chrg_app,
18029: customer_trx_id
18026: GROUP BY customer_trx_id) b,
18027: (SELECT SUM(DECODE(line_type,'LINE', chrg_amount_remaining, 0)) chrg_adj,
18028: SUM(DECODE(line_type,'CHARGES',amount_due_remaining, 0)) chrg_app,
18029: customer_trx_id
18030: FROM ra_customer_trx_lines
18031: GROUP BY customer_trx_id) d,
18032: ar_payment_schedules c
18033: WHERE b.customer_trx_id = a.customer_trx_id
18034: AND a.customer_trx_id = c.customer_trx_id
18064: SUM(ctlgd.ACCTD_AMOUNT) sum_acctd_orig,
18065: ctl.line_type line_type,
18066: ctx.invoice_currency_code invoice_currency_code
18067: FROM ra_customer_trx ctx,
18068: ra_customer_trx_lines ctl,
18069: ra_cust_trx_line_gl_dist ctlgd
18070: WHERE ctx.customer_trx_id = p_customer_trx_id
18071: AND ctx.customer_trx_id = ctl.customer_trx_id
18072: AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
18416: x_return_status IN OUT NOCOPY VARCHAR2,
18417: x_msg_count IN OUT NOCOPY NUMBER,
18418: x_msg_data IN OUT NOCOPY VARCHAR2,
18419: p_llca_from_call IN VARCHAR2 DEFAULT NULL,
18420: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
18421: IS
18422: l_app_rec ar_receivable_applications%ROWTYPE;
18423: BEGIN
18424: IF PG_DEBUG = 'Y' THEN
18773: min(ct.invoice_currency_code) inv_currency,
18774: min(sob.currency_code) ledger_currency,
18775: ctl.LINE_TYPE
18776: from ra_customer_trx ct,
18777: ra_customer_trx_lines ctl,
18778: RA_CUST_TRX_LINE_GL_DIST gld,
18779: gl_sets_of_books sob,
18780: ar_system_parameters ars
18781: where ct.customer_trx_id = p_customer_trx_id