[Home] [Help]
737:
738: /*-------------------------------------------------------------------------+
739: | update_ctl_rem_orig |
740: +-------------------------------------------------------------------------+
741: | Update ra_customer_trx_lines |
742: | remaining and original amount |
743: | base on result in ra_ar_gt |
744: +-------------------------------------------------------------------------+
745: | parameter: |
2870: ,SUM_INT_UNED_LINE_AMOUNT
2871: ,SUM_INT_UNED_TAX_AMOUNT
2872: --}
2873: )
2874: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
2875: p_gt_id , --GT_ID
2876: p_ae_sys_rec.base_currency , --BASE_CURRENCY
2877: trx.invoice_currency_code , --TO_CURRENCY
2878: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
3182: ,CASE WHEN g_uned_line_flag = 'INTERFACE' THEN it.sum_uned_line_amount ELSE NULL END -- SUM_INT_UNED_LINE_AMOUNT
3183: ,CASE WHEN g_uned_tax_flag = 'INTERFACE' THEN it.sum_uned_tax_amount ELSE NULL END -- SUM_INT_UNED_TAX_AMOUNT
3184: --}
3185: FROM ra_customer_trx trx,
3186: ra_customer_trx_lines_gt ctl,
3187: (SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
3188: gt_id gt_id
3189: ,customer_trx_id customer_trx_id
3190: ,customer_trx_line_id customer_trx_line_id
3268: localdebug(' g_uned_req :'||g_uned_req);
3269: END IF;
3270: ------------------------------------------------------------------------
3271: -- Create groups
3272: -- The group_id used currently is ra_customer_trx_lines.SOURCE_DATA_KEY1
3273: ------------------------------------------------------------------------
3274: INSERT INTO ra_ar_gt
3275: (gt_id,
3276: gp_level,
5032: localdebug(' p_source_data_key2 :'||p_source_data_key2);
5033: localdebug(' p_source_data_key3 :'||p_source_data_key3);
5034: localdebug(' p_source_data_key4 :'||p_source_data_key4);
5035: localdebug(' p_source_data_key5 :'||p_source_data_key5);
5036: localdebug(' Update rem amount in ra_customer_trx_lines_gt for regular transaction');
5037: END IF;
5038: IF p_customer_trx_line_id IS NOT NULL THEN
5039: IF p_log_inv_line = 'N' THEN
5040:
5037: END IF;
5038: IF p_customer_trx_line_id IS NOT NULL THEN
5039: IF p_log_inv_line = 'N' THEN
5040:
5041: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N1)*/ ra_customer_trx_lines_gt a
5042: SET (a.AMOUNT_DUE_REMAINING ,
5043: a.ACCTD_AMOUNT_DUE_REMAINING ,
5044: a.AMOUNT_DUE_ORIGINAL ,
5045: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
5206: --}
5207: AND a.line_type IN ('LINE','FREIGHT','TAX','CB','CHARGES');
5208:
5209: ELSE
5210: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N1)*/ ra_customer_trx_lines_gt a
5211: SET (a.AMOUNT_DUE_REMAINING ,
5212: a.ACCTD_AMOUNT_DUE_REMAINING ,
5213: a.AMOUNT_DUE_ORIGINAL ,
5214: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
5374: = DECODE(p_source_data_key5, NULL, '-99', a.source_data_key5)
5375: --}
5376: AND a.line_type IN ('LINE','CB');
5377:
5378: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N2)*/ ra_customer_trx_lines_gt a
5379: SET (a.AMOUNT_DUE_REMAINING ,
5380: a.ACCTD_AMOUNT_DUE_REMAINING ,
5381: a.AMOUNT_DUE_ORIGINAL ,
5382: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
5543: --}
5544: AND a.line_type IN ('FREIGHT','TAX','CHARGES');
5545: END IF; -- End of p_log_inv_line check
5546: ELSE
5547: UPDATE /*+ index(A RA_CUSTOMER_TRX_LINES_GT_N1)*/ ra_customer_trx_lines_gt a
5548: SET (a.AMOUNT_DUE_REMAINING ,
5549: a.ACCTD_AMOUNT_DUE_REMAINING ,
5550: a.AMOUNT_DUE_ORIGINAL ,
5551: a.ACCTD_AMOUNT_DUE_ORIGINAL ,
7106: 'Y' -- ref_mf_dist_flag
7107: FROM ra_ar_gt rar,
7108: psa_mf_adj_dist_all rrc,
7109: ra_cust_trx_line_gl_dist ctlgd,
7110: ra_customer_trx_lines ctl,
7111: ar_adjustments adj
7112: WHERE rar.gt_id = p_gt_id
7113: AND rar.ref_customer_trx_id = p_customer_trx_id
7114: AND rar.gp_level = 'L'
8107: localdebug(' Setting the Original and Remaining amounts R12');
8108: END IF;
8109:
8110: FORALL i IN l_ctl_tab.FIRST .. l_ctl_tab.LAST
8111: UPDATE ra_customer_trx_lines
8112: SET AMOUNT_DUE_REMAINING = l_amt_tab(i),
8113: ACCTD_AMOUNT_DUE_REMAINING = l_acctd_amt_tab(i),
8114: AMOUNT_DUE_ORIGINAL = l_amt_tab(i),
8115: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_tab(i),
8206: localdebug(' Setting the Original and Remaining amounts MFAR');
8207: END IF;
8208:
8209: FORALL i IN l_ctl_tab.FIRST .. l_ctl_tab.LAST
8210: UPDATE ra_customer_trx_lines
8211: SET AMOUNT_DUE_REMAINING = l_rem_amt_tab(i), -- Rem
8212: ACCTD_AMOUNT_DUE_REMAINING = l_rem_amt_tab(i), -- Rem transaction currency = functional
8213: AMOUNT_DUE_ORIGINAL = l_orig_amt_tab(i), -- original
8214: ACCTD_AMOUNT_DUE_ORIGINAL = l_orig_amt_tab(i), -- original
8289: ard.ref_account_class,
8290: ard.activity_bucket,
8291: ctl.line_type
8292: FROM ar_distributions ard,
8293: ra_customer_trx_lines ctl
8294: WHERE ctl.customer_trx_id = p_customer_trx_id
8295: AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
8296: GROUP BY
8297: ard.ref_customer_trx_line_id,
8359: EXIT;
8360: END IF;
8361:
8362: FORALL i IN l_CUSTOMER_TRX_LINE_ID.FIRST .. l_CUSTOMER_TRX_LINE_ID.LAST
8363: UPDATE ra_customer_trx_lines
8364: SET amount_due_original = l_sum_orig(i),
8365: acctd_amount_due_original = l_sum_acctd_orig(i),
8366: AMOUNT_DUE_REMAINING = l_sum_orig(i) + l_REM_TYPE_LINE(i),
8367: ACCTD_AMOUNT_DUE_REMAINING = l_sum_acctd_orig(i) + l_ACCTD_REM_TYPE_LINE(i),
8479: /*FORALL*/
8480: FOR i IN l_ctl_tab.FIRST .. l_ctl_tab.LAST
8481: LOOP
8482: BEGIN
8483: UPDATE ra_customer_trx_lines
8484: SET AMOUNT_DUE_REMAINING = l_amt_tab(i),
8485: ACCTD_AMOUNT_DUE_REMAINING = l_acctd_amt_tab(i),
8486: AMOUNT_DUE_ORIGINAL = l_amt_tab(i),
8487: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_tab(i),
8515: --org_id,
8516: customer_trx_line_id,
8517: l_msg
8518: FROM
8519: ra_customer_trx_lines lines
8520: WHERE
8521: lines.customer_trx_id = l_ctl_hd_tab(i);
8522: END;
8523: END LOOP;
8537: WHERE ctt.CUST_TRX_TYPE_ID = inv.CUST_TRX_TYPE_ID
8538: AND ctt.TYPE in ('INV','DM'))
8539: AND NOT EXISTS (
8540: SELECT 1
8541: FROM ra_customer_trx_lines l, ra_interface_errors e
8542: WHERE l.customer_trx_id = inv.customer_trx_id
8543: AND l.customer_trx_line_id = e.interface_line_id);
8544:
8545: -- Setting the Original and Remaining amounts R12
8592: --org_id,
8593: customer_trx_line_id,
8594: l_msg
8595: FROM
8596: ra_customer_trx_lines lines
8597: WHERE
8598: lines.customer_trx_id = l_ctl_app_tab(i);
8599: END;
8600: END LOOP;
8631: SELECT trx.upgrade_method,
8632: ctl.amount_due_original,
8633: ctl.amount_due_remaining
8634: FROM ra_customer_trx trx,
8635: ra_customer_trx_lines ctl
8636: WHERE trx.customer_trx_id = p_customer_trx_id
8637: AND ctl.customer_trx_id = trx.customer_trx_id;
8638:
8639: l_trx_type VARCHAR2(30);
10450: set_of_books_id,
10451: sob_type
10452: -- se_gt_id
10453: )
10454: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
10455: p_gt_id , --GT_ID
10456: p_ae_sys_rec.base_currency , --BASE_CURRENCY
10457: trx.invoice_currency_code , --TO_CURRENCY
10458: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
10688: p_ae_sys_rec.set_of_books_id,
10689: p_ae_sys_rec.sob_type
10690: -- g_se_gt_id
10691: FROM ra_customer_trx trx,
10692: ra_customer_trx_lines_gt ctl
10693: WHERE trx.customer_trx_id = p_customer_trx_id
10694: AND trx.customer_trx_id = ctl.customer_trx_id
10695: -- AND NVL(ctl.group_id,'00') = p_group_id
10696: --{HYUBPAGP
10789: --
10790: set_of_books_id,
10791: sob_type)
10792: -- se_gt_id)
10793: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
10794: p_gt_id , --GT_ID
10795: p_ae_sys_rec.base_currency , --BASE_CURRENCY
10796: trx.invoice_currency_code , --TO_CURRENCY
10797: trx.customer_trx_id , --REF_CUSTOMER_TRX_ID
10934: p_ae_sys_rec.sob_type
10935: -- g_se_gt_id
10936: FROM ra_customer_trx trx,
10937: ( select *
10938: from ra_customer_trx_lines_gt ctl2
10939: where ctl2.customer_trx_id = p_customer_trx_id
10940: and ctl2.customer_trx_line_id = p_customer_trx_line_id
10941: union all
10942: select *
10939: where ctl2.customer_trx_id = p_customer_trx_id
10940: and ctl2.customer_trx_line_id = p_customer_trx_line_id
10941: union all
10942: select *
10943: from ra_customer_trx_lines_gt ctl2
10944: where ctl2.customer_trx_id = p_customer_trx_id
10945: and ctl2.LINK_TO_CUST_TRX_LINE_ID = p_customer_trx_line_id
10946: ) ctl
10947: WHERE trx.customer_trx_id = p_customer_trx_id;
11624: +------------------------------------------------*/
11625: PROCEDURE copy_trx_lines
11626: (p_customer_trx_id IN NUMBER,
11627: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
11628: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
11629: IS
11630: CURSOR cu_line_loaded (p_customer_trx_id IN NUMBER,
11631: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE ) IS
11632: SELECT 'x'
11627: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
11628: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
11629: IS
11630: CURSOR cu_line_loaded (p_customer_trx_id IN NUMBER,
11631: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE ) IS
11632: SELECT 'x'
11633: FROM ra_customer_trx_lines_gt
11634: WHERE customer_trx_id = p_customer_trx_id
11635: AND customer_trx_line_id = NVL(p_customer_trx_line_id, customer_trx_line_id);
11629: IS
11630: CURSOR cu_line_loaded (p_customer_trx_id IN NUMBER,
11631: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE ) IS
11632: SELECT 'x'
11633: FROM ra_customer_trx_lines_gt
11634: WHERE customer_trx_id = p_customer_trx_id
11635: AND customer_trx_line_id = NVL(p_customer_trx_line_id, customer_trx_line_id);
11636: l_exist VARCHAR2(1);
11637:
11636: l_exist VARCHAR2(1);
11637:
11638: CURSOR c_frt_chrg IS
11639: SELECT MAX(line_type)
11640: FROM ra_customer_trx_lines_gt
11641: WHERE line_type IN ('CHARGES','FREIGHT')
11642: AND customer_trx_id = p_customer_trx_id
11643: GROUP BY line_type;
11644:
11667: localdebug(' g_mode_process = :'||g_mode_process);
11668: localdebug(' g_adj_yn = :'||g_adj_yn );
11669: END IF;
11670:
11671: INSERT INTO ra_customer_trx_lines_gt
11672: (customer_trx_line_id,
11673: link_to_cust_trx_line_id,
11674: customer_trx_id ,
11675: set_of_books_id ,
11746: -- DECODE(typ.type, 'BR','BR_BR_ORIG_ASSIG', --BR assigned to a BR
11747: -- 'BR_TRX_ORIG_ASSIG')--Regular TRX assigned to BR
11748: -- ) -- LINE_ORIGIN
11749: --}
11750: FROM ra_customer_trx_lines tl,
11751: -- ra_customer_trx br,
11752: -- ra_cust_trx_types typ,
11753: -- Amount for original and remaining for all type of lines on reve line
11754: (SELECT SUM(AMOUNT) sum_amt,
11769: ard.ref_customer_trx_line_id,
11770: ard.activity_bucket
11771: FROM ar_adjustments adj,
11772: ar_distributions ard,
11773: ra_customer_trx_lines ctl
11774: WHERE ctl.customer_trx_id = p_customer_trx_id
11775: AND ctl.line_type = 'LINE'
11776: AND adj.customer_trx_id = p_customer_trx_id
11777: AND adj.adjustment_id = ard.source_id
11792: l_rows := sql%rowcount;
11793: localdebug(' rows inserted = ' || l_rows);
11794: ELSE
11795:
11796: INSERT INTO ra_customer_trx_lines_gt
11797: (CUSTOMER_TRX_LINE_ID,
11798: LINK_TO_CUST_TRX_LINE_ID,
11799: CUSTOMER_TRX_ID ,
11800: SET_OF_BOOKS_ID ,
11871: -- DECODE(typ.type, 'BR','BR_BR_ORIG_ASSIG', --BR assigned to a BR
11872: -- 'BR_TRX_ORIG_ASSIG')--Regular TRX assigned to BR
11873: -- ) -- LINE_ORIGIN
11874: --}
11875: FROM ra_customer_trx_lines tl
11876: -- ra_customer_trx br,
11877: -- ra_cust_trx_types typ
11878: WHERE tl.customer_trx_id = p_customer_trx_id
11879: AND (tl.customer_trx_line_id = NVL(p_customer_trx_line_id, tl.customer_trx_line_id)
11925: PROCEDURE final_update_inv_ctl_rem_orig
11926: (p_customer_trx IN ra_customer_trx%ROWTYPE)
11927: IS
11928: CURSOR c(p_customer_trx_id IN NUMBER) IS
11929: SELECT /*+INDEX (ctl ra_customer_trx_lines_gt_n1)*/
11930: b.AMOUNT_DUE_REMAINING ,
11931: b.ACCTD_AMOUNT_DUE_REMAINING,
11932: b.AMOUNT_DUE_ORIGINAL ,
11933: b.ACCTD_AMOUNT_DUE_ORIGINAL ,
11939: b.frt_ed_acctd_amount,
11940: b.frt_uned_amount,
11941: b.frt_uned_acctd_amount,
11942: b.customer_trx_line_id
11943: FROM ra_customer_trx_lines_gt b
11944: WHERE b.customer_trx_id = p_customer_trx_id;
11945:
11946: l_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
11947: l_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
11990: EXIT;
11991: END IF;
11992:
11993: FORALL i IN l_ctl_id_tab.FIRST .. l_ctl_id_tab.LAST
11994: UPDATE ra_customer_trx_lines
11995: SET AMOUNT_DUE_REMAINING = l_amt_rem_tab(i),
11996: ACCTD_AMOUNT_DUE_REMAINING = l_acctd_amt_rem_tab(i),
11997: AMOUNT_DUE_ORIGINAL = l_amt_orig_tab(i),
11998: ACCTD_AMOUNT_DUE_ORIGINAL = l_acctd_amt_orig_tab(i),
12724: END;
12725:
12726:
12727: PROCEDURE display_cust_trx_row
12728: (p_record IN ra_customer_trx_lines_gt%ROWTYPE)
12729: IS
12730: BEGIN
12731: IF PG_DEBUG = 'Y' THEN
12732: localdebug('arp_det_dist_pkg.display_cust_trx_row()+');
12759: PROCEDURE display_cust_trx_gt(p_customer_trx_id IN NUMBER)
12760: IS
12761: CURSOR c IS
12762: SELECT *
12763: FROM ra_customer_trx_lines_gt
12764: WHERE customer_trx_id = p_customer_trx_id;
12765: l_record c%ROWTYPE;
12766: BEGIN
12767: IF PG_DEBUG = 'Y' THEN
12787: x_amt_rem IN OUT NOCOPY g_amt_rem_type) IS
12788: CURSOR c IS
12789: SELECT SUM(NVL(amount_due_original,extended_amount)),
12790: line_type
12791: FROM ra_customer_trx_lines
12792: WHERE customer_trx_id = p_customer_trx_id
12793: GROUP BY line_type;
12794:
12795: CURSOR tl_for_rl IS
12793: GROUP BY line_type;
12794:
12795: CURSOR tl_for_rl IS
12796: SELECT customer_trx_line_id
12797: FROM ra_customer_trx_lines
12798: WHERE customer_trx_id = p_customer_trx_id
12799: AND line_type IN ('LINE','CB')
12800: MINUS
12801: SELECT link_to_cust_trx_line_id
12798: WHERE customer_trx_id = p_customer_trx_id
12799: AND line_type IN ('LINE','CB')
12800: MINUS
12801: SELECT link_to_cust_trx_line_id
12802: FROM ra_customer_trx_lines
12803: WHERE customer_trx_id = p_customer_trx_id
12804: AND line_type = 'TAX';
12805:
12806: l_amt NUMBER;
13964: p_adj_rec IN ar_adjustments%ROWTYPE)
13965: IS
13966: CURSOR c1 IS
13967: SELECT ctl.customer_trx_id
13968: FROM ra_customer_trx_lines ctl
13969: WHERE ctl.customer_trx_id = p_customer_trx_id
13970: AND ctl.autorule_complete_flag||'' = 'N'
13971: GROUP BY ctl.customer_trx_id;
13972: l_dummy NUMBER;
14031: ,x_msg_data OUT NOCOPY VARCHAR2)
14032: IS
14033: CURSOR c1(p_customer_trx_id IN NUMBER) IS
14034: SELECT ctl.customer_trx_id
14035: FROM ra_customer_trx_lines ctl
14036: WHERE ctl.customer_trx_id = p_customer_trx_id
14037: AND ctl.autorule_complete_flag||'' = 'N'
14038: GROUP BY ctl.customer_trx_id;
14039:
16150: | x_freight_rem The remaining freight amount for the level TRANSACTION only|
16151: | x_charges_rem The remaining charges amount for the level TRANSACTION only|
16152: +-----------------------------------------------------------------------------+
16153: | Action : |
16154: | Read the remaining amount on ra_customer_trx_lines_gt |
16155: +-----------------------------------------------------------------------------*/
16156: PROCEDURE get_latest_amount_remaining
16157: (p_customer_trx_id IN NUMBER,
16158: p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
16170: x_msg_data OUT NOCOPY VARCHAR2,
16171: x_msg_count OUT NOCOPY NUMBER)
16172: IS
16173: CURSOR c_trx IS
16174: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
16175: SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
16176: SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
16177: SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) +
16178: SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))) chrg_rem,
16177: SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) +
16178: SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))) chrg_rem,
16179: SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
16180: SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
16181: FROM ra_customer_trx_lines_gt
16182: WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
16183:
16184: CURSOR c_line IS
16185: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
16181: FROM ra_customer_trx_lines_gt
16182: WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
16183:
16184: CURSOR c_line IS
16185: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
16186: SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
16187: SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
16188: SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) +
16189: SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))) chrg_rem,
16188: SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) +
16189: SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))) chrg_rem,
16190: SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
16191: SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
16192: FROM ra_customer_trx_lines_gt
16193: WHERE CUSTOMER_TRX_ID = p_customer_trx_id
16194: AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
16195:
16196: CURSOR c_gp IS
16193: WHERE CUSTOMER_TRX_ID = p_customer_trx_id
16194: AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
16195:
16196: CURSOR c_gp IS
16197: SELECT /*+INDEX (ra_customer_trx_lines_gt ra_customer_trx_lines_gt_n1)*/
16198: SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
16199: SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
16200: SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) +
16201: SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))) chrg_rem,
16200: SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) +
16201: SUM(DECODE(line_type,'CHARGES',NVL(AMOUNT_DUE_REMAINING,0))) chrg_rem,
16202: SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
16203: SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
16204: FROM ra_customer_trx_lines_gt
16205: WHERE CUSTOMER_TRX_ID = p_customer_trx_id
16206: AND source_data_key1 = p_source_data_key1
16207: AND source_data_key2 = p_source_data_key2
16208: AND source_data_key3 = p_source_data_key3
16295: FROM
16296: (SELECT SUM(DECODE(line_type,'LINE',amount_due_remaining,0)) Line_Balance
16297: ,SUM(DECODE(line_type,'TAX',amount_due_remaining,0)) Tax_Balance
16298: ,customer_trx_id
16299: FROM ra_customer_trx_lines
16300: GROUP BY customer_trx_id) a,
16301: (SELECT SUM(DECODE(line_type,'LINE', frt_adj_remaining, 0)) Frt_adj,
16302: SUM(DECODE(line_type,'LINE', frt_ed_amount, 0)) Frt_ed,
16303: SUM(DECODE(line_type,'LINE', frt_uned_amount, 0)) Frt_uned,
16302: SUM(DECODE(line_type,'LINE', frt_ed_amount, 0)) Frt_ed,
16303: SUM(DECODE(line_type,'LINE', frt_uned_amount, 0)) Frt_uned,
16304: SUM(DECODE(line_type,'FREIGHT',amount_due_remaining, 0)) Frt_app,
16305: customer_trx_id
16306: FROM ra_customer_trx_lines
16307: GROUP BY customer_trx_id) b,
16308: (SELECT SUM(DECODE(line_type,'LINE', chrg_amount_remaining, 0)) chrg_adj,
16309: SUM(DECODE(line_type,'CHARGES',amount_due_remaining, 0)) chrg_app,
16310: customer_trx_id
16307: GROUP BY customer_trx_id) b,
16308: (SELECT SUM(DECODE(line_type,'LINE', chrg_amount_remaining, 0)) chrg_adj,
16309: SUM(DECODE(line_type,'CHARGES',amount_due_remaining, 0)) chrg_app,
16310: customer_trx_id
16311: FROM ra_customer_trx_lines
16312: GROUP BY customer_trx_id) d,
16313: ar_payment_schedules c
16314: WHERE b.customer_trx_id = a.customer_trx_id
16315: AND a.customer_trx_id = c.customer_trx_id
16345: SUM(ctlgd.ACCTD_AMOUNT) sum_acctd_orig,
16346: ctl.line_type line_type,
16347: ctx.invoice_currency_code invoice_currency_code
16348: FROM ra_customer_trx ctx,
16349: ra_customer_trx_lines ctl,
16350: ra_cust_trx_line_gl_dist ctlgd
16351: WHERE ctx.customer_trx_id = p_customer_trx_id
16352: AND ctx.customer_trx_id = ctl.customer_trx_id
16353: AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
16673: x_return_status IN OUT NOCOPY VARCHAR2,
16674: x_msg_count IN OUT NOCOPY NUMBER,
16675: x_msg_data IN OUT NOCOPY VARCHAR2,
16676: p_llca_from_call IN VARCHAR2 DEFAULT NULL,
16677: p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
16678: IS
16679: l_app_rec ar_receivable_applications%ROWTYPE;
16680: BEGIN
16681: IF PG_DEBUG = 'Y' THEN