[Home] [Help]
119: g_acctd_tax_uned NUMBER := 0;
120: g_acctd_frt_uned NUMBER := 0;
121: g_acctd_chrg_uned NUMBER := 0;
122:
123: g_cust_inv_rec ra_customer_trx%ROWTYPE;
124:
125: --{
126: g_run_from_amt NUMBER;
127: g_run_from_total NUMBER;
444: END;
445:
446:
447: PROCEDURE set_mode_process
448: (p_customer_trx IN ra_customer_trx%ROWTYPE,
449: p_from_llca IN VARCHAR2 DEFAULT 'N' )
450: IS
451: CURSOR cu_trx IS
452: SELECT * FROM ra_customer_trx
448: (p_customer_trx IN ra_customer_trx%ROWTYPE,
449: p_from_llca IN VARCHAR2 DEFAULT 'N' )
450: IS
451: CURSOR cu_trx IS
452: SELECT * FROM ra_customer_trx
453: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
454: l_mark VARCHAR2(30);
455: BEGIN
456: IF PG_DEBUG = 'Y' THEN
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
3876: ,CASE WHEN g_ed_tax_flag = 'INTERFACE' THEN it.sum_ed_tax_amount ELSE NULL END -- SUM_INT_ED_TAX_AMOUNT
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
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
4130: NULL),
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;
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
8214: WHERE gt_id = p_gt_id
8215: AND ref_customer_trx_id = p_customer_trx_id
8216: AND gp_level = 'L') g,
8217: ar_adjustments adj,
8218: ra_customer_trx trx
8219: --{Line Charge and Freight boundary
8220: -- Insert freight dist or the charge dist only in the no CHARGES or FREIGHT line exist
8221: -- As by default the boundary on those 2 lines exist only if there are no REV line on the invoice
8222: -- But in the case the CHARGES or FREIGHT exist and we are not maintaining the balance on lines
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'
9472: END create_split_distribution;
9473:
9474:
9475: PROCEDURE set_original_rem_amt_r12
9476: (p_customer_trx IN ra_customer_trx%ROWTYPE,
9477: x_return_status IN OUT NOCOPY VARCHAR2,
9478: x_msg_count IN OUT NOCOPY NUMBER,
9479: x_msg_data IN OUT NOCOPY VARCHAR2,
9480: p_from_llca IN VARCHAR2 DEFAULT 'N')
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),
9548:
9549: IF PG_DEBUG = 'Y' THEN
9550: localdebug('Setting transaction.upgrade_method to R12' );
9551: END IF;
9552: UPDATE ra_customer_trx SET upgrade_method = 'R12'
9553: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
9554:
9555: IF PG_DEBUG = 'Y' THEN
9556: localdebug('arp_det_dist_pkg.set_original_rem_amt_r12()-');
9580:
9581:
9582:
9583: PROCEDURE set_original_rem_amt_mfar
9584: (p_customer_trx IN ra_customer_trx%ROWTYPE)
9585: IS
9586: CURSOR cmfar(p_customer_trx_id IN NUMBER) IS
9587: SELECT SUM(NVL(amount_due_remaining,0)) sum_due_rem,
9588: SUM(NVL(amount_due_original,0)) sum_due_orig,
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
9654: END set_original_rem_amt_mfar;
9655:
9656:
9657:
9658: PROCEDURE re_calcul_rem_amt(p_customer_trx IN ra_customer_trx%ROWTYPE)
9659: IS
9660:
9661: /*bug6133206, multiplied APP amounts with '-1' to make them negative,
9662: for calculating the remaining amounts. */
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),
9819: SUM(gld.ACCTD_AMOUNT),
9820: gld.customer_trx_line_id,
9821: inv.customer_trx_id
9822: FROM
9823: ra_customer_trx inv,
9824: ra_cust_trx_line_gl_dist gld,
9825: ra_cust_trx_types ctt
9826: WHERE inv.customer_trx_id = gld.customer_trx_id
9827: AND ctt.CUST_TRX_TYPE_ID = inv.CUST_TRX_TYPE_ID
9832: CURSOR c02 IS
9833: SELECT
9834: customer_trx_id
9835: FROM
9836: ra_customer_trx inv,
9837: ra_cust_trx_types ctt
9838: WHERE request_id = P_request_id
9839: AND ctt.CUST_TRX_TYPE_ID = inv.CUST_TRX_TYPE_ID
9840: AND ctt.TYPE in ('INV','DM')
9854: l_acctd_amt_tab DBMS_SQL.NUMBER_TABLE;
9855: l_ctl_tab DBMS_SQL.NUMBER_TABLE;
9856: l_ctl_hd_tab DBMS_SQL.NUMBER_TABLE;
9857: l_ctl_app_tab DBMS_SQL.NUMBER_TABLE;
9858: l_customer_trx ra_customer_trx%ROWTYPE;
9859: l_last_fetch BOOLEAN := FALSE;
9860:
9861: l_msg RA_INTERFACE_ERRORS.MESSAGE_TEXT%TYPE;
9862: l_return_status VARCHAR2(100);
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;
9945:
9946: IF PG_DEBUG = 'Y' THEN
9947: localdebug('Setting transaction.upgrade_method to R12' );
9948: END IF;
9949: UPDATE ra_customer_trx inv SET upgrade_method = 'R12'
9950: WHERE request_id = p_request_id
9951: AND EXISTS
9952: ( SELECT 1
9953: FROM ra_cust_trx_types ctt
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;
10035: END set_original_rem_amt_r12; -- End of OVERLOAD procedure
10036:
10037:
10038: PROCEDURE set_original_rem_amt
10039: (p_customer_trx IN ra_customer_trx%ROWTYPE,
10040: p_adj_id IN NUMBER DEFAULT NULL,
10041: p_app_id IN NUMBER DEFAULT NULL,
10042: --{HYUNLB
10043: p_from_llca IN VARCHAR2 DEFAULT 'N')
10047: CURSOR c_verif(p_customer_trx_id IN NUMBER) IS
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:
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);
10109:
10110: IF PG_DEBUG = 'Y' THEN
10111: localdebug(' going forward and it should be marked as R12');
10112: END IF;
10113: UPDATE ra_customer_trx
10114: SET upgrade_method = 'R12'
10115: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
10116: --}
10117:
10182: END IF;
10183:
10184:
10185: /* localdebug('Setting transaction.upgrade_method to R12' );
10186: UPDATE ra_customer_trx SET upgrade_method = 'R12'
10187: WHERE customer_trx_id = p_customer_trx.customer_trx_id; */
10188:
10189: ELSE
10190:
10195: --{HYUNLB
10196: -- By setting the upg_make to R12_NLB
10197: -- <=> Do not maintain the line balance
10198: -- R12_NLB
10199: UPDATE ra_customer_trx SET upgrade_method = 'R12_NLB'
10200: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
10201: --}
10202: END IF;
10203:
10214:
10215: IF PG_DEBUG = 'Y' THEN
10216: localdebug('Setting the transaction.upgrade_method to R12_11IMFAR');
10217: END IF;
10218: UPDATE ra_customer_trx SET upgrade_method = 'R12_11IMFAR'
10219: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
10220:
10221:
10222: ELSIF ((l_11i_adj = 'N') AND (l_11i_app = 'Y'))
10246: -- set_original_rem_amt_r12(p_customer_trx => p_customer_trx);
10247:
10248: localdebug('Setting the transaction.upgrade_method');
10249: END IF;
10250: UPDATE ra_customer_trx SET upgrade_method = 'R12_11ICASH'
10251: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
10252: END IF;
10253: ELSE --3
10254: -- CASH legacy R12_11ICASH
10274: -- set_original_rem_amt_r12(p_customer_trx => p_customer_trx);
10275:
10276: localdebug('Setting the transaction.upgrade_method');
10277: END IF;
10278: UPDATE ra_customer_trx SET upgrade_method = 'R12_11ICASH'
10279: WHERE customer_trx_id = p_customer_trx.customer_trx_id;
10280:
10281: END IF;
10282:
10859: GROUP BY line_type;
10860:
10861: CURSOR cu_inv_cur IS
10862: SELECT invoice_currency_code,exchange_rate
10863: FROM ra_customer_trx
10864: WHERE customer_trx_id = p_customer_trx_id;
10865: l_inv_currency VARCHAR2(30) := NULL;
10866: l_exchange_rate ra_customer_trx.exchange_rate%type;
10867: l_sum_line_rem_amt DBMS_SQL.NUMBER_TABLE;
10862: SELECT invoice_currency_code,exchange_rate
10863: FROM ra_customer_trx
10864: WHERE customer_trx_id = p_customer_trx_id;
10865: l_inv_currency VARCHAR2(30) := NULL;
10866: l_exchange_rate ra_customer_trx.exchange_rate%type;
10867: l_sum_line_rem_amt DBMS_SQL.NUMBER_TABLE;
10868: l_sum_line_rem_acctd_amt DBMS_SQL.NUMBER_TABLE;
10869: l_sum_line_orig_amt DBMS_SQL.NUMBER_TABLE;
10870: l_sum_line_orig_acctd_amt DBMS_SQL.NUMBER_TABLE;
10896: p_inv_currency IN VARCHAR2,
10897: p_customer_trx_id IN NUMBER,
10898: p_gt_id IN VARCHAR2,
10899: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
10900: P_exchange_rate IN ra_customer_trx.exchange_rate%type)
10901: IS
10902: l_ra_ar_gt ra_ar_gt%ROWTYPE;
10903: l_ar_base_dist_amts_gt ar_base_dist_amts_gt%ROWTYPE;
10904: l_ra_ar_amounts_gt ra_ar_amounts_gt%ROWTYPE;
11079: p_inv_currency IN VARCHAR2,
11080: p_customer_trx_id IN NUMBER,
11081: p_gt_id IN VARCHAR2,
11082: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
11083: P_exchange_rate IN ra_customer_trx.exchange_rate%type)
11084: IS
11085: l_ra_ar_gt ra_ar_gt%ROWTYPE;
11086: l_ar_base_dist_amts_gt ar_base_dist_amts_gt%ROWTYPE;
11087: l_ra_ar_amounts_gt ra_ar_amounts_gt%ROWTYPE;
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
12132: --
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
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
12388: DECODE(ctl.line_type,'LINE','Y',
12389: 'CB' ,'Y',
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
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;
12530: | p_app_rec ar receivable application record |
12531: | p_ae_sys_rec ar system parameter |
12532: +-------------------------------------------------------------------------*/
12533: PROCEDURE Trx_level_cash_apply
12534: (p_customer_trx IN ra_customer_trx%ROWTYPE,
12535: p_app_rec IN ar_receivable_applications%ROWTYPE,
12536: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
12537: p_gt_id IN VARCHAR2 DEFAULT NULL)
12538: IS
12706: | p_app_rec ar receivable application record |
12707: | p_ae_sys_rec ar system parameter |
12708: +-------------------------------------------------------------------------*/
12709: PROCEDURE Trx_gp_level_cash_apply
12710: (p_customer_trx IN ra_customer_trx%ROWTYPE,
12711: -- p_group_id IN VARCHAR2,
12712: --{HYUBPAGP
12713: p_source_data_key1 IN VARCHAR2,
12714: p_source_data_key2 IN VARCHAR2,
12919: | p_app_rec ar receivable application record |
12920: | p_ae_sys_rec ar system parameter |
12921: +-------------------------------------------------------------------------*/
12922: PROCEDURE Trx_line_level_cash_apply
12923: (p_customer_trx IN ra_customer_trx%ROWTYPE,
12924: p_customer_trx_line_id IN VARCHAR2,
12925: p_log_inv_line IN VARCHAR2 DEFAULT 'N',
12926: p_app_rec IN ar_receivable_applications%ROWTYPE,
12927: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
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,
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,
13223: SUM(ACCTD_AMOUNT) sum_acctd_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)
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)
13365: OR tl.link_to_cust_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;
13455:
13456:
13457:
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 ,
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),
13591: END final_update_inv_ctl_rem_orig;
13592:
13593:
13594: PROCEDURE create_final_split
13595: (p_customer_trx IN ra_customer_trx%ROWTYPE,
13596: p_app_rec IN ar_receivable_applications%ROWTYPE,
13597: p_adj_rec IN ar_adjustments%ROWTYPE,
13598: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
13599: IS
13657: | p_adj_rec ar adjustment record
13658: | p_ae_sys_rec ar system parameter
13659: +-------------------------------------------------------------------------*/
13660: PROCEDURE Trx_level_direct_adjust
13661: (p_customer_trx IN ra_customer_trx%ROWTYPE,
13662: p_adj_rec IN ar_adjustments%ROWTYPE,
13663: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
13664: p_gt_id IN NUMBER DEFAULT NULL)
13665: IS
13816: | p_app_rec ar receivable application record |
13817: | p_ae_sys_rec ar system parameter |
13818: +-------------------------------------------------------------------------*/
13819: PROCEDURE Trx_level_direct_cash_apply
13820: (p_customer_trx IN ra_customer_trx%ROWTYPE,
13821: p_app_rec IN ar_receivable_applications%ROWTYPE,
13822: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
13823: p_gt_id IN NUMBER DEFAULT NULL,
13824: p_inv_cm IN VARCHAR2 DEFAULT 'I')
13905: p_inv_cm = 'I' THEN
13906: BEGIN
13907: select customer_trx_id, nvl(upgrade_method,'R12_NLB')
13908: into g_cm_trx_id, g_cm_upg_mthd
13909: from ra_customer_trx
13910: where customer_trx_id = p_app_rec.customer_trx_id
13911: and previous_customer_trx_id = p_app_rec.applied_customer_trx_id
13912: and nvl(upgrade_method, 'R12_NLB') in ('R12','R12_NLB','R12_11IMFAR');
13913:
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;
15520: PROCEDURE exec_adj_api_if_required
15521: (p_adj_rec IN ar_adjustments%ROWTYPE,
15522: p_app_rec IN ar_receivable_applications%ROWTYPE,
15523: p_ae_rule_rec IN ae_rule_rec_type,
15524: p_cust_inv_rec IN ra_customer_trx%ROWTYPE)
15525: IS
15526:
15527: CURSOR get_group_data_rev IS
15528: SELECT account_class account_class,
15670: END;
15671:
15672:
15673: PROCEDURE exec_revrec_if_required
15674: ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
15675: p_app_rec IN ar_receivable_applications%ROWTYPE,
15676: p_adj_rec IN ar_adjustments%ROWTYPE)
15677: IS
15678: CURSOR c1 IS
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:
15750: GROUP BY ctl.customer_trx_id;
15751:
15752: CURSOR c_trx_number(p_customer_trx_id IN NUMBER) IS
15753: SELECT ct.trx_number
15754: FROM ra_customer_trx ct
15755: WHERE ct.customer_trx_id = p_customer_trx_id;
15756:
15757: l_message VARCHAR2(2000);
15758: l_trx_number VARCHAR2(20);
17166: p_inv_cm IN VARCHAR2 DEFAULT 'I',
17167: p_cash_mfar IN VARCHAR2 DEFAULT 'CASH')
17168: IS
17169: CURSOR cu_trx(p_trx_id IN NUMBER) IS
17170: SELECT * FROM ra_customer_trx_all
17171: WHERE customer_trx_id = p_trx_id;
17172: l_trx_rec ra_customer_trx_all%ROWTYPE;
17173: l_exec_status VARCHAR2(10) := 'N';
17174: l_adj_rec ar_adjustments%ROWTYPE;
17168: IS
17169: CURSOR cu_trx(p_trx_id IN NUMBER) IS
17170: SELECT * FROM ra_customer_trx_all
17171: WHERE customer_trx_id = p_trx_id;
17172: l_trx_rec ra_customer_trx_all%ROWTYPE;
17173: l_exec_status VARCHAR2(10) := 'N';
17174: l_adj_rec ar_adjustments%ROWTYPE;
17175: l_app_rec ar_receivable_applications%ROWTYPE;
17176: next_app EXCEPTION;
17521: WHERE app.customer_trx_id = trx.customer_trx_id
17522: AND NVL(app.upgrade_method,'NULL') IN ('11I_MFAR_UPG','NULL')
17523: AND NVL(p_app_id,-9) <> app.receivable_application_id -- excluded the current app record
17524: AND rownum = 1) cmapp11i
17525: FROM ra_customer_trx trx,
17526: psa_trx_types_all tt
17527: WHERE trx.customer_trx_id = p_trx_id
17528: AND trx.cust_trx_type_id = tt.psa_trx_type_id(+);
17529:
17641: END;
17642:
17643:
17644: PROCEDURE online_lazy_apply
17645: (p_customer_trx IN ra_customer_trx%ROWTYPE,
17646: p_app_rec IN ar_receivable_applications%ROWTYPE,
17647: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
17648: p_gt_id IN NUMBER,
17649: --Add the p_inv_cm flag
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
18063: (SELECT SUM(ctlgd.AMOUNT) sum_orig,
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
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
18090: l_charges_applied NUMBER;
18091: l_base NUMBER;
18092: l_run_amt NUMBER;
18093: l_run_total NUMBER;
18094: l_inv_rec ra_customer_trx%ROWTYPE;
18095: no_rem_for_trx EXCEPTION;
18096: no_amount_remaining EXCEPTION;
18097: no_rem_for_trx_upg EXCEPTION;
18098:
18405: g_uned_tax_flag := p_uned_tax_flag;
18406: END set_interface_flag;
18407:
18408: PROCEDURE adjustment_with_interface
18409: (p_customer_trx IN ra_customer_trx%ROWTYPE,
18410: p_adj_rec IN ar_adjustments%ROWTYPE,
18411: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
18412: p_gt_id IN NUMBER,
18413: p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
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
18556: END;
18557:
18558:
18559: PROCEDURE application_with_interface
18560: (p_customer_trx IN ra_customer_trx%ROWTYPE,
18561: p_app_rec IN ar_receivable_applications%ROWTYPE,
18562: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
18563: p_gt_id IN NUMBER,
18564: p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
18772: min(nvl(ctl.AUTORULE_COMPLETE_FLAG,'Y')) AUTORULE_COMPLETE_FLAG,
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
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
19050:
19051: IF x_upg_method IS NULL THEN
19052: SELECT upgrade_method
19053: INTO x_upg_method
19054: FROM ra_customer_trx
19055: where customer_trx_id = p_customer_trx_id;
19056:
19057: IF PG_DEBUG = 'Y' THEN
19058: localdebug('x_upg_method from db :'||x_upg_method);
19061:
19062: IF x_upg_method IS NULL AND
19063: (nvl(arp_standard.sysparm.create_detailed_dist_flag,'Y') = 'N' ) THEN
19064:
19065: update ra_customer_trx
19066: set upgrade_method = 'R12_MERGE'
19067: where customer_trx_id = p_customer_trx_id;
19068:
19069: x_upg_method := 'R12_MERGE';